Project Description: Tammy Milligan owns a chain of small boutique stores in OH, MI, IL, and IN. She is a big be over in the benefits of business inteligence 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: Instructions Points Step Possible Skip 2 On the SalesData worksheet lab, add each of the tables to the data model. 3 Edit the data model by completing the following tasks on the SalesData2016 worksheet B Format the PurchaseDate field as *3/14/2001 Format the Price field as Currency Create a calculated column to the right of Year with the name Revenue. Use the formula 8 =[Price]"[Quantity] to calculate the revenue generated from each sale. Format the column as Currency. 5 In Power Pivot, create a calculated field for sum of revenue in the Calculation Area just below 6 the Revenue column. Copy the calculated field and paste it as text into cell J41 of the SalesData worksheet in the main workbook. From the Power Pivol 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. In the Power Pivot window, create a Chart and Table (Horizontal) combination on the 15 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. 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 lille that reads Sales Volume and hide all field butions. 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. 7 Name the PivotTable EmployeeRevGoals, Drag the LastName field from VolumeByEmp to 22 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. Rename the Row Labels heading in J2 to Employees. Rename the Sum of Revenue heading in K2 t 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, Pivol Style Medium 14 to the PivotTable.Points Step Instructions Possible 8 From the Power Pivot window, create a PivotTable on the MetricsDashboard worksheet in cell 12 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. 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 0 OrdersByState PivotTable to generate circles onto the map. Position the map within the cell range E25:J39. 11 Prepare the dashboard for production by hiding the SalesData worksheet. Hide the column 9 and row headings as well as the gridlines on the MetricsDashboard worksheet. 12 Minimize the ribbon. Protect the worksheet, allowing only to Use PivotTable & PivotChart and 6 Edit objects.L M C D E F H Price Quantity Month Year State Orders PurchaselD _Purchase Date #|CustomerID #|EmployeelD ~|Category 2016 IN 145 14 1/13/16 28 EM POO12 Toys $15.99 Jan 2016 OH 12 1/14/16 12 EMPO114 Clothing $24.99 Jan 19 MI 66 1/21/16 EMPO254 Health and Beauty $15.99 Jan 2016 28 Jan 2016 IL 120 10 1/30/16 7 EMPO105 Food $15.99 2/9/16 49 EMPO049 Toys $24.99 Feb 2016 9 Feb 2/13/16 EMP4120 Clothing 2016 29 34 $10.99 Feb 2016 EmployeeID - LastName Volume $850 18 2/14/16 7 EMPO650 Health and Beauty Clothing $24.99 Feb 2016 EM POO12 Mcconnell 19 22 2/15/16 34 EMPO254 EM POO45 Rojas 14 2/19/16 49 EMPO049 Food $13.00 Feb 2016 10 27 $7.99 Mar 2016 EM POO80 Mccall 15 11 25 3/8/16 EMPO114 Food 24 2016 EMPO049 31 EMPO105 Clothing $15.99 Mar Kirk 12 30 3/12/16 EMPO254 Kidd 18 4/16/16 46 EMPO049 Clothing $13.00 Apr 2016 13 10 EM POO45 Toys $7.99 May 2016 EM POO65 Brack 14 21 5/4/16 $24.99 May 2016 EMPO105 English 40 32 5/3/16 35 EM POO12 Health and Beauty 15 25 39 EMPO105 Health and Beauty $12.00 May 2016 EMPO114 Ellis 16 17 5/28/16 $24.99 May 2016 EMPO650 Falley 15 17 13 5/29/16 43 EMPO065 Toys 24 Health and Beauty $24.99 Jun 2016 EMP4120 Adams 18 2 6/2/16 42 EMPO049 $65 00 Jun 2016 19 37 6/2/16 47 EMPO254 Health and Beauty 11 EMPO045 Health and Beauty $9.00 Jun 2016 20 16 6/19/16 Food $12 00 Jun 2016 21 11 6/20/16 33 EMPO049 6/21/16 15 EMPOOBO Clothing $24.99 Jun 2016 22 34 EMPO114 Clothing $24.99 Jul 2016 23 39 7/28/16 44 Food $24.99 Jul 2016 24 15 7/29/16 32 EMPO650 Clothing $7.99 AUE 2016 25 33 8/9/16 EMPO114 2016 26 31 8/10/16 EMPO105 Tay's $9 00 AUE Toys $15.99 AUG 2016 27 20 3/13/16 46 EMPO105 $8 50 Aug 2016 28 38 8/22/16 21 EMPO012 Food 35 3/31/16 EMP4120 Food $9 00 AUE 2016 29 25 1 9/3/16 38 EMPOOBO Clothing $7.99 Sep 2016 30 9/12/16 7 EMPO105 Food $15.99 Sep 2016 31 23 2016 32 36 9/24/16 2 EMPO254 Health and Beauty $7.99 Sep 2016 33 3 9/25/16 30 EMPO012 Health and Beauty $8.50 Sep Toys $9.00 Oct 2016 34 5 10/17/16 38 EMPO650 Food $10.99 Oct 2016 35 12 10/23/16 39 EMPO080 $10.99 Oct 2016 36 26 10/23/16 3 EMPO049 Clothing EMP4120 Tay's $65 00 Nav 2016 37 24 11/3/16 21 11/21/16 2 EMPO105 Health and Beauty $15.99 Nav 2016 38 11/24/16 Health and Beauty $15.99 Nav 2016 39 32 EM POO65 Clothing $24.99 Dec 2016 40 12/11/16 EMPO045 41 SalesData MetricsDashboard Documentation +\fA B C D E F Create Date By Whom Description Workbook Name mm/dd/yyyy |Firstname Lastname e07ch 13Metrics.xIsx Mod. Date By Whom Mod. Description Last Version Backup Name 10 Create Date Sheet Name Creator Purpose 22 23 24 25 26 27 28 29 30 31 SalesData MetricsDashboard Documentation +