### Become an OU student

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:

Video 1 Layout and formulae
Interactive feature not available in single page view (see it in standard view).

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
Video 3 Calculate the internal rate of return
Interactive feature not available in single page view (see it in standard view).