Reference no: EM131492111
Assignment: Painted Paradise Golf Uniform Orders
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.
Step Instructions
1 Start Excel. Open the downloaded Excel file named e04capstone_grader_a2_Order.xlsx. Save the workbook as e04Order_LastFirst, using your last and first name. Enable content, if necessary.
2 With the Q1 through Q4 worksheets grouped, change the color of the Q1, Q2, Q3, and Q4 worksheet tabs to Dark Red. Ungroup the worksheets.
3 On the Q1 worksheet, enter a formula in cells H5:H13 to calculate the total number of items sold for each item type. If you use the fill handle to copy the formulas, click the Auto Fill Options and choose Fill Without Formatting.
4 On the Q1 worksheet, select cells A4:H14 and fill Formats across the worksheets Q1:Q4.
5 On the Q1 worksheet, select cells H5:H14 and fill Contents across the worksheets Q1:Q4.
6 On the Q1 worksheet, select cells A14:H14 and fill All across worksheets Q1:Q4.
7 On the Year worksheet, enter a 3D formula in cells C5:G13 to calculate the total items sold for each department. If you use the fill handle to copy the formulas, click the Auto Fill Options and choose Fill Without Formatting.
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. Do not include the total row from each quarter worksheet. Use the labels in Top row and Left column.
AutoFit columns A:I and then hide columns B:C. Select cell A4 type Item.
9 Delete the Prices worksheet.
10 Open the downloaded Excel workbook e04_grader_a2_OrderCost.xlsx. In e04Order_LastFirst, on the Q1:Year worksheets, in cells B5:B13, correct the VLOOKUP to reference the PriceList named range (cells A2:B16) on the e04_grader_a2_OrderCost workbook. Close e04_grader_a2_OrderCost. If you use the fill handle to copy the formulas, click the Auto Fill Options and choose Fill Without Formatting.
11 If necessary, add the Compare and Merge Workbooks button to the Quick Access Toolbar. Share the workbook e04Order_LastFirst and then save a copy of the workbook as e04Order2_LastFirst using your last and first name. Click the Q4 worksheet tab, select cell F5 and change it to 3. Select cell D13 and change it to 2. Save and close the workbook, but leave Excel open.
12 Open e04Order_LastFirst.xlsx. Click Enable Content and click Continue. Compare and merge the workbook with e04Order2_LastFirst.xlsx. Check to see your changes on the Q4 worksheet in cells F5 and D13. Stop sharing the workbook and click Yes.
13 Save and closee04Order_LastFirst with the following worksheets (in this order): Q1, Q2, Q3, Q4, Year, and YearLinked. Close Excel, and submit your work to your instructor as directed.
Attachment:- Attachments.rar