Skip to main content

About this free course

Download this course

Share this free course

Challenges in advanced management accounting
Challenges in advanced management accounting

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

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:

Download this video clip.Video player: Video 1 Layout and formulae
Copy this transcript to the clipboard
Print this transcript
Show transcript|Hide transcript
Video 1 Layout and formulae
Interactive feature not available in single page view (see it in standard view).

 

Download this video clip.Video player: Video 2 Using input ranges
Copy this transcript to the clipboard
Print this transcript
Show transcript|Hide transcript
Video 2 Using input ranges
Interactive feature not available in single page view (see it in standard view).
Download this video clip.Video player: Video 3 Calculate the internal rate of return
Copy this transcript to the clipboard
Print this transcript
Show transcript|Hide transcript
Video 3 Calculate the internal rate of return
Interactive feature not available in single page view (see it in standard view).
Download this video clip.Video player: Video 4 Calculate payback
Copy this transcript to the clipboard
Print this transcript
Show transcript|Hide transcript
Video 4 Calculate payback
Interactive feature not available in single page view (see it in standard view).

You can see the DCF spreadsheet (solution) [Tip: hold Ctrl and click a link to open it in a new tab. (Hide tip)]   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!