This should be completed in an Excel file. Use proper Contribution Margin Income Statement format... This should be completed in an Excel file. Use proper Contribution Margin Income Statement formatting (see below). Each question refers to the same initial data. Treat each question separately. Ignore income taxes. Assume no beginning or ending inventories. Calculations and backup should be completed and submitted in Excel. Leave any formulas used in the Excel cells (do not hard code or type in results); this allows for partial credit if possible.

Data for both Question 1 & Question 2:

Tatzee is preparing their 2020 budget. They want to prepare a flexible budget for use each month. They estimate sales/production will be between 500,000 and 1,000,000 frozen tv dinners per month. Assume that all units produced in a month are also sold in that month. Tatzee's unit of production/sales is a tv dinner (unit/each).

Note on Overtime (OT): If production is below the level specified, there is no overtime needed. If production is above the level specified, there will be overtime costs only for the units produced above that level.

Question 1: Prepare a flexible budget in Excel for Tatzee.

1. Show the flexible budget based on 500,000 units (tv dinner) produced.
2. Show the flexible budget based on 1,000,000 units (tv dinners) produced this scenario will have OT.
3. Show the flexible budget cost formula(s) for Tatzee. (See Textbook & Announcements for hints.)
4. Explain the benefit of flexible budgets and when they should be used.

Additional Data for Question 2:

Question 2: The months of January and February 2020 are complete, and Tatzee wants to compare their FLEXIBLE budget to their actual results. Actual results are shown in the table above.

1. Create the flexible budgets based on actual units produced for each month Jan & Feb.

Note: These flexible budgets should be based on the actual units produced. These are not the same numbers as in Question 1 because actual production is different than those original scenarios.

2. Compare actual results to budgeted results (Favorable or Unfavorable with amounts) for each month and each category.
3. Provide possible explanations for the favorable and unfavorable variances for each line item. Be sure that each explanation is logical for whether the variance is favorable or unfavorable.

Note: Because you cant actually talk to managers/supervisors/employees, you will be making up these explanations. They can be as fantastical as you like, but must be directionally correct.

4. Was net operating income better or worse than budget? Write a letter to the CFO providing recommendations for Tatzee. What should they change in order to improve performance? Support your letter with evidence.

Bonus (Extra Credit) up to 5 points: Tatzee has been trying to determine how their analysis changes if they use a static budget instead of a flexible budget.

1. Compare the budget created in Question 1 part a for 500,000 units to the actual January production (presuming that the 500,000 unit budget is a static budget). Break out the Volume and Price Variances for each line item and identify them.

Volume Sales Variable Costs (listed) Variable Costs Total Contribution Margin Fixed Costs (listed) Fixed Costs Total Operating Income Data for Question 2: Production Sales Ingredient Costs Packaging Costs Salary and Wages Costs Overtime (OT) Fringe Benefits Electricity Waste and Other Costs Rent Costs Insurance Costs Depreciation Costs January Actual 650,500 $520,400,000 $255,555,555 $65,100,000 $3,450,000 $60,000 $1,760,000 $98,100,000 $34,565,000 $5,000,000 $700,000 $1,500,000 February Actual 925,000 $735,000,000 $368,435,000 $92,530,000 $3,550,000 $31,540,000 $17,580,000 $138,540,000 $45,750,000 $5,000,000 $720,000 $1,500,000