M6PLeppert
xlsx
keyboard_arrow_up
School
West Virginia University *
*We aren’t endorsed by this school
Course
201
Subject
Industrial Engineering
Date
Dec 6, 2023
Type
xlsx
Pages
2
Uploaded by LieutenantAntelope3590
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:
ael00019
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
4,850
$
108.00
No
Direct materials
per unit
24.00
Yes
Direct labor
per unit
18.00
Yes
Variable manufacturing overhead
per unit
14.00
Yes
Fixed manufacturing overhead (MOH) :
per month
27.00
Avoidable fixed MOH
per month
8.10
Yes
=G15*0.3
Unavoidable fixed MOH
per month
18.90
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
59.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
$
24.00
$
-
=G12
=0
Direct labor
18.00
-
=G13
=0
Variable manufacturing overhead
14.00
-
=G14
=0
Purchase price
-
59.00
=0
=G20
Shipping costs
-
1.00
=0
=G21
Sales commissions
-
-
=0
=0
Total variable costs
56.00
60.00
=SUM(E27:E32)
=SUM(G27:G32)
Fixed costs
Fixed manufacturing overhead
8.10
-
=G16
=0
Advertising
-
-
=0
=0
Total fixed costs
8.10
-
=E35+E36
=0
Incremental cost
64.10
60.00
=SUM(E37,E33)
=G33+G37
If Dough, Re, Mi outsources, what would its incremental profit (loss) per unit equal?
$
4.10
=E38-G38
If Dough, Re, Mi outsources, what would its incremental profit (loss) given the expected units above?
$
19,885
=E11*I40
Should Dough, Re, Mi Inc. manufacture or outsource its dough?
Outsource
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.40
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,160
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,700
$
4,700
=C51*E65
=C51*G65
Variable costs
Direct materials
2,000
2,000
=C52*E65
=C52*G65
Direct labor
1,500
1,500
=C53*E65
=C53*G65
Variable overhead
250
250
=C54*E65
=C54*G65
Fixed overhead
3
2,163
=C55
=C55+I60
Total costs
3,753
5,913
=SUM(E68:E71)
=SUM(G68:G71)
Expected change in operating income
$
947
$
(1,213)
=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
$
50.00
$
25.00
$
5.00
Sales revenue
$
300,000
$
375,000
$
20,000
$
695,000
=C82*C83
=E82*E83
=G82*G83
=SUM(C85+E85+G85)
Variable costs
180,000
315,000
24,000
519,000
Contribution margin
120,000
60,000
(4,000)
176,000
Yes
=C85-C86
=E85-E86
=G85-G86
=I85-I86
Fixed costs (all allocated)
25,608
64,020
17,072
106,700
No
=(C82/I82)*I88
=(E82/I82)*I88
=(G82/I82)*I88
Operating income
$
94,392
$
(4,020)
$
(21,072)
$
69,300
=C87-C88
=E87-E88
=G87-G88
=SUM(C89,E89,G89)
What is operating income if Quiet Feet Inc. stopped selling Boots?
$
(25,092)
=E89+G89
What is operating income if Quiet Feet Inc. stopped selling Sneakers?
73,320
=C89+G89
What is operating income if Quiet Feet Inc. stopped selling Sandals?
90,372
=C89+E89
What product line (if any) should Quiet Feet Inc. stop producing?
Sandals
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