FIN 322 – Financial Management
Excel and Writing Case
Assignment Description:
ABC Corporation has a machine that requires repairs or should be replaced. ABC has evaluated the two options and calculated the cash flows resulting from each option as follows:
Option A: Repair the Machine
Year Cash Flow
0 -50,000
1 15,500
2 20,100
3 18,900
4 17,100
5 13,700
Option B: Buy a new Machine
Year Cash Flow
0 -400,000
1 51,300
2 155,000
3 127,800
4 126,900
5 125,100
You have recently been hired by ABC Corporation and your first assignment is to help them decide which of these two options should be pursued. You would like to apply Capital Budgeting and Time Value of Money concepts you have learnt to analyze the problem and present your recommendation to your boss, Ms. Jane Austen.
Conduct the analysis by calculating the following:
Payback Period
Net Present Value (NPV)
Internal Rate of Return (IRR)
Modified Internal Rate of Return MIRR
Profitability Index (PI)
Calculate and graph the NPV profile for both options
Calculate cross-over rate if there is one
The company has a Weighted Average Cost of Capital (WACC) of 12%. For this analysis, your boss asked you to conduct a sensitivity analysis by calculating NPV at three different discount rates: 12% (the current WACC), 14% and 16%.
Things to turn in:
A one-page business memo explaining the results of your analysis and your recommendation. The memo should include important results of your analysis such as a summary table or graph. The memo is limited to one page so be very selective on what information to include.
An Excel spreadsheet showing calculation and recommendations
You must use the built-in financial functions and logical functions in Excel spreadsheet
The spreadsheet should be a model such that if the cash-flows are changed, the answer and recommendation should change automatically in the spreadsheet.
Answer will be sent by email as attachment.
