M4PArvon
xlsx
keyboard_arrow_up
School
West Virginia University *
*We aren’t endorsed by this school
Course
201
Subject
Industrial Engineering
Date
Feb 20, 2024
Type
xlsx
Pages
2
Uploaded by MateWillpower16431
M4 Project Instructions
Projects are your own work. It is academic dishonesty to work with or submit a file prepared by another individual. In this project, you will be calculating the predetermined overhead rate for a collegiate-themed quilt manufacturer that uses job costing.
The manufacturer is currently working on a job to produce WVU-themed quilts. You will use the predetermined overhead rate to calculate the cost of this specific job and other required amounts as indicated in the M4 Project Tab.
Instructions:
1. Saving and Entering eCampus Username
a.
Open the Project file using Excel for Microsoft 365 previously downloaded onto your device. You will receive a 0 if you use any other spreadsheet program, such as Numbers, Google Sheets, etc.
b.
Be careful not to include a space before or after your username. Failure to enter your eCampus Username properly makes your submission ungradable and result in a 0.
c.
Save your Project file on your computer. Do not save your project on the Cloud, if you try to submit your project from the Cloud, your file will be ungradable and will result in a 0. When naming your file, do not
include any punctuation, symbols, or dashes in your file name; doing so may cause your file to be ungradable and will result in a 0. Recommendation: You may want to create a folder for this class on your Desktop and save all your course projects there for easy access.
2. M4 Project Tab
a.
Enter information in the blue and yellow cells only. b.
DON'T add or delete any rows or columns and DON'T round as doing so will result in a zero.
c.
Complete everything in yellow highlight following the instructions in red; your score will be based on your input in these yellow cells
You MUST use cell references and formulas for all amounts when applicable; there should be very few typed or hard-coded numbers
Summing contiguous amounts: You MUST use the SUM function and reference the cells being added using a colon between the first and last cell in the range (i.e. =SUM(B6:B7) or =SUM(B8:B11))
3. Submitting Completed Projects via eCampus (DO NOT EMAIL YOUR COMPLETED PROJECT)
a.
Save your file on your PC (not in the Cloud).
b.
Verify that you used correct cell linking and SUM function requirements. Review each and verify that you have followed ALL the above instructions before submitting your project. Remember, if you changed any of your links or formulas, you must save your file again. You are now ready to submit your project.
c.
In eCampus, go to Assignments/Projects… and click on the related project link.
d.
Locate the Assignment Submission section on the new screen.
e.
Click Browse My Computer in the Attach Files box (surrounded by a dashed border).
f.
Locate your saved Excel file, select your file by clicking on it.
g.
Click Open and select Submit.
h.
Review your submission to ensure your file uploaded correctly and you can see the answers you entered. Submissions uploaded incorrectly before the due date and corrected after the due date are considered late and will not be accepted.
i.
No late projects will be accepted. See Makeup Policy provided in the course syllabus.
j.
DO NOT correspond using the Write Submission or Add Comments dialog box as any such correspondence will NOT be viewed.
Go to the M4 Project tab and, in the blue cell, type your eCampus Username, such as npl0001. Do not
capitalize any letters, do not
include “@mix.wvu.edu”, and do not
use your student number.
M4 Project
Enter eCampus Username
ba00040
MANUFACTURING SIMULATION:
WVU Bookstore Special Order (Actual results)
Supervisor
Yards of material used:
Number of quilts made:
13
Measurer
(uses the Measuring Machine)
White:
14
Cutter
(uses the Cutting Machine)
Blue: 21
Assembler
(uses the Sewing Machine)
Yellow:
25
A.
The following production estimates were made for the coming year:
Total quilts to be produced
20,000
Total manufacturing hours available per laborer
2,000
Total direct laborers
3
Total direct labor hours
6,000
The following cost estimates were available at the beginning of the year.
Use these estimates to determine total estimated manufacturing overhead during the year:
Amount
Estimate for Year
Production supervisor's salary per week (use 52 weeks in a year)
$ 1,000 $ 52,000.00 =H24*52
Rent per month
985 11,820.00 =H25*12
Utilities expense per month
589 7,068.00 =H26*12
Cost of Measuring Machine with a useful life of 10 years
20,000 2,000.00 =H27/10
Cost of Cutting Machine with a useful life of 5 years
9,800 1,960.00 =H28/5
Cost of Sewing Machine with a useful life of 5 years
19,600 3,920.00 =H29/5
Cost of indirect materials per Assembler's hours
0.53 1,060.00 =H30*H18
Total Estimated Manufacturing Overhead
$ 27,828.00 =SUM(I25:I30)
B. Calculate the Predetermined Overhead (PDOH) rate using direct labor hours (DLH) as the cost driver during the year to allocate manufacturing
overhead to jobs and Work In Process Inventory:
PDOH=
$ 4.64 per DLH
=I31/H20
C.
Complete the following job cost sheet for the WVU themed quilts:
Job Cost Sheet
Direct Materials
Yards
Cost per Yard
Total
White material
14
$ 2.00 $ 28.00 =J7
=F41*G41
Blue material
21
4.00 84.00 =J8
=F42*G42
Yellow material
25
4.00 100.00 =J9
=F43*G43
$ 212.00 =SUM(H41:H43)
Direct Labor
Hours
Rate per Hr.
Total
Measuring labor
4.00
$ 14.00 $ 56.00 =F48*G48
Cutting labor
4.00
12.00 48.00 =F49*G49
Assembly labor
4.00
16.00 64.00 =F50*G50
Total
12.00
$ 168.00 =SUM(H48:H50)
Manufacturing Overhead
Predetermined OH Rate
DLH Used
Total
$ 4.64 6,000.00 $ 27,828.00 =I35
=H20
=D55*G55
Total Job Cost
$ 28,208.00 =SUM(H44,H51,H55)
Number of Quilts Made
13 =N6
Cost per Quilt
$ 2,169.85 =H57/H58
D.
Answer the following questions:
1.
$ 3,037.78 =SUM(H59,H59*40%)
Use your answers from #1 and the job cost sheet above to answer questions 2-5:
2.
$ 36,453.42 =J63*12
3.
$ 212.00 =H44
4.
$ 212.00 =SUM(H44*13,-(H44*12))
5.
$ 9,113.35 =SUM(J65-H57,(J63-H59))
EMPLOYEES: (one each)
If you decide to charge the bookstore cost plus 40%
for the quilts, the selling price for one
quilt is:
If the WVU Bookstore purchased all but one of the quilts you made, total sales revenue would be:
If the WVU Bookstore purchased all but one
of the quilts you made, the cost of goods sold would be:
If the WVU Bookstore purchased all but one
of the quilts you made, finished goods inventory would be:
If the WVU Bookstore purchased all but one
of the quilts you made, your total gross profit would be:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help