New Perspectives Excel 365 | Module 10: SAM Project A Kirby & Ross
ANALYZE DATA WITH POWER TOOLS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_10A_FirstLastName_1.xlsx as NP_EX365_10A_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. To complete this Project, you will also need the following files:
Support_EX365_10A_Orders.accdb
Support_EX365_10A_Years.csv
3. With the file NP_EX365_10A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
4. To complete this project, you need to add the Power Pivot tab to the ribbon as follows:
a. 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. Darryn Ross is the vice president of Kirby & Ross, a chain of office furniture stores headquartered in Denver, Colorado. Darryn asks for your help in producing a sales report. He wants to analyze sales for the past year and project future sales for all the stores. 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 Darryn wants you to display a summary of the company's annual sales since the first store opened in 2008. He 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:
a. Create a new query that imports data from the Support_EX365_10A_Years.csv text file.
b. Transform the data to remove the Units Sold and Milestones columns.
c. Close and load the query data to a table in cell B3 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. Darryn imported this data from the Orders table in an Access database. He wants you to provide a way to track the changes in monthly sales and project the first six months of this year's monthly sales. Create a forecast sheet as follows to provide the data Darryn requests:
a. Based on the data in the range B3:C15, create a forecast sheet.
b. Use 6/30/2030 as the Forecast End date to forecast the next six months.
c. Use Six Month Forecast as the name of the new sheet.
d. 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 Store Analysis worksheet. Darryn asks you to display information about the types of products purchased in each store. He has been tracking this data in an Access database. Import the data from the Access database as follows:
a. Create a new query that imports data from the Support_EX365_10A_Orders.accdb database.
b. Select the 2029_Orders, Products, and Sales tables for the import.
c. Only create a connection to the data and add the data to the Data Model. On the Store Analysis worksheet, Darryn wants to show the categories of products sold in each of the company's five stores during 2029.
d. In cell B3 of the Store Analysis worksheet, use Power Pivot to insert a PivotTable based on the data in the 2029_Orders table.
4. Edit the PivotTable as follows to provide this information for Darryn:
a. Use the following fields from the 2029_Orders table in the PivotTable: · Category field for the row headings · StoreState field for the column headings · ItemQty field for the values
b. Use Products Sold as the custom name of the Sum of ItemQty field.
c. In cell B4, use Product Types to replace "Row Labels", and then resize column B to its best fit.
d. In cell C3, use Stores to replace "Column Labels".
5. Darryn occasionally likes to focus on the number of office furniture products sold in the five stores per month. Add a Timeline Slicer as follows to the Store Analysis worksheet:
a. Insert a Timeline Slicer that uses the OrderDate field from the 2029_Orders table.
b. Scroll the Timeline Slicer to display periods beginning in January.
c. Move and resize the Timeline Slicer so it covers the range B12:H20.
6. Darryn asks you to provide a way that he can examine the percentage each type of product contributed to total sales in each store. Create a PivotChart as follows:
a. Based on the PivotTable on the Store Analysis worksheet, create a 100% Stacked Column PivotChart.
b. Move and resize the PivotChart so that its upper-left corner is in cell I3 and its lower-right corner is in cell O20.
c. Hide all the field buttons in the PivotChart.
7. Darryn asks you to display the sales by state data in a Map chart. Copy the data in the non-adjacent range C4:G4 and C11:G11, and paste it beginning in cell B22. Create a Filled Map chart based on the range B22:F23. Move and resize the chart so the upper-left corner is within cell I21 and lower-right corner is within cell O33. Remove the chart title.
8. Go to the Product Analysis worksheet. Darryn wants you to compare products sold by category and manufacturer. This data is stored in the Sales and Products tables. Create a PivotTable as follows that provides the product comparison Darryn requests:
a. In cell B3, use Power Pivot to insert a PivotTable in the Product Analysis worksheet.
b. Use the following fields in the PivotTable: · Manufacturer field from the Products table for the row headings · Category field from the Products table for the column headings · ItemCount field from the Sales table for the values
9. 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 ItemID field.
10. Darryn asks you for another way to visualize the products sold by manufacturer. Create a PivotChart as follows:
a. Based on the PivotTable on the Product Analysis worksheet, create a Stacked Bar PivotChart.
b. Move and resize the PivotChart so that its upper-left corner is within cell B15 and its lower-right corner is within cell I27.
c. Hide all the field buttons in the PivotChart.
11. Darryn also wants to be able to focus on a single category of product at a time. Add a slicer to the PivotChart as follows:
a. Add a slicer based on the Category field from the Products table.
b. Move the slicer so that it covers the range J3:L14.
c. Use the slicer to filter the PivotTable and PivotChart to show only products in the Chairs, Desks, and Tables categories.
12. Fill the range G3:I14 with White, Background 1 to match the rest of the worksheet background.
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 website to submit your completed project.
Final Figure 1: Sales History Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Six Month Forecast Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Monthly Sales Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: Store Analysis Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Product Analysis Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.