The Munchkin Theater is a nonprofit organization devoted to staging plays for children. The theater has a very small fulltime 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: 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: Calculate the amount of the flexible budget variances in column F using 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 static budget variance in cell M10. What is the amount of the static budget variance for actors' and directors' wages? b. Copy your formula from cell M10 into cells M11 through M17. What is the amount of the static budget variance for administrative expenses (cell M17)? c. Use the AutoSum feature to calculate the total static budget variance in cell M18. What is the total static budget 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. Calculate the amount of the sales volume variances in column I using 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 sales volume variance in cell N10. What is the amount of the sales volume variance for actors' and directors' wages? b. Copy your formula from cell N10 into cells N11 through N17. What is the amount of the sales volume variance for administrative expenses (cell N17)? c. Use the AutoSum feature to calculate the total sales volume variance in cell N18. What is the total sales volume variance? d. Using the Absolute Value function, input the absolute values of the amounts in cells N10 through N18 into cells I10 through I18. e. Hide column N. The sales volume variances in column I are influenced by two cost drivers-number of productions and number of performances. Therefore: a. In cell I21 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 I21? b. In cell I22 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 I22? c. Create IF-THEN statements in cells J21 and J22 that properly label your variances in cells I21 and I22 as favorable (F) or unfavorable (U). Using 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? The Munchkin Theater Planning Budget For the Year Ended December 31 Planning Budget Number of productions (q1) Number of performances (q2) Number of performances (q2) Cost Formulas The Munchkin Theater Flexible Budget Performance Report For the Year Ended December 31 Activity Variances: By Cost Driver Portion due to number of productions Portion due to number of performances