Document Sample

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 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 Taxes 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. PAYBACK PERIOD 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: =IF(E47<0,"-",IF(D47<0,E45-1+ABS(D47)/E46,"-")) 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: Payback DISCOUNTED PAYBACK 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 payback. 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 NET PRESENT VALUE 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. INTERNAL RATE OF RETURN 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. MODIFIED INTERNAL RATE OF RETURN (MIRR) 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 MIRRL 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 Tables. IRRS MIRRS IRRL MIRRL Reinvestment rate; WACC (D100) -10% 0% 10% 20% 25% 30% IRR and MIRR for Project S 120% IRR 100% MIRR IRR and MIRR 80% 60% 40% 20% 0% -10% 0% 10% 20% 30% Expected Reinvestment Rate COMPARING NPV AND IRR 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 (B73) 0% 5% 10% 0% 0% 25% GRAPHING NPV PROFILES 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% NPV $20,000 NPVs $10,000 NPVs $0 IRRS = 20% -$10,000 0% 5% 10% 15% 20% 25% WACC 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 conflict. 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 Replacement Net total NPVSS 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 NPV IRR1 IRR2 PI MIRR WACC NPV Multiple IRRs (B190) 0% 5% $200,000 7.44% 10% NPV 20% 25% -$300,000 30% 35% 40% 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 -20,000,000. RISK ANALYSIS 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) -$50,000 -$75,000 -$100,000 -$125,000 -$150,000 -$175,000 -$200,000 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.

DOCUMENT INFO

Shared By:

Categories:

Tags:
financial calculator, Calculator Tutorial, how to, interest rate, Real Estate, time value of money, Financial Calculators, CPM designation, Future Value, Present Value

Stats:

views: | 189 |

posted: | 11/14/2010 |

language: | English |

pages: | 7 |

Description:
Financial Calculator Tutorial document sample

OTHER DOCS BY rsl17547

How are you planning on using Docstoc?
BUSINESS
PERSONAL

By registering with docstoc.com you agree to our
privacy policy and
terms of service, and to receive content and offer notifications.

Docstoc is the premier online destination to start and grow small businesses. It hosts the best quality and widest selection of professional documents (over 20 million) and resources including expert videos, articles and productivity tools to make every small business better.

Search or Browse for any specific document or resource you need for your business. Or explore our curated resources for Starting a Business, Growing a Business or for Professional Development.

Feel free to Contact Us with any questions you might have.