Develop a workbook in excel with the stipulations

Develop a workbook in excel with the stipulations.

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

columns.

7. Use Data Sections wherever appropriate. If a Data Section is used, it should be labeled as

such.

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,

$37,500 U.

To test your model, use the following information for the manufacture of 60,000 dashboards

during July:

  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.

Develop a workbook in excel with the stipulations

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.