Charlies Chocolates is preparing their 2018 budget. They want to prepare a flexible budget for use each month. They estimate sales/production will be between 500,000 and 600,000 boxes of specialty chocolates per month.

Question 1: Prepare a flexible budget in Excel for Charlies Chocolates.

a) Show the flexible budget based on 500,000 units (boxes) produced.

b) Show the flexible budget based on 600,000 units (boxes) produced.

c) Show the flexible budget cost formulas for Charlies Chocolates.

d) Explain the benefit of flexible budgets and when they should be used.

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

a) Show the flexible budget based on actual units produced for each month Jan & Feb.

b) Compare actual results to budgeted results (Favorable or Unfavorable with amounts) for each month.

c) Provide possible explanations for the favorable and unfavorable variances. Was net operating income better or worse than budget? Provide recommendations for Charlies Chocolates.

Calculations and backup should be completed and submitted in Excel. Use proper Income Statement formatting. Analysis can either be typed into cells in Excel (formatted to be easily legible) or typed into a text box in Excel. Students are expected to work independently.

Monthly Budget Data for Question 1: Selling price of chocolates: Ingredient Costs Packaging Costs Salary and Wages Costs OT for production over 550,000 units Fringe Benefits Electricity Waste and Other Costs Rent Costs Insurance Costs Depreciation Costs $15.00 per box $4.00 per box $1.50 per box $280,000 per month $6.00 per box 50% of wages and OT $0.80 per box $0.30 per box $325,000 per month $60,000 per month $140,000 per month