ABC Soda Company is preparing their 2020 budget. They want to prepare a flexible budget for use each month. They estimate sales/production will be between 1,700,000 and 2,200,000 cases of soda per month. Assume that all units produced in a month are also sold in that month. ABC Soda Company unit of production/sales is a case. 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 ABC Soda Company:

a) Show the flexible budget based on 1,700,000 units (cases) produced.

b) Show the flexible budget based on 2,200,000 units (cases) produced this scenario will have OT.

c) Show the flexible budget cost formula(s) for ABC Soda Company:

d) 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 ABC Soda Company wants to compare their FLEXIBLE budget to their actual results. Actual results are shown in the table above. a) 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. b) Compare actual results to budgeted results (Favorable or Unfavorable with amounts) for each month and each category. c) 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. d) Was net operating income better or worse than budget? Write a letter to the CFO providing recommendations for ABC Soda Company. What should they change in order order to improve performance?

Monthly Budget Data: Selling price of Cases of Soda: Ingredient Costs Packaging Costs Salary and Wages Costs OT for production over 2,000,000 units Fringe Benefits Electricity Waste and Other Costs Rent Costs Insurance Costs Depreciation Costs $3.50 per case $0.75 per case $0.30 per case $400,000 per month $0.60 per case $0.50 of Wages and OT $0.25 per case $0.15 per case $600,000 per month $80,000 per month $150,000 per month Additional Data for Question 2: Data for Question 2: Production Sales Ingredient Costs Packaging Costs Salary and Wages Costs OT for production Fringe Benefits Electricity Waste and Other Costs Rent Costs Insurance Costs Depreciation Costs January Actual February Actual 1,850,500 2,185,700 cases $6,486,850 $7,593,400 $1,343,000 $1,599,470 $540,000 $660,000 $395,000 $407,000 $15,000 $435,000 $205,000 $421,000 $465,000 $545,500 $299,750 $382,500 $600,000 $600,000 $80,000 $82,000 $180,000 $180,000