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 Complete this question by entering your answers in the tabs below. Req 1 Reg 2 Reg 3 Reg 4 Req5 Reg 6 Req 7 Req 8 Go to the "Planning Budget" tab within your template. Prepare the planning budget in column E of the spreadsheet using the following three-step process. a. Create a formula for actors and directors' wages in cell E10. Make sure your formula is designed so that you can copy and paste it into cells E11 through E17. What is the total amount of actors and directors' wages in your planning budget? b. Copy your formula from cell E10 into cells E11 through E17. What is the total amount of administrative expenses in your planning budget (cell E17)? c. Use the AutoSum feature to calculate the total amount of expenses in your planning budget (cell E18). What is the total amount? Show less 1-a. 1-b. Total amount of actors' and directors' wages Total amount of administrative expenses Total amount of expenses 1.c. Complete this question by entering your answers in the tabs below. Reg 1 Repe Reg 3 Reg 4 Req5 Reg 6 Req 7 Req8 Go to the "Performance Report" tab within the Excel template for the remainder of the requirements. Prepare a flexible budget in column H of the spreadsheet using the following three-step process. a. Create a formula for actors' and directors' wages in cell H10. Make sure your formula is designed so that you can copy and paste it into cells H11 through H17. What is the total amount of actors' and directors' wages in your flexible budget? b. Copy your formula from cell H10 into cells H11 through H17. What is the total amount of administrative expenses in your flexible budget (cell H17)? c. Use the AutoSum feature to calculate the total amount of expenses in your flexible budget (cell H18). What is this total amount? Show less 2-a. 20. Total amount of actors and directors' wages Total amount of administrative expenses Total amount of all expenses 12.6. Complete this question by entering your answers in the tabs below. Reg 1 Req 2 Req4 Reg 5 Reg 6 Req 7 Req 8 Calculate the amount of the spending variances in column Fusing the following five-step process (Do not worry about labeling the variances as U or F at this point): (Negative amount should be indicated by a minus sign.) a. Create a formula for the actors' and directors' wages spending variance in cell M10. What is the amount of the spending variance for actors and directors wages? b. Copy your formula from cell M10 into cells M11 through M17. What is the amount of the spending variance for administrative expenses (cell M17)? c. Use the AutoSurn feature to calculate the total spending variance in cell M18. What is the total spending variance? d. Using the Absolute Value function, input the absolute values of the amounts in cells M10 through M18 into cells F10 through F18. e. Hide column M. Show less 3-a. 3-b. 3-C. Amount of the spending variance for actors and directors wages Amount of the spending variance for administrative expenses Total spending variance Required information -pending variances are highlighted? Complete this question by entering your answers in the tabs below. Reg 1 Reg 2 Req3 Red Reg 5 Reg 6 Req 7 Reg 8 Insert a favorable (F) or unfavorable (U) label in column G for each spending variance shown in column Fusing the following two-step process: a. Create an IF-THEN statement in cell G10 that properly labels the actors' and directors' wages spending variance as favorable or unfavorable. (Hint: Your If-Then statement should be focused on the amounts in cells H10 and E10.) What label appears for this variance, For U? Copy your IF-THEN statement from cell G10 into cells G11 through G18. What label appears for the administrative expense spending variance, For U? Show less 4.a. What fabel appears for this variance For U? What label appears for the administrative expense spending variance, For U? 4.b. Complete this question by entering your answers in the tabs below. Reg 1 Reg 2 Reg 3 Reg 4 nas Reg 6 Req 7 Reg 8 Calculate the amount of the activity variances in column I using the following five-step process (Do not worry about labeling the variances as U or Fat this point): (Negative amount should be indicated by a minus sign.) a. Create a formula for the actors' and directors' wages activity variance in cell N10. What is the amount of the activity variance for actors and directors' wages? b. Copy your formula from cell N10 into cells N11 through N17. What is the amount of the activity variance for administrative expenses (cell N17)? c. Use the AutoSum feature to calculate the total activity variance in cell N18. What is the total activity variance? d. Using the Absolute Value function, input the absolute values of the amounts in cells N10 through N18 into cells 110 through 118. e. Hide column N. Show less Amount of the activity variance for actors and directors' wages Amount of the activity variance for administrative expenses Total activity variance 5-a. 5-b. 5.c. Complete this question by entering your answers in the tabs below. Req 2 Req 1 Relo Req3 Reg 4 Reg 5 Req 7 Req 8 Insert a favorable (F) or unfavorable (U) label in column) for each activity variance shown in column I using the following two-step process: a. Create an IF-THEN statement in cell 10 that properly labels the actors' and directors' wages activity variance as favorable or unfavorable. (Hint: Your If-Then statement should be focused on the amounts in cells K10 and 110.) What label appears for this variance, For U? b. Copy your IF-THEN statement from cell 110 into calls 311 through 218. What label appears for the administrative expense activity variance, For U? Show less es 6-a. 6-b. What label appears for this variance, For U? What label appears for the administrative expense activity variance, For U? pending variances are highlighted? Complete this question by entering your answers in the tabs below. Req 1 Req 2 Req3 Req 4 Req 5 Reg 6 Rees? Reg 8 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 1212 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 1222 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.