New Perspectives Excel 365/2021 Module 10 SAM Project 1b

To Get this Solution Contact us on Email and WhatsApp is given in Video
WhatsApp no : +923146535108
Email address: solution000777@gmail.com

New Perspectives Excel 365/2021 | Module 10: SAM Project 1b

Excel Module 10 SAM Project 1b

New Perspectives Excel 365/2021 SAM Project 1b (Corvallis Group Restore)

Module 10: SAM Project 1b Corvallis Group Restore Excel

NP_EX365_2021_10b Instructions / Answers
SAM Project 1b
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 2011. 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:
a. Create a new query that imports data from the Support_EX365_2021_10b_History.csv text file.
b. Edit the query to remove the Items Sold and Notes columns.
c. 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:
a. Based on the data in the range B2:C14, create a forecast sheet.
b. Use 6/30/2025 as the Forecast End date to forecast the next six months.
c. Use Jan to June 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.

Module 10

NP_EX365_2021_10b

Corvallis Group Restore
Excel 365/2021
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:
a. Create a new query that imports data from the Support_EX365_2021_10b_Restore.accdb database.
b. Select the 2024_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 Material worksheet, Lucia wants to show the material of the products sold in each of the company's five locations during 2024.
d. In cell B2 of the Material 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 Lucia:
a. Use the following fields from the 2024_Orders table in the PivotTable:
· Material field for the row headings
· SiteCity 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 B3, use Product Material to replace "Row Labels", and then resize column B to its best fit.
d. In cell C2, use Locations to replace "Column Labels".

Analyze Data with Power Tools

Power Query

Import Data CSV

Format Table

Pivot Tables

Sales Analysis
Excel SAM Project 1b solution

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:
a. Insert a Timeline Slicer that uses the OrderDate field from the 2024_Orders table.
b. Move and resize the Timeline Slicer so that it covers the range B12:H19.
c. 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:
a. Based on the PivotTable on the Material worksheet, create a 100% Stacked Column PivotChart.
b. 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:

Project 1b Excel help

Module 10 SAM answers

Step by step Excel project 1b

Video tutorial Excel SAM Project 1b
#Excel365

#Excel2021

#NewPerspectivesExcel

#SAMProject1b

#Module10

#PowerQuery

#DataAnalysis

#ExcelHelp

#CorvallisGroupRestore

#ExcelProject