New Perspectives Excel 365/2021 | Module 10: SAM Project 1a | Lighting Designs | NP_EX365_2021_10a


#NewPerspectivesExcel365/2021 #Module10 #Module10SAMProject1a #LightingDesigns #NP_EX365_2021_10a



Contact Me:
WhatsApp : +92 3115298219
Email : [email protected]

Direct WhatsApp Link:
https://tinyurl.com/4wjb4d63






#New Perspectives Excel 365/2021 | Module 10: SAM Project 1a
#New Perspectives Excel 365/2021
#Perspectives Excel 365/2021
#Excel 365/2021
#Module 10: SAM Project 1a
#Module 10
#SAM Project 1a
#Project 1a
#Module 10: SAM Project
#SAM Project
#Project
#Instructions_NP_EX365_2021_10a
#NP_EX365_2021_10a
#EX365_2021_10a
#Lighting Designs


PROJECT STEPS
1. Jacob Sherman is the director of sales for Lighting Designs, a store and website that caters to building contractors in five locations in the Seattle area. Jacob 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 Summary worksheet, where Jacob wants to display a summary of the company's annual sales since the first store opened in the year 2003. 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_2021_10a_Summary.csv text file.
b. Edit the query to remove the Units Sold and Notes columns.
c. Close and load the query data to a table in cell A2 of the existing worksheet.
2. Go to the Previous Year worksheet, which lists the sales per month for the previous year in a table and compares the sales in a chart. Jacob imported this data from the Orders table in an Access database. He wants 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 Jacob requests:
a. Based on the data in the range A2:B14, create a forecast sheet.
b. Use 6/30/2025 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 E12.
3. Go to the Stores worksheet. Jacob wants to display information about lighting products purchased according to store and manufacturer. 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_2021_10a_LD.accdb database.
b. Select the 2024_Orders, Products, and Purchases tables for the import.
c. Only create a connection to the data and add the data to the Data Model. On the Stores worksheet, Jacob wants to show the categories of products sold in each of the company's five stores during 2024.
d. In cell A2 of the Stores worksheet, use Power Pivot to insert a PivotTable based on the data in the 2024_Orders table.
4. Edit the PivotTable as follows to provide this information for Jacob:
a. Use the following fields from the 2024_Orders table in the PivotTable:
· Category field for the row headings
· StoreCity 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 A3, use Lighting Products to replace "Row Labels", and then resize column A to its best fit.
d. In cell B2, use Stores to replace "Column Labels".
5. Jacob occasionally would like to focus on the number of lighting products sold in the five stores per month.
Add a Timeline Slicer as follows to the Stores worksheet:
a. Insert a Timeline Slicer that uses the OrderDate field from the 2024_Orders table.
b. Move and resize the Timeline Slicer so it covers the range A12:G18.
c. Scroll the Timeline Slicer to display all periods.
6. Jacob also wants to examine the percentage each type of lighting product contributed to total sales in each store. Create a PivotChart as follows:
a. Based on the PivotTable on the Stores worksheet, create a 100% Stacked Column PivotChart.
b. Move and resize the PivotChart so that its upper-left corner is in cell H2 and its lower-right corner is in cell N16.