M6 P
.xlsx
keyboard_arrow_up
School
West Virginia University *
*We aren’t endorsed by this school
Course
201
Subject
Industrial Engineering
Date
Apr 25, 2024
Type
xlsx
Pages
2
Uploaded by BarristerGoldfinchMaster1053 on coursehero.com
M6 Project Projects are your own work. It is academic dishonesty to work with or submit a file prepared by another individual. This project requires you to perform incremental analyzes for three different management decisions - outsource, special order, and elimination decisions.
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. M6 Project Tab
a.
Enter information in the blue and yellow cells only. Do not copy and paste from one cell to another. Any attempts to modify or copy/paste will cause your project to be ungradable and result in a 0.
b.
Complete everything in yellow highlight following these instructions and those provided in the next tab; your score will be based on your input in these yellow cells.
Do not change rows or colums on this spreadsheet. Do not round.
Use cell references and formulas when appropriate to do so; most cells require the use of links/formulas; very few are typed, hard-coded numbers
3. Submitting Completed Projects via eCampus (DO NOT EMAIL YOUR COMPLETED PROJECT)
a.
Save your file.
b.
Verify that you linked your cells and used the SUM and ROUND functions correctly. 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 M6 Project tab and, in the blue cell (C2), 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.
M6 Project
Enter eCampus Username:
ecd00002 Outsourcing
Dough, Re, Mi Inc. sells many different types of cookie dough. The company is deciding whether to continue making its own dough or to outsource. If the company outsources, they will eliminate all of the variable overhead and 30% of the fixed manufacturing overhead, but will incur shipping costs. Use the information below to determine whether Dough, Re, Mi Inc. should outsource or not.
Data
Units
Per unit
Relevant?
Cell Formulas
Sales price
per unit
5,050 $ 100.00 No
Direct materials
per unit
22.00 Yes
Direct labor
per unit
18.00 Yes
Variable manufacturing overhead
per unit
14.00 Yes
Fixed manufacturing overhead (MOH) :
per month
25.00 Avoidable fixed MOH
per month
7.50 Yes
=G15*0.3
Unavoidable fixed MOH
per month
17.50 No
=G15*0.7
Sales commissions
per unit
3.00 No
Advertising costs
per month
1.80 No
Purchase price of outsourced product
per unit
65.00 Yes
Shipping costs of outsourced product
per unit
1.00 Yes
Costs per unit Incremental analysis
Manufacture Outsource Manufacture Outsource Variable costs
Enter "=0" in the cell for any cost not relevant to the decision.
Direct materials
$ 22.00 $ - =G12
=0
Direct labor
18.00 - =G13
=0
Variable manufacturing overhead
14.00 - =G14
=0
Purchase price
- 65.00 =0
=G20
Shipping costs
- 1.00 =0
=G21
Sales commissions
- - =0
=0
Total variable costs
54.00 66.00 =SUM(E27:E32)
=SUM(G27:G32)
Fixed costs
Fixed manufacturing overhead
7.50 - =G16
=0
Advertising
- - =0
=0
Total fixed costs
7.50 - =SUM(E35:E36)
=0
Incremental cost
61.50 66.00 =E33+E37
=G33+G37
If Dough, Re, Mi outsources, what would its incremental profit (loss) per unit equal?
$ (4.50)
=E38-G38
If Dough, Re, Mi outsources, what would its incremental profit (loss) given the expected units above?
$ (22,725)
=I40*E11
Should Dough, Re, Mi Inc. manufacture or outsource its dough?
Manufacture
Special Order
Pete's Pizza makes the best pizzas in town. Based on Pete's current volume, the price and cost breakdown is outlined below. The local high school has asked Pete to be their sole pizza provider for a large event and has offered to order 500 pizzas at a special price.
Assuming Pete has the capacity to produce these pizzas, identify which of the following items are relevant in deciding whether to accept this special order.
Per unit
Relevant?
Normal sales price
$ 12.00 No
Special price
9.50 Yes
Direct materials
4.00 Yes
Direct labor
3.00 Yes
Variable overhead
0.50 Yes
Fixed overhead
3.00 No
Should Pete accept the order in either of the following scenarios?
A. Pete has capacity to produce these pizzas with no additional investments.
B. Pete would need to rent a piece of equipment to accommodate the order. The rent would cost Pete: $ 2,000 For each scenario below, enter the relevant amounts of accepting this special order of 500 pizzas in total (not per unit):
Scenario
A
B
Enter "=0" in the cell for any cost not relevant to the decision.
A
B
Number of pizzas ordered
500 500 Sales revenue
$ 4,750 $ 4,750 =C51*E65
=C51*G65
Variable costs
Direct materials
2,000 2,000 =C52*E65
=G65*C52
Direct labor
1,500 1,500 =C53*E65
=G65*C53
Variable overhead
250 250 =C54*E65
=G65*C54
Fixed overhead
- 2,000 =0
=I60
Total costs
3,750 5,750 =SUM(E68:E71)
=SUM(G68:G71)
Expected change in operating income
$ 1,000 $ (1,000)
=E66-E72
=G66-G72
Should Pete accept the order?
Yes
No
Product Line Elimination
Quiet Feet Inc. produces three different types of shoes. Complete the below contribution margin income statement for each product line. Allocate total fixed costs to each shoe type based on units as a percent of total units (i.e. use units sold as the cost driver).
Boots
Sneakers
Sandals
Total
Relevant?
Boots
Sneakers
Sandals
Total
Sales (units)
6,000 15,000 4,000 25,000 Price
$ 5.00 $ 25.00 $ 5.00 Sales revenue
$ 30,000 $ 375,000 $ 20,000 $ 425,000 =C82*C83 =E82*E83 =G82*G83 =SUM(C85,E85,G85) Variable costs
36,000 315,000 24,000 375,000 Contribution margin
(6,000)
60,000 (4,000)
50,000 Yes
=C85-C86 =E85-E86 =G85-G86 =SUM(C87,E87,G87) Fixed costs (all allocated)
26,664 66,660 17,776 111,100 No
=(C82/I82)*I88 =(E82/I82)*I88 =(G82/I82)*I88 Operating income
$ (32,664)
$ (6,660)
$ (21,776)
$ (61,100)
=C85-C86-C88 =E85-E86-E88 =G85-G86-G88 =SUM(C89,E89,G89) What is operating income if Quiet Feet Inc. stopped selling Boots?
$ (28,436)
=I89-C89
What is operating income if Quiet Feet Inc. stopped selling Sneakers?
(54,440)
=I89-E89
What is operating income if Quiet Feet Inc. stopped selling Sandals?
(39,324)
=I89-G89
What product line (if any) should Quiet Feet Inc. stop producing?
Sneakers
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