I need an explanation for this Excel question to help me study.
PROJECT STEPS
- 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.
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 2000. He has a text file that already contains this data.
Import the data from a CSV file into a new table as follows:- Import data from the Support_EX19_10a_Mac_Summary.csv text file.
- Start loading the data at row 1, and separate the columns using comma delimiters.
- Only edit the data to skip the Units Sold and Notes columns, and load the data to a table in cell A2 of the existing worksheet.
- Format the imported data as a table with headers using the Table Style Medium 7 table style (Hint: If an alert comes up, press “Yes” to convert the range to a table. Depending on your version of Office, the table style may be written as Green, Table Style Medium 7.)
- The Previous Year worksheet lists the sales per month for the previous year in a table and compares the sales in a chart. Jacob previously imported this data, and he wants to track the changes in monthly sales and project the first six months of this year’s monthly sales.
Go to the Sheet4 worksheet. Create a chart to track the changes and project a forecast as follows:- Rename the Sheet4 worksheet using Six Month Forecast as the name.
- Copy the formula from the range C14:E14 to the range C15:E20.
- Based on the data in the range A1:E20, create a Line chart.
- Remove the chart title from the line chart, and set the number format of the vertical axis to Currencywith 0 decimal places and $ as the symbol, if necessary.
- Go to the Stores worksheet. Jacob wants to display information about lighting products purchased according to store and manufacturer. He has received this data in a text file. Import the data as follows:
- Import data from the Support_EX19_10a_Mac_LD.txt file to a new worksheet in the NP_EX19_10a_Mac_FirstLastName_2.xlsx workbook.
- Start loading the data at row 1, and separate the columns using tab delimiters.
- Load the data to a new worksheet, using Product Data as the name of the new worksheet.
- Format the imported data as a table with headers using the Table Style Medium 7 table style. (Hint: If an alert comes up, press “Yes” to convert the range to a table. Depending on your version of Office, the table style may be written as Green, Table Style Medium 7.)
- Rename the table using Products as the name.
Go back to the Stores worksheet. Jacob wants to show the categories of products sold in each of the company’s five stores during 2021.
- Edit the PivotTable as follows to provide this information for Jacob:
- Use the following fields from the Products table in the PivotTable:
Category field for the row headings
StoreCity field for the column headings
ItemQty field for the values - Use Products Sold as the custom name of the Sum of ItemQty field.
- In cell A3, use Lighting Products to replace “Row Labels”, and then resize column A to its best fit.
- Use the following fields from the Products table in the PivotTable:
e.Use AutoFit to change the width of columns A through C, if necessary.
e.Resize and move the chart so that the upper-left corner is within cell C2 and the lower-right corner is within cell E13.
f.In cell A2 of the Stores worksheet, insert a PivotTable based on the data in the Products table.
d.In cell B2, use Stores to replace “Column Labels”.
