Complete the Excel file using the Microsoft Word Instructions.
Gaining Proficiency Instructions Grader - InstructionsExcel 2019 Project Exp19_Excel_Ch09_HOEAssessment - Ticket Sales 1.0 Project Description: You are an assistant to the manager of Sugarhouse District Theatre, where touring Broadway shows are performed. You created a workbook for the weekend evening performances to track the number of seats available and sold in each section of the theatre, along with gross revenue. You are ready to complete the workbook. Steps to Perform: Step Instructions Points Possible 1 Start Excel. Download and open the file named Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx. Grader has automatically added your last name to the beginning of the filename. Note: When you open the file, Excel prompts you to fix a circular error. Click OK and continue. 0 2 The first column in the Friday worksheet contains cells with a different font color and levels of indentation. You want to fill this formatting across the Saturday and Sunday worksheets. Group the Friday, Saturday, and Sunday worksheets. Fill the formatting for the range A5:A20. 4 3 You notice that a formula is missing on three worksheets. You will insert the formula simultaneously. With the worksheets still grouped, in cell E21, enter a formula to subtract the Grand Total Percentage Sold from 1. Apply Bold to the cell. Ungroup the worksheets. 15 4 The Totals worksheet contains summary data. You will insert a hyperlink to the number of seats sold in each respective worksheet. On the Totals worksheet, in cell A5, insert a hyperlink to cell C21 in the Friday worksheet with the ScreenTip text Friday. In cell A6, insert a hyperlink to cell C21 in the Saturday worksheet with the ScreenTip text Saturday. In cell A7, insert a hyperlink to cell C21 in the Sunday worksheet with the ScreenTip text Sunday. Test the hyperlinks to ensure the work correctly. 15 5 Now you are ready to insert a formula with a reference to the number of seats sold for Friday's performance. In cell B5, insert a formula with a 3-D reference to cell C21 in the Friday worksheet. 5 6 Now you are ready to insert a formula with a reference to the number of seats sold for Saturday's performance. In cell B6, insert a formula with a 3-D reference to cell C21 in the Saturday worksheet. 5 7 Now you are ready to insert a formula with a reference to the number of seats sold for Sunday's performance. In cell B7, insert a formula with a 3-D reference to cell C21 in the Sunday worksheet. 5 8 The next section of the Totals worksheet is designed to display total gross revenue by section. In cell B11, insert the SUM function with a 3-D reference to calculate the total Orchestra Front gross revenue (cell G9) for the Friday, Saturday, and Sunday worksheets. 5 9 You are ready to insert a function to calculate the total Orchestra Back gross revenue. In cell B12, insert the SUM function with a 3-D reference to calculate the total Orchestra Back gross revenue (cell G14) for the Friday, Saturday, and Sunday worksheets. 5 10 You are ready to insert a function to calculate the total gross revenue for the Tiers. In cell B13, insert the SUM function with a 3-D reference to calculate the total Tiers gross revenue (cell G20) for the Friday, Saturday, and Sunday worksheets. 5 11 The third section of the totals worksheet needs to link to a value in another workbook. Open the Exp19_Excel_Ch09_HOEAssessment_Venue.xlsx workbook. Go back to the Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx workbook. In cell B17 on the Totals worksheet, insert a link to the Weekend Evening value (cell C5). Close the Venue workbook. 4 12 The worksheet contains a circular error. Now you want to find and correct it. On the Totals worksheet, check for errors and correct the formula with the circular reference. 5 13 You want to create a validation rule to prevent the user from entering too many seats sold. For now, you will create a validation for just one input cell. In cell C6 in the Friday worksheet, create a validation rule to allow whole numbers less than or equal to data stored in cell B6. Enter the input message title Seats Sold and the input message Enter the number of seats sold. (including the period). Use the Stop alert with the error alert title Invalid Number and the error alert message The number cannot exceed total seats in section. (including the period). Test the data validation by attempting to enter 200 in cell C6 and then cancel the change. 12 14 You want to unlock data-entry cells so that the user can change the number of seats sold in the worksheets. Group the Friday, Saturday, and Sunday worksheets. Select the ranges C6:C8, C11:C13, and C16:C19 and unlock these cells. 7 15 Now that you unlocked data-entry cells, you are ready to protect the worksheets to prevent users from changing data in other cells. Individually, protect each sheet using the default allowances with the password Expl0r!ng. 6 16 Create footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of all worksheets. 2 17 Mark the workbook as final. Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab. 0 18 Save and close Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx. Exit Excel. Submit the file as directed. 0 Total Points 100 Created On: 08/25/20201Exp19_Excel_Ch09_HOEAssessment - Ticket Sales 1.0 Friday Sugarhouse District Theatre Friday, April 16, 2021 Seating SectionSeats in SectionSeats SoldPercentage SoldPercentage Not SoldPrice Per SeatGross Revenue Orchestra Front Left19218998.4%1.6%$ 130$ 24,570 Center350350100.0%0.0%$ 155$ 54,250 Right19219099.0%1.0%$ 130$ 24,700 Section Totals73472999.3%0.7%$ 103,520 Orchestra Back Left13012696.9%3.1%$ 110$ 13,860 Center20019798.5%1.5%$ 125$ 24,625 Right13012898.5%1.5%$ 110$ 14,080 Section Totals46045198.0%2.0%$ 52,565 Tiers Tier 1 Center184184100.0%0.0%$ 100$ 18,400 Tier 1 Sides22422098.2%1.8%$ 90$ 19,800 Tier 2 Center16015093.8%6.3%$ 80$ 12,000 Tier 2 Sides18017597.2%2.8%$ 75$ 13,125 Section Totals74872997.5%2.5%$ 63,325 Grand Totals1,9421,90998.3%$ 219,410 Saturday Sugarhouse District Theatre Saturday, April 17, 2021 Seating SectionSeats in SectionSeats SoldPercentage SoldPercentage Not SoldPrice Per SeatGross Revenue Orchestra Front Left192192100.0%0.0%$ 120$ 23,040 Center350350100.0%0.0%$ 155$ 54,250 Right192192100.0%0.0%$ 120$ 23,040 Section Totals734734100.0%0.0%$ 100,330 Orchestra Back Left13012898.5%1.5%$ 110$ 14,080 Center200200100.0%0.0%$ 120$ 24,000 Right130130100.0%0.0%$ 110$ 14,300 Section Totals46045899.6%0.4%$ 52,380 Tiers Tier 1 Center184184100.0%0.0%$ 100$ 18,400 Tier 1 Sides22422299.1%0.9%$ 90$ 19,980 Tier 2 Center16015093.8%6.3%$ 80$ 12,000 Tier 2 Sides18017597.2%2.8%$ 75$ 13,125 Section Totals74873197.7%2.3%$ 63,505 Grand Totals1,9421,92399.0%$ 216,215 Sunday Sugarhouse District Theatre Sunday, April 18, 2021 Seating SectionSeats in SectionSeats SoldPercentage SoldPercentage Not SoldPrice Per SeatGross Revenue Orchestra Front Left19218897.9%2.1%$ 120$ 22,560 Center35034097.1%2.9%$ 155$ 52,700 Right19218294.8%5.2%$ 120$ 21,840 Section Totals73471096.7%3.3%$ 97,100 Orchestra Back Left13011588.5%11.5%$ 110$ 12,650 Center20019095.0%5.0%$ 120$ 22,800 Right13012092.3%7.7%$ 110$ 13,200 Section Totals46042592.4%7.6%$ 48,650 Tiers Tier 1 Center18418298.9%1.1%$ 100$ 18,200 Tier 1 Sides22421495.5%4.5%$ 90$ 19,260 Tier 2 Center16015596.9%3.1%$ 80$ 12,400 Tier 2 Sides18017898.9%1.1%$ 75$ 13,350 Section Totals74872997.5%2.5%$ 63,210 Grand Totals1,9421,86496.0%$ 208,960 Totals Sugarhouse District Theatre Weekend Evening Performances Evening PerformanceNo. of Seats Sold Friday Saturday Sunday Average Seats Sold- 0 Seating SectionGross Revenue Orchestra Front Orchestra Back Tiers Grand Totals$ - 0 Venue Costs Weekend Evening Fee No. of Evening Performances3 Total Evening Fee$ - 0 Venue Venue Fees Weekday MatineeTuesday-Thursday$ 55,000 Weekday EveningTuesday-Thursday$ 60,000 Weekend MatineeFriday-Sunday$ 65,000 Weekend EveningFriday-Sunday$ 75,000