I have an assignment for Finance Management: Payback Periods, Net Present Value, Internal rate of return , NPV, Equivalent Annual Annuity, Estimation CF, Capital Rationing. Please see excel attachment. Please show all work
MBA684.71 Module 6 Graded Assignment (Available points=100 pts) *You are not allowed to do this assignment with other people. *You are not allowed to discuss this assignment with other people. *Show all of your work for any possible partial credit. *All necessary calculations should be done on this excel file to show how to get your outputs. Then, I can figure out the whole process as to how your answers were obtained. Therefore, simply showing numbers as a result does not earn any point. * Be sure to answer exactly what is asked in the problems. Save your work file in the following format of the file name: M06_Assignment_Last Name_First Name.xls. Any submission through email won't be accepted. Note that including any special symbol in the file name may cause a trouble in uploading or downloading your file. So, don't use any special symbols, such as $, #, *, &, etc. Absolutely....\"One File Policy!!!\" You have to pack all your answers into this excel file. If you need a word document to write your answer down, please incorporate MS word document into this excel file. Follow these operations to import MS document into the excel file: Insert (from menu bar)>Object...>choose \"Word Document\" from the drop-down list. I 1 2 3 4 5 pts 5 5 5 5 7 1 2 7 7 IV V II VI 41 pts 7 III 1 2 1 2 3 4 A B 8 8 4 4 4 4 10 10 59 100 2) Calculate the Net Present Value, NPVA and NPVB NPV(A)= NPV(B) 3) Calculate the Internal Rate of Return, IRRA and IRRB. IRR(A)= IRR(B) 4) Calculate the Profitability Index, PIA and PIA PI(A)= PI(B)= 5) Find Crossover Rate (Fisher's rate of intersection), if it exists. Roughly, draw NPV files of the projects. Indicate the rate, intercept (Y-axis) and IRRs for each project on your drawing. A B ($2,050) ($4,300) $750 $1,500 Crossover Rate k 0.00% 0.50% 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50% 6.00% 6.50% NPV_A NPV_B $760 $1,518 $770 $1,536 $780 $1,554 1. J oon.com is considering two projects given below: The two projects have the same payback. What is P roject 2's internal rate of return (IR )? R Year 0 1 2 3 4 Project 1 Project 2 Cash Flow Cash Flow -$100 30 50 40 50 ? 40 80 60 60 2. The F ederal R eserve recently shifted its monetary policy, causing Lasik Vision's WAC to change. Lasik C had recently analyzed the project whose cash flows are shown below. However, the C wants to FO reconsider this and all other proposed projects in view of the F action. How much did the changed ed WAC cause the forecasted NPV to change? Assume that the F action does not affect the cash flows, C ed and note that a project's projected NPV can be negative, in which case it should be rejected. New WACC= Year: Cash flows: 0 ($1,000) 7% 1 $500 Old WACC= 2 $520 3 $540 10% Your firm has to replace one of its fender-bender machine. One of your financial wizards has determined that the appropriate discount rate for the machine cash flows is 10%. your firm has two alternatives: A: Fender-bender machine A costs $400,000 and produces annual net cash flows of $200,000 at the end of each year of its six years of life. B: Fender-bender machine B costs $200,000 but has only a two-year life. However, it produces a $300,000 annual cash flow at the end of each of these two years. Year 0 1 2 3 4 5 6 Machine A Machine B -400 200 200 200 200 200 200 -200 300 300 Calculate the equivalent Annual Annuity (also, known as "the Equivalent Annual Annuity Cash Flow"; EAA) (Hint: You have to calcualte NPV first. Please refer to the lecture notes) Machine A NPV EAA Machine B 1 2 The management of Jasper Equipment Company is planning to purchase a new milling machine that will cost $160,000 installed. The old milling machine has been fully depreciated but can be sold for $15,000. The new machine will be depreciated on a straight-line basis over its 10-year economic life to an estimated salvage value of $10,000. If this milling machine will save Jasper $20,000 a year in production expenses, what are the annual net cash flows associated with the purchase of this machine? Assume a marginal tax rate of 40 percent. Greater Findlay Development Consortium is preparing to open a new retail strip mall and have multiple businesses that would like lease space in it. Each business will pay a fixed amount of rent plus a percentage of the gross sales generated each year. The cash flows from each of the businesses has approximately the same amount of risk. The business names, annual expected cash flows, and initial capital outflow for each of the businesses that would like to lease space in the strip mall are provided below. Greater Findlay Development Consortium uses a 12% hurdle rate which is its cost of capital. All business will be evaluated based on 4-year term because the contract will expire in four years. Initial Capital Outlay Annual Net Cash Flows Year 1 Year 2 Year 3 Year 4 NPV PI IRR Video Now $200,000 Apple Garden $298,000 Croger Mart $248,000 Horizon Wireless $272,000 65,000 70,000 80,000 40,000 ($3,797.57) 0.9810121434 100,000 135,000 90,000 65,000 $4,275.79 1.0143482723 80,000 95,000 90,000 80,000 $14,063.66 1.0567082999 95,000 125,000 90,000 60,000 $14,661.97 1.0539043025 1. Calculate NPV, PI, and IRR of all business projects. (fill the table above). 2. Which business (es) should Greater Findlay undertake during the upcoming year assuming it has no budget restrcitions? Make sure to juastify your answer. 3. Which business(es) should Greater Findlay undertake during the upcoming year if it has only $600,000 of funds available? Make sure to juastify your answer. 4. Which business(es) should Greater Findlay undertake during the upcoming year if it has only $300,000 of capital funds available? Make sure to juastify your answer. 1. Estimate the design A's Net Operating Cash Flows (NCF) using a given Excel worksheet. Complete the worksheet given along with this assignment. (Click the tab of Design A) 2. Estimate the design B's Net Operating Cash Flows (NCF) using a given Excel worksheet. Complete the worksheet given along with this assignment. (Click the tab of Design B) 3. Calculate NPV of the two mutually exclusive designs. Which design should be chosen? Note: Please answer the problems above by completing the worksheets named \"Applachian_Design A\" and \"Applachian_Design B\" Calculation of Net cash flows from adopting new project (Design A)-Expansion Project year 0 1 2 3 4 5 $0 $0 $0 $0 $0 $0 $0 $0 Step A: Estimating Initial Cash Outflow - Cost of "new" asset - Capitalized expenditures(shipping&installation) + Net proceeds from sale of "old"asset (current system) -(+) Taxes(Tax savings, tax rate=34%)due to sale of "old" asset (current system) = Initial Cash Outflow Step B: Calculating Interim Incremental Net Cash Flows (years 1 to 5) a) Operating costs from present equipment (excluding taxes and depreciation) b) Operating costs if Design A is invested (excluding taxes and depreciation) $138,000 $110,321 D EBIT_other than D Depreciation : a)-b) D Depreciation D Net Working Capital (NWC) D Capital Expenditure $27,679 $0 $0 Interim Incremental Net Cash Flow Step C: Calculating Terminal-year Incremental Net Cash Flow Incremental cash flow from the terminal year before project windup considerations + Final salvage value of "new" asset (Design A) - Tax due to sale or disposal of "new" asset (tax effect of capital gain or loss) = Terminal-year Incremental Net Cash Flow 0 1 End of Year 2 3 4 5 Incremental Net cash flows NPV MACRS depreciation rate for three-year property class Net Increase in tax depreciation chareges New Design's depreciation basis x MACRS depreciation (%) = New Design's periodic depreciation Current System's depreciation basis x MACRS depreciation (%) = Current System's remaining periodic depreciation 33.00% 45.00% 15.00% 7.00% 0.00% 0.3300 0.4500 0.1500 0.0700 0.0000 Not Applicable Applicable Applicable Applicable Applicable Not Not Not Not Not Applicable Applicable Applicable Applicable Applicable Not Not Not Not Net increase in tax depreciation charges Be advised that NOT all cells highligted in yellow necessarily need to be filled. Some cell may be filled with zero or "not applicable." Calculation of Net cash flows from adopting new project (Design B)-Replacement Project year 0 1 2 3 4 5 $0 $0 $0 $0 $0 $0 $0 $0 Step A: Estimating Initial Cash Outflow - Cost of "new" asset - Capitalized expenditures(shipping&installation) + Net proceeds from sale of "old"asset (Current System) -(+) Taxes(Tax savings, tax rate=34%)due to sale of "old" asset (Current System) = Initial Cash Outflow Step B: Calculating Interim Incremental Net Cash Flows (years 1 to 5) a) Operating costs from present equipment (excluding taxes and depreciation) b) Operating costs if Design B is invested (excluding taxes and depreciation) $138,000 67073 D EBIT_other than D Depreciation : a)-b) D Depreciation D Net Working Capital (NWC) D Capital Expenditure $70,927 $0 $0 Interim Incremental Net Cash Flow Step C: Calculating Terminal-year Incremental Net Cash Flow Incremental cash flow from the terminal year before project windup considerations + Final salvage value of "new" asset (Design B) - Tax due to sale or disposal of "new" asset (tax effect of capital gain or loss) = Terminal-year Incremental Net Cash Flow 0 1 End of Year 2 3 4 5 Incremental Net cash flows MACRS depreciation rate for three-year property class Net Increase in tax depreciation chareges New Design's depreciation basis x MACRS depreciation (%) = New Design's periodic depreciation Current System's depreciation basis x MACRS depreciation (%) = Current System's remaining periodic depreciation 33.00% 45.00% 15.00% 7.00% 0.00% 0.3300 0.4500 0.1500 0.0700 0.0000 $0 Not Applicable $0 Net increase in tax depreciation charges Be advised that NOT all cells highligted in yellow necessarily need to be filled. Some cell may be filled with zero or "not applicable."
