Excel Analytics $09-01($Static$)$ Flexible Budget Performance Analysis $[$LO$2,$ LO$4]$

Skip to question

$[$The 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. 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 Formulas

Per Production Per Performance Fixed

Actors$$ and directors$$ wages $ $2,400$

Stagehands$$ wages $ $450$

Ticket booth personnel and ushers$$ wages $ $180$

Scenery, costumes, and props $ $8,600$

Theater hall rent $ $750$

Printed programs $ $175$

Publicity $ $2,600$

Administrative expenses $ $1,296$ $ $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,600$

Required:

$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 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 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 incorrect.$)$

check all that apply

$$ Actors$$ and directors$$ wages

$$ Stagehands$$ wages

$$ Ticket booth personnel and ushers$$ wages

$$ Scenery, costumes, and props

$$ Theater hall rent

$$ Printed programs

$$ Publicity

$$ Administrative expenses