Financial Analysis Using Excel

The Essentials of Investment Decision Making

3 September 2008

Topics: capital investments, cash flow, decision making, financial analysis, incremental analysis, investments, projects, time value of money, NPV, net present value, IRR, internal rate of return, BCR, benefit cost ration, MCR, maximum capital at risk, payback, discount rate

This 4-part series presents the essentials of investment decision-making. Financial analysis is a broad discipline for assessing the viability and profitability of business. This series shows how to evaluate investments, particularly those associated with projects.

Episode 1 covers the time value of money, cash flows, and incremental analysis.

Episode 2 discusses two of the most important financial tools—NPV (net present value) and IRR (internal rate of return).

Episode 3 discusses financial tools—BCR (benefit cost ratio), MCR (maximum capital at risk), and payback.

Episode 4 applies the financial tools, which were discussed in earlier episodes, to decision making.

Script

Part 1: Time Value of Money, Cash Flows, and Incremental Analysis

Time Value of Money

What is meant by the term time value of money? Let’s consider an example. What is better, $500 today or $500 two years from now? You probably would agree that receiving $500 today is superior. You are happier having money in your hands now rather than hoping to receive money sometime in the future.

You also encounter the time value of money when you work with interest. Interest, of course, is the charge paid for a loan. The longer it takes you to repay a loan, the more interest you pay. Even the interest rate is higher on longer loans.

Consider another example. You borrow $1,000 for one year at 10% interest. The money borrowed is principal. At the end of one year you own $100 in interest as well as still having the obligation to repay the principal of $1,000. If you do not repay the principal, nor the interest, but keep the money for a second year, the interest continues to accumulate on the unpaid interest as well as on the principal so that you owe $1,210.

This phenomenon of interest growing on itself is called compounding or compound interest. This is the snowball effect of interest.

When analyzing investments, you can base your analysis upon any point in time or upon a series of recurring payments. To keep this discussion as simple as possible, we will consider investments exclusively from the point of view of the present moment. Because we will translate all future income and costs to the present, this type of analysis is called present value analysis. Most investors follow this convention. We will adhere to this rule. But be aware that other methods exist.

Reiterating what we just considered, present value analysis converting all money into present dollars, and present dollars represent the present value of money you expect to receive in the future.

Generally the present time is the moment when an investor makes an investment decision. We call this point time zero. Most analysts consider time zero to be of short length, such as several days or a few months. In some instances, time zero can be a full year; however, time zero never exceeds one year and typically runs much less than a full year.

Note that present value analysis brings all future money back to the present using compound interest. Investors often use the term discount rate or discount factor, but it is nothing more than compound interest. We will talk about the discount factor a bit later, but first let us discuss another important term, cash flow.

Cash Flow

Cash flow is the movement of money in and out of a business or an account. Examples include:

• A personal checking account
• A company’s cash flow statement
• The tabulation of a project’s costs and revenues

Business analysts call the cash generated by a company the operating cash flow. It is the firm’s net earnings tallied after the company pays taxes and adds back in depreciation. Business people also talk about other types of cash flows, such as the cash flow from assets, the cash flow to creditors, and the cash flow to stockholders, but we will not concern ourselves with these. Just be aware they exist.

We will focus on project cash flows. A project cash flow is a series of values that reflects the investment used to initiate a project and the net revenue stream generated by the investment. Mathematically, the net cash flow for each period of time (generally lasting one year) consists of:

+ Revenue or savings

- Production cost

- Taxes

- Capital

= Net cash flow

Please note that production cost includes both operating and repair costs.

To help us understand how money flows in and out of a project, we show money we gain as positive numbers, and money we disperse as negative values. Capital is the money spent at the beginning of a project to buy or build an asset, which later generates revenue or savings. In the bar chart shown here, the first two bars with negative numbers represent the investment made at time zero and year 1, and the subsequent bars and positive numbers in years 2 through 5 reflect income.

Incremental Analysis

The last concept we need to consider to set the stage is incremental analysis. Many investments represent small portions of large businesses. As an investor, business owner, engineer, manager, or analyst, you need to understand the incremental revenue generated from each incremental investment. As you focus on incremental inputs and outputs, you place yourself in the position to understand and to control investment capital so as to ensure that each dollar works for you or for your organization. This enables you to avoid lumping non-essential work with profit-making elements. Failing to approach investments from an incremental point of view can easily result in gold-plating and waste.

Summary to Part 1

To recap, the time value of money looks at the timing of investments and income; money received sooner is more important than equal money received in the future. The cash flow is a series of negative and positive values reflecting how money flows from and to an investor. Investors must look at incremental cash flows.

Part 2: Financial Tools: NPV and IRR

Net Present Valve

The first tool we encounter is net present value, NPV. The net present value is the revenue or savings derived from an investment, less its cost. Future values are brought back to the present at a compound interest rate called the discount rate.

People the world over use NPV, largely because it provides a sense of how much money a project will generate, but also because it represents the best tool for deciding among mutually exclusive projects. But I am getting ahead of myself, a fuller consideration of how to use NPV for decision making comes in the 4th video/article in this series. We first need to assemble our full bag of financial tools before we begin decision making.

Discount Rate

We indicated that NPV is defined at a particular discount rate. This discount rate is also called the

• Cost of capital
• Opportunity cost of capital
• Hurdle rate
• Minimum rate of return
• Minimum acceptable rate of return

The term discount rate implies the idea of discounting the future by bringing money back to the present. The cost of capital is a company’s average cost of borrowing money and raising equity. And the opportunity cost of capital conveys the thought that an investment opportunity possesses a cost. The term hurdle rate imparts the notion that investments must surpass a minimum acceptable return. This minimum acceptable return is the minimum rate of return, MRR. Whatever the language, the concept is the same.

www.toweringskills.com/docs/FA002.xls.

When the dialog box appears, you can either Open the file or Save it to your computer.

You will find Example 1 on the tab <1>. This spreadsheet illustrates a simple discounted cash flow analysis. Line 3 provides the discount rate of 10%. Line 5 shows the project periods in years. Line 6 gives the income, followed on line 7 by the capital. Note that the capital expenditures are entered as negative numbers. On line 8 you will see the cash flow, which is the sum of lines 6 and 7. Line 10 provides the discount factors. They are calculated from the discount rate using the formula 1 / (1+ the interest rate) taken to the power of the year. This is the compound interest factor we discussed in video 1. Line 11 provides the discounted cash flow, which is the product of the cash flow and the discount factor. Each value in line 11 represents present dollars at time zero. Line 12 shows the cumulative discounted cash flow. We will see later how to use these values.

At cell C14 we encounter the NPV, which is calculated using the Excel NPV function. The function terms include the interest rate and a range of values, which starts with the first discounted period and continues to the last, or in other words from cell D8 through cell H8. Because the NPV function begins discounting the first period, you need to exclude time zero from the function and add time zero’s cash flow as a separate item. Let’s move to the right and insert the NPV function again using Excel’s function wizard. Although Excel provides a number of ways to access the wizard, go to the formula bar and clicking on the function icon. In the “Search for a function” box you can type NPV and click Go or you can use the “select a category” pull-down menu and select Financial and then in the “Select a function” box scroll down to find the NPV. Once you find NPV, click “OK.” This opens the Function Arguments window. Here we select the interest rate and the values.

Note that cell I11 also provides the NPV. Why? Because cell I11 totals the discounted cash flow, which is the definition of the NPV. Also note that cell H12 also shows the NPV, since it too sums the discounted cash flow. So, several methods exist to determine the NPV.

Internal Rate of Return

Another widely used investment analysis tool is internal rate of return, IRR. The internal rate of return measures an investments ability to repay capital. Internal rate of return gauges the internal merits of a project. It tells you the rate at which a project generates money. This rate is the compounded return rate, also called investment yield. Manual calculation of the IRR is difficult, but fortunately, Excel provides a built-in function.

Referring again to Example 1, the IRR function at cell C15 contains the values from the undiscounted cash flow, range C8:H8. Freshly inserting an IRR function, the Excel wizard prompts you to enter the values arguments. The values always consist of a series of raw, non-discounted cash flows starting with the initial investment and continuing to the last period. Thus the IRR function includes time zero. The IRR function also asks for a guess, but you can ignore the guess by leaving the argument blank.

Summary of Part 2

In this video/document you encountered two of the most common financial analysis tools NPV and IRR. NPV is an extensive value, where the number gauges an opportunity’s overall size. On the other hand, IRR is an intensive measure of an investment’s profitability.

Part 3: Financial Tools: BCR, MCR, and Payback

Benefit/Cost Ratio

An important, but underutilized economic analysis tool is the benefit/cost ratio or profitability index. The benefit/cost ratio, B/C or BCR, is the present value of an investment’s 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.50 describes a project that returns $1.50 in discounted money for each $1.00 invested.

Maximum Capital at Risk

Determining the cost that go into the denominator of the B/C ratio occasionally can prove confusing. However, the introduction of another investment analysis tool, the maximum capital at risk, MCR, simplifies the calculation while simultaneously providing additional investment insight. The MCR 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 MCR is not only important in itself, but it also provides a convenient means to determine the B/C. You calculate the B/C by the formula:

B/C = NPV / MCR + 1

www.toweringskills.com/docs/FA002.xls )

Cell C16 calculates the MCR by finding the most negative cumulative discounted cash flow, which is the most negative cell in the range C12:H12.

Cell C17 illustrates that the benefit/cost ratio is found by dividing the NPV (cell C14) by the MCR (cell C16) and adding 1.

Example 2, on tab <2>, shows four projects where I have calculated the MCR, NPV, and B/C for each project. This example illustrates the challenging aspect of determining the MCR.

With each project, the yellow highlights show the years with negative cash flows, the orange highlights show the most negative cumulative discounted cash flows, and these match the green highlighted MCR values.

Project 1 is straightforward, because a negative cash flow only occurs in year 0, as seen on line 23. Project 2 is more complex, because negative cash flows occur in years 0 and 1. Project 3 falls along the same lines, except negative cash flows occur in years 0 and 2. Note that the positive cash flow in year 1 more than offsets the negative cash flow in year 2. And project 4 offers another variation with negative cash flows in years 0 and 2, but where year 1’s positive cash flow fails to offset year 2’s negative value.

This type of complexity illustrates the need to automate the determination of MCR. The method I’ve provided to find the MCR 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 recovery an 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
• Starting at the point when 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 3 shows a project that achieves a 5-year payback as measured from time zero or a 4-year payback as measured from the end of year 1, when the project cash flow turns positive.

Rate of Return

Rate of return, ROR, accounting rate of return, and return on investment, ROI, are defined in a variety of ways by different people. Many people still use ROR or ROI, even though they fail to provide consistently valid comparisons. All ROR methods employ ratios that in general express:

Measure of profit

Measure of investment

A common method is:

Net annual income for any year

Average book value for that year

Discussion of these ratios falls outside the scope of this series.

Summary of Part 3

In this video/document you encountered the financial analysis tools BCR, MCR, and payback. These tools go hand in hand with NPV and IRR, which we discussed in episode 2. NPV, MCR, and BCR each depend upon a specified interest rate or discount rate. On the other hand, the IRR and payback do not require a discount rate to be understood.

IRR and BCR both reflect the intensity of capital return, while the NPV is an extensive value, where the number represents an opportunity’s overall size. Larger IRRs, BCRs, and NPVs indicate better projects.

Part 4: Analyzing Projects and Making Investment Decisions

Be aware that investments fall into two categories: mutually exclusive projects and independent projects. When deciding among mutually exclusive alternatives, investors must select the best project, because they can only do one. On the other hand, investors can perform any number of independent projects (also called non-mutually exclusive projects), as long as their funds last.

The following tables summarize the two investment categories and show the appropriate decision tool for each.

Mutually Exclusive Investments