Homework answers / question archive / New Perspectives Excel 2013 Tutorial 11: SAM Project 1a 1

New Perspectives Excel 2013 Tutorial 11: SAM Project 1a 1

MS Excel

Share With

---

New Perspectives Excel 2013 Tutorial 11: SAM Project 1a

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 column of 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.txt

into 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 in the Assignment file).

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 places to range

C5:G8.

4. Insert a High-Low-Close chart into the Overview worksheet, based on

range P3:S286. (Hint: In most cases, selecting cell P4 prior 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 Range properties 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 Earnings worksheet. 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 Ascending order 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 in the Assignment file.

7. Modify the properties of the connection (created in step 6) in the FY18

Earnings worksheet 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 SafeClean FY18 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 Earnings worksheet and update the worksheet

as follows:

a. If necessary, move the new worksheet so that it appears between the

FY18 Earnings worksheet and the FY18 Orders worksheet.

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 Earnings worksheet) 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 SafeClean FY18 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 SafeClean FY18 Q1

Commissions.

c. Change the connection description to SafeClean Products FY18 Q1

Commissions imported from the SafeClean database. (include the

period).

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 Employees table, add the EmployeeFirstName column to the

query. Move the EmployeeFirstName column up in the query, so that it

appears between the CustomerName and EmployeeLastName columns.

c. From the Orders table, add the Commission column to the query (Hint:

The columns in your query should be in the following order:

CustomerName, EmployeeFirstName, EmployeeLastName, Product,

Amount, and Commission).

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 ranges E5:E9 and 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

default 2 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 in the Assignment file.

11. 

Go to the FY18 Orders 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 PivotTable

report. (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 field to 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 area of 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 in the Assignment file.

12. 

Go to the Order Details worksheet. Import data from the

support_NP_E13_T11_P1a_SCOrders.html support file as follows:

a. Get the external data from the

support_NP_E13_T11_P1a_SCOrders.html support 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.html support 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 in the Assignment file) so that only the SafeClean table is

highlighted.

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 Details worksheet. 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 SafeClean Order Status.

g. Confirm your worksheet matches Figure 8 in the Assignment file.

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.xml file

when you add the .xml map to the workbook.)

a. In the XML Source pane, add the Employees data map from the

support_NP_E13_T11_P1a_Employees.xml support 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.

d. Select cell B5 and only refresh the data associated with the XML data

source (Hint: The Refresh option is available in a drop-down menu

accessed by clicking on the Refresh All text. 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.