Fundamentals of 3-way cashflow modelling
Picture (Source Pexels : Miguel Á. Padriñán)
19 December 2019 Written by Furqan Baig and Lance Rubin
Introduction to the co-author
Furqan Baig (“Furqan”) has historically been working in a range of different sectors from manufacturing and production of textiles, Real Estate Management and Investments as a business consultant and accountant.
Furqan, from the humble but bustling Dundee in Scotland he has recently become the managing director at Instruo Accountants and taken the leap to join the gig economy.
At Instruo he aspires to nurture a culture of interaction, learning and educating together with businesses, bookkeepers and accountants.
He does this through the use of technology and the human touch, in today’s technology driven world.
Why did Furqan select the topic and is passionate about it
It is a topic of great interest, as it can give us very accurate data about changes in our “cash situation” (net cash flow).
We base our forecasts on assumptions, for example we may have to “account” for the changes in working capital or net working capital.
Having used these techniques within a manufacturing environment previously, they have provided me with the results needed to find meaningful insights in relation to the business and its cash demands and future cash position.
Insights driven by the impact made on the numbers (including the key drivers), we can then begin to understand better the “behaviours” of the change in a business.
The subsequent effects of changes in business on the future for example seasonal changes, inflows and outflows of cash and realising our working capital could help us with better decision making and “adding more value” for us.
For example answering some of these important questions business owners might have:
Is it possible to reduce time taken for payments to be received and what impact would that have?
Could the inventory “turn-around” time be reduced, and inventory converted into cash?
How would the above affect our growth and cash?
Lack of such information to answer these questions and lengthy reporting cycle times are not allowing us to foresee or change processes in order to help the business generate cash needed for future growth targets.
Once we better understand the tweaks required in the drivers we were able to ensure those changes are reflected accurately in the 3-way financial model we have created and test those new assumptions.
We will be able to make the decisions and therefore the outcomes faster and more efficiently than before.
It can result in substantial changes in the cashflow of the company which is desperately needed for its survival, competition and development opportunities.
These opportunities often provide benefits beyond the business itself, but other areas on the balanced scorecard, such as community and environment.
The fundamentals of 3-way cashflow modelling is essential to helping to achieve these goals and are key tools needed for businesses both small and large, to foresee “easier” and respectively “harder” times ahead of time.
It will also help to foster business resilience and development, through managing its cash effectively and efficiently.
Describe the topic and context in only a few sentences
The fundamentals of 3-way cash flow modelling is often also referred to as the use of 3-way models that include the 3 financial statements that are all integrated through formulas and assumptions.
The 3 financial statements are the Income Statement, Balance Sheet and the Cashflow Statement.
They provide a significantly higher level of accuracy into the net changes in the cash position, year on year vs the simplified approach of opening and close cashflow basis only and which is why banks won’t rely on models for large funding unless they are 3-way.
A 3-way cashflow model allows us to accurately forecast the year on year changes on the balance sheet and analyse deeply, the changes on the profit and loss and cash flow from historical and forecast information that is more accurate than simply looking at the cashflow or the income statement on a standalone basis.
We’ll be looking closely at assumptions made in 3-way cashflow models and how these assumptions impact our forecasting.
It’s important to check for discrepancies in our assumptions when compared to reality and whether our strategy still makes sense or do we need to revisit the fundamental business model as it might be unsustainable, based on the initial set of assumptions being incorrect or unrealistic.
If you had to teach this topic in a class to school kids what key tips would you give them to focus on.
The key focus for those less familiar with 3-way modelling is to focus on areas where discrepancies (think mistakes) can often exist.
These may be in reference to our inventories (think widgets we are selling, maybe slime or lemonade) are recorded inaccurately or items unrecorded (think stashed in your parents pantry and you didn’t know about it) due to manual errors or due to internal controls (think of your parent’s house rules).
There may be discrepancies in currency fluctuations if you are trading internationally (think of buying slime ingredients on Ebay from overseas and prices change constantly), which are wrongly calculated (your parents see what it actually costs on their credit card vs what you told them).
There could also be fluctuations in the price of glitter, activator, colours and fragrance materials, depending on the type of stock you are purchasing (apparently there is a high demand for slime globally at the moment).
Furthermore, changes can differ depending on what our business objective might be and impact other aspects. i.e. maybe we start buying or selling in Amazon and not Ebay, or both.
The financial model will help us develop and shape (like clay and slime) those decisions and assumptions.
We therefore need to make some assumptions which are a combination of numbers and calculations, examples include;
What is the current and future growth percentage of volume of sales (number of slime kits we are selling or going to sell)?
What does each slime kit cost as a percentage of the revenue ie what is our cost of goods sold?
What other costs might be involved in those slime kits which we cannot physically see like the kitchen space we are using, the electricity or our own labour time (when we should be doing homework) as percentage of revenue? These are often referred to as operational or capital expenditure and are part of the overall profit calculation.
How long is it taking for the company to receive cash for its inventory after making a sale (how quickly can we use the cash to go watch movies or buy video games)?
What is the time frame on supplier payments, can we increase this to retain cash for longer (when does Mom or Dad expect me to pay them for the ingredients we bought)?
For short-term investments, how much cash have we retained from “holding onto supplier payments for longer? – longer credit terms. Can we convince your parents to get paid once we have sold 12 slime kits not just the first one?)
Are we generating cash faster than before? – what is the ratio between our annual sales and the average working capital over 12/18 months? (are we getting our cash sales quicker than we were when we started selling slime at the beginning?)
Often the reason for modelling is to forecast growth and monitor changes in net working capital (the net amount of how much slime ingredients we own at cost, what we owe people and people owe us).
Understanding and managing all the 3 items (ingredients, owe to people, people owe us) is the key cause of cashflow issues.
It is, in this case, vital, to know the liquidity of cash within the business as there may be an additional “injection” of cash needed to increase working capital (Parents might have to give us more money upfront), which allows us the choose for example purchase a higher amount of inventory (more slime ingredients) or to make supplier payments early for example (if we are trying to negotiate longer credit terms in the future).
This is an example of how we would ensure that working capital is sustained and the cash conversion cycle can continue at its “most efficient” level.
Capital for new machinery may need to be bought as a replacement or help us grow the business to produce more slime. It’s also possible that skilled workforce may be needed at a moment’s notice (maybe your brother or sister can help you). In this case it would be necessary to have liquid cash available for sudden capital or operational expenditure.
So it’s important to be looking at all of these items; sales, working capital, operational and capital expenditure in a strategic way (in the future, not just now).
As a result, your slime company will save cash outflows (payments to your parents) and create inflows (from your customers) quicker and retain cash for longer (so you can buy cool stuff).
This is why, it is important to understand the outcomes of our decisions to reach our objectives and the assumptions we are using in those decisions to understand it all in an accurate way through “the numbers”.
We will ensure that our 3-way model is being used in the most insightful way when combining all the different items listed and help us make a positive impact in the slime (or any) business and ensure accuracy and efficiency of cashflows.
What practical steps can people take now to learn more
So let’s get a little more technical now. The following steps should not be undertaken by children and require adult supervision:
If you think the Cashflow Statement and the Income Statement are the most important you are wrong.
If your Balance Sheet doesn’t balance or you don’t have a Balance Sheet (a fudged Balance Sheet is kinda like not having one so don’t do force balance it) to begin with how do you know that that other two are accurate? Answer – you don’t!
Revenue is vanity, Profit is sanity, but Cash is King (or Queen I was told), but the Joker is on you if you don’t have a Balance Sheet in your model (yes and it balances without a fudge factor).
If you have never built a 3-way model perhaps its time to start. Even a basic one can be found (free downloads) in some of the links below.
You would never believe this one, but crystal-clear understanding of double entry accounting is vital so that you can balance that all important Balance Sheet above. You need to understand how business transactions impact all 3 statements from end to end ie from cost of good or service sold all the way to cash in the bank via working capital of course.
Working capital modelling and understanding debtors, creditors and inventory (for product businesses) is one of the most important aspects of a 3-way model’s operational cashflows. Get this wrong or not sure how to model it and you will be in trouble. The days basis is often the best approach to model working capital which then allows you to see your cash conversion cycle in days.
An often-forgotten area of complexity (when doing just a standalone cashflow or income statements) are the investing and financing cashflows of a business in terms of modelling the full impact of capitalised assets, debt funding tranches and associated impacts.
Practice, practice and more practice until you balance your Balance Sheet. For those that have built a 3-way you would agree that the first model you ever build takes ages. At times it feels like you will never actually get it to balance. I spent 5 hours one late night in an Investment Bank just trying to get the Balance Sheet to balance and having to unpick all aspects and re-connect the Balance Sheet piece by piece. I forgot to start with a Balance Sheet and created it retrospectively. Rookie error.
A few other suggestions might be worth considering:
We can interact with people who have expertise in building these models so that we can ask them relevant questions like why they used the model in the first place?
Using financial models to possibly measure the feasibility of future strategic goals that an organisation may have? i.e. Is it within the company’s means to have big goals which it may not be able to sustain due to the size of it’s cash pocket? I’m sure many of us have been there personally and in business! Build your own personal financial model.
Increasing our working knowledge of software such as Microsoft Excel and Power BI etc. and help with better insight and foresight of assumption to put into our models.
Download models and take a closer look at them to learn more. Many models, including those built by Lance and his team, can also help you learn and understand their design.
Where are good places (links) to find out more on the topic
Useful links include the following:
Models shared by Lance and his team on Eloquens.com
The world’s financial modelling certification program by the Financial Modeling Institute which has 3 levels of expertise and require you to build a 3-way model from scratch under exam conditions for level 1.
3-way statement models can be found and learnt to a proficient level by the Corporate Finance Institute;
Book a demo for a chat with Furqan and talk about how me might help you think outside the box.
How important is this skill in the context of learning FM?
The skill of having an accurate “cash position” for a business is extremely important, if not its sole purpose and method of survival.
No cash = no business.
For financial modelling, it allows us to create a tool, to equip businesses to see the value in cash management and essentially being educated on the movements of cash within the business and how they are inter-related.
It is a topic which is near and dear to key stakeholders, in any business at times without realising the value of such skills/models.
For this to be reported accurately and faster than before a financial model is the perfect tool for the job of attaining future insights into the liquidity and planning development opportunities for a business.
Financial modelling allows for dynamism in reporting and flexibility in amending and forecasting assumptions and therefore the outcome-driven results achieved, in comparison to non-integrated models (standalone Income Statement and Cash Flows).
How does all this disruption, AI and automation talk impact this topic
3-way cashflow models can be something that automation is able to achieve, especially when we refer to “hard-coded” numbers, however what software is not able to do, is marry an organisations strategic goals and assumptions of historical or future trends and market conditions in custom built logic and data.
These, arguably, may be judgements, better considered by humans who can interact with each other and “connect the dots” – for example “what is going on around our market?” and “how we might respond to a possible threat.”
Will artificial intelligence be able to “sense” these market related assumptions, such as; X supplier may be going bankrupt, or supplier Y will not have a certain type of supply available to ensure our inventory is sufficient for continued sales?
Customer Z, who is normally great for making payment, is struggling at the moment and needs an additional 30 days to pay, can automation approve this?
Can artificial intelligence make a judgement on the value of goodwill today, and goodwill tomorrow due to changes in market perception, which has heavily affected the value of assets?
Can “What-if” analysis be created entirely through automation and AI?
Possibly for some of the above, however the drivers and outcomes of such assumptions, will always need a human element of judgement, therefore financial modellers/accountants are going to remain in high demand along with the skill of building robust 3-way financial models whilst even leveraging automation and AI to do this better and faster.
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App
If you want to find more information on financial modelling and content visit the Model Citizn website.