# Develop a workbook in excel with the stipulations

Jul 20th, 2013
HelloWorld
Category:
Price: \$25 USD

Question description

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

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.

(Top Tutor) Daniel C.
(997)
School: University of Virginia

Studypool has helped 1,244,100 students

## Review from our student for this Answer

FratBro23
Jul 22nd, 2013
"Excellent!"

1829 tutors are online

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors