Question
Course ProjectM4 Working Ahead Cost-Volume-Profit (CVP) Analysis Review the Course Project Guidelines. In the last module, you completed your estimate of cash flows for your
Course ProjectM4 Working Ahead Cost-Volume-Profit (CVP) Analysis Review the Course Project Guidelines. In the last module, you completed your estimate of cash flows for your project. In this module, you will calculate the break-even point for the project and the expected financial returns. Open the Cost-Volume-Profit spreadsheet that you have been working in and calculate the break-even point.of your proposed project. (Access the CVP template if you have not yet begun this work) The project must use a 6.5% cost of capital and a tax rate of 25%. Complete IRR (Internal Rate of Return) and NPV (Net Present Value) for the project. Make sure you show your Excel formulas or provide calculations so your instructor can review your work. You should have also considered key points of any intangible benefits or costs associated with the project and begun supplementing your pro forma statement with sufficient background information to enable a prospective investor to decide if your company is worth investing in. Submit this worksheet to the M4 Course Project Submissions Area by the due date assigned. Your grade for this work will be included in your course project final submission in Module 5. The completed worksheet will be worth 220 points or 22% of your grade. This is how your spreadsheet will be graded in Module 5.
This is the teacher's feedback and my grading rubric.
These are the worked that I did.
MS6010 M4 Project Submission Rubric No points are actually earned in this submission as all points for the project (470) are applied in M5 grading. 220 points from the spreadsheet review in M4 and 250 points from the slide presentation in M5 This review is to help you understand what is missing or what needs correction. Grade earned on worksheets if you do not make changes before end of M5 TaitoS Needs CorrectionNot Available Available or Yes Notes tem CVP or Breakeven Worksheet or tab) Inflows were over a 5 year period Outflows were over a 5 year Ouflows had various types of outflows detailed De Depreciation was added back to inflows after tax calculation Project used a 6.5% Discount rate Project used a 25% tax rate Net Present Value or NPV Internal Rate of Return or IRR provided You must show the detail of all inflows and outflows over the 5 year period. Items must be calculated correctly. Please see notes on your worksheet Depreciation is not correct not correct rate not correct rate not correct NPV not correct. Use NPVC) formula in Excel IRR not correct. Use IRR() formula in Excel ation was Public company financial statements were provided Module 4 Grading Rubric Assignment Components By end of Module 2, complete the Project Approval Input and answer the symbol. Explain Proficient Max Points 30 PointsComments, if any on Mod 2 Please see class progress for M2 Input Approval Quiz Excel Worksheet Requirements Identify the various revenues, expenses, costs, expenses, and cash flows.p implement the Calculate the CVP or break-even point for the project. Calculate NPV and IRR. Provides the numeric viability of the project in Please see module 4 grading area for my initial comments on your Excel Presentation. used 180 15 25 220 70 15 are complete a are complete a what you provided in Module 4 90 K6 1 Breakeven Analysis (valueVariable costs are only those costs that change in equilevant terms Fixed costs are only those costs that stay the same even when unit sales changes. Not all of these items in this ist will be fixed for your particular case. Each one needs to be Property Insurance Holdings, Inc. when sales units change. Not all of these items in this list will be fixed 2 4 6 for your particular case. Each one needs to be evaluated. If you use CGS, you will not use the other three. Change titles if needed. Enter Cost Description Variable Costs Fixed Costs Variable Costs Mixed Costs 8 Cost of Goods Sold y Inventory 10 Raw Materials 11 Direct Labor (Includes Payroll Taxes) 12 13 Fixed Costs 14 Salaries (includes payroll taxes) 15 Supplies 1b Repairs& maintenance 1 Advertising 18 Car, delivery and travel 19 Acoounting and legal 20 Rent 0% 500 950 450 costs go in both Use dollars in and a percentage in These costs are a combination of both variable and mixed. For example your cell phone bill has a monthly rent that never changes and a price per minute for u fixed and variable 0% 700 22 Utlities 23 Insurance 16,278 24 Taxes (Real estate, etc.) 0% sage. | Interest 2b Depreciation LI Other (Operating ltems) 2 Other (Operating Activities) 445 3UPrincipal portion of debt payment 31 Owners draw 33 Total Fixed Costs 34 Total Variable Costs 36 Enter your sales units 50 78220 1564 Cost Volume Profit (CVP) analysis allows you to determine how changes in costs, changes in the units(volume), changes in sales or sales units, or changes in variable cost effect the overall profit of the company. Using this model you can adjust these items and see the result on breakeven. 38 Breakeven Sales level 39 40 Breakeven Sales in Units 42 44 with the 46 43VIP: What ever numbers you have in your break even (costs) must be used in your cash forecast. Your projected units should be higher than B/E. Your costs should align expenses shown in the cash forecast. 20xx 20xx 20xx 20xx Investment Outlays at Time Zero: Equipment or other investment (10,000) Operating Cash Flows over the Project's Life: Sales revenue Cost of Sales Gross Profit 38,148 32,256 27,130 16,510 7,39210,62010,824 18,965 8,141 16,599 26, 980 24,864 1,168 10,000 Adverstiing Depreciation (equipment) Dues and subscriptions 6,918 7,253 3,025 |Insurance 5,703 1,492 1,492 1,198 119(2,335,729 119(2,335,729 (663) 2,083 1,392 Payroll Taxes Other Taxes Other expenses Total expenses Oper. income before taxes (EBIT) Taxes on operating income Net Operating Profit After Taxes (NOPAT) 1,392 108 1,781 11(1,6733,646 Note: Change the tax rate in these cells to what is required 2,691 16 $433(S1.338)$3,672%2,707 11(1,673) 3,646 Add back depreciation 445 $739 422 Operating cash flow Terminal Year Cash Flos: After-tax salvage value of equipment that you sell Total termination cash flows S1 Net Cash Flow (Time line of cash flows) ($10,000) $739 $433(S1338 $3,672 $2,708 Net Present Value IRR Create a formula using the NPV function as specified Create a formula using the IRR function as specified click the help file (? On top right of screen) and search for NPV Click the help file and search for IRR MS6010 M4 Project Submission Rubric No points are actually earned in this submission as all points for the project (470) are applied in M5 grading. 220 points from the spreadsheet review in M4 and 250 points from the slide presentation in M5 This review is to help you understand what is missing or what needs correction. Grade earned on worksheets if you do not make changes before end of M5 TaitoS Needs CorrectionNot Available Available or Yes Notes tem CVP or Breakeven Worksheet or tab) Inflows were over a 5 year period Outflows were over a 5 year Ouflows had various types of outflows detailed De Depreciation was added back to inflows after tax calculation Project used a 6.5% Discount rate Project used a 25% tax rate Net Present Value or NPV Internal Rate of Return or IRR provided You must show the detail of all inflows and outflows over the 5 year period. Items must be calculated correctly. Please see notes on your worksheet Depreciation is not correct not correct rate not correct rate not correct NPV not correct. Use NPVC) formula in Excel IRR not correct. Use IRR() formula in Excel ation was Public company financial statements were provided Module 4 Grading Rubric Assignment Components By end of Module 2, complete the Project Approval Input and answer the symbol. Explain Proficient Max Points 30 PointsComments, if any on Mod 2 Please see class progress for M2 Input Approval Quiz Excel Worksheet Requirements Identify the various revenues, expenses, costs, expenses, and cash flows.p implement the Calculate the CVP or break-even point for the project. Calculate NPV and IRR. Provides the numeric viability of the project in Please see module 4 grading area for my initial comments on your Excel Presentation. used 180 15 25 220 70 15 are complete a are complete a what you provided in Module 4 90 K6 1 Breakeven Analysis (valueVariable costs are only those costs that change in equilevant terms Fixed costs are only those costs that stay the same even when unit sales changes. Not all of these items in this ist will be fixed for your particular case. Each one needs to be Property Insurance Holdings, Inc. when sales units change. Not all of these items in this list will be fixed 2 4 6 for your particular case. Each one needs to be evaluated. If you use CGS, you will not use the other three. Change titles if needed. Enter Cost Description Variable Costs Fixed Costs Variable Costs Mixed Costs 8 Cost of Goods Sold y Inventory 10 Raw Materials 11 Direct Labor (Includes Payroll Taxes) 12 13 Fixed Costs 14 Salaries (includes payroll taxes) 15 Supplies 1b Repairs& maintenance 1 Advertising 18 Car, delivery and travel 19 Acoounting and legal 20 Rent 0% 500 950 450 costs go in both Use dollars in and a percentage in These costs are a combination of both variable and mixed. For example your cell phone bill has a monthly rent that never changes and a price per minute for u fixed and variable 0% 700 22 Utlities 23 Insurance 16,278 24 Taxes (Real estate, etc.) 0% sage. | Interest 2b Depreciation LI Other (Operating ltems) 2 Other (Operating Activities) 445 3UPrincipal portion of debt payment 31 Owners draw 33 Total Fixed Costs 34 Total Variable Costs 36 Enter your sales units 50 78220 1564 Cost Volume Profit (CVP) analysis allows you to determine how changes in costs, changes in the units(volume), changes in sales or sales units, or changes in variable cost effect the overall profit of the company. Using this model you can adjust these items and see the result on breakeven. 38 Breakeven Sales level 39 40 Breakeven Sales in Units 42 44 with the 46 43VIP: What ever numbers you have in your break even (costs) must be used in your cash forecast. Your projected units should be higher than B/E. Your costs should align expenses shown in the cash forecast. 20xx 20xx 20xx 20xx Investment Outlays at Time Zero: Equipment or other investment (10,000) Operating Cash Flows over the Project's Life: Sales revenue Cost of Sales Gross Profit 38,148 32,256 27,130 16,510 7,39210,62010,824 18,965 8,141 16,599 26, 980 24,864 1,168 10,000 Adverstiing Depreciation (equipment) Dues and subscriptions 6,918 7,253 3,025 |Insurance 5,703 1,492 1,492 1,198 119(2,335,729 119(2,335,729 (663) 2,083 1,392 Payroll Taxes Other Taxes Other expenses Total expenses Oper. income before taxes (EBIT) Taxes on operating income Net Operating Profit After Taxes (NOPAT) 1,392 108 1,781 11(1,6733,646 Note: Change the tax rate in these cells to what is required 2,691 16 $433(S1.338)$3,672%2,707 11(1,673) 3,646 Add back depreciation 445 $739 422 Operating cash flow Terminal Year Cash Flos: After-tax salvage value of equipment that you sell Total termination cash flows S1 Net Cash Flow (Time line of cash flows) ($10,000) $739 $433(S1338 $3,672 $2,708 Net Present Value IRR Create a formula using the NPV function as specified Create a formula using the IRR function as specified click the help file (? On top right of screen) and search for NPV Click the help file and search for IRRStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started