excel 3
olaz
YO22_Excel_BU04_Assessment1_Order_Instructions.docxGrader - Instructions Excel 2022 Project
YO22_Excel_BU04_Assessment1_Order
Project Description:
At the Painted Paradise Golf Resort and Spa, the hotel places uniform orders each quarter. Management would like to see just how many items are purchased each quarter by each department so they can decide whether they need to stop selling items or sell them more frequently. You will help by summarizing the quarterly worksheets and consolidating the data. You will also share a copy with the assistant manager to update any necessary items.
Steps to Perform:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Download and open the file named Excel_BU04_Assessment1_Order.xlsx. Grader has automatically added your last name to the beginning of the file name. Save the file to a location where you are storing your files. |
0 |
|
2 |
Group the Q1 through Q4 worksheets, and then change the color of the Q1:Q4 worksheet tabs to Red. Ungroup the worksheets. |
3.2 |
|
3 |
On the Q1 worksheet, enter a function in cell H5 to calculate the total cost of the total number of items sold for each item type. Copy the formula through cell range H6:H19. On the Auto Fill Options dialog box, choose Fill Without Formatting. Format cell range H6:H20 with the Accounting Number Format. |
4 |
|
4 |
Group the Q1 through Q4 worksheets. On the Q1 worksheet, select cell range A4:H19, and fill Formats across the worksheets. |
5.6 |
|
5 |
With the worksheets still grouped, select Q1 worksheet. Select cell range H5:H19, and fill All across the worksheets Q1:Q4. |
4.8 |
|
6 |
With the worksheets still grouped, select Q1 worksheet. Select cell range A20:H20, and fill All across worksheets Q1:Q4. |
4.8 |
|
7 |
On the Year worksheet, enter a 3-D SUM function in cell C5 to calculate the total items sold in Q1:Q4. Using the Fill Handle, copy the formula through cell range C6:C19. On the Auto Fill Options, choose Fill Without Formatting. With the range still selected, copy the formula through cell range C5:G19. |
4.8 |
|
8 |
On the YearLinked worksheet, select cell A4 and create a linked consolidated summary based on position that links to the data to find the total number of items sold for each department each quarter. Reference cell range A4:H20 on the Q1, Q2, Q3, and Q4 worksheets. Use the labels in Top row and Left column. AutoFit columns A:I and then hide columns B:C. Select cell A4 type Item |
6.4 |
|
9 |
Delete the Prices worksheet. Note the effect of this change on the Q1:Q4 worksheets. |
2.4 |
|
10 |
Open the downloaded Excel workbook Excel_BU04_Assessment2_OrderCost.xlsx. In Excel_BU04_Assessment2_Order_LastFirst, on the Q1:Year worksheets, in cell range B5:B19, correct the VLOOKUP to reference the PriceList named range (cell range A2:B16) on the Excel_BU04_Assessment2_OrderCost.xlsx workbook. Copy the formula without formatting. Close the OrderCost workbook. |
4 |
|
11 |
Save and close the Excel_BU04_Assessment1_Order.xlsx workbook. Exit Excel, and then submit your file. |
0 |
|
Total Points |
40 |
Created On: 08/02/2023 1 YO22_Excel_BU04_Assessment1 - Order 1.1
