The Munchkin Theater Flexible Budget Performance Report For the Year Ended December 31 Actual Spending Flexible Activity Planning Results Variances Budget Variances Budget Number of productions (q,) 4 4 5 Number of performances(q2) 64 64 60 Cost Formulas N Per Per Production Performance Fixed Activity/sales STATIC/Spending Actors' and directors' wages 2,400 159,200 $ (5,600) F $ 153,600 $ 9,600 U $ 144,000 $ 9,600 $ (5,600) $ (5,600) Stagehands' wages 450 28,600 200 U $ 28,800 $ 1,800 U 27,000 1,800 $ 200 $ 200 Ticket booth personnel and ushers' wages 180 12,300 S (780) F 11,520 720 U 10,800 720 $ (780) $ (780) Scenery, costumes, and props $ 8,600 39,300 $ (4,900) F 34,400 (8,600) 43,000 (8,600) $ (4,900) $ 3,700 Theater hall rent 750 49,600 $ (1,600) F $ 48,000 3,000 U 45,000 S 3,000 $ (1,600) $ (1,600) Printed programs 175 10,950 250 U 11,200 700 U 10,500 700 250 $ 250 Publicity 2,600 12,000 $ (1,600) F 10,400 $ (2,600) F 13,000 S (2,600) $ (1,600) $ 1,000 Administrative expenses 1,296 72 $ 32,400 41,650 S 542 U $ 42,192 (1,008) F S 43,200 S (1,008) $ 542 $ 1,838 Total 12,496 4,027 32,400 353,600 $ (13,488) F 340,112 3,612 U $ 336,500 S 3,612 $ (13,488) $ (992) Activity Variances: By Cost Driver Portion due to number of productions 992 Portion due to number of performances (29,596){The folio wing information appiies to the questions displayed below} The Munchkin Theater is a nonprot organization devoted to staging plays for children. The theater has a very small full- time professional administrative staff. Through a special arrangement with the actors' union, actors and directors rehearse without pay and are paid only for actual performances. The Munchkin Theater has asked for your help in preparing a Planning budget at the beginning of the year and evaluating actual expenses at the end of the year. The theater expects to put on five different productions with a total of 60 performances. For example, one of the productions is Peter Rabbit which had been budgeted for five performances. After interviewing various people affiliated with the theater you have developed the following estimated cost formulas for each of the eight expenses that will be included in your Planning budget: Cost Fo rnulas Per Per Production Performance Fixed Actors' and directors' wages $2,499 Stagehands' wages $ 459 Ticket booth personnel and ushers' wages $ 183 Scenery, costumes, and props $8,588 Theater hall rent $ T5B Printed programs $ 1?5 Publicity $2,633 Administrative expenses $1,296 $ T2 $32,486 By the end of the year, The Munchkin Theater actually put on four productions and a total of 54 performances. The actual expenses incurred during the year were as follows: Actors' and directors' wages $159,268 Stagehands' wages 28,568 Ticket booth personal and ushers' wages 12.393 Scenery, costumes, and props 39,393 Theater hall rent 49,5 Printed programs l,958 Publicity 12,668 Adminin+rn+iun nunnnnno A1 RED By the end of the year, The Munchkin Theater actually put on four productions and a total of 64 performances. The actual expenses incurred during the year were as follows: Actors' and directors' wages $159, 200 Stagehands' wages 28,600 Ticket booth personal and ushers' wages 12, 300 Scenery, costumes, and props 39,300 Theater hall rent 49, 600 Printed programs 10,950 Publicity 12,000 Administrative expenses 41, 650 Total $353, 600 Click here to download the Excel template that you will use to create the Planning Budget and Flexible Budget Performance Report.The sales volume 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 sales volume 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 sales volume 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 122? c. Create IF-THEN statements in cells J21 and J22 that properly label your variances in cells 121 and 122 as favorable (F) or unfavorable (U). Show less A 7-a. The portion of the overall sales volume variance due to productions 5,600 7-b. The portion of the overall sales volume variance due to performances $ 29,596Using Conditional Formatting, highlight all static budget variances that varied by an absolute value of $1,000 or more. How many of the static budget variances are highlighted? Number of static budget variances highlighted9. Using Conditional Formatting, highlight all static budget variances that vary from the exible 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 static budget variances deviate from the exible budget by at least 5%? {You may select more than one answer. Single click the box with the question mark to produce a check marl: 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 Ticket booth personnel and ushers' wages Scenery, costu mes, and props Theater hall rent Printed programs Publicity Administrative expenses