See attached file.
UNIT 3 PROFESSIONAL CHALLENGE- INSTRUCTIONS
Please follow the instructions below to complete the assignment. I have attached a Unit 3 Professional Challenge- Blank Answer Template. Please use it to fill in your answers. The cells that need to be filled in are in the color gray. Virtually all of the cells should have formulas (or linked to other cells) in order to show your work and logic. You must use formulas for your calculations rather than just pasting in numbers. Feel free to use a financial calculator to verify your answers but you want to use the Excel formulas. Points will be deducted without showing how your calculations are derived. Also, notice that your NPV & IRR answers should be in the gray cells to the right of where it says NPV & IRR. The memo should be approximately two pages.
1. For the WACC, use 7.50%. I fixed the WACC rate for everyone so you will not have to attempt to calculate it from your chosen company. This will save you time. So you will not need to calculate the WACC, only explain it in your paper. You will be calculating 1) Depreciation, 2) Tax Rate, 3) Taxable Income, 4) NOPAT, 5) Net Cash Flow, and the two major calculations- NPV & IRR.
2. You need to find the Net Cash Flows for years 0 thru 5 for Machine A and B. Then find the NPV and IRR of these cash flows. Year 0 thru Year 5 will be the time line for your cash flows.
3. Plug in the given EBITD numbers for the 5 years for each Machine.
4. For Depreciation, use 33% for year 1, 45% for year 2, 15% for year 3, 7% for year 4, and 0% for year 5. You will multiply these percentages by 120,000 for Machine A and 60,000 for Machine B. (Note: Make sure the $120,000 and $60,000 are POSITIVE numbers when you multiply the depreciation percentages by them.
5. Taxes for each year will equal (Taxable Income times 40%.)
6. Taxable Income will equal (EBITD minus Depreciation) for each year.
7. Net Operating Profit After Taxes (NOPAT) equals (Taxable Income minus Taxes@40%).
8. To find the Net Cash Flow, negative -$120,000 will be the year 0 Net Cash Flow for Machine A and negative -$60,000 will be the year 0 Net Cash Flow for Machine B. (Note: Ignore Salvage Value for Machine A).
9. Net Cash Flow equals (NOPAT + Depreciation). Remember that depreciation is a noncash expense so you add it back to NOPAT.
10. Once you have calculated the Net Cash Flows for Year 0 thru Year 5 for each Machine then you are ready to find the NPV and IRR. Remember, For year 0 for Machine A, you will use the initial cash outlay of -120,000 and -60,000 for Machine B. For the NPV calculation, use the 7.50% WACC as the discount rate.
11. Be sure to use the NPV & IRR formulas in Excel. For the NPV calculations, you want to assume that the cash flows occur at the BEGINNING of each period. So be sure to keep the year 0 cash flow outside and added to the parenthesis of the formula. The Excel Examples spreadsheet that I posted under the Faculty Expectations thread has an example of it.
12. In your Word document, be sure to discuss the following topics. It needs to be approximately two pages in length:
A) After calculating the NPV and IRR for Machine A and Machine B, choose which one you would choose based on being the most profitable.
B) Would an investor choose an investment with the highest NPV or IRR? Justify which one and why.
C) Discuss capital rationing limits firms have and what, if anything they can do,
D) Discuss the MIRR and its advantages/disadvantages.
E) Discuss the WACC.
The solution explains how to calculate the cash flows and determine the WACC, NPV, IRR, EBITD, Depreciation and Taxes