Loading

Alison's New App is now available on iOS and Android! Download Now

Study Reminders
Support
Text Version

Set your study reminders

We will email you at these times to remind you to study.
  • Monday

    -

    7am

    +

    Tuesday

    -

    7am

    +

    Wednesday

    -

    7am

    +

    Thursday

    -

    7am

    +

    Friday

    -

    7am

    +

    Saturday

    -

    7am

    +

    Sunday

    -

    7am

    +

We can also use our spreadsheet financial model to estimate the value of a going concern – a project or business which will continue to generate profits into the future.
It should be borne in mind that this is an estimate of the value of the project or business. It is not necessarily the value that a buyer would pay. The price that an investor would be willing to pay is going to depend on factors that include considerations outside of the pure financial projections – for example, the number of competing investors who are interested in buying into the market sector; the perceived long-term prospects for that sector; and the reputation your business has in that sector.

The Discounted Cashflow Valuation Model is straightforward and comprises three elements:
1. Free Cashflow. This is the net cash flow (outflow or inflow) in each period of our financial Model. This is most easily directly extracted from our Cashflow Model. It can also be calculated by adjusting earnings for non-cash costs and other changes. The link is to a useful article on Investopedia which gives more detail https://www.investopedia.com/university/dcf/dcf2.asp)
2. Terminal Value. This is the discounted value of future cash flows beyond the end of our Cashflow Model. The Gordon Growth Model uses the following formula for Terminal Value:
Terminal Value = final project year cash flow x (1 + long term cash flow growth rate)
(Discount rate - long term cash flow growth rate)

3. Discount Rate. We have already discussed the discount rate. The rate used in valuation should be the rate of return that the potential buyer (or the marketplace generally) expects.

The Discounted Cashflow Valuation Model uses the following formula
Estimated Value = NPV at applied discount rate of (project free cash flow s + Terminal Value)

Slide
Before we review an example, it is important to consider the weaknesses in the Discounted Cashflow Valuation Model:
• It is an estimate only, designed to give a “feel” for the possible value of a business, stand-along project, or subsidiary. It bears no relation to the current marketplace for business investment or acquisition; or the availability of people interested in buying
• Like all forecast models, the “value” generated is heavily dependent on the quality of the data used in the Model. It is useful to calculate a “best case”, “Worst case” and “Most Likely” values.
• A lot of weight attaches to the Terminal Value calculation. In effect, this assumes cash flow growth in perpetuity. I doubt many buyers would accept that proposition!

The Terminal Value issue is, I believe, the most contentious. For example, a final year cash flow of $100,000 with an assumed growth rate of 2% and a discount rate of 10% gives a Terminal Value of $1.27m. I find it hard to accept that a potential buyer would be willing to pay $1.27m (discounted to its present value) for a business with an annual cash inflow of £100,000 at the end of the projection period modelled.


While I am no expert on business valuation, I suspect that buyers in the current climate would be unwilling to value a business beyond 15 (or perhaps 20) years of cash flow projections, making the concept of Terminal Value redundant.

Valuing a Project or Business: Example
Let us return to the three projects that Pugh, Pugh, Barney McGrew and Investment Partners are considering. The partners now want to compare the possible sale “value” of the three projects.
Being conservative, they are assuming the rate of cash flow growth after Year 15 to be 0%, and they wish to compare the values using a discount rate of 10%.


As can be seen, the Terminal Values have a significant impact on the Discounted Cashflow Valuation. I can’t say I am convinced and find it hard to believe that a prospective buyer would value the project cash flow s of the Colley’s Mill development from Year 16 onwards at a present value of $718k when the first 15 years of cash flow only have a PV of $408k.
Regardless of this weakness, the Discounted Cashflow Valuation model can still be a useful way to compare projects as it provides more information for critical review. Not only can we review the profile of the cash flow s over the period covered by the Model, but it gives us an estimate of the project’s “value” beyond the period modelled. This gives us additional insight into project risk. A high Terminal Value suggests that our projections are reliant on cash flow s many years into the future – where forecasting risk is highest. By contrast, a relatively low Terminal Value suggests that we have less reliance on distant cash flow s.

End of lesson 18


Lesson 19: Exercise: Valuing Camberwick Green Care Home
Some of the private shareholders in the Camberwick Green Care Home have requested a business valuation for the planned extension in order that they might assess future growth in the value of their shares.
The following 10-year input data sheet has been prepared for the planned extension:


Assuming that all elements of income and expenditure are paid in the year to which they relate, and ignoring corporate Taxes, complete the following tasks:
1. Prepare a 10-Year Cashflow Model for the planned extension
2. Calculate the NPV of the project over the 10 years using discount rates of 10% and 15%. Comment on the impact on the NPV of increasing labour costs 5% above forecast in each year.
3. Use the Discounted Cashflow Valuation Model to estimate the market value of the extension project. The discount rate is 10%, and 1% growth in net cash flow after year 10 is assumed.
4. Instead of using Terminal Value for the valuation, discount an additional 10 years’ cash flow s using the Year 10 net cash flow growing at 1% per annum for years 11-20. The discount rate is 10%
5. What difference does using a cash flow growth rate of 0% make to both valuations?

Slide
The formula for calculating terminal value is:




Transition
Pause the video here while you prepare your answers
10 seconds

Screen Capture; Camberwick Green Valuation
Suggested Answer
The Model for the valuation of Camberwick Green Care Home extension is in the tab “Camberwick Green Valuation” in the spreadsheet with this course.

Here’s the input datasheet, and I also have a drop-down box so that I can vary the labour cost forecast to provide some sensitivity analysis.


1) The cash flow Model for this project is easy to prepare as I have kept everything simple in order to focus on the discounted cash flow s. The project shows a positive net cash flow in every year except year 7 where refurbishment costs kick in. The cash balance at the end of the project is $544,174
2) The net present value of the project after 10 years at a10% rate of return is $300k. If I change the rate of return to 15 % -– the net present value reduces to $237,709
If I change the labour costs to 5% above the original forecast-– the net present value at 15% drops to $124,304, almost half the previous net present value. Clearly, the project is very sensitive to cost changes
3) Going back to a discount rate of 10% and resetting the labour costs to the original forecast – we can calculate the terminal value at the end of year 10, assuming 1% growth in revenues thereafter. Using the formula, the terminal value is $1,386759. But that is 10 years in the future so discounting that to its present value is $534,656. Added to the net present value of the project after 10 years gives a Discounted Cashflow Valuation of $834,758
4) In this section at the bottom, I have added an additional 10 years to the net cash flow growing at 1% per year. Discounting the 20 years of cash flow s at 10% gives a net present value of $607,059 some way below the discounted cash flow valuation.
5) If instead, I change the growth rate in net cash flow to 0%– we get a discounted cash flow valuation of $776,528 compared to the net present value of 20 years cash flow s of $592,845

To my mind, I feel that the discounted cash flow valuation method is unrealistically optimistic and that potential buyers of the business would pay more heed to the net present value of the 20 years of cash flow s. Even then, they would want to ensure they have full confidence in the cash flow s projected.
Have a play with the variables in the spreadsheet to make your own mind up about the discounted cash flow valuation method.
Lesson 20 Example - Investment Options
You have decided to invest your $500,000 investment pot in a local business opportunity. You have three business projects to choose from:
1) Treadle’s Wharf Apartments is an investment in apartments for rent by the canal. There is strong demand for property in the area, meaning the net cash flow s projected are relatively low risk. A refurbishment programme is planned to take place during Years 12 to 15, which explains the fall in net cash flow s for this part of the project.
2) Clutterbuck’s Construction Company are looking for an investment in construction machinery which will allow the business to bid for larger projects. Net cash flow s are forecast to rise steadily with some further investment required in Years 12 to 14. There is good confidence in the figures for Years 1 to 10, but more speculative beyond that as other competitors may come into the area.
3) Chigley Ceramic Designs are looking for investment to expand their production of high quality, design-orientated ceramic pieces for discerning (and well-off) customers. Success depends on getting space in the “right” luxury retailers and on attracting the attention of a fairly fickle market.

Let’s go to the spreadsheet that accompanies this course to review the data for each opportunity. 5 seconds
I present 20 years of net cash flow s for each project from cell I6
And I summarise them on the left here for ease of viewing.
The net present value for 20 years of net cash flow s at the set discount rate – currently 10% - is shown in blue for each project, and, for comparison, I also show the net present value for 10 years of net cash flow s. We can change the discount rate in cell C4 to see the impact on the net present value
Below that I calculate the Terminal Value of each project after 20 years and then the present value of that terminal value.
Finally, that gives us the estimated value of each business opportunity using the Discounted cash flow method.

Based on the assumptions and forecasts used, all three opportunities present a very attractive net present value over the 20 years. On paper, at least, they would all enhance the investment pot.
But we should examine the data more closely to gain an understanding of the profile of each opportunity and the risks therein.
Treadle’s Wharf Apartments has the lowest NPV after 20 years and, by far, the lowest valuation using the DCF Valuation model, but it also seems the lowest risk. The apartments generate a steady cash inflow – except when refurbishment is required. Provided the area remains popular, and demand for rental accommodation healthy, this project should easily meet the required rate of return and deliver an attractive bonus on top.
Clutterbuck’s Construction Company has an attractive NPV over 20 years, but it is a riskier prospect – we are investing in the long term success of a business in a sector prone to cyclical peaks and troughs. We are told that there is high confidence in the forecasts for the first 10 years – and the NPV for those 10 years is $203k (which is half of the 10-year NPV for Treadle’s Wharf). However, the data supplied for years 11 to 20 is considered “speculative”. Given that risk, the present value of the Terminal Value for this business (£312,960 at the assumptions given) seems overly generous. Nevertheless, the business does seem to have the potential for healthier cash flow s throughout its life than Treadle’s Wharf Apartments.
Chigley Ceramic Designs is, essentially, a fashion business. If their ceramic designs grab the attention of influential figures in the art and fashion worlds and catch-on with style conscious and affluent buyers, then the business could be very successful. However, the risk of failure would also seem to be much higher than with the other businesses. Given that fashions do change, the present value of the Terminal Value calculated for this opportunity seems much too high, giving a valuation well above what, I believe, an investor would pay. In addition, the most significant cash flow s for this business are projected for years 11 onwards, and, particularly, in years 16 to 20 and beyond. Those forecasts are the most risky.

To my mind, the Chigley’s Ceramic Designs opportunity is too risky. That leaves Treadle’s Wharf Apartments or Clutterbuck’s Construction. The apartments are the lowest risk (on the face of it), but the construction company offers the possibility of stronger returns. More research needs to be done on both opportunities. The choice after that is yours!
Final thoughts on Spreadsheet Modelling with Excel
Microsoft Excel is a great tool. It is powerful and flexible and can handle surprisingly large amounts of data. It allows us to build useful financial models relatively easily.
There are problems with using Excel to build financial models – most notably that formulae can quickly become complex, meaning that errors (particularly in large spreadsheets) are difficult to detect. For this reason, specialist financial modelling software is commonly used for the most critical models.
Microsoft Excel allows us to build a financial model quickly and without too much pain. Importantly, if we structure the Model well, we can easily stress test the performance of the project being modelled by changing assumptions and input data.
And, essentially, that is the purpose of financial modelling – to help us identify the elements which are most critical to financial performance. Once we have identified those elements, we can focus management attention on getting them right and, thus, increase the likelihood that the project is successful.

To quote Bjarte Bogsnes:
“The purpose of a forecast is to get issues on the radar screen early enough to be able to take necessary actions. It is not necessarily about being right, but about being ready.”
Bjarte Bogsnes, Implementing Beyond Budgeting. 2nd Edition 2016

Thank you for taking this course Spreadsheet Models – Cash flow, Net Present Value and Pricing. I hope it will help you build your own spreadsheet models for pricing decisions, cash flow forecasting and investment analysis.
I hope you may be interested in the companion course to this one called Building a spreadsheet forecasting model. That course covers the structure for a spreadsheet model and the creation of a spreadsheet model of your business.

Thank you for taking this course. I wish you luck with your model building and hope that they bring you success. I’m Ross Maynard. Goodbye.