Ladies and Gentlemen. Welcome to Spreadsheet Models: Cashflow, Net Present Value and Pricing. In this course, we use good spreadsheet modelling practice to build three types of spreadsheet models - a pricing decision model, a cash flow statement, and a project lifecycle analysis using discounted cash flows.
This course is a companion to my course Building a Spreadsheet Forecasting Model. In that course, I introduced the structure I recommend for building a spreadsheet model. I will only summarise that framework in this course. The focus of this course is practical examples of some of the most important spreadsheet models.
This course assumes a good basic knowledge of Microsoft Excel, but advanced knowledge is not necessary and, as with the previous course, Macros and visual basic are avoided.
Our agenda for this course is as follows:
• In Lesson 1, we revisit the guiding principles of spreadsheet model building and the key elements of a spreadsheet model
• In lesson 2, we look at the types of financial models that can be built using Microsoft Excel and also the risks of using Excel
• In lesson 3, I cover the work involved in getting ready to build a spreadsheet model, including identifying stakeholders and defining the problem that the model will address
• In Lesson 4, we have an exercise on stakeholders and assumptions
• In lesson 5, we start to look at models for pricing decisions. What is the profit maximising price for a business
• Lessons 6, 7 and 8 are all examples of pricing models
• And lesson 9 is an exercise giving you the chance to get to grips with a pricing decision model
• In Lesson 10, we turn to cashflow models with an example cash flow model in lesson 11
• In lesson 12, we look at the issues you need to consider when developing your cashflow model to include an income statement and balance sheet
• In lesson 13, we have a cash flow model exercise
• In lesson 14, we move onto investment analysis and project lifecycles
• Lesson 15 discusses how to select an appropriate discount rate for net present value analysis, and lesson 16 covers an example analysis
• In lesson 17, we look at comparing projects using discounted cash flows and net present value
• And in lesson 18, look at the Discounted Cashflow Valuation Model and its weaknesses
• Lesson 19 is an exercise in valuing a business investment, and we finish the course in lesson 20 with an example comparing investment opportunities
I hope you enjoy the course. Let us get started.
In the companion course to this - Building a Spreadsheet Forecasting Model – I introduced 10 principles that guide my model building. In summary, those principles are:
1. Keep the Model Simple and Flexible. The purpose of your spreadsheet model is to identify and analyse the impacts of possible outcomes, not identify a definite outcome. The power of the model lies in understanding how different variables affect it and it is better to have a simple, relatively small, model that is easy for users to understand, and to manipulate, than a complex model that users struggle to understand.
2. The spreadsheet model should read logically from left to right. In separate tabs, it should have inputs, calculations and outputs, with additional appendices as required.
3. The model should allow users to easily change inputs and variables so that they can quickly evaluate the new outcome compared to the baseline.
4. There should be an “Executive Summary” tab at the very front of the spreadsheet model to highlight the most important results of the analysis.
5. All the assumptions used in the model should be spelled out in the “Assumptions” area of the model. Factors such as tax rates, commission rates, and so on should be user-definable for scenario analysis.
6. Use colour coding and other formatting to highlight the different types of cell.
7. Formulae should be consistent across the cells in a table (with the exception of a TOTAL column at the end). In addition, date progression in tables should cover the same time periods to allow comparison
8. Avoid excessively complex formulae in cells. If the necessary build up complex computations rather than do it all in one step.
9. Do not hide columns or rows. Either use grouping to present headline numbers and allow sub-elements to be revealed, or use intermediate calculations sheets to show the detailed build-up.
10. Avoid the use of macros and VBA (visual basic) as far as possible. IT departments fear them because they are a security risk; and non-expert users don’t like “magic tricks” where they can’t see what is happening.
The Key Elements in a Spreadsheet Model
The key elements that should appear in every spreadsheet model, are as follows:
• Executive Summary
• Purpose and Scope
• Definitions, Assumptions and Sources of Data
• Version Control
• Input Data
• Intermediate Calculations
• The Model Itself
• Output Data
• Appendices, including look-up tables if required
• User Guide
In large models each section would have a separate tab. In smaller models, it may be possible to combine sections into one tab.
I also use colour coding for each element
• Documentation sheets are coloured light brown. This is used for tabs which cover the spreadsheet purpose and scope, definitions, assumptions, sources of data, version control and appendices
• Input data sheets have a yellow tab. All input data and user-controlled assumptions are placed on a sheet with a yellow tab, and the data entry cells are also coloured yellow
• The spreadsheet model itself, including any intermediate calculation sheets, are located on tabs coded blue
• Output data sheets, including the Executive summary, have a green coloured tab
There are many types of financial models that can be built using Microsoft Excel. The main types are:
• A Project Finance Model. Large projects will use a financial model as a basis for budgeting and to forecast outcomes, including cash flow s to match debt repayment.
• A Pricing Model. The price charged for products or services is part of the “marketing mix” for the operation. Pricing has a significant impact on demand and, therefore, profitability.
• A Cashflow Model and Integrated Financial Statements. A financial model can be built to integrate the cash flow, profit and loss and balance sheet statements. These models can easily become very complex, and the challenge is to keep to the principle of keeping it simple and flexible.
• A Valuation Model. A valuation model will be built to assess the value of a business or business unit or potential new business opportunity. In the case of a business acquisition, the seller will present a model that maximises the value of the business; and the potential buyer will critically analyse that Model to determine its feasibility and to establish their own valuation. The Model may also be required to illustrate how debt raised for the purchase will be serviced.
The Risks of Excel Models
Microsoft Excel is a versatile and powerful spreadsheet programme. It is perfect for building flexible and user-friendly financial models, but it is not necessarily ideal for every type of Model. There are dangers in using Excel for modelling, and there are specialist financial modelling packages available for the most complex and challenging situations.
Various studies have suggested that up to 90% of spreadsheet models contain material errors. While Excel does contain some error-checking capability, it will rarely detect a formula error in an IF statement with many clauses or similar core errors.
Errors develop in Excel models for a variety of reasons:
1. Over-complexity is the main cause of errors. As we seek to build sophistication into our Model, it becomes ever more complex. Formulae can soon become too complicated, with too many clauses, to understand, and errors can then arise. Applied diligently, our Guiding Principles of Spreadsheet Model Building will help guard against this problem – particularly clearly defining assumptions; building up complex formulas in stages to allow transparency, and avoiding macros or VBA
2. Re-using previous models. Re-tooling a model that has been previously used has its appeal and its benefits – the hard development work has already been done. But it also has risks. Any errors in the previous Model are carried forward; and, if the earlier Model had a slightly different purpose, or was created in a different context, then it may manipulate the data in a way that is not quite right for the present purpose; or it may use assumptions which are not right for the present purpose. In many cases it will be better to start afresh and build a new model using the old Model as inspiration. That way errors are less likely.
3. Delegation of Responsibility. It is extremely tempting for a manager or project lead to delegate the task of building the Model to a junior. It is a time-taking and tedious task after all, and the keen young graduate or trainee is often better suited to the task and motivated to learn. It is a great learning opportunity, but completely leaving your Model to an inexperienced junior comes with many risks, particularly when they are not aware of how errors arise and have not experienced the damaging impact of errors for themselves. Involving a junior member of staff is fine, provided their work is regularly reviewed in detail. If the financial model turns out to be wrong, the buck stops with senior management, and the financial impacts can be catastrophic. Don’t hand over its creation to an inexperienced trainee!
4. A Lack of Boundaries. Microsoft Excel is flexible and powerful. It has few boundaries and allows you to structure and build a model in almost any way you want. That means it has an enormous variety of uses. It also means there are few checks and balances, and inexperience or indiscipline can easily lead to errors. Again, using the Guiding Principles of Spreadsheet Model Building and the structure suggested, or a similar one, will reduce this danger.
Microsoft Excel is great for simple modelling, particularly for small and medium-sized businesses; but it is usually not suitable for complex modelling. I don’t think I would trust the Financial Model for a business merger (or demerger), or major new investment to Excel. There are specialist financial modelling packages available and it is not the purpose of this course to review those. Here we concentrate on robust, simple and flexible financial models.
One of the biggest temptations of spreadsheet modelling is to dive straight in and start building. Resist that temptation! You will often end up having to scrap large parts of your Model if you rush in because you didn’t identify all the requirements correctly.
There are five key steps you should take before you start to build the Model. They will help you build a better model more quickly and with less rework.:
1. Identify all the Stakeholders in the Model
2. Define the Problem that the Model will Address
3. Agree the Assumptions for the Model
4. Get Good Data!
5. Plan your Outputs
Identify all the Stakeholders in the Model
Who are the key stakeholders in the Model you have been asked to create? The manager or project leader who asked you to work on it? Possibly, but not necessarily. In most cases, the Model will have a wider range of stakeholders and interested parties than just the person who requested it. It will greatly help the planning of your Model to identify everyone who will use the Model or who will be impacted by its results. The Model needs to meet the needs of all its stakeholders, so the first step is to identify who those stakeholders are.
The stakeholders in the Model may include everyone who will use the Model and everyone impacted by decisions based on the Model. For example:
• The business owners and/ or shareholders
• All senior managers impacted by the decision or using the Model
• The funders of the project
• Key customers
• Managers and staff in the part of the business being modelled or affected by the decision.
As the modeller, you may not have the authority to approach all of these stakeholders, but you should advise the project leader and senior management that it is important to determine the needs of the stakeholders and to gather their views on the purpose of the Model and the outputs they want from it.
The requirements of the stakeholders can be gathered through one-to-one interviews or as part of the workshop described in the next section.
The key question for each stakeholder is:
“What is the purpose of the model for you, and how will you use the outputs?”
Define the Problem that the Model will Address
A project gets started; a model is needed, but what, exactly, is the purpose of the Model? The problem being addressed and the purpose of the Model needs to be defined and understood by everyone involved, otherwise, the project leader may make one assumption, the modeller may make another assumption, and other stakeholders may have a whole different set of expectations.
In my experience, the best way to get an agreement on the problem to be addressed and the purpose of the Model is in a face-to-face meeting or workshop. The meeting should involve all the stakeholders in the project and anyone else who will use the resultant Model to make decisions. The meeting can also be used to define the requirements that each stakeholder expects from the Model, including outputs, scenarios to test; stress tests to apply, and so on.
On its own, the problem can probably be defined in a session of one to two hours, though it is usually advisable to bring other elements into the meeting – for example agreeing the assumptions that will be used.
I like to get the group to agree a “Problem Statement” before starting work on the Model itself. The Problem Statement summarises the issue we are addressing in three or four sentences:
– What is the business problem we are seeking to address with the Model?
– What are the consequences of the problem?
– Who is affected, and how?
– What are the potential impacts of the problem on the unit or organisation?
The Problem Statement brings all of the stakeholders together and agrees a common purpose for the Model. Here are some examples:
• The company seeks to review the costs and benefits of extending business opening hours. The impact on costs, profitability, staffing and other resources is required to be assessed. The marketing team will provide data on the likely customer response.
• The business seeks to assess the viability of opening branches in the Eastern Region, including potential profitability under a variety of scenarios; and cash availability, in each scenario, to service the debt required.
• The company seeks to review the impact on profitability of various pricing strategies, including the marketing and development costs required to sustain each pricing strategy.
The key question to define the problem is:
“What is the issue that the model will address, and what is its purpose as agreed by the stakeholders together?”
Agree the Assumptions for the Model
The assumptions that drive the Model have a material impact on the results that it generates. It is important, therefore, that all the stakeholders in the Model understand the assumptions used in it and, as far as possible, agree that they are appropriate.
Many assumptions will be uncontroversial – the tax rates applied, the capital allowance rates used, and so on.
Other assumptions, however, may be subject to much debate:
• What rate of inflation should be used for revenues and costs if any? Should the same rate be used for both revenues and costs (probably not)? What rates should be used for different cost items (for example, wages, raw materials, energy etc)? The inflation rates chosen will have a major impact on the Model.
• How will the project be financed? Higher levels of debt financing will impact profitability and corporation tax. Different stakeholders may have differing views on the appropriate financing profile for the project.
• How should the project deal with tax? It may be that different stakeholders have different views on how potential tax liabilities should be handled.
• What levels of profitability (or payback) does each stakeholder require for them approve the project?
• What is the risk appetite of each stakeholder?
• What are the ethical and environment requirements of each stakeholder that need to be built into the assumptions? For example, sourcing of raw materials; labour contracts; and so on.
• What level of oversight and governance does each stakeholder require?
All the assumptions need to be agreed by all the stakeholders and this may require an iterative cycle of calls and meetings until everything is settled.
The key question to agree the assumptions with each stakeholder is:
“What are the fundamental assumptions all of the stakeholders agree are required as a basis for the model?”
Get Good Data!
It sounds self-evident, but your financial Model is only as good, and as robust, as the data you put into it. And I have seen models built on some pretty shaky data!
Before you start to build the Model, it is important to understand where the data for it is to come from and how robust that data is. You may have to source data from the I.T team, from finance, from marketing, from operations, from engineering, or from other sources. You and your Model may not be the top priority of any of these teams which means it is necessary to plan ahead – well before you need the data for the Model.
A number of considerations are necessary:
• Discuss with each relevant team the information you need (and its purpose). Review with the team the availability of the data; its source; the format it can be extracted in; how recent the data available is; and how complete it is comparted to your needs.
• Agree a timetable with the team to extract and prepare the data.
• Work out how you need to manipulate the data in order to get it into a format/ structure you can use. If possible, get an early “trial” data extract so that you can practise. This may be data from previous periods, or a small sub-set of the information you want.
• Make an assessment of the robustness of the data that you can get. Is it based on hard facts (real data)? Or is it estimates and best-guesses? What risk factors do you need to build into the data to mitigate against perceived weaknesses in the data? You probably need to define those risk-factors as assumptions and make the stakeholders aware of them. This can be politically sensitive are you are, in effect, calling out a team’s inability to provide robust data. Building sensitivity analysis into your Model will also help you (and other users) explore the impact of changes in input data.
The key question on data is:
“What sources of data do we have for the model and how “good” is it?”
Plan your Outputs
When building a model, it is important to know who is going to use it and what they need from it. Much of this information will come from the other preparatory activities discussed in this lesson:
• Identifying the stakeholders for the Model allows you to start to understand what the Model will be used for
• Defining the problem provides the opportunity to formally discuss and agree purpose of the Model, its expected outputs, and the tests and scenarios the stakeholders would like to see.
• Agreeing the assumptions to be used in the Model will further help you understand the requirements for outputs and scenarios.
• Reviewing the quality of available data may modify the expectations you have of the outputs that you can deliver.
The key question you must answer is:
“What outputs are expected from the model, and what can I realistically deliver?”
Lesson 4 Exercise: Getting Started with a Model
You have been asked to help prepare a pricing model for your friends Suzi and Sam, who run the Trumpton Town Café. They opened the café two years ago with funds loaned by a wealthy family member and by a locally owned catering supplier keen to encourage businesses to use their products.
The café employs four part-time members of staff, including a bookkeeper.
The number of customers using the café in the morning from its opening time of 9.30 am to 11.30 am has been low. Trade is very good at lunchtime and for afternoon teas, but Suzi and Sam are losing money on those first two hours.
They would like a simple financial model to model different pricing strategies for that morning period to see how they might improve profitability. Data on price and potential demand has been supplied by the catering supplier based on the experiences of other catering outlets. The information on costs has been extracted from the café’s own records.
The options are:
1. Close the café for the morning period, opening at 11 am to prepare for lunch. That would save wage costs of $1200 per month, and energy costs of $300 per month.
2. Open at 9.30am as at present and offer a “morning coffee” promotion to attract early shoppers.
3. Open at 8am and offer a special breakfast promotion to attract early business. The catering supplier loan-holder has provided data on possible outcomes. This would incur additional wage costs of $1200 per month, and energy costs of $300 per month. The “morning coffee” promotion could also be offered.
1. Who are the stakeholders for the pricing model?
2. What aim is each of the stakeholders likely to have for the Model? And, consequently, what assumptions used in the Model might each stakeholder have a particular interest in? It is assumed that the loans in place have agreed interest rates, and that assumptions about inflation, taxation and other regulatory issues are already set.
3. Write a couple of sentences as a “Problem Statement” for the Trumpton Town Café.
4. What data sources have been made available for the Model? As a modeller, are there any other data sources you would like to research?
Pause the video here while you consider your answers
1. The stakeholders for this pricing model are Suzi and Sam themselves; the family member who has invested in the business; and a representative of the catering company who has invested. I believe that the staff members who may gain or lose work as a result of the changes should also have a representative stakeholder.
2. Suzi and Sam are likely to want to improve the overall profitability of their business and, therefore, its long-term success. They will be interested in risk assessing the pricing-demand data in order to make an informed decision. They will be particularly interested in assumptions about growth in customer sales and costs.
The family investor will be primarily interested in the financial security of the business in order to get their money back, with the interest agreed. Like Suzi and Sam, he or she will be interested in the assumptions about customer numbers and sales. He or she will also be interested in any assumptions that may impact cash flow – for example, creditor payments; and any assumptions about any additional capital requirement and how it would be funded.
The catering supplier funder will also be interested in getting their money back with interest and growing the café as a customer. They will be interested in the same assumptions as the investor. In addition, as a supplier to the café, they will be interested in any assumptions made about the sourcing of the additional supplies needed, including assumptions about catering costs. There is the possibility of friction between Suzi and Sam, and this stakeholder if the café wishes to use other suppliers – for example, artisan bakers for the “morning coffee” promotion or local farms for the breakfast menu. Nevertheless, their primary concern will be the security of their loan.
The staff will be concerned about any impact on wages and workload. They will be interested in assumptions about how additional hours will be resourced; and what the possible reduction in hours will do for their jobs. The café’s staff may be enthusiastic about the potential for additional hours (or a pay-rise if the promotions are successful). However, some of the staff may not want to work extra hours; or may be worried about the impact of bringing in new staff.
3. My Problem Statement for the Trumpton Town Café is as follows:
“Presently, Trumpton Town Café is making a loss on its activities before 11.30am. This puts the business and its staff at risk, and we seek to reverse this situation by testing different pricing strategies for the early morning. The aim is to find a solution that boosts profitability by at least 10%; while allowing a wage increase of the same amount; and ensure the financial security of the business”.
4. The data sources for the Model will be the company’s own accounts and the supplier’s provided price-demand information. We might also seek the views of the staff – they know the customers best - and might have useful insights in the likely demand and suitable price-points for the promotions suggested.
I would suggest that the modeller also seek other information about the potential impacts of various pricing strategies. A local catering college may be able to provide information and insight, or even students to assist with research. I would also suggest talking to café owners in similar (but not competing) areas to understand what pricing they use and the levels of demand they achieve. Café or catering trade bodies may also be able to help
Lesson 5 Building a Pricing Model
Pricing is one of the most powerful tools available to businesses, and the strategy chosen can have a major impact on profitability. However, I believe that many small and medium-sized businesses, in particular, spend little time modelling the potential impacts of different pricing strategies.
The purpose of a pricing model is to test the impact of different pricing strategies on the business. The aim is to identify the likely level of customer demand at different price points and then assess the costs of operating at each price point, giving a contribution at each price. Assuming that the company can market the product or service successfully at the chosen price point and attract the number of customers forecast at that price, then that would be selected as the profit-maximising price.
To quote Warren Buffett
“The single most important decision in evaluating a business is pricing power... If you’ve got the power to raise prices without losing business to a competitor, you’ve got a very good business.”
Warren Buffett, in “Inspiration to become a better investor”, Forbes Magazine 2013.
Planning the Input Data Sheet
Having identified and consulted with the stakeholders in the Model, agreed the assumptions with them; and sourced suitably robust data, we can now structure the input data sheet for the Model.
The following considerations need to be borne in mind when building the Input Data sheet for a Pricing Model:
• The key is to be able to test the impact of different price-demand scenarios. The input datasheet, therefore, needs to be designed to allow the user to select the different data sets for each pricing strategy
• Pricing is an inexact science, so it is also important to test the risk in each scenario – either with “best”, “worst” and “most likely” outcomes for each strategy; or reviewing the impact of a selected percentage change in revenues.
• The easiest way to present the input data is usually as a price-demand curve. We discuss this further in the next lesson.
• Remember, pricing strategy is not just about the price you set. There is a whole “marketing mix” involved – presentation, customer service, support, location, promotion, and so on. The financial Model can reveal the possibilities, but the whole package has to be delivered on the ground to make it work.
The Input Data sheet needs to be planned in a way which provides for these considerations and presents the input data clearly so that users can test the various options and fully understand the implications and requirements of each.
The Price-Demand Curve
It is generally believed that demand increases as the price falls, and vice versa. But this is not true! Certainly, price promotions can get more people trying your product or service, but the demand for it is a function of the whole marketing mix, with price only being one factor. The “image” of the brand and product that is created in the customer’s mind by the marketing mix is more important than the price alone. I am sure you can think of plenty of premium-priced products or services which achieve high levels of demand because the marketing mix is very strong, and this is true across all business sectors.
The Price-Demand curve that you build for your pricing model, therefore, has to be based on well-researched information. Sourcing good data is even more important here than with many other types of financial models because it is speculative. You may be able to get comparable data from other businesses in your sector or region or from employ specialist consultants, but even that data is speculative. No one can know how it will work for your business. Stress-testing the scenarios is vital.
Here are some of the things you can do to develop a price-demand curve:
• Speak to friendly businesses in related but not competing fields and find out how pricing affects demand for their products and services.
• Speak to similar businesses in different territories to see how price and demand are related for them
• Trade Associations can be a useful source of information and contacts to help your research.
• Speak to your customers to determine how they might react to a change in prices (combined with changes in other elements of the marketing mix – for example, better support, faster delivery, more “added value” services, and so on). Subtle questioning will work better than straight-out questions since, when asked straight, few customers are likely to say they are willing to pay
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.