Balmer Corporations master (static) budget for the year is shown below: Required 1. During the year the company actually manufactured and sold 42,000 units of

Balmer Corporation’s master (static) budget for the year is shown below:

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 spreadsheet 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 controlpurposes?

Sales (50,000 units) S1,600,000 Cost of goods sold: Direct materials $150,000 Direct labor 450,000 Overhead (Variable overhead applied at 40% of direct labor cost) 240,000 840,000 S 760,000 Gross profit 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 51,000 Depreciation (all fixed) 450,000 S 310,000 Opera ting income