Module 5 Project Feedback.xlsx
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
5
Uploaded by BailiffFlag12217
Project Instructions
Projects are your own work. It is academic dishonesty to work with or submit a file prepared by another individual. 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.
2.
Worksheet Instructions
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.
Amounts: Always enter amounts preceded by an equal sign (=). For example, a $1,000 increase should be entered as =1000 (do not use a plus sign (+)). For negative amounts (decreases), use a minus sign (-) after the equal sign like =-1000. c.
Complete everything in yellow highlight following the instructions in red; your score will be based on your input in these yellow cells
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
Units: use Excel formula =roundup to the next whole unit; use the round function to round answers [=Roundup(cell ref,0)]
Unit dollar amounts: use Excel formula =round to 2 digits or decimal places; [=Round(cell ref,2)]
All other dollar amounts: use Excel formula =round to nearest whole dollar; [=Round(cell ref,0)]
DON'T add or delete any rows or columns as doing so will result in a zero
Subtotals and Totals
- 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))
- Non-contiguous amounts: You MUST use the SUM function and reference each cell being added using a comma between each (i.e. =SUM(B23,B28) or =SUM(B41,B43,B47))
- When entering your SUM function, if a line item amount is generally included in the subtotal or total, you must include it in your formula even if its value is zero. 3.
Submitting Completed Projects via eCampus (DO NOT EMAIL YOUR COMPLETED PROJECT)
a.
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.
i.
DO NOT use the Write Submission or Add Comments features as any such correspondence will NOT be viewed.
Go to the M5 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.
Recommendation: You may want to create a folder for this class on your Desktop and save your project there. Do not include any symbols, ', or dashes in your file name. Do not
save your project on the Cloud; if you try to submit your project from the Cloud, your file will be ungradable and result in a 0.
Save your file. Do
not
include any symbols, ', or dashes in your file name. Do not
save your project on the Cloud; if you try to submit your project from the Cloud, your file will be ungradable and result in a 0.
Review your submission to ensure your file uploaded correctly. Submissions uploaded incorrectly before the due date and corrected after the due date are LATE and will not be accepted
.
r.
M5 Project
Enter eCampus Username:
mfp00011 A. Calculate the missing amounts in MVP's Income Statement presented for financial reporting purposes below:
MVP, Inc.
Income Statement
For the Year Ended December 31, 2020
Sales $ 375,000 Cost of Goods Sold:
Direct Materials (variable)
100,000 Direct Labor (variable)
20,400 Manufacturing Overhead ($40,000 is fixed)
90,500 Cost of Goods Sold
210,900 Gross Profit
164,100 =ROUND(SUM(D8,-D13),0)
Operating Expenses:
Sales Commissions (variable)
12,500 Shipping (variable)
2,500 Advertising (fixed)
10,200 Billing (of which $10,000 is fixed)
10,250 Sales and Administrative Salaries (fixed)
109,000 Total Operating Expenses
144,450 Operating Income (Loss)
$ 19,650 =ROUND(SUM(D14,-D21),0)
Additional information:
Sales Price per unit:
$15.00 Plant capacity (relevant range) is 60,000 units per year
All variable expenses in the company vary in terms of units sold
There was no change in inventory levels between the beginning and end of the year
B. Using the information above, calculate the missing amounts below:
a. Units of product MVP sold in 2020:
25,000 =ROUNDUP(D8/C25,0)
Variable cost per unit for the following mixed costs:
b. Variable Manufacturing Overhead per unit: $ 2.02 =ROUND((C12-40000)/C31,2)
c. Variable Billing per unit: $ 0.01 =ROUND((C19-10000)/C31,2)
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
C. Using MVP's 2020 income statement above, prepare a contribution margin income statement below (all amounts should be a formula/link to the information
Total Units
Amount
25,000
Per Unit
=ROUNDUP(C31,0)
Sales
$ 375,000 $ 15.00 =ROUND(D8,0)
=ROUND(C39/$C$38,2)
Variable Costs:
Direct Materials
100,000 4.00 =ROUND(C10,0)
=ROUND(C41/$C$38,2)
Direct Labor
20,400 0.82 =ROUND(C11,0)
=ROUND(C42/$C$38,2)
Variable Manufacturing Overhead
50,500 2.02 =ROUND((C12-40000),0)
=ROUND(C43/$C$38,2)
Sales Commissions
12,500 0.50 =ROUND(C16,0)
=ROUND(C44/$C$38,2)
Shipping
2,500 0.10 =ROUND(C17,0)
=ROUND(C45/$C$38,2)
Variable Billing
250 0.01 =ROUND((C19-10000),0)
=ROUND(C46/$C$38,2)
Total Variable Costs
186,150 7.45 =ROUND(SUM(C41:C46),0)
=ROUND(C47/$C$38,2)
Contribution Margin
188,850 7.55 =ROUND(SUM(C39,-C47),0)
=ROUND(C48/$C$38,2)
Fixed Costs:
Fixed Manufacturing Overhead
40,000 =ROUND(40000,0)
Advertising
10,200 =ROUND(C18,0)
Sales and Admin. Salaries
109,000 =ROUND(C20,0)
Fixed Billing
10,000 Total Fixed Costs
169,200 =ROUND(SUM(C50:C53),0)
Net Operating Income (Loss)
$ 19,650 =ROUND(SUM(C48-C54),0)
D. Calculate MVP's current breakeven point in both units (rounding up to nearest whole unit) and dollars (rounded to nearest whole dollar):
Units:
22,411 =ROUNDUP(C54/D48,0)
Dollars (use the Contribution Margin Ratio to calculate):
$ 336,159 =ROUND(C54/(D48/D39),0)
E. Redo MVP's Contribution Margin Income Statement using the Vice President of Sales (VP) suggestions and projected increase in sales volume below (all amou
Reduce selling price by:
5.00%
Increase advertising costs by:
$ 5,100 Projected sales volume increase:
25.00%
Total Units
Amount
31,250
Per Unit
=ROUNDUP(C31*(1+C64),0)
Sales
$ 445,313 $ 14.25 =ROUND($C$67*D68,0)
=ROUND(C25*(1-C62),2)
Variable Costs:
Direct Materials
125,000 4.00 =ROUND($C$67*D70,0)
=ROUND(D41,2)
Direct Labor
25,625 0.82 =ROUND($C$67*D71,0)
=ROUND(D42,2)
Variable Manufacturing Overhead
63,125 2.02 =ROUND($C$67*D72,0)
=ROUND(D43,2)
Sales Commissions
15,625 0.50 =ROUND($C$67*D73,0)
=ROUND(D44,2)
Shipping
3,125 0.10 =ROUND($C$67*D74,0)
=ROUND(D45,2)
Variable Billing
313 0.01 =ROUND($C$67*D75,0)
=ROUND(D46,2)
Total Variable Costs
232,813 7.45 =SUM(C70:C75)
=ROUND(D47,2)
Contribution Margin
212,500 6.80 =SUM(C68,-C76)
=SUM(D68,-D76)
Fixed Costs:
Fixed Manufacturing Overhead
40,000 =ROUND(C50,0)
Advertising
15,300 =ROUND(SUM(C51,C63),0)
Sales and Admin. Salaries
109,000 =ROUND(C52,0)
Fixed Billing
10,000 =ROUND(C53,0)
Total Fixed Costs
174,300 =ROUND(SUM(C79:C82),0)
Net Operating Income (Loss)
$ 38,200 =ROUND(SUM(C77,-C83),0)
F. Using the budgeted contribution margin income statement in part E. above, calculate the following:
a. Breakeven in units:
25,633 =ROUNDUP(C83/D77,0)
b. Operating Leverage Multiplier:
5.56 =ROUND(C77/C84,2)
Given a sales volume increase of 8%, operating income will increase by:
c. Percent:
44.48% =ROUND(C88*8%,4)
d. Dollars:
$ 16,991 =ROUND(C90*C84,0)