The Munchkin Theatre Flexible Budget Performance report For the Year Ended December 31 Actual Results Spending Variances Flexible Budget 4 Activity Variances Actual number of productions (1) 7 Actual number of performances (2) Planning Budget 3 60 Per Production Performance Feed 400 450 199200 20000 12100 100 5000 2000 70 1500 2000 11000 4400 2000 22000 10000 1000 100 9 10 Actors and directors' wages 11 Stagehandswages Ticket booth personnel and users' wages Scenery, costumes, and props Theater hal rent 1 Printed programs 16 Publicity 11 Administrative expenses 16 Total expense ju 1800) 720) 1600 00 700 2800 13 10 12000 400 2000 120 12036 100 250 1000V 5422 1 11200 30100 10500 13000 41300 136500 140112 MUZU 2400 450 3000 750 20 formulas used 21 Actors and directors wages 22 Stagehands wages Ticket booth personnel and users wages 24 Scenery, costumes, and props 25 Theater hall rent 26 Printed programs 27 Publicity 28 Administrative expenses 29 Total expense 30 31 159200 E10-30 (U) OCTO 06-10 (UK7C10 28600 HI-1 t) C11 1-11 UK7C11 12300012-12 TU) OC12 H12-12 JUJK?*12 39300 123 UD 113 Buko 4WOO 114-34 CUCI H24-34 u 147 10950 H15 E15 F) CISH7 H15-15 UK7CES 12000 116-H36 CUSHIT K36-H16 19 BISK 43650 H17E17 LF) B17"GHT"C1 17 17 FB17"K5+C17*K74017 353600 7101121131141F16-F15 F12 F11 U SUMROHIT 130-11:112113114115 116 117 USUM KOK17) 2600 1296 1200 72 32400 32400 Req1 Req 2 Req3 Req 4 Req 5 Reg 6 Req 7 Req 8 Using Conditional Formatting, highlight all spending variances that varied by an absolute value of $1,000 or more. How many of the spending variances are highlighted? Number of spending variances highlighted Reg 1 Req 2 Reg 3 Req 4 Req 5 Req 6 Req 7 Req8 The activity variances in column I are influenced by two cost drivers-number of productions and number of performances. Therefore: a. In cell 121 create a formula that quantifies the portion of the overall activity variance that is caused solely by the fact that The Munchkin Theatre actually put on four productions instead of the planned number of five productions. What is the amount in cell 121? b. In cell 122 create a formula that quantifies the portion of the overall activity variance that is caused solely by the fact that The Munchkin Theatre actually put on 64 performances instead of the planned number of 60 performances. What is the amount in cell 1227 C. Create IF-THEN statements in cells 321 and 322 that properly label your variances in cells 121 and 122 as favorable (F) or unfavorable (U). Show less 7-a. The portion of the overall activity variance due to productions The portion of the overall activity variance due to performances 7.b. 9. Using Conditional Formatting, highlight all spending variances that vary from the flexible budget by an absolute value of 5% or more (Hint: Within the conditional formatting drop down menu look for the appropriate formula in the "More rules" section. Also, make sure - your formula does not include any absolute references.) Which spending variances deviate from the flexible budget by at least 5%? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) Actors' and directors' wages Stagehands wages 7 Ticket booth personnel and ushers' wages ? Scenery, costumes, and props 2 Theater hall rent 2 Printed programs Publicity ? Administrative expenses The Munchkin Theatre Flexible Budget Performance report For the Year Ended December 31 Actual Results Spending Variances Flexible Budget 4 Activity Variances Actual number of productions (1) 7 Actual number of performances (2) Planning Budget 3 60 Per Production Performance Feed 400 450 199200 20000 12100 100 5000 2000 70 1500 2000 11000 4400 2000 22000 10000 1000 100 9 10 Actors and directors' wages 11 Stagehandswages Ticket booth personnel and users' wages Scenery, costumes, and props Theater hal rent 1 Printed programs 16 Publicity 11 Administrative expenses 16 Total expense ju 1800) 720) 1600 00 700 2800 13 10 12000 400 2000 120 12036 100 250 1000V 5422 1 11200 30100 10500 13000 41300 136500 140112 MUZU 2400 450 3000 750 20 formulas used 21 Actors and directors wages 22 Stagehands wages Ticket booth personnel and users wages 24 Scenery, costumes, and props 25 Theater hall rent 26 Printed programs 27 Publicity 28 Administrative expenses 29 Total expense 30 31 159200 E10-30 (U) OCTO 06-10 (UK7C10 28600 HI-1 t) C11 1-11 UK7C11 12300012-12 TU) OC12 H12-12 JUJK?*12 39300 123 UD 113 Buko 4WOO 114-34 CUCI H24-34 u 147 10950 H15 E15 F) CISH7 H15-15 UK7CES 12000 116-H36 CUSHIT K36-H16 19 BISK 43650 H17E17 LF) B17"GHT"C1 17 17 FB17"K5+C17*K74017 353600 7101121131141F16-F15 F12 F11 U SUMROHIT 130-11:112113114115 116 117 USUM KOK17) 2600 1296 1200 72 32400 32400 The Munchkin Theatre Flexible Budget Performance report For the Year Ended December 31 Actual Results Spending Variances Flexible Budget 4 Activity Variances Actual number of productions (1) 7 Actual number of performances (2) Planning Budget 3 60 Per Production Performance Feed 400 450 199200 20000 12100 100 5000 2000 70 1500 2000 11000 4400 2000 22000 10000 1000 100 9 10 Actors and directors' wages 11 Stagehandswages Ticket booth personnel and users' wages Scenery, costumes, and props Theater hal rent 1 Printed programs 16 Publicity 11 Administrative expenses 16 Total expense ju 1800) 720) 1600 00 700 2800 13 10 12000 400 2000 120 12036 100 250 1000V 5422 1 11200 30100 10500 13000 41300 136500 140112 MUZU 2400 450 3000 750 20 formulas used 21 Actors and directors wages 22 Stagehands wages Ticket booth personnel and users wages 24 Scenery, costumes, and props 25 Theater hall rent 26 Printed programs 27 Publicity 28 Administrative expenses 29 Total expense 30 31 159200 E10-30 (U) OCTO 06-10 (UK7C10 28600 HI-1 t) C11 1-11 UK7C11 12300012-12 TU) OC12 H12-12 JUJK?*12 39300 123 UD 113 Buko 4WOO 114-34 CUCI H24-34 u 147 10950 H15 E15 F) CISH7 H15-15 UK7CES 12000 116-H36 CUSHIT K36-H16 19 BISK 43650 H17E17 LF) B17"GHT"C1 17 17 FB17"K5+C17*K74017 353600 7101121131141F16-F15 F12 F11 U SUMROHIT 130-11:112113114115 116 117 USUM KOK17) 2600 1296 1200 72 32400 32400 Req1 Req 2 Req3 Req 4 Req 5 Reg 6 Req 7 Req 8 Using Conditional Formatting, highlight all spending variances that varied by an absolute value of $1,000 or more. How many of the spending variances are highlighted? Number of spending variances highlighted Reg 1 Req 2 Reg 3 Req 4 Req 5 Req 6 Req 7 Req8 The activity variances in column I are influenced by two cost drivers-number of productions and number of performances. Therefore: a. In cell 121 create a formula that quantifies the portion of the overall activity variance that is caused solely by the fact that The Munchkin Theatre actually put on four productions instead of the planned number of five productions. What is the amount in cell 121? b. In cell 122 create a formula that quantifies the portion of the overall activity variance that is caused solely by the fact that The Munchkin Theatre actually put on 64 performances instead of the planned number of 60 performances. What is the amount in cell 1227 C. Create IF-THEN statements in cells 321 and 322 that properly label your variances in cells 121 and 122 as favorable (F) or unfavorable (U). Show less 7-a. The portion of the overall activity variance due to productions The portion of the overall activity variance due to performances 7.b. 9. Using Conditional Formatting, highlight all spending variances that vary from the flexible budget by an absolute value of 5% or more (Hint: Within the conditional formatting drop down menu look for the appropriate formula in the "More rules" section. Also, make sure - your formula does not include any absolute references.) Which spending variances deviate from the flexible budget by at least 5%? (You may select more than one answer. Single click the box with the question mark to produce a check mark for a correct answer and double click the box with the question mark to empty the box for a wrong answer. Any boxes left with a question mark will be automatically graded as incorrect.) Actors' and directors' wages Stagehands wages 7 Ticket booth personnel and ushers' wages ? Scenery, costumes, and props 2 Theater hall rent 2 Printed programs Publicity ? Administrative expenses The Munchkin Theatre Flexible Budget Performance report For the Year Ended December 31 Actual Results Spending Variances Flexible Budget 4 Activity Variances Actual number of productions (1) 7 Actual number of performances (2) Planning Budget 3 60 Per Production Performance Feed 400 450 199200 20000 12100 100 5000 2000 70 1500 2000 11000 4400 2000 22000 10000 1000 100 9 10 Actors and directors' wages 11 Stagehandswages Ticket booth personnel and users' wages Scenery, costumes, and props Theater hal rent 1 Printed programs 16 Publicity 11 Administrative expenses 16 Total expense ju 1800) 720) 1600 00 700 2800 13 10 12000 400 2000 120 12036 100 250 1000V 5422 1 11200 30100 10500 13000 41300 136500 140112 MUZU 2400 450 3000 750 20 formulas used 21 Actors and directors wages 22 Stagehands wages Ticket booth personnel and users wages 24 Scenery, costumes, and props 25 Theater hall rent 26 Printed programs 27 Publicity 28 Administrative expenses 29 Total expense 30 31 159200 E10-30 (U) OCTO 06-10 (UK7C10 28600 HI-1 t) C11 1-11 UK7C11 12300012-12 TU) OC12 H12-12 JUJK?*12 39300 123 UD 113 Buko 4WOO 114-34 CUCI H24-34 u 147 10950 H15 E15 F) CISH7 H15-15 UK7CES 12000 116-H36 CUSHIT K36-H16 19 BISK 43650 H17E17 LF) B17"GHT"C1 17 17 FB17"K5+C17*K74017 353600 7101121131141F16-F15 F12 F11 U SUMROHIT 130-11:112113114115 116 117 USUM KOK17) 2600 1296 1200 72 32400 32400