New PerspectivesExcel 2013
Tutorial11: SAM Project 1a
SafeClean Products
USING data from external files
| New PerspectivesExcel 2013 Tutorial11: SAM Project 1a SafeClean Products USING data from external files
Project Goal M Project Name Project Goal
|
PROJECT DESCRIPTION
Fiona Walsh works forSafeClean Products, a publically-traded cleaning products company in New York.The company maintains numerous files and databases to trackcustomer orders,sales commissions, employee data, and market data. Fiona has asked you to create a comprehensive workbook that combines all of the data into a single file. You will need to import andformat data from numerous types of data files, including Access databases, text files, and XML files.
GETTING STARTED
· Download the following file from the SAM website:
o NP_Excel2013_T11_P1a_FirstLastName_1.xlsx
· Open the file you just downloaded and save it with the name:
o NP_Excel2013_T11_P1a_FirstLastName_2.xlsx
o Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.
· To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
o support_NP_E13_T11_P1a_10Year_Stock.txt
o support_NP_E13_T11_P1a_Employees.xml
o support_NP_E13_T11_P1a_Employees.xsd
o support_NP_E13_T11_P1a_SafeClean.accdb
o support_NP_E13_T11_P1a_Sales.txt
o support_NP_E13_T11_P1a_SCOrders.html
o support_NP_E13_T11_P1a_Stock.txt
· With the file NP_Excel2013_T11_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
· If a security warning appears at the top of the Excel window, click the Enable Content button in the message bar to enable the macros contained in the file, see Figure 1.
Figure 1: Security Warning Message Bar
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
· To complete this project you will need to display the Developer tab. To add this tab to the Excel ribbon, click on the File tab to open Backstage view and then click the Options button. In the Excel Options dialog box, click on the Customize Ribbon option and click the Developer check box, see Figure 2. Click the OK button to close the Excel Options dialog box and confirm the Developer tab appears in the Excel Ribbon.
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
PROJECT STEPS
1. Go to the Overview worksheet. Import data from the text file support_NP_E13_T11_P1a_Sales.txt into the worksheet using the following parameters:
a. The data in the text file has headers and is delimited with a comma.
b. Apply the Text column data format to the first column and the General format to the remaining columns.
c. Do not import the second columnof data (with the column heading 2012).
d. Import the data into the Overview worksheet, starting in cell B4.
2. Import data from the text file support_NP_E13_T11_P1a_10Year_Stock.txtinto the Overview worksheet using the following parameters:
a. The data has fixed width columns with no headers.
b. Begin importing the data at the 6 row.
c. Place column breaks at the following marks on the ruler: 4, 9, and 14 (as shown in Figure 3).
Figure 3: Column Breaks for Support_NP_E13_T11_P1a_10Year_Stock.txt
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
d. Apply the Date column data format to the first column and the General format to the remaining columns.
e. Import the data into the existing worksheet (the Overview worksheet), starting in cell B12.
3. Format the Overview worksheet as follows:
a. Adjust the width of column B to 4.5 characters.
b. Change the width of columns C:G to 12 characters.
c. For range C4:G4, Center cell contents and apply Bold formatting.
d. Apply the Accounting number format with no decimal placesto range C5:G8.
4. Insert a High-Low-Close chartinto the Overview worksheet, based on range P3:S286. (Hint: In most cases, selecting cell P4prior to creating the chart will result in Excel automatically selecting the necessary data range for your chart.) Format the chart as described below:
a. Change the chart title to SafeClean Products - Stock Trends 2017.
b. Remove the chart legend.
c. For the vertical axis, modify the Minimum bounds to be 35 and the Major units to be 5.
d. Enter the text Stock Price as the Primary Vertical axis title.
e. Enter the text Dates as the Primary Horizontal axis title.
f. Resize and reposition the chart so the upper-left corner is in cell F10 and the lower-right corner is in cell O29.
5. Select cell P5 and edit the External Data Rangeproperties as follows (Hint: Remember to edit the External Data Range properties, not the Connection properties):
a. Rename the support_NP_E13_T11_P1a_Stock external data range to be SafeClean 2017 Stock Performance.
b. Modify the Refresh control property to Prompt for File name on refresh.
c. Modify the Data Formatting and Layout properties to Adjust column width.
6. Go to the FY18 Earningsworksheet. Import data from the support_NP_E13_T11_P1a_SafeClean.accdb database, using the Microsoft Query Wizard as follows(Hint: When importing this data into the workbook, use the From Other Sources button to access the Query Wizard):
a. In the Microsoft Query wizard, choose the MS Access Database* option as the data source, and then navigate to and select the support_NP_E13_T11_P1a_SafeClean.accdb database.
b. When adding columns to your query, select the CustomerName and Contact columns (in that order) from the Customer table.
c. Then select the EmployeeLastName column from the Employees table.
d. Then select the Product and Amount fields from the Orders table (Hint: The columns in your query should be in the following order: CustomerName, Contact, EmployeeLastName, Product, and Amount).
e. Do not filter the data.
f. Sort the data by the Product field in Ascendingorder and then by the Amount field in Ascending order.
g. Return the data to Microsoft Excel as a Table in the existing worksheet (the FY18 Earnings worksheet), starting in cell B4.
h. Rename the table FY18_Q1Earnings.
i. Change the number format of the range F5:F13 to the Accounting number format with 0 decimal places.
j. Confirm the table matches Figure 4.
Figure 4: FY18_Q1Earnings
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
7. Modify the properties of the connection (created in step 6) in the FY18 Earningsworksheet as follows:
a. In the Workbook Connections dialog box, select the connection created in step 6. The connection should be named Query from MS Access Database (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Earnings).
b. Change the name of the connection to SafeCleanFY18 Q1 Earnings.
c. Change the connection description to SafeClean Products FY18 Q1 Earnings imported from the SafeClean database. (include the period).
d. For the Refresh Control options in the Usage tab, uncheck all checkboxes associated with the Last Refreshed property.
8. Make a copy of the FY18 Earningsworksheet and update the worksheet as follows:
a. If necessary, move the new worksheet so that it appears between the FY18 Earningsworksheet and the FY18 Ordersworksheet.
b. Update the worksheet tab to read FY18 Commissions.
c. Update cell A1 to read SafeClean Products - Fiscal Year 2018 - Commissions.
d. Change the name of the table in the worksheet to FY18_Q1Commissions.
9. Modify the connection associated with the FY18 Commissions worksheet (which is a copy of the connection from the FY18 Earningsworksheet) as follows:
a. Open the Workbook Connections Dialog box and select the connection associated with the FY18 Commissions worksheet created in step 8. The connection should be named SafeCleanFY18 Q1 Earnings1 (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value FY18 Commissions).
b. Change the name of the connection to SafeCleanFY18 Q1 Commissions.
c. Change the connection description to SafeClean Products FY18 Q1 Commissions imported from the SafeClean database. (include the period).
d. Do not close the Connection Properties Dialog box.
10. With the SafeClean FY18 Q1 Commissions connection still open in the Connection Properties dialog box, select the Definition tab and edit the connection query as follows:
a. Remove the Contact column from the query.
b. From the Employeestable, add the EmployeeFirstName column to the query. Move the EmployeeFirstNamecolumn up in the query, so that it appears between the CustomerName and EmployeeLastName columns.
c. From the Orderstable, add the Commission columnto the query (Hint: The columns in your query should be in the following order: CustomerName, EmployeeFirstName, EmployeeLastName, Product, Amount, andCommission).
d. Add a filter to the query, so that only data with a Commission column value greater than 1250.0000 are displayed in the table.
e. Change the sort order of the query to sort only by the Commission column values in Ascending order.
f. Return the data to Microsoft Excel (Hint: You will need to close the Connections Properties Dialog box and the Workbook Connections Dialog box before returning to the FY18 Commissions worksheet).
g. If necessary, change the number format of the rangesE5:E9and G5:G9 to the Accounting number format with 0 decimal places. Be certain to confirm that the number format uses 0 decimal places, rather than the default2 decimal places. (Hint: Depending on how you complete this substep, the number format may appear as Custom instead of Accounting.)
h. Confirm the table matches Figure 5.
Figure 5: FY18_Q1Commissions
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
11. Go to the FY18Orders worksheet. Create a PivotTable report based on the tables in the support_NP_E13_T11_P1a_SafeClean.accdb database as described below:
a. From the support_NP_E13_T11_P1a_SafeClean.accdb database, import data from the Customers, Employees, and Orders table for the PivotTablereport. (Hint: Use the From Access option in the Get External Data section of the Data tab when importing the tables from the support file.)
b. Add the PivotTable report to cell B4 in the existing (FY18 Orders)worksheet.
c. From the Employees table, add the EmployeeLastName field to the Columns area of the PivotTable Fields Pane.
d. From the Orders table, drag the Product fieldto the Rows area of the PivotTable Fields Pane.
e. From the Customers table, drag the CustomerName field to the Rows area below the Product field.
f. From the Orders table, drag the Quantity field to the Values areaof the PivotTable Fields Pane (Hint: The field name will be updated to Sum of Quantity).
g. Change the width of column B to 33 characters.
h. Change the label in cell B4 to Products Sold by Sales Team.
i. Change the label in cell B5 to Product and Customer Name.
j. Change the label in cell C4 to Employee.
k. Confirm the PivotTable matches Figure 6.
Figure 6: FY18 Orders PivotTable
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
12. Go to the Order Detailsworksheet. Import data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file as follows:
a. Get the external data from the support_NP_E13_T11_P1a_SCOrders.htmlsupport file using the From Web option.
b. In the new Web Query dialog box, enter the path to the support_NP_E13_T11_P1a_SCOrders.htmlsupport file into the address box (Hint: The path is the location on your computer that the support file is stored).
c. In the New Query Dialog box, select the second arrow (as shown in Figure 7) so that only the SafeClean table is highlighted.
Figure 7: Portion of New Web Query Dialog Box
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
d. Import the data into cell B4 of the Order Details worksheet.
e. Open the Workbook Connections Dialog box and select the connection associated with the Order Detailsworksheet. The connection should be named Connection. (Hint: If necessary, use the Click here to see where the selected connections are used link to confirm the selected connection has a sheet value Order Details.)
f. Change the name of the connection to SafeCleanOrder Status.
g. Confirm your worksheet matches Figure 8.
Figure 8: Portion of New Web Query Dialog Box
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
13. Go to the Payroll worksheet and complete the following actions to import data from the support_NP_E13_T11_P1a_Employees.xml support file. (Hint: The support_NP_E13_T11_P1a_Employees.xsd file needs to be in the same directory as the support_NP_E13_T11_P1a_Employees.xmlfile when you add the .xml map to the workbook.)
a. In the XML Source pane, add the Employees data map from thesupport_NP_E13_T11_P1a_Employees.xmlsupport file to the workbook.
b. Rename the XML Map from dataroot_Map to Payroll Information.
c. Bind the XML element (under the Employees data map) to the Payroll worksheet as shown in Table 1.
Table 1: XML Mapping for the Payroll Worksheet
| Element | Cell |
| ID | B4 |
| Last Name | C4 |
| First Name | D4 |
| Title | E4 |
| Start Date | F4 |
| Status | G4 |
| Compensation Type | H4 |
| Total Compensation | I4 |
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
d. Select cell B5 and only refresh the data associated with the XML data source (Hint: The Refreshoption is available in a drop-down menu accessed by clicking on the Refresh Alltext. If you click on the Refresh All icon in the Connections section of the Data tab, Excel will not provide you with the option to only update the data associated with this XML data source.
Your workbook should look like the Final Figure on the following page. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.
Final Figure 1: Overview Worksheet
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
Final Figure 2: FY18 Earnings Worksheet
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
Final Figure 3: FY18 Commissions
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
Final Figure 4: FY18 Orders Worksheet
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
Final Figure 5:Order Details Worksheet
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
| Microsoft product screenshots used with permission from Microsoft Corporation. Copyright © 2014 Cengage Learning. All Rights Reserved. |
Final Figure 6: Payroll Worksheet
