New Perspectives Excel 2019 | Module 10: SAM Project 1b Corvallis Group Restore ANALYZE DATA WITH POWER TOOLS     GETTING STARTED Open the file NP_EX19_10b_FirstLastName_1

Subject:MS ExcelPrice:25.01 Bought8

Share With

New Perspectives Excel 2019 | Module 10: SAM Project 1b

Corvallis Group Restore

ANALYZE DATA WITH POWER TOOLS

• GETTING STARTED

• Open the file NP_EX19_10b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_10b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:
  • Support_EX19_10b_History.csv
  • Support_EX19_10b_Restore.accdb
• With the file NP_EX19_10b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
• To complete this project, you need to add the Power Pivot tab to the ribbon as follows:
  • From the File tab, click the Options button. In the Data Options section of the Data tab, click the checkbox next to Enable Data Analysis add-ins: Power Pivot, Power View and 3D Map, and click OK.

• PROJECT STEPS

1. Lucia Campos and three friends own a small business called Corvallis Group Restore, an upcycling business in Corvallis, Oregon, that sells useful and attractive products made from old or discarded materials. Lucia asks for your help in producing a sales report. She wants to analyze sales for the past year and project future sales. To create the report, you need to import data from various sources and use the Excel Power tools.
   Go to the Sales History worksheet, where Lucia wants to display a summary of the company's annual sales since the business started in the year 2008. She has a text file that already contains this data.
   Use Power Query to create a query and load data from a CSV file into a new table as follows:
   1. Create a new query that imports data from the Support_EX19_10b_History.csv text file.
   2. Edit the query to remove the Items Sold and Notes columns.
   3. Close and load the query data to a table in cell B2 of the existing worksheet.
2. Go to the Monthly Sales worksheet, which lists the sales per month for the previous year in a table and compares the sales in a chart. Lucia imported this data from the Orders table in an Access database. She wants to track the changes in monthly sales and project the first six months of this year's sales.
   Create a forecast sheet as follows to provide the data Lucia requests:
   1. Based on the data in the range B2:C14, create a forecast sheet.
   2. Use 6/30/2022 as the Forecast End date to forecast the next six months.
   3. Use Jan to June Forecast as the name of the new sheet.
   4. Resize and move the forecast chart so that the upper-left corner is within cell C2 and the lower-right corner is within cell E13.
3. Go to the Material worksheet. Lucia wants to display information about products sold according to location and material. She has been tracking this data in an Access database. Import the data from the Access database as follows:
   1. Create a new query that imports data from the Support_EX19_10b_Restore.accdb database.
   2. Select the 2021_Orders, Products, and Sales tables for the import.
   3. Only create a connection to the data and add the data to the Data Model.
      
      On the Material worksheet, Lucia wants to show the material of the products sold in each of the company's five locations during 2021.
   4. In cell B2 of the Material worksheet, use Power Pivot to insert a PivotTable based on the data in the 2021_Orders table.
4. Edit the PivotTable as follows to provide this information for Lucia:
   1. Use the following fields from the 2021_Orders table in the PivotTable:
      · Material field for the row headings
      · SiteCity field for the column headings
      · ItemQty field for the values
   2. Use Products Sold as the custom name of the Sum of ItemQty field.
   3. In cell B3, use Product Material to replace "Row Labels", and then resize column B to its best fit.
   4. In cell C2, use Locations to replace "Column Labels".
5. Lucia occasionally would like to focus on the number of products sold in the five locations per month.
   Add a Timeline Slicer as follows to the Material worksheet:
   1. Insert a Timeline Slicer that uses the OrderDate field from the 2021_Orders table.
   2. Move and resize the Timeline Slicer so that it covers the range B12:H19.
   3. Scroll the Timeline Slicer to display all periods.
6. Lucia also wants to examine the percentage each type of product contributed to total sales in each location. Create a PivotChart as follows:
   1. Based on the PivotTable on the Material worksheet, create a 100% Stacked Column PivotChart.
   2. Move and resize the PivotChart so that its upper-left corner is in cell I2 and its lower-right corner is in cell N17.
7. Go to the Product Types worksheet. Lucia wants to compare products sold by category and vendor. This data is stored in the Sales and Products tables. Create a PivotTable as follows that provides the products sold by manufacturer information for Lucia:
   1. In cell B2, use Power Pivot to insert a PivotTable in the Product Types worksheet.
   2. Use the following fields in the PivotTable:
      · Vendor field from the Products table for the row headings
      · Category field from the Products table for the column headings
      · ItemQty field from the Sales table for the values
8. In order to relate the data in the Products and Sales tables to make a proper comparison, use the Power Pivot window to create a relationship between the Sales and Products tables based on the ProductID field.
9. Lucia asks for another way to visualize the products sold by vendor. Create a PivotChart as follows:
   1. Based on the PivotTable on the Product Types worksheet, create a Stacked Bar PivotChart.
   2. Move and resize the PivotChart so that its upper-left corner is within cell B24 and its lower-right corner is within cell I38.
   3. Hide all the field buttons in the PivotChart.
10. Lucia also wants to be able to focus on a single category of product at a time.
    Add a slicer to the PivotChart as follows:
    1. Add a slicer based on the Category field from the Products table.
    2. Move the slicer below the PivotChart so that it covers the range J24:L37.
    3. Use the slicer to filter the PivotTable and PivotChart to show only products in the Housewares category.
11. Return to the Monthly Sales worksheet. In the range B17:C21, Lucia wants to display the number of items sold by material in 2021. She can retrieve this information from the PivotTable on the Material worksheet.
    Display the number of items sold by store as follows:
    1. In cell C17, insert a formula using the GETPIVOTDATA function to insert the number of fabric products sold from cell H4 on the Material worksheet.
    2. In cell C18, insert a formula using the GETPIVOTDATA function to insert the number of glass products sold from cell H5 on the Material worksheet.
    3. In cell C19, insert a formula using the GETPIVOTDATA function to insert the number of metal products sold from cell H6 on the Material worksheet.
    4. In cell C20, insert a formula using the GETPIVOTDATA function to insert the number of plastic products sold from cell H7 on the Material worksheet.
    5. In cell C21, insert a formula using the GETPIVOTDATA function to insert the number of wood products sold from cell H8 on the Material worksheet.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Sales History

Final Figure 2: Jan to June Forecast Worksheet

Final Figure 3: Monthly Sales Worksheet

Final Figure 4: Material Worksheet