Financial Calculator Tutorial - Excel

Document Sample
Financial Calculator Tutorial - Excel Powered By Docstoc
					Case 06-PNC (Basics of Capital Budgeting)                          Medium Student Model                           7/20/04
  This model first describes how project cash flows are estimated, then uses simplified data to explain how the
primary capital budgeting decision criteria are calculated and evaluated. We also provide, in the lower section of
the worksheet, an illustration of risk analysis, using data tables to do a sensitivity analysis of the effects of
changing variables on the NPV and IRR. The next case, Case 7, extends capital budgeting analysis by examining a
major project, where risk analysis and real options are explored in depth.
  Note that most inputs are entered in blue type; generally speaking, these are the only cells you should change.
Also, some cells are blank and must be filled in. The cells to be filled in are shaded in light green. Until those cells
are filled in, the model will not produce valid results. You should start at the top and as you work your way
through the model, filling in the blank cells, the model will start to show "reasonable" results. When you finish, the
model should produce valid results.
  Note also that most of the calculations can be done with a financial calculator. Thus, you can check your Excel
results with those obtained with a calculator.
   Finally, note that we have an Excel Tutorial that provides explanations for most of the Excel functions used in
the case. Instructors have access to the Tutorial file and can provide it to students. The Tutorial can also be
obtained from the case website.
Project cash flows are normally estimated as shown in the following table. Note (1) that cash flows are
estimated on the basis of forecasted income statements, (2) that S's sales and cash flows are projected to
decline over time, but (3) L's flows are projected to increase.
Table 1. Cash Flows and Other Inputs for Projects S and L
Other inputs:   WACC:                  10.00%     Tax Rate:            40%

Project S                              Year (t)          0               1              2              3            4
Required investment                                    -$100,000
Sales revenues                                                          $170,450        $58,333      $20,000       $20,000
 Operating costs less deprn                                              $50,000        $25,000      $20,000       $20,000
 Depreciation (straight line)
Operating income
Net operating income
Add back depreciation
                Net (free) cash flow

Project L                              Year (t)          0               1              2              3            4
Required investment                                    -$100,000
Sales revenues                                                           $20,000        $36,667     $116,667     $209,583
 Operating costs less deprn                                              $20,000        $20,000      $50,000     $100,000
 Depreciation (straight line)
Operating income
 Taxes          40%
Net operating income
Add back depreciation
                Net (free) cash flow
Notice that S is projected to have negative income but positive cash flows after year 2, while L has a reverse
pattern. These are both common occurrences: it takes time to build revenues for some projects while with others
cash flows decline over time due to obsolescence, increasing competition, and so forth.
Capital Budgeting Decision Criteria
Given S's and L's projected cash flows, should those projects be accepted or rejected, and if they are
mutually exclusive, which one, if either, should be selected? We explore below the various criteria, or
decision rules, that are used to help answer these questions.

In the following example, we first calculate the cumulative cash flows. The payback is the time it takes for the
cumulative cash flows to equal zero. We can see by inspection that S's payback is 1 year plus the fraction
17,730/30,000 = 0.59, or 1.59 years. To find the payback for one or two projects, the "by inspection" method is
clearly the easiest, but where many projects must be analyzed, or where the project will be analyzed under different
conditions, it is far more efficient to use Excel's logical functions and construct a formula to find the payback.

  Students can skip the Excel model and just use the "by inspection" method without loss of continuity. However
for those who are interested, we provide a detailed explanation in the Tutorial, Section 3/279. Even if you do use
the Excel function, be sure to calculate the payback "by inspection" to insure that you know what's happening--
Excel can be used as a "black box" to generate answers that you don't really understand, and that's bad!

Project S           Year (t)           0             1               2               3                4
Net cash flows from above:
Cumulative cash flows:
   Payback                                                                                                   See Tutorial, 3/
We used Excel's IF function to find the payback. Here is the function as written in E48:
Excel looks to see if the Cumulative CF is negative, and if it is, it puts a dash in the payback row. If the cumulative
CF is positive, it checks to see if the prior year's CF was negative, and if that is true, then it calculates the payback
and puts it in the cell. If the prior year's CF was positive, then the payback year has already occurred, so Excel
again puts a dash in the cell. Excel's IF function is useful, but it does require you to think. Try creating the payback
function for the discounted payback function from scratch for a challenge.
Project L             Year (t)              0              1               2               3             4
Net cash flows from above:
Cumulative cash flows:
The Discounted Payback is the time it takes for a project to pay back based on the present value of the cash flows.
The PV of each cash flow is found, and then these PV cash flows are processed just as we explained above. The
Discounted Payback recognizes the time value of money, hence it is regarded as being superior to the regular
Project S
    Year (t)                                0              1               2               3             4
Net cash flows from above:
PV of the cash flows at WACC:
Cumulative disc. cash flows:
 Disc Payback

Project L
                    Year (t)            0               1               2              3              4
Net cash flows from above:
PV of the cash flows at WACC:
Cumulative disc. cash flows:
 Disc Payback
WACC =              10.00%
                    Year (t)           0              1               2              3             4
   Project S's cash flows:
   Project L's cash flows:

    NPVS                          See Tutorial, 3/72 for information on the NPV function. Since the NPV function
    NPVL                          assumes that all cash flows occur at the end of the period, for S we must use
                                  the range D75:G75 and then add the negative cost to find the NPV. Check your
                                  answer with a calculator.
    IRRS                          See Tutorial, 3/53. Excel's IRR function assumes that the first cash flow
    IRRL                          occurs at Year 0, so the range is C75:G75.

Notice that NPV and IRR rank the two projects differently. L has the higher NPV, S the higher IRR, so the
two method produce conflicting rankings. If they are mutually exclusive, we must somehow choose
between them. If we focus on the IRR, we would select S. If we focus on the NPV, we would choose L. At
any rate, a conflict exists.
The IRR method makes the implicit assumption that cash flows can be reinvested at the IRR itself, in this
case 20% for S and 15% for L. The NPV method assumes that cash flows can only be reinvested at the
WACC, or 10%. If the firm can get money at a 10% cost, it will raise funds and make investments until
all potential projects that yield more than 10% have been taken, thus cash flows can only be used to
replace 10% money. That means reinvesting at the 10% WACC, and that means the NPV assumption is
more correct than the IRR for firms with good access to capital. The MIRR was developed to address the
reinvestment rate problem. Each cash inflow is compounded to the end of the project's life at the WACC,
then the compounded values are summed to find the Terminal Value, and then we find the discount rate
that causes the PV of the TV to equal the PV of the costs. That discount rate is the MIRR. Excel
automates all this with its MIRR function, which we use to evaluate S and L.

Finance rate = WACC:                               10.00%
Reinvestment rate (usually WACC):                  10.00%

    MIRRS                          See Tutorial, 3/210
We demonstrate in the following data table and graph that the IRR is a constant, given the project's cash flows, but
the MIRR varies depending on the reinvestment rate and the WACC. See Tutorial 4/2 for an explanation of Data
                    IRRS           MIRRS             IRRL          MIRRL
WACC (D100)
                               IRR and MIRR for Project S
                           100%               MIRR
     IRR and MIRR

                    -10%          0%        10%             20%     30%
                                Expected Reinvestment Rate

The NPV and IRR methods occasionally lead to conflicting rankings for mutually exclusive projects. Such conflicts
can arise because of differences in the sizes of the projects' required investment (size differences), cash flow
timing differences (as for our projects S and L), or both.
   If our projects S and L were independent, both would be accepted. However, L ranks higher under the NPV
method and S ranks higher under the IRR method. So, if they are mutually exclusive, a conflict exists. We can
analyze the situation using a DATA TABLE and a CHART, as shown below.

                                                                  See the Tutorial, 4/2 for information on data tables, and 4/65
        Data table for creating NPV profiles
                                                                  for information on creating charts (also called graphs).
    WACC                          NPVS               NPVL
Once we have the data table, we can graph the NPVs against the WACCs, as shown below:

                       Figure 6-1a. Project S's
                             NPV Profile
   $60,000                                                                                               No conflict;
                                                           Reject:             Conflict                  S dominates
   $50,000                                                 NPV<0
   $40,000                          Accept:                and IRR
                                    NPV>0 and              <WACC              IRRS = NPVL
                                                                                     18.46%         Crossover:
   $30,000                          IRR >WACC                                                        10.59%

                                 IRRS = 20%
                 0%   5%          10%         15%    20%       25%

  Panel a shows that for a single, independent project like S there can be no conflict between NPV and IRR, for if
IRR>WACC, then NPV must be positive. However, as Panel b shows, if we are dealing with two mutually exclusive
projects, we will have two NPV profiles, those profile lines can cross, and thus the NPV and IRR rankings can
  The "crossover rate" where the two NPVs are equal is important. Note that if the WACC is to the left
of this rate, NPVL > NPVS but IRRS > IRRL, hence a conflict occurs. If WACC > crossover rate, no
conflict occurs.
DETERMINING THE CROSSOVER RATE                        See Tutorial, 5/1, for more on Goal Seek
We can see from the graph that the crossover occurs between 10% and 15%, but we can use Excel's Goal Seek
function to calculate the exact rate. If the two NPVs are equal, then NPVS - NPVL = 0. We can use Goal Seek to find
the WACC that causes this to happen. We first set up the equation shown below, then put the pointer on E165, and
then click Tools > Goal Seek to open the dialog box. We want to set E165 to zero, so enter E113 in the "Set cell".
We want to obtain zero in the "To value" cell, so enter 0 there. Finally, we want to change the WACC, which enters
the model in B73 up above. With the box completed, click OK to get the crossover rate, which will be shown in
G165, 10.59%. After Goal Seek has been completed, you need to record the crossover rate and then change C11
back to 10%. See Tutorial, 5/1, for a discussion of the Goal Seek function.
         NPVS                -                NPVL             =       Zero               when     WACC =
         B63                                  B64
                             -                                 =                          C11 =

Profitability Index, or Benefit/Cost Ratio (PI)
PI = PV Inflows / PV Costs = Ratio of the PV of benefits to the PV of the Costs. With our Projects
S and L, the costs are all incurred at T = 0, so the PI for the two projects is calculated as follows:
The PI shows the "bang per buck," and if it is positive this means a profitable project.

         PIS =         #DIV/0!
         PIL =
Table 2. Mutually Exclusive Projects with Unequal Lives
       WACC:         10.00%
                        0               1                    2                 3              4
Project SS         -$8,212,670     $5,000,000           $5,000,000
Project LL        -$15,000,000     $5,000,000           $5,000,000         $5,000,000    $5,000,000
    NPVSS                                                    IRRSS
    NPVLL                                                    IRRLL
Replacement Chain for SS:
Year (t)                                    0                    1             2              3               4
Project SS     Machine 1
                 Net total
 considering                                                 IRRSS
extended life:

Table 3. Multiple IRRs: Palladium Project (Negative Cash Flows at End)
    WACC:           10.00%
                    Year (t)            0                    1                  2              3
                                  -$10,000,000          $14,000,000        $14,000,000   -$18,800,000

    WACC              NPV                                              Multiple IRRs

      25%                                       -$300,000
      45%                                       -$800,000
    49.56%                                                  0%       10%     20%     30%      40%       50%
      55%                                                                          WACC

Note: Excel's IRR function assumes a "guess" of 10%, so if you just calculate IRR in the normal
way, leaving "guess" blank, it assumes 10% and gives, for Project SS, an IRR of 7.44%. However,
as the graph shows, there is a second IRR, at 49.56%. As the graph also shows, the MAX IRR
is at WACC = approximately 25%. If you make a guess less than 25%, Excel says IRR = 7.44%. If
you guess above 25%, the IRR is shown as 49.56%. The MIRR is 10.26%, which is consistent with
a positive NPV at WACC = 10%.
MIRR:                    10.26%
Incidentally, you can see that this project could have no IRR at all by changing the last cash flow to
In all of the above analysis, we simply took the cash flows as given and then calculate the NPV and other
decision criteria. Obviously, though, if the cash flows turn out to be different from their predicted levels, the
NPV, IRR, and so forth will be incorrect. Thus, decision makers want to know just how much a given change in
one of the determinants of cash flows, say the cost of the project, the sales, or the operating costs, will affect
the NPV, IRR, and so forth. We look in depth at risk analysis in Case 7, but we set up the following data table
to show how variations in the investment cost would affect NPV for Projects S and L. Changes in sales, costs,
taxes, and the WACC would result in similar changes. Risk analysis involves studying such changes in the
input variables, learning how they would affect the output variables, and then seeking ways to (1) control the
range in the inputs and (2) seeking ways to minimize the effects of adverse changes in the inputs.

Sensitivity Analysis: Variations in NPV and IRR as Investment Cost Changes
  Investment          NPVS            NPVL             IRRS             IRRL             PIS         PIL      PaybackS
   Cost (D14)
The expected NPVs are both positive, the IRRs are greater than the 10% WACC, the PI's exceed 1.0, the MIRRs
exceed the WACC, and the paybacks exceed 3.0, so at the expected investment cost, the projects appear to be
good. However, if it turns out to cost more to develop the project, as often happens when high tech companies
are developing new projects, then the projects could easily turn out to be losers. This type of sensitivity
analysis can be applied to other variable, such as sales and costs, and the analysis can give us an idea of how
risky the project is. We can also look for strategies that will mitigate the effects of bad inputs. For example, in
our example, we might try to get a cap on the construction cost. All of this is explored in more depth for PNC
in Case 7.

Description: Financial Calculator Tutorial document sample