Prepare a flexible budget for the actual volume of units

Question

Case Questions

The difficulty in this case stems from the fact that the revenue (and therefore cost of goods sold) has three different cost drivers (instead of the usual 2, price and quantity). Therefore, I propose that this case is significantly easier if you separate your study of the Revenue (and resulting COGS and GM) and the Expenses.

Let's start with the Expenses:

Requirement 1:

Complete the following table indicating the planning assumptions and cost formula for each expense:Hint: You will need to read the case information (especially the "Annual Operating Budget" section on page 2) to determine which costs are fixed/variable. No costs are mixed.

	Fixed or Variable?	Fixed Cost per Year	Variable cost per unit sold	Cost Formula
Fulfillment
Marketing
Technology & content
General & administrative
Depreciation /amortization

Requirement 2: Complete the following table, which requires computing the flexible budget, activity variance, and spending variance for each expense.

	Actual	Spending Variance		Flex	Activity Variance		Planning
Quantity of units sold:
Fulfillment	$          76,557,800						$          74,400,000
Marketing	$          25,115,800						$          23,212,800
Technology & content	$          23,950,000						$          23,600,000
General & administrative	$          18,750,000						$          19,000,000
Depreciation /amortization	$          31,000,000						$          31,000,000
Total Expenses	$        175,373,600			$        172,852,800			$        171,212,800

Requirement 3

In the problems from the textbook, Revenue is computed as Price X Quantity. The complication with revenue (or "twist") in this case is that because there is more than one type of product (books, music, DVD/video, toys & electronics), there are actually 3 variables that can create variances in revenues. To see this for yourself, complete the following table:

	Actual Volume	Actual Sales Volume %	Actual Revenue per Unit	Actual Revenue per Category		Plan Volume	Plan Sales Volume %	Plan Revenue per Unit	Plan Revenue per Category
Books	10,000,000		$    17.50			9,000,000		$    18.00
Music	11,000,000		$    14.00			12,000,000		$    13.00
DVD/Video	8,300,000		$    14.00			7,980,000		$    16.00
Toys	750,000		$    29.00			600,000		$    34.00
Electronics	210,000		$    43.00			180,000		$    40.00
Total	30,260,000					29,760,000

Requirement 4

This means we need two additional flexible budgets for revenue instead of just one like we normally do. Let's do each flexible budget separately. First, prepare a flexible budget for the actual volume of units sold but using the sales mix from the planning budget. Complete the following table:

	Flex Volume	Plan Sales Volume %	Plan Revenue per Unit	Flex Revenue per Category
Books
Music
DVD/Video
Toys
Electronics
Total	30,260,000

Requirement 5

Next, prepare a flexible budget for the actual volume of units sold but using the actual sales mix. The only way this will differ from the actual revenue is due to changes between actual price and planning price. Complete the following table:

Requirement 6

We are now ready to put it all together. Complete the following table:

	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 1
	Plan	Volume Variance	Mix Variance	Price Variance	COGS Variance	Fulfillment Variance	Marketing Variance	Actual
Revenue	$      473,280,000							$    475,980,000
Cost of goods sold	$      359,692,800							$    364,124,700
Gross profit	$      113,587,200							$    111,855,300
Fulfillment	$        74,400,000							$      76,557,800
Marketing	$        23,212,800							$      25,115,800
Technology and Content	$        23,600,000							$      23,950,000
General & Administrative	$        19,000,000							$      18,750,000
Depreciation & Amortization	$        31,000,000							$      31,000,000
Loss from operations	$  (57,625,600)							$  (63,518,300)
Variance								$  (100,000)
Net loss per item sold	$  (1.94)							$  (2.10)
Variance per item sold								$  0.00

Hints:
Step 2: Use the revenue from Requirement 4; use the planning COGS cost ratio of 76%; use all other expenses from the planning budget.
Step 3: Use the revenue from Requirement 5; use the planning COGS cost ratio of 76%; use all other expenses from the planning budget.
Step 4: Use the actual revenue, planning COGS cost ratio of 76%; use all other expenses from the planning budget.
Step 5 Use the actual COGS.
Step 6: Change the planned Fulfillment expense to actual Fulfillment expense.
Step 7: Change the planned Marketing expense to actual Marketing expense.

Requirement 7: Supplemental extension questions

Use your knowledge and analysis of the table prepared in Requirement 6 to answer the following questions:
a. What was the actual average selling price per item?
b. What was the actual fulfillment cost per unit?
c. What was the actual marketing cost per unit?
d. What was the actual COGS cost ratio?

Requirement 8:

The most interesting thing about this case is that the company sold 500,000 more units than planned - yet incurred a larger loss than planned!!Prepare 5 key comments that Mark Dibbs may want to share with the senior management team in their next strategy meeting that explain how this could occur. Note that there is no right/wrong answers here but I am looking for answers that: (a) are clear, (b) apply insights from the budget data.

As a thought exercise, you also may wish to attempt to fully reconcile planned profit to actual profit in your own words.