In: Finance
A firm has the opportunity to make a capital investment with the expectation that the investment will add economic value to the firm, increase customer satisfaction, and increase shareholder return. The investment is for a new supply chain inventory system, using state of the art 5G technologies and tracking capabilities. This system was recently developed and very few of your industry competitors are currently using it. To complete this case you will use Excel to format and solve a capital budget problem. Then you will answer a series of questions based on your calculations. Finally, you’ll prepare a analysis of the project using both derived data and qualitative factors. The data for the project is as follows:
Year | Cash Inflows | |
1 | $600,000 | |
2 | $1,000,000 | |
3 | $1,000,000 | |
4 | $2,000,000 | |
5 | $3,000,000 | |
6 | $3,500,000 | |
7 | $4,000,000 | |
8 | $6,000,000 | |
9 | $8,000,000 | |
10 | $12,000,000 | |
Current Cost of Capital | 13% | |
Initial Investment | $15,000,000 | |
Project Life | 10 Years | |
For the case analysis, you are to:
1. Create an Excel spreadsheet to format and solve for each of the
capital budget calculations.
a. All calculations are to be completed using Excel formulas
b. Format all dollar values w/ corresponding currency symbol
c. Use whole dollar amounts, so no decimal or cents figures
2. Answer all budget questions thoroughly. Prepare the narrative in
Word or similar program, and cut and paste the narrative into the
Excel spreadsheet. Please note: do not type the narrative answers
directly into the spreadsheet cells.
Capital budget questions:
1. Calculate the project’s net present value. Is the project
acceptable under the NPV decision-rule? Explain.
2. Calculate the project’s internal rate of return. Is the project
acceptable under the IRR decision-rule? Explain.
3. Compare the NPV and IRR result for this investment. Given the
result, is the project favorable and meet the economic
considerations of the firm? Is there typically a preference between
the NPV and IRR techniques? Explain.
4. Calculate the payback period for the project. If the firm
accepts projects with payback at 7 years
or less, is the project acceptable?
Discuss in detail two qualitative factors and considerations
related to this investment that the firm
should consider in addition to the quantitative and economic
aspects of the project.
I will show you two snapshots, the first one will contain figures and answers. The second one will contain the excel formula so that you can create this sheet at your end. Your questions have been answered after the snapshots:
Snapshot 1
Snapshot 2
1. Calculate the project’s net present value. Is the project acceptable under the NPV decision-rule? Explain.
NPV = $ 1,698,543
Since, the porject has positive NPV, the project is acceptable under the NPV decision rule.
2. Calculate the project’s internal rate of return. Is the project acceptable under the IRR decision-rule? Explain.
IRR = 14.76%
Since IRR > Current cost of capital, the project is acceptable under the IRR decision-rule.
3. Compare the NPV and IRR result for this investment. Given the result, is the project favorable and meet the economic considerations of the firm? Is there typically a preference between the NPV and IRR techniques? Explain.
NPV and IRR result both warrant this investment to be done. The project is favorable and meet the economic considerations. NPV should be preferred to IRR technique because NPV straightway measures the shareholders' value creation, in absolute terms. Besides, IRR has its own internal weakness like multipe IRR problem, giving contrary results to NPV rule and so on.
4. Calculate the payback period for the project. If the firm
accepts projects with payback at 7 years
or less, is the project acceptable?
The cumulative cash flows is positive at the end of year 7. Hence, the project does payoff before the end of year 7. Hence, the project is acceptable.
Discuss in detail two qualitative factors and considerations related to this investment that the firm should consider in addition to the quantitative and economic aspects of the project.