Calculating budget variances using standard costing approach

AC556
Week 6 Problem
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.