Generating a Flexible Budget Balmer Corporation's master (static) budget for the year is shown below:
Sales (50,000 units) Cost of goods sold: Direct materials
|
$150,000
|
$1,600,000
|
Direct labor
|
450,000
|
|
Overhead (Variable overhead
|
|
|
applied at 40% of direct labor cost)
|
240,000
|
840,000
|
Gross profit
|
|
$ 760,000
|
Selling expenses:
|
|
|
Sales commissions (all variable)
|
$160,000
|
|
Rent (all fixed)
|
40,000
|
|
Insurance (all fixed)
|
30,000
|
|
General expenses:
|
|
|
Salaries (all fixed)
|
92,000
|
|
Rent (all fixed)
|
77,000
|
|
Depreciation (all fixed)
|
51,000
|
450,000
|
Operating income
|
|
$ 310,000
|
Required
1. During the year the company actually manufactured and sold 42,000 units of product. Prepare an Excel spreadsheet that contains a flexible budget for this level of output.
2. Suppose, however, that the actual level of output had been 52,000 units of output. Rerun your spread- sheet to generate a flexible budget for this level of output.
3. Of what relevance is the notion of "relevant range" when preparing pro forma budgets or a flexible budget for control purposes?