3.8 Using a spreadsheet to model project evaluation
In practice the factors to be included in a project evaluation decision are likely to be more detailed and complex than in the examples above. It is useful to model them in a spreadsheet that can be used for different projects and to allow different scenarios to be analysed quickly and easily. In this section we will work through an example of a spreadsheet designed for this purpose.
You will watch four videos which show you how to build a spreadsheet discounted cash flow model. The four videos show a range of techniques and complement each other so watch them in the order they are presented. In your first viewing, watch the four videos one after the other. They are:
- Layout and formulae
- Using input ranges
- Calculate the internal rate of return
- Calculate payback
All the videos use the following investment scenario:
A company plans to invest £1,000,000 in a new product that will generate revenues (cash receipts) of £300,000 in Year 1, £500,000 in Year 2 and £600,000 in Years 3 to 5. Costs (cash payments) will be 25% of revenues (cash receipts) and are paid in the same year as the relevant cash receipts.
This company uses a 10% discount rate for projects of this type.
The corporate tax rate is 28%. Taxation cash flows occur in the same year as the relevant taxable receipts and payments. There are no taxation cash flows arising from the investment of £1,000,000.
Calculate the NPV, the IRR and the payback period of this investment.
Now watch the videos:
You can see the DCF spreadsheet (solution) here.
Spreadsheets can be useful tools for decision making, but it is very important that they are constructed and tested carefully. Research suggests that poorly designed and controlled spreadsheets are reasonably common in business and can cause sizable losses (Powell et al, 2009).
This section has looked at the principles which are the foundation of the project evaluation. These include what information to include in an appraisal, using concepts such as relevant cash flows, sunk costs and opportunity costs. The time value of money and how project appraisals account for it by using a discount rate were discussed. Some approaches to estimating the discount rate, and their advantages and disadvantages, were discussed and the use of a spreadsheet to model discounted cash flow decisions introduced.
When performing project appraisal for decisions in practice, you would need to decide what the appropriate discount rate to use is, and the information in this section should help you do that. The guidance about designing a spreadsheet should also assist in applying this understanding to practical situations, but note the warning from research studies that it is important to be very careful using any tool - but spreadsheets in particular!