New Perspectives Excel 2016 | Module 11: SAM Project 1b JM Office Supply DATA ANALYSIS WITH BUSINESS INTELLIGENCE GETTING STARTED ? Open the file NP_EX16_11b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought4

Share With

New Perspectives Excel 2016 | Module 11: SAM Project 1b

JM Office Supply

DATA ANALYSIS WITH BUSINESS INTELLIGENCE

GETTING STARTED

?

Open the file

NP_EX16_11b_

FirstLastName

_1.xlsx

, available for download

from the SAM website.

?

Save the file as

NP_EX16_11b_

FirstLastName

_2.xlsx

by changing the “1” to

a “2”.

o

If you do not see the

.xlsx

file extension

in the Save As dialog box, do not

type it. The program 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_EX16_11b_Sales.csv

o

Support_NP_EX16_11b_Two_Years.csv

o

Support_NP_EX16_11b_Office.accdb

?

With the file

NP_EX16_11b_

FirstLastName

_2.xlsx

still open, ensure that your

first and last name is displayed in cell B6 of the Documentation sheet.

o

If cell B6 does not display your name, delete the file and download a new

copy from the SAM website.

?

To complete this project, you need to add the Power Pivot tab to the ribbon

as

follows:

o

From the File tab, click the Options button. In the Data section of the

Advanced tab, click the check box next to Enable Data Analysis add-ins:

Power Pivot, Power View, and Power Map, and click OK.

*Power Pivot, Power View, and Power Map are not available on Microsoft

Office Home and Business 2016 and Microsoft Office 365 Business.

PROJECT STEPS

1.

As a part-time manager at a JM Office Supply store in Baltimore, Maryland, you

are working with Michael Herrera to produce a sales report. Four other cities

have JM Office Supply stores, and Michael wants to analyze sales for the past

two years and project future sales for all the stores. To create the report, you

need to import data from various sources and use the Excel Business

Intelligence (BI) tools.

Switch to the

Sales History

worksheet.

Use Power Query to create a query and

load data from a CSV file into a new table as follows:

a.

Create a new query that imports data from the

Support_NP_EX16_11b_Sales.csv

file, available for download from the

SAM website.

b.

Edit the query to remove the Units Sold and Notes columns, then close

and load the query data to a table in cell A2 of the existing worksheet.

(

Hint

: Use the Close & Load to... button.)

2.

Insert a chart to identify trends in the sales history data as follows:

a.

Insert a

Scatter with Smooth Lines and Markers

chart based on the

values in the range B2:C24.

b.

Move and resize the chart so that the upper left corner is within cell D2

and the lower right corner is within cell J19.

c.

Add the axis title

Sales ($mil)

to the vertical axis.

d.

Use

Sales Trend

as the chart title.

e.

Add a

Logarithmic

trendline to the chart.

3.

Switch to the

Current Sales

worksheet.

Use Power Query as follows to create a

query that loads daily sales data from a CSV file and transforms the data to

display only monthly totals:

a.

Beginning in cell A2, create a new query that imports data from the

Support_NP_EX16_11b_Two_Years.csv

file, available for download from

the SAM website.

b.

Remove all columns except the OrderDate and Sales columns.

c.

Based on the OrderDate column, add a new

Date

column to the query

using the

End of Month

option. (

Hint

: Select the OrderDate column

before selecting options on the Add Column tab.)

d.

Use

Month

as the name of the new column.

To

display

the total sales for each month, group the dates in the Month column

and create a new column displaying the sum of the Sales values as follows:

e.

Transform the data in the Month column by grouping the data by month.

f.

Use

Sales per Month

as the name of the new column.

g.

Use

Sum

as the operation.

h.

Use

Sales

as the column to sum within the Month group.

i.

Load the query data to a table in the existing worksheet beginning in cell

A2.

j.

Format the data in the range B3:B26 using the

Currency

number format

with

2

decimal places and the

$

symbol.

k.

If an extra blank row appears at the end of the new table, remove it.

4.

Create a forecast sheet to track the changes in monthly sales and project next

year’s monthly sales as follows:

a.

Based on the data in the range A2:B26, create a forecast sheet.

b.

Using the Options menu in the Create Forecast Worksheet dialog box,

manually set the seasonality to

12

.

c.

Use

12/31/2020

as the Forecast End date. (

Hint

: After clicking the Create

button, click the Got it! button if the FORECAST SHEET dialog box opens.)

 

d.

Use

Monthly Sales Forecast

as the name of the new sheet.

e.

Resize and move the forecast chart so that the upper left corner is within

cell C2 and the lower right corner is within cell E25.

5.

Switch to the

Products Sold by Store

worksheet. Create a query and load data

from an Access database to display information about products purchased from

JM Office Supply stores as follows:

a.

Create a new query that imports data from the

Support_NP_EX16_11b_Office.accdb

Access database, available for

download from the SAM website.

b.

Preview the 2018 and 2019 Orders table, and then only create a

connection to the data. (

Hint

: Use the Load To... button arrow to open the

Load To... dialog box.)

c.

Add the data to the Data Model.

Create a PivotTable that shows the products sold by category in each of the five

stores during 2018 and 2019 as follows:

d.

In cell A2, insert a PivotTable using the workbook’s Data Model.

e.

Use the following fields from the 2018 and 2019 Orders table in the

PivotTable areas:

o

ItemQty field: Values box

o

Category field: Rows box

o

StoreCity field: Columns box

6.

Format the PivotTable to make the data easier to interpret as follows:

a.

Use

Items Sold

as the custom name of the Sum of ItemQty field.

b.

Format the Sum of ItemQty values using the

Number

format with

0

decimal places and a

1000 separator

.

7.

Insert a Timeline Slicer to show items sold in March, 2019 as follows:

a.

Insert a Timeline Slicer that uses the OrderDate field from the 2018 and

2019 Orders table.

b.

Move and resize the Timeline Slicer so that the upper left corner is within

cell A12 and the lower right corner is within cell G21.

c.

Use the Timeline Slicer to display items sold only in March, 2019.

8.

Switch to the

Products Sold by Date

worksheet. Create a PivotTable as follows

that shows each category of product sold by date:

a.

In cell A2, insert a PivotTable using the workbook’s Data Model.

b.

Use the following fields from the 2018 and 2019 Orders table in the

PivotTable areas:

o

OrderDate field: Rows box

o

ItemQty field: Values box

o

Category field: Columns box

 

9.

Create hierarchies for the order dates and product categories as follows so you

can easily find information about a specific product sale on any date:

a.

Use the Manage option on the Power Pivot tab to display the Data Model in

Diagram View.

b.

Create a new hierarchy and use

Date

as the hierarchy name.

c.

Add the OrderDate(Year), OrderDate(Quarter), OrderDate(Month) and

OrderDate fields in that order to the new Date hierarchy. (

Hint

: Do not

select the OrderDate(Month Index) field.)

d.

Create another new hierarchy and use

Product

as the hierarchy name.

e.

Add the Category and Description fields in that order to the new Product

hierarchy, and close the Power Pivot window.

Revise the PivotTable to use the hierarchies you created as follows:

f.

Remove the fields from the Columns and Rows boxes.

g.

Use the following hierarchies from the 2018 and 2019 Orders table in the

PivotTable areas:

o

Date hierarchy: Rows box

o

Product hierarchy: Columns box

h.

Leave the Sum of ItemQty field in the Values box.

10.

Format the new PivotTable to match the one on the

Products Sold by Store

worksheet as follows:

a.

Use

Items Sold

as the custom name of the Sum of ItemQty field.

b.

Format the Sum of ItemQty values using the

Number

format with

0

decimal places and a

1000 separator

.

11.

Use

Drill Down

to display the daily sales for March, 2019.

Your workbook should look like the Final Figures on the following pages. Save your

changes, close the workbook, and then exit Excel. Follow the directions on the SAM

website to submit your completed project.

Final Figure 1: Sales History Worksheet

Final Figure 2: Monthly Sales Forecast Worksheet

Final Figure 3: Current Sales Worksheet

Final Figure 4: Products Sold by Store Worksheet

Final Figure 5: Products Sold by Date Worksheet