The Worksheet should include a tab for the Original Data, Test Data and the Chart.
VARIANCE ANALYSIS Workbook
Bonnell Manufacturing makes dashboards for cars. During June, 40,000 dashboards were
manufactured with standard costs and actual costs for direct materials, direct labor, and factory
overhead as follows:
Standard Costs Actual Costs
Direct materials 10,000 pounds@$9 10,600 pounds @ $10.50
Direct labor 20,000 hours@$13 20,600 hours @ $12.50
Factory overhead Rates per direct labor hour, based on normal
capacity of 30,000 labor hours:
Variable cost $5.00 Variable cost $84,000
Fixed cost $3.75 Fixed cost $49,000
You have been asked to develop a worksheet that will calculate the quantity variance, price
variance, total direct materials cost variance, time variance, rate variance, total direct labor cost variance, volume variance, controllable variance, and total factory overhead cost variance (file name VARIANCE). Use the information above as input for the Data Section of your worksheet.
Review the Model-Building Problem Checklist to ensure that your worksheet is complete.
MODEL-BUILDING PROBLEM CHECKLIST
Before submitting any model-building solution to your instructor, review the following list to
ensure that your worksheet is presented in a clear, concise manner.
1. Include the name of the company, the name of the statement or schedule presented, and
the date (e.g. 2009, 4th Quarter, June). The date should be in an unprotected cell.
2. Use cell references in your formulas wherever possible.
3. Format all cells properly. Place dollar signs ($) at the top of all amount columns and below
all subtotal rules.
4. Use zero decimal places whenever decimal accuracy is not required. Generally, if the
problem statement does not include cents, your answer will not require cents.
5. Vary column widths to fit the data presented.
6. Place titles at the top of all data columns (one exception is on financial statements where
the statement heading is sufficient). Titles should be centered or right justified in the
7. Use Data Sections wherever appropriate. If a Data Section is used, it should be labeled as
8. Use file protection where appropriate on the worksheet. Unprotect the cells where
changeable data or labels are to be entered.
9. Use upper- and lowercase letters as appropriate. Generally, uppercase letters are needed
as the first letter in all headings and titles.
10. Use the =ROUND function to eliminate rounding discrepancies.
11. Include a chart graph
Check figure: Factory overhead volume variance,
To test your model, use the following information for the manufacture of 60,000 dashboards
Standard Costs Actual Costs
Direct materials 16,000 pounds @ $11 14,800 pounds @ $11.75
Direct labor 30,000 hours@ $15 28,400 hours@ $16.25
Factory overhead Rates per direct labor
hour, based on normal capacity of 30,000 labor hours:
Variable cost $5.00 Variable cost $133,000
Fixed cost $3.75 Fixed cost $98,000
Check figure: Factory overhead volume variance, $0.