Data for both Question 1 & Question 2: uFone is preparng 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 smart phones per month. Assume that all units produced in a month are also sold in that month. uFone's unit of production/sales is a phone (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. Monthly Budget Data: Selling price per unit: Raw Material Costs Packaging Costs Salary and Wages Costs OT for production over 800,000 units Fringe Benefits $800.00 per each $400.00 per each $100.00 per each $3,500,000 per month $250.00 per each 50% of Wages and OT $150.00 per each Electricity Waste and Other Costs $50.00 per each $5,000,000 per month $700,000 per month Rent Costs Insurance Costs $1,500,000 per month Depreciation Costs Question 1: Prepare a flexible budget in Excel for Super Soda. a) Show the flexible budget based on 500,000 units (phones) produced. b) Show the flexible budget based on 1,000,000 units (phones) produced -this scenario will have OT. c) Show the flexible budget cost formula(s) for Super Soda. (See Textbook & Announcements for hints.) d) Explain the benefit of flexible budgets and when they should be used. Additional Data for Question 2: Data for Question 2 February Actual January Actual Production 650,500 925,000 Sales $520,400,000 $735,000,000 Ingredient Costs Packaging Costs Salary and Wages Costs $255,555,555 $65,100,000 $3,450,000 $60,000 $368,435,000 $92,530,000 $3,550,000 $31,540,000 Overtime (OT) Fringe Benefits Electricity $1,760,000 $98,100,000 $17,580,000 $138,540,000 Waste and Other Costs $34,565,000 $5,000,000 $700,000 $1,500,000 $45,750,000 $5,000,000 $720,000 $1,500,000 Rent Costs Insurance Costs Depreciation Costs Question 2: The months of January and February 2020 are complete, and uFone 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 Provide possible explanations for the favorable and unfavorable variances for each line item. Be sure c) that each explanation is logical for whether the variance is favorable or unfavorable. Note: Because you can't 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. d) Was net operating income better or worse than budget? Write a letter to the CFO providing recommendations for Super Soda. What should they change in order to improve performance? Support your letter with evidence Data for both Question 1 & Question 2: uFone is preparng 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 smart phones per month. Assume that all units produced in a month are also sold in that month. uFone's unit of production/sales is a phone (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. Monthly Budget Data: Selling price per unit: Raw Material Costs Packaging Costs Salary and Wages Costs OT for production over 800,000 units Fringe Benefits $800.00 per each $400.00 per each $100.00 per each $3,500,000 per month $250.00 per each 50% of Wages and OT $150.00 per each Electricity Waste and Other Costs $50.00 per each $5,000,000 per month $700,000 per month Rent Costs Insurance Costs $1,500,000 per month Depreciation Costs Question 1: Prepare a flexible budget in Excel for Super Soda. a) Show the flexible budget based on 500,000 units (phones) produced. b) Show the flexible budget based on 1,000,000 units (phones) produced -this scenario will have OT. c) Show the flexible budget cost formula(s) for Super Soda. (See Textbook & Announcements for hints.) d) Explain the benefit of flexible budgets and when they should be used. Additional Data for Question 2: Data for Question 2 February Actual January Actual Production 650,500 925,000 Sales $520,400,000 $735,000,000 Ingredient Costs Packaging Costs Salary and Wages Costs $255,555,555 $65,100,000 $3,450,000 $60,000 $368,435,000 $92,530,000 $3,550,000 $31,540,000 Overtime (OT) Fringe Benefits Electricity $1,760,000 $98,100,000 $17,580,000 $138,540,000 Waste and Other Costs $34,565,000 $5,000,000 $700,000 $1,500,000 $45,750,000 $5,000,000 $720,000 $1,500,000 Rent Costs Insurance Costs Depreciation Costs Question 2: The months of January and February 2020 are complete, and uFone 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 Provide possible explanations for the favorable and unfavorable variances for each line item. Be sure c) that each explanation is logical for whether the variance is favorable or unfavorable. Note: Because you can't 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. d) Was net operating income better or worse than budget? Write a letter to the CFO providing recommendations for Super Soda. What should they change in order to improve performance? Support your letter with evidence