Data Files needed for the Review Assignments: NP_EX_10-2.xlsx, Support_EX_10_Sales01.csv, Support_EX_10_Sales02.csv, Support_EX_10_Sales03.accdb Dmitry has another set of sales data for you to analyze. This time, you will look at the short- and long-term trends in sales from Cup and Platter's website. This data has been stored in a pair of CSV files. Dmitry has also compiled sample data on revenue generated from electric items, bakeware, and dinnerware. You will use that data to generate a revenue report and display the data on a map chart and 3D Maps tour. Complete the following: 1. Open the NP_EX_10-2.xisx workbook located in the Excel10 > Review folder included with your Data Files. Save the workbook as NP_EX_10_StoresReport in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. Dmitry wants to view how website sales have increased over the year. Create a query to retrieve data from the Support_EX_10.Sales01.csv file. Edit the data and use Power Query to remove the first four rows of the text file and use the titles in the fifth row as column headers. Load the three columns of data into an Excel table in cell B4 of the Website History worksheet. 4. Create a scatter chart of the Website Revenue column vs. the Website Year column. Move the chart to the Summary Report worksheet in the range B4:H16. Add a polynomial trendline of order 2 to the chart. (Use the Format Trendiline pane and choose the Polynomial option.) Forecast the trendline forward 2 periods (or years). 5. Dmitry wants to view monthly website sales from the past two years. Create a query to retrieve data from the Support_EX_10 Sales02.csv file. Load the data as an Excel table to cell B4 in the Recent Website Sales worksheet. Format the data in the range C5:C28 as currency with no decimal places. 6. Using the data in the range B4:C28 of the Recent Website Sales worksheet, create a Forecast sheet, forecasting website revenue through 12/31/2022. Assume a seasonality in the data of 12 periods (or months). Name the Forecast sheet as Forecasts. 7. Move the forecast chart to the Summary Report worksheet in the range B18:H29. Remove the chart legend 7. Move the forecast chart to the Summary Report worksheet in the range B18:H29. Remove the chart legend I. and add the chart title Two-Year Forecast. 8. Dmitry has a sampling of product sales from three Cup and Platter classes. Create a query that retrieves the Customers, Customer Orders, Items, Item Sales, and Stores tables from the Support_EX_10_Sales03.accdb Access database. Create a connection to the database file and add the tables in the Excel Data Model. Do not load any tables in an Excel table, PivotTable, or PivotChart. 9. Go to Power Pivot and create the following table relations between the five database tables: - Connect the Customer Orders and Customers tables through the Cust ID field. - Connect the Customer Orders and Stores tables through Store ID field - Connect the Customer Orders and Item Sales tables through the Order ID field. - Connect the ltem Sales and items tables through the ltem ID field. 10. Add a hierarchy to the ltems table named Item Tree and add the Class, Subclass, and Group fields to it in that order. 11. Close Power Pivot and return to the workbook. In the Items Pivot worksheet, in cell B4, insert a PivotTable from the Data Model. Rename the table as items Pivot. Add the Item Tree hierarchy from the ltems table to the Rows area box, add the Type field from the Stores table to Column area box, and add the Revenue field from the Item Sales table to the Values area box (displaying the revenue sum). 12. Create a clustered bar PivotChart from ths items Pivot PivotTable. Move the chart to the Summary Report worksheet in the range J11:P29. Remove the field buttons, primary horizontal axis, and gridlines from the chart. Move the chart legend to the bottom of the chart area. Add data labels to the chart. 13. Dmitry wants to examine revenue from coffee maker sales. Use the Quick Explore tool to drill down the bar chart categories through the Electrics class and the Coffee Makers subclass down to the group level, displaying total revenue of three coffee maker types. 14. Dmitry wants to view sales by state. In the State Revenue worksheet, add a PivotTable from the Data Model to cell B4. Name the PivotTable as State Revenue. Place the State field from the Customers table in the Rows area and the Revenue field from the Item Sales table in the Values area. In the PivotTable Options dialog box, on the Display tab, click the check boxes to show items with no data on the rows and columns. 15. Copy the data in the range B5:C55. Use Paste Link to paste references to the data values in the range E5:F55. Format the values in the range F5:F55 as currency. Enter State in cell E4 and enter Revenue in cell F4. 16. Create a Map chart of the data in the range E4:F55. Move the map to the Summary Report worksheet in the range R11:X29. Remove the chart title, add data labels to the chart, and then move the legend to the bottom of the chart area. 17. In the Summary Report worksheet, create a timeline using the Date field from the Customer Orders table. Place the timeline across the range J4:X10. Create connections between the timeline and the Items Pivot and State Revenue PivotTables. Use the timeline to filter the PivotTables to show data only from July 2021 through December 2021. 18. Dmitry wants a 3D Map presentation of this data. Insert a 3D Map. In scene 1, place the Postal Code field from the Customers table in the Location area, the Revenue field from the Items Sales table in the Height area, and the Store Name field from the Stores table in the Category area. Add map labels to the map. 19. Change the view of the globe to a location above Minnesota, looking down and to the east. 20. Create a second scene of this data positiothed above Cuba looking northwest. 21. Change the durations of Scene 1 and Scene 2 to 1 second and play the tour verifying that your viewpoint moves across the United States from northwest of Chicago around to south of Florida. 22. Return to the workbook, save the workbook, and then close it. Data Files needed for the Review Assignments: NP_EX_10-2.xlsx, Support_EX_10_Sales01.csv, Support_EX_10_Sales02.csv, Support_EX_10_Sales03.accdb Dmitry has another set of sales data for you to analyze. This time, you will look at the short- and long-term trends in sales from Cup and Platter's website. This data has been stored in a pair of CSV files. Dmitry has also compiled sample data on revenue generated from electric items, bakeware, and dinnerware. You will use that data to generate a revenue report and display the data on a map chart and 3D Maps tour. Complete the following: 1. Open the NP_EX_10-2.xisx workbook located in the Excel10 > Review folder included with your Data Files. Save the workbook as NP_EX_10_StoresReport in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. Dmitry wants to view how website sales have increased over the year. Create a query to retrieve data from the Support_EX_10.Sales01.csv file. Edit the data and use Power Query to remove the first four rows of the text file and use the titles in the fifth row as column headers. Load the three columns of data into an Excel table in cell B4 of the Website History worksheet. 4. Create a scatter chart of the Website Revenue column vs. the Website Year column. Move the chart to the Summary Report worksheet in the range B4:H16. Add a polynomial trendline of order 2 to the chart. (Use the Format Trendiline pane and choose the Polynomial option.) Forecast the trendline forward 2 periods (or years). 5. Dmitry wants to view monthly website sales from the past two years. Create a query to retrieve data from the Support_EX_10 Sales02.csv file. Load the data as an Excel table to cell B4 in the Recent Website Sales worksheet. Format the data in the range C5:C28 as currency with no decimal places. 6. Using the data in the range B4:C28 of the Recent Website Sales worksheet, create a Forecast sheet, forecasting website revenue through 12/31/2022. Assume a seasonality in the data of 12 periods (or months). Name the Forecast sheet as Forecasts. 7. Move the forecast chart to the Summary Report worksheet in the range B18:H29. Remove the chart legend 7. Move the forecast chart to the Summary Report worksheet in the range B18:H29. Remove the chart legend I. and add the chart title Two-Year Forecast. 8. Dmitry has a sampling of product sales from three Cup and Platter classes. Create a query that retrieves the Customers, Customer Orders, Items, Item Sales, and Stores tables from the Support_EX_10_Sales03.accdb Access database. Create a connection to the database file and add the tables in the Excel Data Model. Do not load any tables in an Excel table, PivotTable, or PivotChart. 9. Go to Power Pivot and create the following table relations between the five database tables: - Connect the Customer Orders and Customers tables through the Cust ID field. - Connect the Customer Orders and Stores tables through Store ID field - Connect the Customer Orders and Item Sales tables through the Order ID field. - Connect the ltem Sales and items tables through the ltem ID field. 10. Add a hierarchy to the ltems table named Item Tree and add the Class, Subclass, and Group fields to it in that order. 11. Close Power Pivot and return to the workbook. In the Items Pivot worksheet, in cell B4, insert a PivotTable from the Data Model. Rename the table as items Pivot. Add the Item Tree hierarchy from the ltems table to the Rows area box, add the Type field from the Stores table to Column area box, and add the Revenue field from the Item Sales table to the Values area box (displaying the revenue sum). 12. Create a clustered bar PivotChart from ths items Pivot PivotTable. Move the chart to the Summary Report worksheet in the range J11:P29. Remove the field buttons, primary horizontal axis, and gridlines from the chart. Move the chart legend to the bottom of the chart area. Add data labels to the chart. 13. Dmitry wants to examine revenue from coffee maker sales. Use the Quick Explore tool to drill down the bar chart categories through the Electrics class and the Coffee Makers subclass down to the group level, displaying total revenue of three coffee maker types. 14. Dmitry wants to view sales by state. In the State Revenue worksheet, add a PivotTable from the Data Model to cell B4. Name the PivotTable as State Revenue. Place the State field from the Customers table in the Rows area and the Revenue field from the Item Sales table in the Values area. In the PivotTable Options dialog box, on the Display tab, click the check boxes to show items with no data on the rows and columns. 15. Copy the data in the range B5:C55. Use Paste Link to paste references to the data values in the range E5:F55. Format the values in the range F5:F55 as currency. Enter State in cell E4 and enter Revenue in cell F4. 16. Create a Map chart of the data in the range E4:F55. Move the map to the Summary Report worksheet in the range R11:X29. Remove the chart title, add data labels to the chart, and then move the legend to the bottom of the chart area. 17. In the Summary Report worksheet, create a timeline using the Date field from the Customer Orders table. Place the timeline across the range J4:X10. Create connections between the timeline and the Items Pivot and State Revenue PivotTables. Use the timeline to filter the PivotTables to show data only from July 2021 through December 2021. 18. Dmitry wants a 3D Map presentation of this data. Insert a 3D Map. In scene 1, place the Postal Code field from the Customers table in the Location area, the Revenue field from the Items Sales table in the Height area, and the Store Name field from the Stores table in the Category area. Add map labels to the map. 19. Change the view of the globe to a location above Minnesota, looking down and to the east. 20. Create a second scene of this data positiothed above Cuba looking northwest. 21. Change the durations of Scene 1 and Scene 2 to 1 second and play the tour verifying that your viewpoint moves across the United States from northwest of Chicago around to south of Florida. 22. Return to the workbook, save the workbook, and then close it