Exp19_Excel_Ch09_HOEAssessment - Ticket Sales 1.0 Step Instructions Points Possible 9 You are ready to insert a function to calculate the total Orchestra Back gross revenue. 5 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: 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. 10 You are ready to insert a function to calculate the total gross revenue for the Tiers. 5 Step Instructions Points Possible 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. 1 0 11 The third section of the totals worksheet needs to link to a value in another workbook. 4 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. Open the Exp19_Excel_Ch09_HOEAssessment_Venue.xlsx workbook. Go back to the Exp19_Excel_ch09_HOEAssessment_Ticket Sales.xlsx workbook. In cell B17 on the Totals worksheet, insert a link to the Weekend Evening value (cell C5). Close the Venue workbook. Note: When you open the file, Excel prompts you to fix a circular error. Click OK and continue. 2 4 12 The worksheet contains a circular error. Now you want to find and correct it. 5 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. On the Totals worksheet, check for errors and correct the formula with the circular reference. 13 12 3 15 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. 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 formula to subtract the Grand Total Percentage Sold from 1. Apply Bold to the cell. Ungroup the worksheets. 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. 4 15 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 Screen Tip text Sunday. Test the hyperlinks to ensure the work correctly. 14 7 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. 5 Now you are ready to insert a formula with a reference to the number of seats sold for Friday's performance. 5 15 6 Now that you unlocked data-entry cells, you are ready to protect the worksheets to prevent users from changing data in other cells. In cell B5, insert a formula with a 3-D reference to cell C21 in the Friday worksheet Individually, protect each sheet using the default allowances with the password Explor!ng. 6 5 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. 16 2 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 17 Mark the workbook as final. 0 7 5 Now you are ready to insert a formula with a reference to the number of seats sold for Sunday's performance, Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab. In cell B7, insert a formula with a 3-D reference to cell C21 in the Sunday worksheet. 18 0 8 5 Save and close Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx. Exit Excel. Submit the file as directed 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. Total Points 100 Fle Home Insert Draw Rev VIEW Help Shane Comments Page Layout Formulas Data - 20-AA === Calon Wrap lest General F Bad Good Neutral AutoSum AY 4 X Paste tury - Format Punto Clipboard Merge & Center $% 3 Normal Condional Forma Calculation Format ng table Check Cell Explanatory.. Input Insul Delete Fummal Sortivi Sort & Find & Fiber Select Editing Any Data Analais Fon: Alignment Number Styles Cels Sensitivity A1 f Sugarhouse District Theatre A D H M N P R S w Y 2 Sugarhouse District Theatre 1 2 2 Sunday, April 18, 2021 Grace Seats in Section Seats Percentage Percentage Price per Sold Sold Not Sold Seat Revenue 192 350 192 734 183 340 102 710 97.9% 97.1% 94.596 46.7% 2.1% 2.91 5.2% $ 120 $ 22,560 $ $ 155 $ 52.700 $ 120 $ 21.540 $ 47,100 4 Seating Section 5 Orchestra front 6 Lott 7 Center UR o Section Totals 10 Orchestra Back 11 Left 12 Center 13 Right 14 Section Totals 15 Tiers 120 200 130 400 115 190 120 425 89.5% 25.07 92.3 92.4% 11.3% 5.08 7.78 $ 110 $ 12,600 $ 120 S 22.00 $ 110 $ 13,200 $ 48,650 1.19 16 Tier 1 Center 184 224 435 17 Tier 1 sides 18 Tier 2 Center 19 Tier 2 Sides 20 Section Total 21 Grand Totals 22 23 100 10 749 1.942 182 214 155 178 729 1.964 90.9% 95.3% 96.9% 48.99 97.5% 95.0% 3.1% 1.15 2.5% $ $100 $ 1.200 s 90 $ 19.200 $ yu$ 12 400 S 25 $ 18.350 $ 63,210 $ 205.900 25 76 27 28 29 30 21 22 33 34 25 36 37 29 Friday Sunday Saturday Total + File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments 1 Calibri 11 ~ A 2 Wrap Text General PA Normal Bad Good Neutral X Cut LECopy Format Painter AutoSum Fill 47 0 Paste BIU A $ %) S. 3 Merge & Center Check Cell Insert Delete Format Conditional Format as Calculation Formatting Table Explanatory... Input Sensitivity Clear Sort & Find & Filter Select Analyze Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity J17 f A B C D E F G 1 J K L M N o P o R S T U V w Y z AA 1 Sugarhouse District Theatre Saturday, April 17, 2021 2 3 Seats in Section Seats Sold Percentage Percentage Price Per Sold Not Sold Seat Gross Revenue 192 350 192 350 192 734 100.0% 100.0% 100.0% 100.0% 0.0% 0.0% 0.0% 0.0% $ $ $ 120 $ 155 $ 120 $ $ 23,040 54,250 23,040 100,330 192 734 128 200 130 200 130 460 98.5% 100.0% 100.0% 99.6% 1.5% 0.0% 0.0% 0.4% $ $ $ 110 $ 120 $ 110 $ $ 14,080 24,000 14,300 52,380 130 458 184 0.0% 100 90 0.9% 222 150 6.3% $ $ S $ 80 184 224 160 180 748 1,942 100.0% 99.1% 93.8% 97.2% 97.7% 99.0% 4 Seating Section 5 Orchestra Front 6 Left 7 Center 8 Right 9 Section Totals 10 Orchestra Back 11 Left 12 Center 13 Right 14 Section Totals 15 Tiers 16 Tier 1 Center 17 Tier 1 Sides 18 Tier 2 Center 19 Tier 2 Sides 20 Section Totals 21 Grand Totals 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 $ 18,400 $ 19,980 S 12,000 $ 13,125 $ 63,505 $ 216,215 2.8% 75 175 731 1,923 2.3% Friday Sunday Saturday Totals + File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri 14 ~ A General Normal Bad Good Neutral X Cut L6 Copy Format Painter 48 O s 2 Wrap Text E Merge & Center Fill Paste B I U A $ %, Conditional Format as Calculation Formatting Table Check Cell Explanatory... Input Insert Delete Format Sensitivity Clear Sort & Find & Filter Select Editing Analyze Data Clipboard Font Alignment Number Styles Cells Analysis Sensitivity A1 X Sugarhouse District Theatre D E F G H j L M N o P 0 R S T U v w Y Z AA AF A B 1 Sugarhouse District Theatre 2. Weekend Evening Performances 3 No. of Seats 4 Evening Performance Sold 5 Friday 6 Saturday 7 Sunday Average Seats Sold 2 Gross 10 Seating Section Revenue 11 Orchestra Front 12 Orchestra Back 13 Tiers 14 Grand Totals $ 15 16 Venue Costs 17 Weekend Evening Fee 8 No. of Evening Perforr 9 Total Evening Fee $ 20 21 22 23 24 25 26 27 28 29 30 31 32 33 4 35 36 37 38 Friday Sunday Saturday Totals + File Draw Home Formus Data Rew View Hop Share Comments FIP Page Layout 2.1 E Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Becaule from the Intumat can contain views. Unless you need to edit, it's safe to stay in Protected View Share Comments Enable Editing LO ID Paste 4 General S - % 3 5 Numer Cond bonal Formatting Formatos de D. Col Styles Insert Dede- Format Analyse Sersity AA A A1 Venues alebord r Algnment Styles Analysis Sensitivity A D E G 11 1 K L16 V A c D E G H 1 j 2 Weekday Matinee 3 Weekday Evening 4 Weekend Matinee s Weekend Evening Venue Fees Thesday Thursday Tuesday Thursday Friday-Sunday Friday-Sunday $ $ $ $ 55,000 60,000 65,000 75,000 1 2 Sugarhouse District Theatre Friday, April 16, 2021 Seats in Section Seats Percentage Percentage Price Per Sold Sold Not Sold Seat Gross Revenue 192 350 192 734 189 330 190 720 98.4% 100.0 99.0% 00.3% 1.6% 0.0% 1.0% 0.7% 5 $ 130 $ 24,570 $ 155 $ 54.250 $ 130 S 24,00 $ 103,520 8 9 10 11 12 13 14 15 16 17 18 19 125 125 197 95% 98.5 130 200 130 466 3.1% 1.5% 1.5% 2.0% 9 110S 13.560 $ $ 125 $ 24.525 $ $ 10 $ 14,000 $ 451 UROR 194 221 160 190 Seating Section 5 Orchestra Front Left 7 Center o Hught 0 Section Totals 10 Orchestra Back 11 Lelt Center 13 Right 14 Section Tools 15 Tiers 16 Tier 1 Center 17 Tier 1 Sides Tier 2 Center 19 Tier 2 Sides 20 Section Totals 21 Grand Totals 22 23 24 25 26 27 24 29 30 31 32 33 34 35 36 194 220 150 17 729 1,900 100.0% 98.2% 93.8% 97.25 47.5% 98.3% 0.0% 1.8% 6.3% 2.8% 2.5% $ 9 $ $ 100 $ 18.400 90 $ 19.00 80 $ 12.000 75 $ 11,125 13,125 $ 13,225 $ 219,410 22 23 23 24 25 1,942 ZA 20 31 23 34 23 20 37 39 40 41 42 43 38 Friday Saturday Sunday Total Venue + Cruir References 1000 Circular Referentes 1 100 Exp19_Excel_Ch09_HOEAssessment - Ticket Sales 1.0 Step Instructions Points Possible 9 You are ready to insert a function to calculate the total Orchestra Back gross revenue. 5 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: 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. 10 You are ready to insert a function to calculate the total gross revenue for the Tiers. 5 Step Instructions Points Possible 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. 1 0 11 The third section of the totals worksheet needs to link to a value in another workbook. 4 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. Open the Exp19_Excel_Ch09_HOEAssessment_Venue.xlsx workbook. Go back to the Exp19_Excel_ch09_HOEAssessment_Ticket Sales.xlsx workbook. In cell B17 on the Totals worksheet, insert a link to the Weekend Evening value (cell C5). Close the Venue workbook. Note: When you open the file, Excel prompts you to fix a circular error. Click OK and continue. 2 4 12 The worksheet contains a circular error. Now you want to find and correct it. 5 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. On the Totals worksheet, check for errors and correct the formula with the circular reference. 13 12 3 15 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. 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 formula to subtract the Grand Total Percentage Sold from 1. Apply Bold to the cell. Ungroup the worksheets. 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. 4 15 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 Screen Tip text Sunday. Test the hyperlinks to ensure the work correctly. 14 7 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. 5 Now you are ready to insert a formula with a reference to the number of seats sold for Friday's performance. 5 15 6 Now that you unlocked data-entry cells, you are ready to protect the worksheets to prevent users from changing data in other cells. In cell B5, insert a formula with a 3-D reference to cell C21 in the Friday worksheet Individually, protect each sheet using the default allowances with the password Explor!ng. 6 5 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. 16 2 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 17 Mark the workbook as final. 0 7 5 Now you are ready to insert a formula with a reference to the number of seats sold for Sunday's performance, Note: Mark as Final is not available in Excel for Mac. Instead, use Always Open Read-Only on the Review tab. In cell B7, insert a formula with a 3-D reference to cell C21 in the Sunday worksheet. 18 0 8 5 Save and close Exp19_Excel_Ch09_HOEAssessment_TicketSales.xlsx. Exit Excel. Submit the file as directed 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. Total Points 100 Fle Home Insert Draw Rev VIEW Help Shane Comments Page Layout Formulas Data - 20-AA === Calon Wrap lest General F Bad Good Neutral AutoSum AY 4 X Paste tury - Format Punto Clipboard Merge & Center $% 3 Normal Condional Forma Calculation Format ng table Check Cell Explanatory.. Input Insul Delete Fummal Sortivi Sort & Find & Fiber Select Editing Any Data Analais Fon: Alignment Number Styles Cels Sensitivity A1 f Sugarhouse District Theatre A D H M N P R S w Y 2 Sugarhouse District Theatre 1 2 2 Sunday, April 18, 2021 Grace Seats in Section Seats Percentage Percentage Price per Sold Sold Not Sold Seat Revenue 192 350 192 734 183 340 102 710 97.9% 97.1% 94.596 46.7% 2.1% 2.91 5.2% $ 120 $ 22,560 $ $ 155 $ 52.700 $ 120 $ 21.540 $ 47,100 4 Seating Section 5 Orchestra front 6 Lott 7 Center UR o Section Totals 10 Orchestra Back 11 Left 12 Center 13 Right 14 Section Totals 15 Tiers 120 200 130 400 115 190 120 425 89.5% 25.07 92.3 92.4% 11.3% 5.08 7.78 $ 110 $ 12,600 $ 120 S 22.00 $ 110 $ 13,200 $ 48,650 1.19 16 Tier 1 Center 184 224 435 17 Tier 1 sides 18 Tier 2 Center 19 Tier 2 Sides 20 Section Total 21 Grand Totals 22 23 100 10 749 1.942 182 214 155 178 729 1.964 90.9% 95.3% 96.9% 48.99 97.5% 95.0% 3.1% 1.15 2.5% $ $100 $ 1.200 s 90 $ 19.200 $ yu$ 12 400 S 25 $ 18.350 $ 63,210 $ 205.900 25 76 27 28 29 30 21 22 33 34 25 36 37 29 Friday Sunday Saturday Total + File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments 1 Calibri 11 ~ A 2 Wrap Text General PA Normal Bad Good Neutral X Cut LECopy Format Painter AutoSum Fill 47 0 Paste BIU A $ %) S. 3 Merge & Center Check Cell Insert Delete Format Conditional Format as Calculation Formatting Table Explanatory... Input Sensitivity Clear Sort & Find & Filter Select Analyze Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity J17 f A B C D E F G 1 J K L M N o P o R S T U V w Y z AA 1 Sugarhouse District Theatre Saturday, April 17, 2021 2 3 Seats in Section Seats Sold Percentage Percentage Price Per Sold Not Sold Seat Gross Revenue 192 350 192 350 192 734 100.0% 100.0% 100.0% 100.0% 0.0% 0.0% 0.0% 0.0% $ $ $ 120 $ 155 $ 120 $ $ 23,040 54,250 23,040 100,330 192 734 128 200 130 200 130 460 98.5% 100.0% 100.0% 99.6% 1.5% 0.0% 0.0% 0.4% $ $ $ 110 $ 120 $ 110 $ $ 14,080 24,000 14,300 52,380 130 458 184 0.0% 100 90 0.9% 222 150 6.3% $ $ S $ 80 184 224 160 180 748 1,942 100.0% 99.1% 93.8% 97.2% 97.7% 99.0% 4 Seating Section 5 Orchestra Front 6 Left 7 Center 8 Right 9 Section Totals 10 Orchestra Back 11 Left 12 Center 13 Right 14 Section Totals 15 Tiers 16 Tier 1 Center 17 Tier 1 Sides 18 Tier 2 Center 19 Tier 2 Sides 20 Section Totals 21 Grand Totals 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 $ 18,400 $ 19,980 S 12,000 $ 13,125 $ 63,505 $ 216,215 2.8% 75 175 731 1,923 2.3% Friday Sunday Saturday Totals + File Home Insert Draw Page Layout Formulas Data Review View Help Share Comments AutoSum Calibri 14 ~ A General Normal Bad Good Neutral X Cut L6 Copy Format Painter 48 O s 2 Wrap Text E Merge & Center Fill Paste B I U A $ %, Conditional Format as Calculation Formatting Table Check Cell Explanatory... Input Insert Delete Format Sensitivity Clear Sort & Find & Filter Select Editing Analyze Data Clipboard Font Alignment Number Styles Cells Analysis Sensitivity A1 X Sugarhouse District Theatre D E F G H j L M N o P 0 R S T U v w Y Z AA AF A B 1 Sugarhouse District Theatre 2. Weekend Evening Performances 3 No. of Seats 4 Evening Performance Sold 5 Friday 6 Saturday 7 Sunday Average Seats Sold 2 Gross 10 Seating Section Revenue 11 Orchestra Front 12 Orchestra Back 13 Tiers 14 Grand Totals $ 15 16 Venue Costs 17 Weekend Evening Fee 8 No. of Evening Perforr 9 Total Evening Fee $ 20 21 22 23 24 25 26 27 28 29 30 31 32 33 4 35 36 37 38 Friday Sunday Saturday Totals + File Draw Home Formus Data Rew View Hop Share Comments FIP Page Layout 2.1 E Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Becaule from the Intumat can contain views. Unless you need to edit, it's safe to stay in Protected View Share Comments Enable Editing LO ID Paste 4 General S - % 3 5 Numer Cond bonal Formatting Formatos de D. Col Styles Insert Dede- Format Analyse Sersity AA A A1 Venues alebord r Algnment Styles Analysis Sensitivity A D E G 11 1 K L16 V A c D E G H 1 j 2 Weekday Matinee 3 Weekday Evening 4 Weekend Matinee s Weekend Evening Venue Fees Thesday Thursday Tuesday Thursday Friday-Sunday Friday-Sunday $ $ $ $ 55,000 60,000 65,000 75,000 1 2 Sugarhouse District Theatre Friday, April 16, 2021 Seats in Section Seats Percentage Percentage Price Per Sold Sold Not Sold Seat Gross Revenue 192 350 192 734 189 330 190 720 98.4% 100.0 99.0% 00.3% 1.6% 0.0% 1.0% 0.7% 5 $ 130 $ 24,570 $ 155 $ 54.250 $ 130 S 24,00 $ 103,520 8 9 10 11 12 13 14 15 16 17 18 19 125 125 197 95% 98.5 130 200 130 466 3.1% 1.5% 1.5% 2.0% 9 110S 13.560 $ $ 125 $ 24.525 $ $ 10 $ 14,000 $ 451 UROR 194 221 160 190 Seating Section 5 Orchestra Front Left 7 Center o Hught 0 Section Totals 10 Orchestra Back 11 Lelt Center 13 Right 14 Section Tools 15 Tiers 16 Tier 1 Center 17 Tier 1 Sides Tier 2 Center 19 Tier 2 Sides 20 Section Totals 21 Grand Totals 22 23 24 25 26 27 24 29 30 31 32 33 34 35 36 194 220 150 17 729 1,900 100.0% 98.2% 93.8% 97.25 47.5% 98.3% 0.0% 1.8% 6.3% 2.8% 2.5% $ 9 $ $ 100 $ 18.400 90 $ 19.00 80 $ 12.000 75 $ 11,125 13,125 $ 13,225 $ 219,410 22 23 23 24 25 1,942 ZA 20 31 23 34 23 20 37 39 40 41 42 43 38 Friday Saturday Sunday Total Venue + Cruir References 1000 Circular Referentes 1 100
