Build Your Cash Flow with a Spreadsheet: Create a Practical Forecast

About this episodeCash keeps the lights on in business. Profit matters, but if cash runs out, the business can quickly feel under pressure. A cash flow forecast helps us see what money may come in, what money may go out, and what could be left in the bank each month.In this episode, we explain how to build your cash flow with a spreadsheet. We look at the first steps, spreadsheet building principles, the business Lego bricks approach, formulas, copy and paste, money in, money out, cash contribution, and how to use the forecast to reshape your business story.What you’ll learn in this episodeWhy a cash flow forecast is essential for business planning.How spreadsheets can help tell your cash story.Why we should decide what the spreadsheet needs to show before building it.How to separate inputs, formulas, calculations, and outputs.How the business Lego bricks approach makes forecasting easier.Why copy and paste can save time and reduce mistakes.How to use the forecast to test and improve business decisions.Why cash flow forecasting mattersA cash flow forecast helps us look ahead. It shows whether the business may have enough cash to pay suppliers, wages, overheads, tax, loan repayments, and other commitments.This matters whether we are starting up, growing, pivoting, or managing an established business. Cash flow forecasting gives us visibility before problems arrive, rather than waiting until the bank balance creates panic.If you want a broader foundation before building the spreadsheet, our episode on Making your cashflow forecast is a useful starting point.Why use a spreadsheet for cash flow?Spreadsheets are one of the most useful tools in the financial toolbox. They take the heavy lifting out of number crunching and help us organise the cash story of the business.The principles in this episode apply whether we use Microsoft Excel, Google Sheets, or another spreadsheet package. We do not need to be an IT expert or maths genius to build a useful forecast, but we do need to think carefully about design, layout, formulas, and how the spreadsheet will be used.A good spreadsheet should be clear, readable, flexible, and easy to update. It should help us understand the business, not create confusion.Start with what you want the spreadsheet to showBefore building the forecast, we need to decide what we want it to tell us. In the episode, we use the example of the I Hate Numbers Food Palace, a business that prepares and delivers food to corporate clients.The example may be food-based, but the approach works across many businesses. The key question is simple: what is the financial outcome of our planned activity over the next 12 months?We may want to see:cash coming in from customers;cash going out to suppliers, staff, and overheads;cash contribution from sales after direct costs;cash left after wider commitments;monthly closing bank balance;whether cash is building up or running down.Do not edit the story too earlyWhen building a cash flow forecast, it is tempting to edit as we go along. We may look at an idea and think, “I cannot afford that,” before the full picture is even in the spreadsheet.The first step is to get the story into the forecast. Put in the assumptions, the activity, the timing, and the numbers. Let the spreadsheet show what the plan means in cash terms.After that, we can go back and change the story. If the forecast shows a weak cash position, we can adjust prices, sales levels, credit terms, costs, overheads, or timing. The spreadsheet gives us the power to test different choices before making them in real life.Spreadsheet building principlesA good forecast is easier to use when it is built properly from the start. The episode highlights several practical principles.Save the file immediatelyOpen a blank workbook, give it a clear file name, and save it straight away. Then keep saving as you work. Losing a spreadsheet after building formulas and assumptions is frustrating and avoidable.Separate inputs from formulasInputs are the raw numbers and assumptions. Formulas are the instructions we give the spreadsheet. Outputs are the results we want to review.For example, cash from deliveries may depend on the number of deliveries, the price per delivery, and when customers pay. Those assumptions may change, so we should build them as separate Lego bricks rather than typing final totals directly into the summary.Make the spreadsheet user-friendlyThe forecast should be easy to read. Use clear headings, sensible font sizes, helpful worksheet names, and a layout that makes sense. We do not need unnecessary decimals or clutter if they make the spreadsheet harder to use.Use copy and paste carefullySpreadsheets are powerful because repeated calculations can be copied across months. If the calculation structure is the same each month, copy and paste can save time and reduce repeated typing.However, formulas should still be checked. A copied mistake can spread quickly across a forecast.The business Lego bricks approachThe business Lego bricks approach means breaking the forecast into smaller building blocks. Instead of typing one final number for sales, costs, or wages, we build the calculation from its parts.For the I Hate Numbers Food Palace example, the key Lego bricks include:number of food deliveries;price per delivery;when customer money is received;food purchase costs;supplier payment timing;staff hours;rate of pay;overheads and other cash commitments.This structure makes the forecast more flexible. If the result does not look right, we can change the assumptions and see the forecast update.Building the cash flow summaryThe summary sheet should show the main cash story. In the episode, the summary includes money in, money out, cash contribution, other overheads, and the bank balance at the end of each month.Cash contribution is the money left after direct cash costs linked to sales. In the food example, that means cash from deliveries less food purchase costs and wages linked to preparing and delivering the food.After that, we deduct wider cash commitments such as rent, rates, salaries, marketing, accounting fees, loan repayments, tax, and other overheads.This helps us see whether the business is building cash, using cash, or heading towards a shortfall.Using formulas to reduce mistakesThe real strength of a spreadsheet is that it can calculate for us. Instead of doing calculations elsewhere and typing in the answer, we should let the spreadsheet perform the calculation.For example, total sales can be calculated by multiplying the number of deliveries by the price per delivery. Wage costs can be calculated by multiplying hours worked by the rate of pay. Food costs can be calculated as a percentage of sales.This approach reduces manual work and makes the model easier to update. If one assumption changes, the spreadsheet can update the result automatically.Using the forecast to make better decisionsThe cash flow forecast is not just a document. It is a decision-making tool. Once the spreadsheet is built, we can use it to test different scenarios and see what happens to cash.If the forecast shows a cash shortage, we can review pricing, payment terms, sales activity, costs, overheads, staffing, stock levels, or borrowing needs. If the forecast shows cash building up, we can plan investment, debt repayment, tax reserves, or growth.For more practical cash control steps, listen to Six steps to managing your cashflow.Practical spreadsheet tips for cash flow forecastingDecide what you want the forecast to show before building it.Save the file immediately and keep saving as you work.Use separate worksheets for summary and detailed calculations.Separate assumptions, formulas, and outputs.Use formulas instead of typing calculated answers manually.Use copy and paste where the calculation structure is repeated.Keep the spreadsheet readable and user-friendly.Build the forecast around money in, money out, and closing bank balance.Use the forecast to test and reshape business decisions.Related episodesMaking your cashflow forecastSix steps to managing your cashflowUnderstanding Your Financial Statements: Cash Flow, Profit and Balance SheetKey takeawayBuilding your cash flow with a spreadsheet helps us turn plans, assumptions, and activity into a clear cash story. It shows what money may come in, what money may go out, and what could be left in the bank.The aim is not to build a complicated spreadsheet for the sake of it. The aim is to build a practical tool that helps us make better decisions, spot problems earlier, and reshape the business story before cash...