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.

1	Start Excel. Open the file named e07ws13_grader_a1.xlsx. Save the file with the name e07ws13_grader_a1_LastFirst, replacing LastFirst with your name.
2	On the SalesData worksheet tab, add each of the four tables to the data model.
	3. Edit the data model by completing the following tasks on the SalesData2013 worksheet: Format the PurchaseDate field as *3/14/2001 Format the Price field as Currency Create a relationship between the SalesData2013 data and the MonthOrder data using the Month field.
	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. Create a calculated field to the right of Revenue with the name Month Order. Use the formula =RELATED(MonthOrder[Order])&"-"&[Month] to generate a value that can be used for charts and slicers that allow the months to be sorted in their natural order. Copy the SalesData2013 worksheet from the PowerPivot window and paste it onto a new worksheet in the main workbook. Name the new worksheet SalesData2013. Resize the columns as necessary.
	5. Create a calculated field for sum of revenue in the Calculation Area just below the Revenue column. Copy the calculated field and paste it into cell J41 of the SalesData2013 worksheet in the main workbook. From the PowerPivot 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 SalesData2013 and VolumeByEmp using the EmployeeID field.
	6.From the PowerPivot window, create a PivotTable on the MetricsDashboard worksheet in cell A1. Add the LastName field from VolumeByEmp to the ROWS area. Add the Sum of Revenue and Sum of Revenue KPI Status fields from SalesData2013 to the VALUES area, in that order.
	7.Name the PivotTable EmployeeRevGoals. Rename the Row Labels heading in A1 to Employees. Rename the Sum of Revenue heading in B1 to 2013 Revenue.
	8.Rename the Sum of Revenue Status heading in C1 to $500 Goal Status. Apply the PivotTable Style Pivot Style Medium 14 to the PivotTable.
	9.From the PowerPivot window, create a PivotChart on the MetricsDashboard worksheet in cell E1. Add the LastName field from VolumeByEmp to the AXIS (CATEGORIES) area. Add the Category field from SalesData2013 to the LEGEND (SERIES) area. Add the Quantity field from SalesData2013 to the VALUES area to calculate the sum of quantity sold.
	10.Change the chart type to a Stacked Column chart. Apply Style 9 to the chart. Add the title Category Volume by Employee above the chart.
	11.Add the title Sales Volume to the vertical axis. Hide all the field buttons. Position the PivotChart within the range E1:L14.
	12.Add a Month Order Slicer connected to the PivotChart and position it to the right of the chart with a horizontal position of 9.2" and a vertical position of 0". Change the height of the slicer to 4" and the width to 1.4". Apply Slicer Style Light 6.
	13.From the PowerPivot window, create a PivotTable on the MetricsDashboard worksheet in cell A16. Add the State field from OrdersByState to the ROWS area and the Orders field to the VALUES area. Rename the Row Labels heading in A16 to State and the Sum of Orders heading in B16 to Total Orders.
	14.Apply Pivot Style Medium 14 to the PivotTable and name it OrdersByState.
	15.Insert the Bing Maps App for Office on the MetricsDashboard worksheet. Use the data in the OrdersByState PivotTable, range A17:B20, to generate circles on the map. Change the pin color to Pine Green. Position the map within the range C16:I30.
	16.Prepare the dashboard for production by hiding the SalesData and SalesData2013 worksheet. Hide the column and row headings as well as the gridlines on the MetricsDashboard worksheet.
	17.Minimize the ribbon. Protect the worksheet, allowing only to Use PivotTable & PivotChart and Edit objects.
	18.Save the workbook and close Excel. Submit the file as directed by your instructor.