Develop a workbook in excel with the stipulations

Jul 20th, 2013
HelloWorld
Category:
Business & Finance
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

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.


Tutor Answer

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

Studypool has helped 1,244,100 students

8 Reviews


Summary
Quality
Communication
On Time
Value
Five Star Tutor
Dec 8th, 2016
" Outstanding Job!!!! "
kpcutie
Nov 25th, 2016
" Excellent job "
Joemoe
Nov 16th, 2016
" <3 it, thanks for saving me time. "
Hemapathy
Nov 11th, 2016
" all I can say is wow very fast work, great work thanks "
pmallory
Nov 2nd, 2016
" Totally impressed with results!! :-) "
kevin12622
Oct 20th, 2016
" Goes above and beyond expectations ! "
kiln82
Oct 11th, 2016
" awesome work thanks "
likeplum4
Sep 27th, 2016
" Excellent work as usual "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1823 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