fThe following 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. Throuah 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 vear and evaluatinc actua expenses at the end of the vear. The theater expects to put on five different productions with a tota 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 eiaht expenses that will be included in vour Plannina budaet: ( show in excel worksheet)

Questions:

3.Calculate the amount of the static budget variances in column F using the following five-step process (Do not worry about labelinc 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 directorswages? 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 budaet variance in cell M18. What is the total static budaet variance?

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?

A28 Q fx A B C D E F G H J K M N O P Q The Munchkin Theater N Flexible Budget Performance Report For the Year Ended December 31 AW 5 Actual Spending Flexible Activity Planning Results Variances Budget Variances Budget 6 Number of productions (q1) 5 Number of performances(q2) 64 64 60 8 Cost Formulas Per Performanc 9 Production Fixed 10 Actors' and directors' wages $ 2,400 $ 159,200 11 Stagehands' wages 450 $ 28,600 12 Ticket booth personnel and ushers' wages 180 $ 12,300 13 Scenery, costumes, and props $ 8,600 $ 39,300 14 Theater hall rent 750 $ 49,600 15 Printed programs $ 175 $ 10,950 16 Publicity 2,600 $ 12,000 17 Administrative expenses 1,296 $ 72 $ 32,400 $ 41.650 18 Total $ 12,496 $ 4.027 $ 32.400 $ 353.600 19 20 Activity Variances: By Cost Driver 21 Portion due to number of productions 22 Portion due to number of performances 23 24 25 K > IF Statements Conditional Formatting EA 9-1, Planning Budget Performance Report +A1 @ fx A B C D E F G H K L M N Conditional Formatting: Conditional Formatting is a tool within Microsoft Excel that allows you to format/highlight cells based on a certain criteria MI A W N When utilizing Conditional Formatting you should proceed as follows: 6 1. The first step is to choose and then highlight the cell or cells you wish to format based on the criterial. You may 7 choose entire columns or rows or a subset of cells. 8 9 2. Under the "Home" tab click on "Conditional Formatting" in the "Styles Section" 10 3. From that drop down choose from either "Highlight Cell Rules" or "Top/Bottom Rules" 11 If you choose "Highlight Cell Rules" you will be given a number options including "Less than", "Greater Than", 12 "Equal to" and so on. Chose the option that mirrors your intent. 13 i. Once you do this, a prompt will appear where you can input the value you that you using as your 14 benchmark or reference and then you can choose the format that the cells which meet this criteria 15 will appear. 16 ii. After this click "OK". 17 b. if you choose "Top/Bottom Rules" you will be given options such as "Bottom 10 Items", "Top 10 Items", "Top 18 19 10%", "Above Average" and so on. 20 i. Once you choose and option you can modify the percentage or number of items as you like then you 21 can choose the format the cells which meet this criteria will appear. 22 ii. After this click "OK". 23 4. You should now be able to scroll through the data selected in step one and see which data meets the criteria based 24 on which cells are now formatted. 25 26 27 28 29 30 31 32 K > IF Statements Conditional Formatting EA 9-1, Planning Budget Performance Report + 11 4