Financial Analysis Using Excel: The The Essentials of Investment Decision Making. This video is the third in a four part series that presents the essentials of financial analysis for investment decision making. This series shows how to evaluate investments, particularly those associated with the projects. Episode one explored the time value of money, the cash flow, and incremental analysis.
In episode two, we discussed Net Present Value and Internal Rate of Return. This episode discusses more financial tools, in particular the benefit cost ratio, maximum capital at risk, and payback. And important but underutilized economic analysis tool is the Benefit Cost Ratio, or probability index. The benefit cost ratio or BCR is the present value at the investments benefits divided by the present value of the initial cost. The benefit cost ratio shows in an intuitive manner, how much discounted money an investment will yield.
For example, a benefit cost ratio of 1.5, describes the project that returns $1.50 in discounted money, for each dollar invested. Determining the cost that goes into the denominator of the benefit cost ratio, occasionally can prove confusing. However, the introduction of another investment analysis tool, the maximum capital at risk, or MCR, simplifies the calculation, well, simultaneously providing additional investment inside. The maximum capital at risk is the present value of the investment at the point where the investment most exposes an investor to loss. The maximum capital at risk is the most negative number from the series of cumulative discounted cash flows.
The maximum capital at risk is not only important in itself, but it also provides the convenient means to determine the benefit cost ratio. You calculate the benefit cost ratio by the formula,
Benefit Cost Ratio = Net Present Value/ Maximum Capital at Risk + 1
To understand this let's consider example one again. Cell C16 calculates the the maximum capital at risk, by finding the most negative cumulative discounted cash flow, which is the most negative cell in the rage of C12 through H12. And cell C17 illustrates that the benefit cost ratio is found by dividing the net present value of C14 by the maximum capital at risk of C16, and adding 1.
Example two on tab two shows four projects we have calculated, the maximum capital at risk, the net present value, and the benefit cost ratio for each project. This example illustrates the challenging aspect of calculating the maximum capital at risk. For each example, the discounted cash flow our lines are showing negative cash flows with yellow highlighting, and on the cumulative discounted cash flow, I am showing the most negative cumulative discounted cash flow in orange, and then the maximum capital at risk is shown in green.
Project one again, it's straightforward with a negative cash flow only occurring in year zero. On project two, we have negative cash flows occurring in year zero, and in year one. And their most negative cumulative discounted cash flow occurs in year one, with the value of negative 34.5.
And then in project three, we have a case where we have negative cash flows occurring in year zero, and in year two, straddled by a positive cash flow that occurs in year one. In this case, the most negative cumulative discount of cash flow occurs in year zero. In project four, we have negative cash flows again occurring in the year zero and the year two, but in this case the positive cash flow that occurs in the year one fails to overcome the negative cash flow that we have in the year two. And so, the maximum capital at risk occurs in the year two with that being negative 32.6.
This type of complexity illustrates the need to automate the determination of maximum capital at risk. The method I have provided to find maximum capital at risk, first finds the cumulative discounted cash flow, and then finds the minimum point on that range. This approach reliably automates the calculation.
Many investors use payback as an investment tool, indicating the time it takes to recover investment, payback is easy to calculate. And it provides very useful information. Unfortunately payback fails to fully account for the time value of money. Because it excludes compounding. Various methods for calculating payback include, starting at the point of first investment, and starting at the point where the project begins generating revenue.
Payback can either use discounted values, or non-discounted values. The examples accompanying this discussion employ non-discounted cash, and track the time from the point when the project begins generating a positive cash flow. Example three shows the project that achieves a five year payback as measured from time zero. So here on line ten we have one, two, three, four, five; five years to achieve full payback. However, if we begin calculating from the point at which the project turns positive, which is the beginning of year two, we would count one, two, three, four for a four year payback.
Rate of Return or ROR, accounting rate of return, and return on investment or ROI are defined in various ways by different people. Many people still use ROR or ROI, even though they fail to provide consistently valid comparisons. All rate of return methods employ ratios that in general express a measure of profit, divided by a measure of investment. The common method is the net annual income for any year divided by the average book value for that year.
Discussion of these ratios falls outside the scope of this series. In this video you encountered the financial analysis tools, benefit cost ratio, maximum capital at risk, and pay back. These tools go hand-in-hand with that present value, and internal rate of return, which we discussed in the episode two. Net Present Value, Maximum Capital at Risk, and Benefit Cost Ratio, each depend upon a specific interest rate or discount rate.
On the other hand, the internal rate of return and payback, do not require discount rate to be understood. Internal rate of return and benefit cost ratio, both reflect the intensity of capital return of the net present value as an extensive value, where the number represents an opportunities overall size.
Larger internal rates of return, benefit cost ratios, and net present values indicate better projects. You can find the Excel Financial Analysis examples discussed in this series on our website at the following address. In the next video, part four, you will see how to use these tools to make investment decisions. For more information on this topic, visit us at toweringskills.com. If you have specific questions relating to financial analysis, or project management, you can post your queries on our form, also at towering skills.
Transcription by:
Scribe4you Transcription Services