Chat with us, powered by LiveChat As the new accounting assistant at Kelly Computer Services, you have been asked to expand upon the content of an existing spreadsheet that tracks hardware sales over a three-month - Writingforyou

As the new accounting assistant at Kelly Computer Services, you have been asked to expand upon the content of an existing spreadsheet that tracks hardware sales over a three-month

Start Excel. Download and open the file named Excel_BU03_Assessment2_KellyComputers.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

Grader – Instructions Excel 2022 Project

YO22_Excel_BU03_Assessment2_Kelly_Computers

Project Description:

As the new accounting assistant at Kelly Computer Services, you have been asked to expand upon the content of an existing spreadsheet that tracks hardware sales over a three-month time period. The owner wants a monthly snapshot report as part of the spreadsheet, which will help track sales for future orders.

Steps to Perform:

Step

Instructions

Points Possible

1

Start Excel. Download and open the file named Excel_BU03_Assessment2_KellyComputers.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.

0

2

On the SalesData worksheet in cell B4, use an XLOOKUP function to retrieve the product name for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. If the value is not found, display a blank ( ""). Use the AutoFill handle to copy the function down to cell B18. Use the AutoFill Options to Fill Without Formatting.

5.6

3

In cell C4, use a VLOOKUP function to retrieve the Unit Price for the ProductID in cell A4, using the table of data about the products at Kelly Computers on the Data worksheet. Copy the formula to cells C5, C7:C9, C11:C12, C14:C15, C17:C18.

4.8

4

On the SalesData worksheet, in cell G4, create a formula that will perform a calculation that multiplies the Unit Price by the sum of products sold for January, February, and March. If a product has not sold any units over the past three months, display Discontinue. If the project has sold units, display the results of the Unit Price multiplied by the sum of the products sold for January, February, and March. Copy the formula, and then paste it in cells G5, G7:G9, G11:G12, G14:G15, and G17:G18. Resize the column as needed.

4.8

5

In cells D6:G6, D10:G10, D13:G13, D16:G16, and D19:G19, use a function to calculate the total product units and revenue for January through March.

5.6

6

Your supervisor has requested an area on the spreadsheet that quickly summarizes sales over the past few months. In cell D25, calculate the sum for the Hard Drive category that totals the three months results in D6:F6. Use a similar formula in cells D26:D29 substituting the correct formula cell references for each product.

4.8

7

In cell E25, create an IF function that will determine if sales goals have been met for each product listed. Return Goal Met if true, otherwise return Not Met. Copy the formula down to cell E29.

4.8

8

In cell I8, enter the FILTER function to display the Monthly Snapshot data for those products that met their goal in Quarter 1.

4.8

9

Create a PivotTable on a new worksheet named PivotAnalysis that summarizes the items by the sales goals and actual sales of the items. Add the Totals by Item field to the Rows area, and then add the Goal and Actual fields to the Values area (in that order). Type Category in cell A3, type Total Goal in cell B3, and type Actual Sales in C3. Apply the Light Grey, Pivot Style Dark 1. Create a clustered column PivotChart. Move the chart so the upper left corner is in cell A15 and the lower right corner is in the G30. Add the chart title Sales Goals vs Actual Sales and apply the Style 4 Chart Style .

4.8

10

Save and close Excel_BU03_Assessment2_KellyComputers. Exit Excel. Submit the file as directed.

0

Total Points

40

Created On: 08/01/2023 1 YO22_Excel_BU03_Assessment2 – Kelly Computers 1.1

,

SalesData

Quarter 1 Sales
ProductID Product Unit Price January February March Total
PRD-00078 12 11 12
PRD-000587 7 6 8
Met Goal Last Month
PRD-00056 Product Type Goal Actual Outcome
PRD-000837 20 12 13
PRD-000173 18 20 19
PRD-000382 5 4 5
PRD-000663
PRD-000446 2 6 5
PRD-000660 13 11 12
PRD-000229 3 5 4
PRD-00065 2 3 5
Totals
Monthly Snapshot
Totals by Item Goal Actual Outcome
Hard Drives 50
Flash Drives 75
Monitors 52
Keyboard 55
RAM 12

&F

Data

ProductID Product Name Category Price
PRD-00078 1TB SSD Hard Drive Storage $ 169.99
PRD-000587 8TB HDD Hard Drive Storage $ 249.00
PRD-00056 2TB Flash Drive USB 3.0 Storage $ 47.99
PRD-000837 32GB Flash Drive USB 2.0 Storage $ 19.99
PRD-000173 128 GB Flash Drive USB 2.0 Storage $ 21.75
PRD-000382 27" Monitor LED Display $ 175.00
PRD-000663 32" Monitor LED QHD Display $ 280.00
PRD-000446 Keyboard Keyboard $ 20.00
PRD-000660 Ergo Keyboard Keyboard $ 35.00
PRD-000229 64GB RAM Memory $ 360.00
PRD-00065 128GB RAM Memory $ 650.00