PurchaseID
14
19
28
10
9
29
18
22
27
25
30
4
21
32
17
13
2
37
16
11
34
39
15
33
31
20
38
35
1
23
36
3
5
12
26
24
8
6
7
PurchaseDate
1/13/2016
1/14/2016
1/21/2016
1/30/2016
2/9/2016
2/13/2016
2/14/2016
2/15/2016
2/19/2016
3/8/2016
3/12/2016
4/16/2016
5/4/2016
5/8/2016
5/28/2016
5/29/2016
6/2/2016
6/2/2016
6/19/2016
6/20/2016
6/21/2016
7/28/2016
7/29/2016
8/9/2016
8/10/2016
8/13/2016
8/22/2016
8/31/2016
9/8/2016
9/12/2016
9/24/2016
9/25/2016
10/17/2016
10/23/2016
10/23/2016
11/3/2016
11/21/2016
11/24/2016
12/11/2016
CustomerID
28
12
8
7
49
34
7
34
49
43
31
46
1
35
39
43
42
47
11
33
15
44
32
4
4
46
21
25
38
7
2
30
38
39
3
21
2
32
4
EmployeeID
EMP0012
EMP0114
EMP0254
EMP0105
EMP0049
EMP4120
EMP0650
EMP0254
EMP0049
EMP0114
EMP0105
EMP0049
EMP0045
EMP0012
EMP0105
EMP0065
EMP0049
EMP0254
EMP0045
EMP0049
EMP0080
EMP0114
EMP0650
EMP0114
EMP0105
EMP0105
EMP0012
EMP4120
EMP0080
EMP0105
EMP0254
EMP0012
EMP0650
EMP0080
EMP0049
EMP4120
EMP0105
EMP0065
EMP0045
20200417224411forman_e07ch13_grader_h1_metrics_2_
Category
Toys
Clothing
Health and Beauty
Food
Toys
Clothing
Health and Beauty
Clothing
Food
Food
Clothing
Clothing
Toys
Health and Beauty
Health and Beauty
Toys
Health and Beauty
Health and Beauty
Health and Beauty
Food
Clothing
Clothing
Food
Clothing
Toys
Toys
Food
Food
Clothing
Food
Health and Beauty
Health and Beauty
Toys
Food
Clothing
Toys
Health and Beauty
Health and Beauty
Clothing
Price
$15,99
$24,99
$15,99
$15,99
$24,99
$10,99
$8,50
$24,99
$13,00
$7,99
$15,99
$13,00
$7,99
$24,99
$12,00
$24,99
$24,99
$65,00
$9,00
$12,00
$24,99
$24,99
$24,99
$7,99
$9,00
$15,99
$8,50
$9,00
$7,99
$15,99
$7,99
$8,50
$9,00
$10,99
$10,99
$65,00
$15,99
$15,99
$24,99
Quantity
2
9
4
8
6
8
4
9
3
6
9
3
8
8
1
7
3
2
3
1
3
3
8
7
5
2
5
9
6
6
3
4
3
6
8
7
9
3
3
Month
Jan
Jan
Jan
Jan
Feb
Feb
Feb
Feb
Feb
Mar
Mar
Apr
May
May
May
May
Jun
Jun
Jun
Jun
Jun
Jul
Jul
Aug
Aug
Aug
Aug
Aug
Sep
Sep
Sep
Sep
Oct
Oct
Oct
Nov
Nov
Nov
Dec
Year
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
2016
State
IN
OH
MI
IL
EmployeeID
EMP0012
EMP0045
EMP0080
EMP0049
EMP0254
EMP0065
EMP0105
EMP0114
EMP0650
EMP4120
20200417224411forman_e07ch13_grader_h1_metrics_2_
Orders
145
12
66
120
LastName
Mcconnell
Rojas
Mccall
Kirk
Kidd
Brock
English
Ellis
Foley
Adams
Volume
19
14
15
24
18
10
40
25
15
24
Create Date By Whom
mm/dd/yyyy Firstname Lastname
Mod. Date
By Whom
Description
Create Date
Creator
Sheet Name
Mod. Description
Workbook Name
e07ch13Metrics.xlsx
Last Version Backup Name
Purpose
Grader - Instructions
Excel 2016 Project
YO_Excel16_Ch13_PS1_v3
Project Description:
Tammy Milligan owns a chain of small boutique stores in OH, MI, IL, and IN. She is a big believer in the benefits of business
intelligence for measuring progress and making strategic decisions. She has provided you with some sample sales data and
would like for you to create a dashboard that will give her an overview of how her business is doing. In this exercise, you will
build a data model based on the data provided and create a dashboard, complete with KPIs and a map showing which states
are generating the most orders.
Steps to Perform:
Step
Points
Possible
Instructions
1
Start Excel. Open the file named e07ch13_grader_h1_Metrics.xlsx. Save the file with the
name e07ch13_grader_h1_Metrics_LastFirst, replacing LastFirst with your Last and First
name.
0
2
On the SalesData worksheet tab, add each of the tables to the data model.
10
3
Edit the data model by completing the following tasks on the SalesData2016 worksheet:
6
Format the PurchaseDate field as *3/14/2001
Format the Price field as Currency
4
Create a calculated column to the right of Year with the name Revenue. Use the formula
=[Price]*[Quantity] to calculate the revenue generated from each sale. Format the column as
Currency.
8
5
In Power Pivot, create a calculated field for sum of revenue in the Calculation Area just below
the Revenue column. Copy the calculated field and paste it as text into cell J41 of the
SalesData worksheet in the main workbook.
6
From the Power Pivot window, create a KPI that measures the Sum of Revenue value against
the absolute value of $500.00. Maintain the default thresholds of 200 and 400 and select the
first set of icon styles.
Create a relationship between SalesData2016 and VolumeByEmp using the EmployeeID field.
6
In the Power Pivot window, create a Chart and Table (Horizontal) combination on the
MetricsDashboard worksheet in cell B2. In the PivotChart, drag the LastName field from
VolumeByEmp to the Axis (Categories) area. Drag the Category field from SalesData2016 to
the Legend (Series) area. Drag the Quantity field from SalesData2016 to the Values area to
calculate the sum of quantity sold.
15
Change the chart type to a Stacked Column chart. Apply Style 9 to the PivotChart. Add a
Chart Title that reads Category Volume by Employee. Add a Primary Vertical Axis title that
reads Sales Volume and hide all field buttons.
Add a Timeline slicer connected to the PivotChart, click the PurchaseDate check box, and
position it below the chart. Apply the Light Green, TimelineStyle Light 6 to the slicer.
Created On: 07/05/2019
1
YO_Excel16_Ch13_PS1_v3
Grader - Instructions
Excel 2016 Project
Points
Possible
Step
Instructions
7
Name the PivotTable EmployeeRevGoals. Drag the LastName field from VolumeByEmp to
the Rows area. Drag the Sum of Revenue Value field from SalesData2016 to the Values area.
Drag the Sum of Revenue KPI Status field to the Values area.
22
Rename the Row Labels heading in J2 to Employees. Rename the Sum of Revenue heading
in K2 to 2016 Revenue. Rename the Sum of Revenue Status heading in L2 as $500 Goal
Status. If necessary, resize the column to fit the text. Apply the PivotTable Style, Light Green,
Pivot Style Medium 14 to the PivotTable.
8
From the Power Pivot window, create a PivotTable on the MetricsDashboard worksheet in cell
B25. Drag the State field from OrdersByState to the Rows area and the Orders field to the
Values area. Rename the Row Labels heading in B25 to State and the Sum of Orders
heading in C25 to Total Orders.
12
9
Apply Light Green, Pivot Style Medium 14 to the PivotTable and name it OrdersByState.
6
10
Insert the Bing Maps App for Office to the MetricsDashboard worksheet. Use the data in the
OrdersByState PivotTable to generate circles onto the map. Position the map within the cell
range E25:J39.
0
11
Prepare the dashboard for production by hiding the SalesData worksheet. Hide the column
and row headings as well as the gridlines on the MetricsDashboard worksheet.
9
12
Minimize the ribbon. Protect the worksheet, allowing only to Use PivotTable & PivotChart and
Edit objects.
6
13
Save the workbook, exit Excel, and then submit the file as directed by your instructor.
0
Total Points
Created On: 07/05/2019
2
100
YO_Excel16_Ch13_PS1_v3
Purchase answer to see full
attachment