Question

Peter’s Pretzels is preparing their 2018 budget. They want to prepare a flexible budget for use each month. They estimate sales/production will be between 700,000 and 900,000 boxes of pretzels per month.

Monthly budget data                     

Selling price of pretzels $5.00    per box

Ingredient cost $1.50    per box

Packaging cost   $0.50    per box

Salary and wages cost    $270,000.00 per month

Overtime for production over 800,000 units         $1.00    per box

Fringe benefits 50% of wages and overtime

Electricity             $0.40    per box

Waste and other costs   $0.20    per box

Rent cost             $225,000.00      per month

Insurance cost   $40,000.00        per month

Depreciation cost             $120,000.00      per month

Question 1: Prepare a flexible budget in Excel for Peter’s Pretzels.

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

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

c) Show the flexible budget cost formulas for Peter’s Pretzels.

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

Answer 1

Answer a, b & c.
Flexible Budget
	Cost Formula	700,000 Boxes	900,000 Boxes
Sales	$5q	3,500,000.00	4,500,000.00
Less: Expenses
Ingredient Cost	$1.50q	1,050,000.00	1,350,000.00
Packaging Cost	$0.50q	350,000.00	450,000.00
Salaries & Wages Cost	$270,000 + $1 (q-800,000)	270,000.00	370,000.00
Fringe Benefit	($270,000 + $1 (q-800,000)) X 50%	135,000.00	185,000.00
Electricity	$0.40q	280,000.00	360,000.00
Waste and other costs	$0.20q	140,000.00	180,000.00
Rent Cost	$225,000	225,000.00	225,000.00
Insurance Cost	$40,000	40,000.00	40,000.00
Depreciation Cost	$120,000	120,000.00	120,000.00
Total Costs		2,610,000.00	3,280,000.00
Answer d.
Advantages of Fleible Budget:
1. Usage in Variable Cost Environment.
2. Performance Measurement
3. Budgeting Efficiency.