In: Accounting
All analysis should be done in MS Excel, and a memo to your boss explaining the situation, your
analysis, and conclusion should be prepared in MS Word. You must upload both files to be graded.
You will only get full credit for the assignment if your analysis is correct, all calculations and/or formulas
use cell references, and your memo has the right content, flows well, and is properly formatted, and
grammatically correct. An example of a good memo, and an Excel template is provided for your benefit.
You can add or delete columns in the excel template as needed.
Playtan Corporation has a machine that either should be repaired or should be replaced soon.
You have recently been hired by Playtan 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 repairing the machine is only $500,000 and cost to replace the machine
is $3,000,000. Repairing the machine will cut down on the number of returns from customers
and increase cash flows, while replacing the machine may increase the number of units sold.
Playtan 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
197,000
1,613,000
2
160,000
1,185,000
3
178,000
978,100
4
139,000
997,800
5
117,000
990,000
Playtan does not accept projects that will pay back in more than 3 years, or any projects that will not
maximize shareholder wealth. So, you will need to use all capital budgeting techniques you’ve learnt in
your Finance class to ensure that the right option is chosen.
Cost of capital for the project (repair or replacement) could be anywhere from 8% to 14%, so you will
conduct your analyses at various weighted average cost of capital (WACC) or Required rate of return
points including at least five at 8%, 9%, 11%, 13%, and 14% for both scenarios, and also draw an NPV
profile for both situation using your calculations.
Use your knowledge of capital budgeting and time value of money to decide which of the options is better
for Playtan. All analysis must be done in Excel. Your excel model should be such that it can be used by
Playtan or any other corporation for similar capital budgeting 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.
Make sure that all your submissions have your full name, class, and section number included.
Discount % | 8% | ||||||
9% | |||||||
11% | |||||||
13% | |||||||
14% | |||||||
To repair | |||||||
Year | 0 | 1 | 2 | 3 | 4 | 5 | |
PV of outflow | ($500,000) | ||||||
Cash inflows | $197,000 | $160,000 | $178,000 | $139,000 | $117,000 | ||
Net inflows | ($500,000) | $197,000 | $160,000 | $178,000 | $139,000 | $117,000 | |
NPV@8% | $132,112 | ||||||
NPV@9% | $116,848 | ||||||
NPV@11% | $88,727 | ||||||
NPV@13% | $63,502 | ||||||
NPV@14% | $51,870 | ||||||
Payback | 2 years 9 Months & 19 days | ||||||
To replace | |||||||
Year | 0 | 1 | 2 | 3 | 4 | 5 | |
PV of outflow | ($3,000,000) | ||||||
Cash inflows | $1,613,000 | $1,185,000 | $978,100 | $997,800 | $990,000 | ||
Net inflows | ($3,000,000) | $1,613,000 | $1,185,000 | $978,100 | $997,800 | $990,000 | |
NPV@8% | $1,567,687 | ||||||
NPV@9% | $1,452,091 | ||||||
NPV@11% | $1,238,651 | ||||||
NPV@13% | $1,046,581 | ||||||
NPV@14% | $957,784 | ||||||
Payback | 2 years 2 Months & 14 days | ||||||
Based on the above calculation is it better to replace the machine which gives higher cash flows as compared to reparing the machine
Even the NPV is way much higher for replacing the machine than repairing the machine. So management must go for repairing the machine.
Note: tax impact and depreciation is excluded in the above calculation