Question
All analysis should be done in MS Excel. A memo to your boss explaining the situation, your analysis and conclusion should be written in MS
All analysis should be done in MS Excel. A memo to your boss explaining the situation, your analysis and conclusion should be written in MS Word. You must upload both files to be graded)
Aswath Corporation has a machine that requires repairs or should be replaced. You have recently been hired by Aswath and one of your assignments is to help decide whether replacing the machine or repairing it is in the best interest of your company.
Investment required for replacing the machine is $2,995,000 and cost to repair the machine is $790,000. Repairing the machine will cut down on the number of returns from customers and therefore increase cash flows, while replacing the machine may increase the number of units sold. Aswath has evaluated and calculated additional cash flows that will be generated under the two scenarios over the next five years as follows:
Year | Cash flow (if repaired) | Cash Flow (if replaced) |
1 | 325,500 | 970,300 |
2 | 310,000 | 975,000 |
3 | 269,800 | 990,100 |
4 | 210,100 | 720,800 |
5 | 140,700 | 530,000 |
Cost of capital for the project (repair or replacement) may vary anywhere from 12 to 16% for both scenarios. You should therefore conduct your analysis with at least five WACC points 12%, 13%, 14%, 15%, and 16%. Aswath does not like to accept projects that will pay back in more than 4 years, so you must calculate Payback period to know which if any project is acceptable.
You must conduct the analysis using your knowledge of capital budgeting (NPV, IRR etc.) and time value of money to decide which of the options is better for Aswath. All analysis must be done in Excel. Your excel model should be such that it can be used by Aswath for other similar situations so anyone can change any numbers and use the model again. This would require all calculations to be using cell references and excel functions.
You must write a memo to your boss, explaining the situation, your analysis, and your conclusion. You must also explain the drawbacks of your technique/s if there are any.
NOTE: You will only get full credit for the assignment if your analysis is correct, all calculation cells are either cell references or formulae and your memo is properly formatted, grammatically correct, have the right content and the right flow. An Excel template with few inputs already entered is provided for your ben
excel - template 17 (2).lsx Excel Alycialean J. Robinson -O Insert Page Layout FormulasData Review View Tell me what you want to do AutoSum Cut Copy Fonmat Painter -A 9- Wrap Text General Fill 1 Clear, Paste Merge & Center. $. % , 2 Sort & Find & Filter Select Conditional Fonmat as Cell Insert Delete Format B 1 u . ormatting TableStyles Clipboard Font Alignment Number Cells Editing A35 | Option A 21 22 ANALYSIS AREA (Calculations) 24 25 WACC 26 NPV (A) 27 NPV (B) 28 IRR (A) 29 IRR (B) 30 PI(A) 31 PI (B) Scenario1 Scenario2 Scenario3 Scenario4 Scenario5 33 PAYBACK PERIOD CALCULATION Option B 35 Option A 36 37 Year CALCULATION AREA CALCULATION AREA Cash Flow Cum CF Year Cash Flow 39 41 SheetShet3 Sheet4 Sheet5 Sheet6 . + 100% 8-54 PM 10/24/2017Step 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