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: 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: 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: Click here to download the Excel template that you will use to create the Planning Budget and Flexible Budget Performance Report Once you complete the Planning Budget and Flexible Budget Performance Report, refer to your work to answer the fortheoming questions. You will upload your final file in Part 2. Click here for a brief tutorial on IF Statements in Excel Click here for a brief tutorial on Conditional Formatting in Excel. Go to the "Variance Analysis" 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 E17. What is the total amount of actors' and directors' wages in your planning budget? b. Copy your formula from cell E10 into cells E11 through E17. What is the total amount of administrative 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? Complete this question by entering your answers in the tabs below. Go to the "Variance Analysis" tab within the Excel template for the remainder of the requirements. Prepare a flexible budget in column H of the spreadsheet using the following three-step process. a. Create a formula for actors' and directors' wages in cell H10. Make sure your formula 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 flexible budget? b. Copy your formula from cell H10 into cells H11 through H17. What is the total amount of administrative expenses in your flexible budget (cell H17)? c. Use the AutoSum feature to calculate the total amount of expenses in your flexible budget (cell H18 ). What is this total amount? Complete this question by entering your answers in the tabs below. Calculate the amount of the spending 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 spending variance in cell M10. What is the amount of the spending variance for actors' and directors' wages? b. Copy your formula from cell M10 into cells M11 through M17. What is the amount of the spending variance for administrative expenses (cell M17)? c. Use the AutoSum feature to calculate the total spending variance in cell M18. What is the total spending variance? d. Using the Absolute Value function, input the absolute values of the amounts in cells M10 through M18 into cells F10 through F18. Complete this question by entering your answers in the tabs below. Insert a favorable (F) or unfavorable (U) label in column G for each spending variance shown in column F using the following two-step process: a. Create an IF-THEN statement in cell G10 that properly labels the actors' and directors' wages spending variance as favorable or unfavorable. (Hint: Your If-Then statement should be focused on the amounts in cells H10 and E10.) What label appears for this variance, F or U? b. Copy your IF-THEN statement from cell G10 into cells G11 through G18. What label appears for the administrative expense spending variance, F or U ? Complete this question by entering your answers in the tabs below. Using Conditional Formatting, highlight all spending variances that varied by an absolute value of $1,000 or more. How many of the spending variances are highlighted? 6. Using Conditional Formatting, highlight all spending variances that vary from the flexlble 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 spending 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.) ? Actors' and directors' wages ? Stagehands' woges Ticket booth personnel and ushers' wages Scenery, costumes, and props ? Theater hall rent ? Printed programs ? Publicity ? Administrative expenses Complete this question by entering your answers in the tabs below. Review the "M-Budgeting Assumptions" and "F-Budgeting Assumptions" tabs: a. What is the only difference between these two tabs (Hint: Focus on cell A7)? b. Which of these two tabs corresponds with the master budget? The flexible budget? Complete this question by entering your answers in the tabs below. Go to the "F-Sales Budget" tab: a. How are the sales for July (cell H8) being calculated? b. How are the cash collections for August (cell I15) being calculated? Required information \begin{tabular}{|l|l|l|l|l|l|l|l} Req1 & Req2 & Req3 & Req4 & R Req 5 & Req 6 & Req 7 \end{tabular} Go to the "F-Merchandise Purchases Yudget" tab: a. How are the required merchandise purchases for April (cell E10) being calculated? b. How are the cash disbursements for merchandise purchases for May (cell F17) being calculated? 3-a. Budgeted cost of goods sold - desired ending merchandise inventory = total needs + beginning merchandise inventory = required merchandise purchases Budgeted cost of goods sold + beginning merchandise inventory = total needs - desired ending merchandise inventory = required merchandise purchases Budgeted cost of goods sold - beginning merchandise inventory = total needs + desired ending merchandise inventory = required merchandise purchases Budgeted cost of goods sold + desired ending merchandise inventory = total needs - beginning merchandise inventory = required merchandise purchases 3-b. The cash disbursements related to the prior month's (April's) purchases plus the cash disbursements related to the current month's (May's) purchases. OThe cash disbursements related to the current month's (May's) purchases minus the cash disbursements related to the prior month's (April's) purchases. OThe cash disbursements related to the next month's (June's) purchases minus the cash disbursements related to the current month's (May's) purchases. OThe cash disbursements related to the current month's (May's) purchases minus the cash disbursements related to the next month's (June's) purchases. Go to the "F-Selling \& Admin Budget" tab: a. How are the total selling and administrative expenses for September (cell J16) being calculated? b. How are the cash disbursements for selling and administrative expenses for November (cell L18) being calculated? Go to the "F-Cash Budget" tab: a. How is the excess (deficiency) of cash available over disbursements for March (cell D14) being calculated? b. How is the ending cash balance for June (cell G20) being calculated? Go to the "F-Cash Budget" tab: a. How is the excess (deficiency) of cash avallable over disbursements for March (cell D14) being calculated? b. How is the ending cash balance for June (cell G20) being calculated? Go to the "F-Budgeted Income Statements" tab: a. How is the cost of goods sold for February (cell C8) being calculated? b. How is the net income for April (cell E13) being calculated? 7-a. November's expected sales (based on the actual level of activity) multiplied by the budgeted percentzge of sales that are collected in the month of sale (20%). November's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month after sale (80%). December's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month of sale (20%). December's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month after sale (80%). 7-b. The prior month's (September's) expected cost of goods sold (based on the budgeted lovel of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory" (25\%). The prior month's (September's) expected cost of goods sold (based on the actual level of activity) multiplied by the percentage of next month's cost of goods sold in ending merchandise inventory (25%). The next month's (November's) expected cost of goods sold (based on the actual level of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory" (25\%). October's expected cost of goods sold (based on the actual level of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory' (25%). 7-c. Pebruary's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase* (15\%). February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase- (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of (1) Required information percentage or next montn s cost or gooas soia in enaing mercnanaise inventory ( 0%). October's expected cost of goods sold (based on the actual level of activity) multiplied by the "percerhage of next month's cost of goods sold in ending merchandise inventory" (25\%). 7-c. February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase" (15%). February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase" (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase" (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase" (15%). 7-d. The beginning retained earnings plus the expected net income (based on the actual level of activity). The beginning retained earnings plus the expected net income (based on the budgeted level of activity). The beginning retained earnings minus the expected net income (based on the actual level of activity). The beginning retained earnings minus the expected net income (based on the budgeted level of activity). 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: 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: 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: Click here to download the Excel template that you will use to create the Planning Budget and Flexible Budget Performance Report Once you complete the Planning Budget and Flexible Budget Performance Report, refer to your work to answer the fortheoming questions. You will upload your final file in Part 2. Click here for a brief tutorial on IF Statements in Excel Click here for a brief tutorial on Conditional Formatting in Excel. Go to the "Variance Analysis" 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 E17. What is the total amount of actors' and directors' wages in your planning budget? b. Copy your formula from cell E10 into cells E11 through E17. What is the total amount of administrative 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? Complete this question by entering your answers in the tabs below. Go to the "Variance Analysis" tab within the Excel template for the remainder of the requirements. Prepare a flexible budget in column H of the spreadsheet using the following three-step process. a. Create a formula for actors' and directors' wages in cell H10. Make sure your formula 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 flexible budget? b. Copy your formula from cell H10 into cells H11 through H17. What is the total amount of administrative expenses in your flexible budget (cell H17)? c. Use the AutoSum feature to calculate the total amount of expenses in your flexible budget (cell H18 ). What is this total amount? Complete this question by entering your answers in the tabs below. Calculate the amount of the spending 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 spending variance in cell M10. What is the amount of the spending variance for actors' and directors' wages? b. Copy your formula from cell M10 into cells M11 through M17. What is the amount of the spending variance for administrative expenses (cell M17)? c. Use the AutoSum feature to calculate the total spending variance in cell M18. What is the total spending variance? d. Using the Absolute Value function, input the absolute values of the amounts in cells M10 through M18 into cells F10 through F18. Complete this question by entering your answers in the tabs below. Insert a favorable (F) or unfavorable (U) label in column G for each spending variance shown in column F using the following two-step process: a. Create an IF-THEN statement in cell G10 that properly labels the actors' and directors' wages spending variance as favorable or unfavorable. (Hint: Your If-Then statement should be focused on the amounts in cells H10 and E10.) What label appears for this variance, F or U? b. Copy your IF-THEN statement from cell G10 into cells G11 through G18. What label appears for the administrative expense spending variance, F or U ? Complete this question by entering your answers in the tabs below. Using Conditional Formatting, highlight all spending variances that varied by an absolute value of $1,000 or more. How many of the spending variances are highlighted? 6. Using Conditional Formatting, highlight all spending variances that vary from the flexlble 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 spending 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.) ? Actors' and directors' wages ? Stagehands' woges Ticket booth personnel and ushers' wages Scenery, costumes, and props ? Theater hall rent ? Printed programs ? Publicity ? Administrative expenses Complete this question by entering your answers in the tabs below. Review the "M-Budgeting Assumptions" and "F-Budgeting Assumptions" tabs: a. What is the only difference between these two tabs (Hint: Focus on cell A7)? b. Which of these two tabs corresponds with the master budget? The flexible budget? Complete this question by entering your answers in the tabs below. Go to the "F-Sales Budget" tab: a. How are the sales for July (cell H8) being calculated? b. How are the cash collections for August (cell I15) being calculated? Required information \begin{tabular}{|l|l|l|l|l|l|l|l} Req1 & Req2 & Req3 & Req4 & R Req 5 & Req 6 & Req 7 \end{tabular} Go to the "F-Merchandise Purchases Yudget" tab: a. How are the required merchandise purchases for April (cell E10) being calculated? b. How are the cash disbursements for merchandise purchases for May (cell F17) being calculated? 3-a. Budgeted cost of goods sold - desired ending merchandise inventory = total needs + beginning merchandise inventory = required merchandise purchases Budgeted cost of goods sold + beginning merchandise inventory = total needs - desired ending merchandise inventory = required merchandise purchases Budgeted cost of goods sold - beginning merchandise inventory = total needs + desired ending merchandise inventory = required merchandise purchases Budgeted cost of goods sold + desired ending merchandise inventory = total needs - beginning merchandise inventory = required merchandise purchases 3-b. The cash disbursements related to the prior month's (April's) purchases plus the cash disbursements related to the current month's (May's) purchases. OThe cash disbursements related to the current month's (May's) purchases minus the cash disbursements related to the prior month's (April's) purchases. OThe cash disbursements related to the next month's (June's) purchases minus the cash disbursements related to the current month's (May's) purchases. OThe cash disbursements related to the current month's (May's) purchases minus the cash disbursements related to the next month's (June's) purchases. Go to the "F-Selling \& Admin Budget" tab: a. How are the total selling and administrative expenses for September (cell J16) being calculated? b. How are the cash disbursements for selling and administrative expenses for November (cell L18) being calculated? Go to the "F-Cash Budget" tab: a. How is the excess (deficiency) of cash available over disbursements for March (cell D14) being calculated? b. How is the ending cash balance for June (cell G20) being calculated? Go to the "F-Cash Budget" tab: a. How is the excess (deficiency) of cash avallable over disbursements for March (cell D14) being calculated? b. How is the ending cash balance for June (cell G20) being calculated? Go to the "F-Budgeted Income Statements" tab: a. How is the cost of goods sold for February (cell C8) being calculated? b. How is the net income for April (cell E13) being calculated? 7-a. November's expected sales (based on the actual level of activity) multiplied by the budgeted percentzge of sales that are collected in the month of sale (20%). November's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month after sale (80%). December's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month of sale (20%). December's expected sales (based on the actual level of activity) multiplied by the budgeted percentage of sales that are collected in the month after sale (80%). 7-b. The prior month's (September's) expected cost of goods sold (based on the budgeted lovel of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory" (25\%). The prior month's (September's) expected cost of goods sold (based on the actual level of activity) multiplied by the percentage of next month's cost of goods sold in ending merchandise inventory (25%). The next month's (November's) expected cost of goods sold (based on the actual level of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory" (25\%). October's expected cost of goods sold (based on the actual level of activity) multiplied by the "percentage of next month's cost of goods sold in ending merchandise inventory' (25%). 7-c. Pebruary's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase* (15\%). February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase- (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of (1) Required information percentage or next montn s cost or gooas soia in enaing mercnanaise inventory ( 0%). October's expected cost of goods sold (based on the actual level of activity) multiplied by the "percerhage of next month's cost of goods sold in ending merchandise inventory" (25\%). 7-c. February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase" (15%). February's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase" (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month after purchase" (85%). January's expected merchandise purchases (based on the actual level of activity) multiplied by the "percentage of merchandise purchases that are paid for in the month of purchase" (15%). 7-d. The beginning retained earnings plus the expected net income (based on the actual level of activity). The beginning retained earnings plus the expected net income (based on the budgeted level of activity). The beginning retained earnings minus the expected net income (based on the actual level of activity). The beginning retained earnings minus the expected net income (based on the budgeted level of activity)