Question

Prepare a flexible budget in Excel for Vroom-Vroom. (36 points)

1. Show the flexible budget for December in Contribution Margin Income Statement format.
2. Compare December’s flexible budget to December’s actual results. Specify which line items are favorable or unfavorable and how much.
3. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for December. Provide potential explanations
4. Show the flexible budget for January in Contribution Margin Income Statement format.
5. Compare January’s flexible budget to January’s actual results. Specify which line items are favorable or unfavorable and how much.
6. For Ingredient Costs and Packaging Costs, break out the Price and Volume Variances for January. Provide potential explanations for each one.

Budget for 200,000 Units
Units per Month	200,000
	Per Unit	Total
Selling Price	$59	$        11,800,000
Variable Costs:
Raw Material	$24	$4,800,000
Packaging Cost	$15	$3,000,000
Electricity	$4	$800,000
Waste and other costs	$6	$1,200,000
Total Variable Costs	$49	$9,800,000
Contribution Margin	$10	$2,000,000
Fixed Costs:
Salary & Wages		$350,000
Fringe Benefits		$175,000
Rent Cost		$750,000
Insurance Cost		$50,000
Depreciation Cost		$250,000
Total Fixed Costs		$1,575,000
Net Income	$2.12	$425,000

	December Actual
Units	275,000
Revenue	$        16,375,000
Variable Costs:
Raw Material	$          6,450,000
Packaging Cost	$          4,150,000
Electricity	$          1,095,000
Waste and other Costs	$          1,775,000
Total Variable Costs	$        13,470,000
Contribution Margin	$          2,905,000
Fixed Costs:
Salary & Wages	$             375,000
Fringe Benefits	$             175,000
Rent Cost	$             750,000
Insurance Cost	$               50,000
Depriciation Cost	$             250,000
Total Fixed Costs	$          1,600,000
Net Income	$          1,305,000

January Actual
150,000
$      8,845,000
$      3,450,000
$      2,250,000
$         575,000
$      1,025,000
$      4,195,000
$      4,650,000
$         345,000
$         175,000
$         750,000
$           60,000
$         250,000
$      1,580,000
$      3,070,000

Answer 1

a.

Flexible Budget For December
Activity Level			275,000 units
Sales			$ 16,225,000
Variable Costs
Raw materials	$ 24	$ 6,600,000
Packaging	15	4,125,000
Electricity	4	1,100,000
Waste and other costs	6	1,650,000
Total Variable Costs	49		13,475,000
Contribution Margin			2,750,000
Fixed Costs
Salary and wages		350,000
Fringe benefits		175,000
Rent		750,000
Insurance		50,000
Depreciation		250,000
Total Fixed Costs			1,575,000
Net income			$ 1,175,000

b.

Flexible Budget Performance Report For December
	Flexible Budget	Actual Results	Variances
Activity Level	275,000 units	275,000 units
Sales	$ 16,225,000	$ 16,375,000	$ 150,000 F
Variable Costs
Raw materials	$ 6,600,000	6,450,000	150,000 F
Packaging	4,125,000	4,150,000	25,000 U
Electricity	1,100,000	1,095,000	5,000 F
Waste and other costs	1,650,000	1,775,000	125,000 U
Total Variable Costs	13,475,000	13,470,000	5,000 F
Contribution Margin	2,750,000	2,905,000	155,000 F
Fixed Costs
Salary and wages	350,000	375,000	25,000 U
Fringe benefits	175,000	175,000	None
Rent	750,000	750,000	None
Insurance	50,000	50,000	None
Depreciation	250,000	250,000	None
Total Fixed Costs	1,575,000	1,600,000	25,000 U
Net Income	$ 1,175,000	$ 1,305,000	$ 130,000 F

c.

Flexible Budget Performance Report For December
	Actual Results	Price Variances	Flexible Budget	Volume Variances	Planning Budget
Activity Level	275,000 units		275,000 units		200,000 units
Raw materials	$ 6,450,000	$ 150,000 F	$ 6,600,000	$ 1,800,000 U	$ 4,800,000
Packaging	4,150,000	25,000 U	4,125,000	1,125,000 U	3,000,000

Explanations:

• Volume variances are due to actual volume being higher than budgeted volume.
• Raw materials/ packaging price variance could be due to purchase of materials at a price higher than budgeted. This may happen because the standards have not been updated, or there is a sudden spike the cost of materials, or for higher transportation cost or not being able to avail of trade discounts.
• Raw materials/ packaging price variance could also result from adverse usage variance. An adverse usage variance would result if the actual quantity of ingredients used exceed the standard quantity allowed for actual output. This could happen due to substandard materials, poor production supervision, untrained workforce etc.