So far, we have looked at spreadsheet models that cover relatively short periods – typically a year. However, many projects or activities continue beyond a year – some for many years – and, in this final module, we consider financial models over the whole project lifespan. As the life of a project increases, the risks increase:
• market behaviour becomes harder to model
• incomes and expenditures become much more difficult to forecast
• unforeseen regulatory or geo-political changes may arise
• the whole economic landscape may change – we’ve seen that with the sudden onset of the coronavirus crisis
No model we build can resolve these risks. However, modelling long term projects can help us gain an understanding of the risk profile of a project and, thus, get a feeling for the level of “comfort” we have with the project.
I am not going to teach you about the time value of money, discounted cash flow s, and present values: you already know about them. If you need a refresher, Wikipedia has a good article on Discounted Cashflow at the link shown - https://en.wikipedia.org/wiki/Discounted_cash_flow
I am going to assume that you know how to discount cash flow s to establish a net present value.
The Project Lifecycle
When modelling a project over a number of years, it is important to be aware that all projects, products and services go through a lifecycle with the following stages:
1. Development – the planning stage, including preparation of initial financial models
2. Introduction – the start of project delivery, or launch of a new product or service
3. Growth – the hard work of getting the project, product or service established and successful
4. Maturity – the project, product or service reaches its peak in the marketplace. Growth slows as demand is fulfilled or alternatives come into the market. Income levels out, costs stabilise.
5. Decline – newer offerings become more attractive to consumers, or assets come to the end of their life. Income declines (sometimes over many years) and investment in the project, product or service reduces.
The financial Model for the project will go through a similar cycle with expenditures outweighing income in the Development and Introduction phases; income rising above expenditure in the Growth and Maturity phases; and either a managed profitable Decline phase, or a round of further investment and expenditure to try and extend its life.
The aim, of course, is that over the whole lifecycle, the project, product or service will generate an acceptable return.
Many projects, products and services fail in the Growth phase. The market simply does not find them attractive or prefers the competition. In such cases, losses are almost inevitable. Nevertheless, the financial Model can help users understand the risks they are taking at this crucial stage by showing the profile of expenditures and income.
The payback period is the period for which investors have to “hold firm” until they can expect cash inflows to at least cover the outflows they have committed. They may be able to take action to reduce the payback period by adapting their strategy for the Introduction and Growth phases – perhaps by a smaller scale introduction in a particular market to get it established before expanding into other markets; or by profiling expenditure differently.
At the same time, for successful projects, products and services, decline is not inevitable. There are many products and services which have been successful in the marketplace for decades. However, this often disguises the vast investments that their owners have made in refreshing the products and services for changes in the market. In terms of their financial profile, such long-lived products, services, and projects go through the cyclical product lifecycle curve multiple times.
The short-term models we have developed so far have had a fairly static income and expenditure profile. Now it is time to bring the project lifecycle into our thinking.
Let’s look at an example.
Project Lifecycle Example
We met Carraway’s Takeaway in the section of the course looking at modelling pricing options. They have decided to expand their traditional soup and sandwich lunchtime offering by adding sandwich wraps. £2.99 was selected as the profit-maximising price with the volume of sales expected to grow over the first six-months
Here’s the data we had before:
The team have now projected data for a five-year horizon.
We see that
1) The rate of sales growth is expected to slow and then decline as competition for lunchtime trade heats up.
2) Even to achieve the levels of sales forecast, price reductions are expected to have to be offered from Year 3
3) Wages and related costs are expected to rise at 5% per annum
4) An additional capital cost of £10,000 is expected in Year 4 to refresh the food preparation and sales areas.
5) All costs are assumed paid in the year to which they relate
6) Sales taxes are ignored.
7) Corporation Tax is assumed 20% of earnings after depreciation, payable in the following year, with any losses carried forward to set against future earnings.
Based on these figures, we are going to prepare a high-level cash flow model for the five-year project and calculate its Net Present Value (NPV).
The corporation tax due is calculated in this section.
Notice that both the cash flow s and the earnings of the project exhibit a lifecycle – growth, maturity and decline.
As it happens, the total earnings over the project life equal the total cash surplus generated. This is because the assets are written off over the lifespan of the project. This may not be the case for every project – for example, if part-used assets are transferred to another activity. Likewise, there may be other non-cash costs which affect earnings but not the cash flow of the project.
Using a discount rate of 10%, the project returns a positive net present value of £315,601 over its life. That is to say, even taking the 10% expected return into account, the project still generates a positive contribution to profitability.
Lesson 15 Selecting a Discount Rate for Net Present Value
Opinions vary as to the best discount rate to select for the Net Present Value calculation. An article in the Harvard Business Review (https://hbr.org/2014/11/a-refresher-on-net-present-value) states:
It is “the rate of return that the investors expect or the cost of borrowing money. If shareholders expect a 12% return, that is the discount rate the company will use to calculate NPV. If the firm pays 4% interest on its debt, then it may use that figure as the discount rate.”
Any positive NPV above the “expected” rate of return shows that the project will exceed the investors’ requirements (subject to the assumptions and forecasts used in the Model).
In the example of Carraway’s Takeaway, we looked at in the previous lesson, changing the discount rate used in the NPV calculation does not have a massive impact on the present value. With a 10% discount rate, the NPV is £315k, but using a discount rate of 5% only increases the NPV to £357k; while using a rate of 20% reduces NPV to £252k. This is because this project has a relatively short lifespan in the scheme of things.
The impact on longer projects would be much more significant.
This leads us to the three main causes of problems in net present value models:
1) The input data. The Model is (obviously) massively dependent on the quality of the forecast data used for income, expenditure and, particularly, capital investment. The longer the lifespan of the project, the more prone the financial forecasts are to error. Scenario analysis and stress-testing can help us gauge the possible impacts of changes in the data.
2) The discount rate. NPV uses one fixed discount rate for the whole life of the project. The business and economic trials of the last 15 years suggest that this is wishful thinking. One way around this might be to split the project into stages (of perhaps five years) and to calculate “mini” NPVs for each stage using different discount rates; and then to discount the stage NPVs. Stress-testing the project at different discount rates can also help understand the impact of possible changes in interest rates.
3) Inflation. The discount rate is not a measure of inflation, it is an expected (or minimum) rate of return used to see if the project generates a positive return above the expectation. Inflation should be built into the figures used in the Model since rates of inflation may be different for different elements of cost and income. It is not reasonable to ignore inflation for models with a long lifespan (more than, say, five years)
The discount rate chosen should be the minimum rate of return that the business, or its investors, from the project.
“The only function of economic forecasting is to make astrology look respectable”.
Ezra Solomon, quoted in Psychology Today, March 1984
Forecasting is the process of making predictions about the future. Usually, it involves analysing trends and projecting them into the future, and there are many businesses that make their money by employing specialists and highly sophisticated computer programmes to predict commodity and input prices, rates of growth or inflation, and so on. Most large companies will subscribe to a provider (or aggregator) of such forecasts for their financial modelling teams to use as “standard” inputs. This ensures consistency across models. Small businesses can buy-in such data on an ad-hoc basis.
Few forecasts are ever accurate, and it is common to use a range of prediction data. This makes modelling more complicated – what if the energy price forecast is at the top-end of the range, but the raw materials cost at the bottom-end of its range? We can build flexibility into the Model, but it still gives a vast range of possible scenarios.
One example of the difficulty involved in forecasting future revenues and costs is that of crude oil. The U.S. Energy Information Administration forecast the price of crude oil to average $61 per barrel in 2019 and $62 per barrel in 2020. By 2025 they forecast the price will average $82 per barrel (excluding inflation); rising to $93 per barrel in 2030 and $108 per barrel by 2050.
They were broadly correct for the 2019 price but the price of crude oil per barrel was well below the $62 forecast due to the coronavirus. The current price in May 2021 is $65 per barrel for West Texas crude. Who knows if the future prices will come to pass? If you travelled back in time to 2014, the forecast was that prices would hit $270 per barrel by 2020. The expansion in shale oil, and slower economic growth knocked that forecast on the head. In the last 10 years the oil price has actually ranged from $26 to $145 per barrel. How do you forecast for that?
The fact is that all forecasts are best guesses. Previous data does help us, but even with relatively stable elements of cost and income, long-term financial models are prone to tremendous levels of uncertainty. Our labour costs may be stable now, but who knows how robots and AI might impact labour costs in 10- or 15-years’ time? Our market may appear loyal and growing now, but what upstart newcomer, political upheaval, or social media trend might change all that in the future?
We can only do the best we can with the information available to us. But, with a long-term financial model, the important thing is to keep reviewing and updating. The Model needs to be revisited and refreshed every year (more often if significant events occur).
Lesson 16 Project Lifecycle Example
Pippin Fort Business Centre has experienced a decline in occupancy of its business units in recent years. There is strong competition from other business centres with better facilities, and the owners of Pippin Fort - Pugh, Pugh, Barney McGrew and Investment Partners, are planning a major upgrade: high-speed broadband; data cabling; an on-site restaurant; and state of the art video conferencing.
The upgrade work has been quoted at $400,000 to be paid for with a loan. Repayment of capital and interest will be made at the end of each year at a fixed rate of $44,000 per annum over 15 years.
The Model is in the spreadsheet that accompanies this course. Let’s go there now.
I have a detailed assumption sheet here which allows a lot of user definition in the Model.
We have the rental for each business unit in year 1, the number of business units available, the average occupancy of the units in each year, the various cost elements, different inflation rates for rental fees, labour costs and other operating costs, capital costs and the required rate of return which is currently set at 12%. All of these can be set by the user and they feed into the input data sheet, which shows the elements of revenue and costs over the 15 years of the project with inflation applied.
I then have an intermediate calculation – a short form income statement – to calculate earnings and corporate taxes, and I have created a simple graph to show earnings across the 15 years – it shows the classic project lifecycle. Earnings grow steeply as the project gets established in the marketplace peaking in years 3 to 5. Earnings then drop quite steeply as competition kicks in but increased spending on maintenance from year 8 onwards slows the decline, but the decline does continue as costs rise at a faster rate than revenues. By the end of the 15 years, the project is barely covering its costs, and it is time to think again about what to do about the business units.
Finally, at the bottom of the sheet I have the cash flow Model for Pippin Fort Business Centre, showing the cash inflows from rental income and the cash outflows for expenditure, giving a cash balance which builds steadily over the project.
The graph below shows the cash inflows and outflows over the period. The cash inflows are always ahead of outflows, but the gap narrows considerably in the final few years of the project.
The net present value of the project at a rate of return of 12% is $342, 456. This is a healthy positive return, so the project is definitely viable. If I was presenting this analysis to the management team for Pippin Fort, I would advise them that they should review their position in year 5 and make plans that might mitigate the decline in profitability in the later years. For example, some further capital investment for a refurbishment might reinvigorate demand for the business units and allow higher rents to be sustained for longer.
The project extends over 15 years which means there is considerable uncertainty about the forecasts used in the Model. We can test the risk in the Model in a number of ways by adjusting the figures in the Assumptions sheet.
Many combinations are possible. For example, reducing the forecasted occupancy of the business units for the years 8 to 15 from 75% to 65% results in losses in the Income Statement from year 8, but still manages to generate a positive NPV of $235k.
Changing the required rate of return to 15% (all other assumptions unchanged) reduces the NPV to just under $300k. This is because the higher returns occur in, the earlier years of the project. The rapid decline in cash flow (and profitability) from Year 10 onwards are disguised in the NPV calculation because it “downgrades” later figures.
This leads us to the weakness of NPV – which is that it does not reflect the financial lifecycle of a project. The use of discount rates gives higher weight to earlier years and much less weight to later years. This, of course, reflects the time-value of money: we would prefer to get our money back sooner rather than later. But it can divert attention away from the lifecycle of the project as a whole. In the Pippin Fort Business Centre project, the performance of the last five years of the project is, in my view, unacceptable. Costs are beginning to outstrip revenues, and we are overly reliant on relatively high occupancy. A fall in occupancy in the later years has a devastating impact on cash flow. I suggest that further work is needed in the project design phase to improve the performance of the project in its last five years.
Another weakness of the Model as presented is that we are assuming fixed rates of inflation and a relatively static cost profile over the whole life of the project. This does seem unreasonable, and, in real life, a project like this would require a more sophisticated, and, therefore, more complicated, Model.
Lesson 17 Comparing Projects
Financial models are very useful for comparing different investment opportunities. At the most basic level, we might compare the Net Present Value of each opportunity and select the project with the highest NPV. While a useful indicator, this should not be the only analysis carried out. As mentioned in the previous lesson, it is important to review the financial profile of the project, as well as consider the risk inherent in the projections.
Let us consider the three projects that Pugh, Pugh, Barney McGrew and Investment Partners have to consider:
Project 1 is the Pippin Fort Business Centre that we analysed in the previous lesson, and the table shows its net cash flows over time and the net present value. It has a payback period – without discounting of cash flow s – of 7 years.
The second project is a theme park at nearby Winkstead Hall. Again the table shows the cash flow s. This project has a higher net present value but a slightly longer payback period
The third project at Colley’s Mill has the highest net present value of all and a payback period of 8 years.
Which should we choose? All have an NPV at a similar level. The choice depends on the investors’ attitude to risk, their confidence in the forecasts used in the Model; and their long-term versus short-term perspective.
The risk-averse investor might choose Project 1 since it generates a positive cash flow right from the start. The chances of recovering our investment are good with the shortest payback period of all the options. However, this project has a relatively short life and shows a significant decline in cash flow from Year 6 onwards.
Project 3 gives the highest NPV, but its cash flows are heavily loaded into years 11 to 15. The risk of forecast errors or unforeseen events would seem to be relatively high.
Project 2 suffers the highest cash outflows in Years 1 to 5 and, therefore, presents the highest risk should the project fail. Nevertheless, after the initial implementation period, the project shows strong growth in cash flow. Its payback period is only marginally longer than Project 3, and the opportunity for long term profitability would seem good – as far as we can rely on the forecasts.
Which would you choose?
Log in to save your progress and obtain a certificate in Alison’s free The Fundamentals of Spreadsheet Modelling online course
Sign up to save your progress and obtain a certificate in Alison’s free The Fundamentals of Spreadsheet Modelling online course
Please enter you email address and we will mail you a link to reset your password.