Flexible Budget and Variances:
NOTE: It is expected that this problem will be completed using an Excel spreadsheet using formulas. Please see the Excel Tutorial that is available under the course home tab.
The Grant Company had developed a Static Budget for 2005 for one of its major departments. By the end of the year, the owner was quite pleased since he knew that sales were running above the projections. He was confident that 2005 was going to be the best year ever for this important part of the company!
When the 2005 financial statements were completed, it was discovered that the gross profit for the department was lower than had been originally projected. But sales were up! What happened?
You have been hired to determine why 2005 was not a “banner year”! You have been supplied with the original Static Budget and the actual results as follows:
|
|
Static Budget
|
Actual 2005 Figures
|
|
Units Sold
|
10,000
|
11,000
|
|
Revenue
|
$1,000,000
|
$1,078,000
|
|
Material
|
300,000
|
350,900
|
|
Labor
|
250,000
|
247,500
|
|
Variable Overhead
|
150,000
|
194,700
|
|
Fixed Overhead
|
200,000
|
195,000
|
|
Gross Profit
|
$100,000
|
$89,900
|
You are also provided with the following information regarding standards and actual results:
|
|
Standard
|
Actual
|
|
Selling price
|
$100 per unit
|
$98 per unit
|
|
Cost of materials
|
$15 per foot
|
$14.50 per foot
|
|
Materials per unit
|
2 feet per unit
|
2.2 feet per unit
|
|
Labor rate
|
$25 per hour
|
$30 per hour
|
|
Labor usage
|
1 hour per unit
|
0.75 hours per unit
|
|
Variable overhead rate
|
$30 per machine hour
|
$29.50 per machine hour
|
|
Variable overhead usage
|
0.5 machine hours per unit
|
0.6 machine hours per unit
|
Assume purchase quantities equal usage for materials.
Required:
Part 1: Prepare a flexible budget for 2005
Part 2: Calculate the following variances
a. Sales volume variance (use CM)
b. Sales price variance
c. Material price variance
d. Material usage variance
e. Labor rate variance
f. Labor efficiency variance
g. Variable overhead spending variance
h. Variable overhead efficiency variance
i. Fixed overhead spending variance
j. Fixed overhead volume variance (Unit Volume is application base)
Part 3: Prepare a short report indicating possible reasons for the lower than expected gross profit.