Required information Excel Analytics 09-01 (Static) Flexible Budget Performance Analysis [L02, L04] [The foiiowing information applies to the questions displayed below] The Munchkin Theater is a nonprofit 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 ve 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 Formulas Per Per Production Performance Fixed Actors' and directors' wages $2,400 Stagehands' wages $ 450 Ticket booth personnel and ushers' wages 5 130 Scenery, costumes, and props $8,600 Theater hall rent 5 T50 Printed programs 5 175 Publicity $2,600 Administrative expenses $1,296 5 72 $32,400 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,500 Performance Report. Once you complete the Planning Budget and Flexible Budget Performance Report, refer to your work to answer the forthcoming questions. You will upload your final file in Part 2. Click here for a brieftutorial on IF Statements in Excel. Click here for a brieftutorial on Conditional Formatting in Excel. Excel Analytics 09-01 (Static) Part 1 Required: 1. 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 E11 What is the total amount of actors' and directors' wages in your planning budget? b. Copy yourformula from cell E10 into cells E11 through E11 What is the total amount ofadministrative 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? 2. Go to the \"Performance Report" tab within the Excel template for the remainder ofthe requirements. Prepare a flexible budget in column H of the spreadsheet using the following three-step proces s. a. Create a formula for actors' and directors' wages in cell H10. Make sure yourformula 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 exible budget? b. Copy yourformula from cell H10 into cells H11 through H11 What is the total amount of administrative expenses in your exible budget {cell H1?:I? c. Use the AutoSum feature to calculate the total amount of expenses in your exible budget {cell H18). What is this total amount? 3. Calculate the amount of the flexible budget variances in column F using the following ve-step process (Do not worry about labeling the variances as U or F at this point): 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 yourformula from cell M10 into cells M11 through M12. 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? cl. 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. 4. insert a favorable (F) or unfavorable {U} label in column G for each static budget variance shown in column F using the following two- step process: a. Create an lF-THEN statement in cell 610 that properly labels the actors' and directors' wages static budget variance as favorable or unfavorable. (Hint: Your lf-Then statement should be focused on the amounts in cells H10 and E10.) What label appears for this variance, F or U? h. Copy your lF-THEN statement from cell G10 into cells G11 through 618. What label appears for the administrative expense static budget variance. F or U? 5. Calculate the amount ofthe sales volume variances in column I using the following ve-step process {Do not worry about labeling the variances as U or F at this point}: 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 N11 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? 11. 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. 6. Insert a favorable {F} or unfavorable (U) label in column J for each sales volume variance shown in column i using the following two- step process: a. Create an iF-THEN statement in cell J10 that properly labels the actors' and directors' wages sales volume variance as favorable or unfavorable. (Hint: Your If-Then statement should be focused on the amounts in cells K10 and H10.) What label appears forthis variance, F or U? h. Copy your iF-THEN statement from cell J10 into cells J11 through J18. What label appears for the administrative expense sales volume variance, F or U? I The sales volume variances in column l are influenced by two cost driversnumber of productions and number of performances. Therefore: a. In cell I21 create a formula that quanties the portion ofthe overall sales volume variance that is caused solely by the fact that The Munchkin Theatre actually put on four productions instead ofthe planned number offive productions. What is the amount in cell i21? b. In cell |22 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 lF-THEN statements in cells J21 and J22 that properly label your variances in cells i21 and I22 as favorable {F} or unfavorable {U}. 8. 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? 9. Using Conditional Formatting. highlight all static budget variances that vary from the flexible budget by an absolute value of 5% or more. (Hint: Within the conditional formatting clrop clown 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 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 incorrectJ E Actors' and directors' wages E Stagehands' wages a Ticket booth personnel and ushers' wages E Scenery, costumes, and props E Theater hall rent a Printed programs E Publicity E Administrative expenses