Saginaw Valley State University Business Intelligence Strategic Decisions Excel Project

User Generated

Graqf30

Business Finance

Saginaw Valley State University

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.

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The soluti...


Anonymous
Very useful material for studying!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags