About this episode

Cash 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 episode

• Why 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 matters

A 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 show

Before 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 early

When 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 principles

A good forecast is easier to use when it is built properly from the start. The episode highlights several practical principles.

Save the file immediately

Open 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 formulas

Inputs 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-friendly

The 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 carefully

Spreadsheets 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 approach

The 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 summary

The 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 mistakes

The 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 decisions

The 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 forecasting

• Decide 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 episodes

• Making your cashflow forecast
• Six steps to managing your cashflow
• Understanding Your Financial Statements: Cash Flow, Profit and Balance Sheet

Key takeaway

Building 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 pressure becomes real.

If cash flow forecasting, spreadsheets, or business planning feel unclear, visit ihatenumbers.co.uk or listen to the related episodes above to build more confidence with your numbers.

Plan it, Do it, Profit.

“Let your spreadsheet tell the cash story first. Then use that story to make better decisions.”

Share this episode: Listen on Apple Podcasts

🎧 Enjoyed this episode? Subscribe and leave a review on Apple Podcasts — it helps more business owners understand cash flow, finance, and their numbers.

Episode Timecodes

• 00:00 – Building a cash flow forecast with a spreadsheet
• 00:58 – Why cash flow matters for every business
• 01:36 – Using Excel, Google Sheets, or another spreadsheet tool
• 02:13 – Deciding what the spreadsheet should show
• 02:56 – Why we should not edit the cash story too early
• 03:16 – Saving the workbook and setting up the file
• 03:38 – Inputs, formulas, data and text explained
• 05:17 – Worksheets, summary sheets and layout
• 07:02 – The business Lego bricks approach
• 08:10 – Building the summary page
• 10:30 – Cash received, deliveries, price and timing
• 12:32 – Food purchases, supplier payments and wages
• 15:22 – Linking calculations to the summary sheet
• 16:28 – Cash contribution, overheads and monthly cash flow
• 18:32 – Using the forecast to reshape your story

About the Podcast

The I Hate Numbers podcast helps business owners understand accounting, tax, finance, profit, cash flow, and business planning in a practical way. We simplify financial topics so you can make better decisions and feel more confident with your numbers.

You can also watch more practical finance and tax support on the I Hate Numbers YouTube channel, or listen and follow on Apple Podcasts.

Further Support

📘 Book

https://www.ihatenumbers.co.uk/i-hate-numbers-book/

🎧 Podcast

https://www.ihatenumbers.co.uk/i-hate-numbers-podcast/

🌐 Website

https://www.ihatenumbers.co.uk

I am going to show you how you build your own cash flow forecast by tapping into the most powerful resource in your financial toolbox, the spreadsheet. In this episode, I'm going to look at your initial steps. What's the first thing you should do? I'm then going to be looking at the principles of spreadsheet building, how you should approach building your cashflow forecast, and how you should build pretty much any spreadsheet, the general considerations and the business Lego bricks approach that we will be adopting in

building our cash flow forecast.

You are listening to the I Hate Numbers Podcast with Mahmood Reza. The I Hate Numbers podcast mission is to help your business survive and thrive by you better understanding and connecting with your numbers. Number love and care is what it's about. Tune in every week. Now, here's your host, Mahmood Reza.

Hi folks. Welcome to another weekly episode of I Hate Numbers, the show, the podcast that's there with a mission to get you closer to your numbers so you can make more money, save tax and time, improve your money mindset and well-being, and have the business you aspire to have. What's not to love? Let's crack on with a podcast.

Now, a cash flow forecast is a must. Whether you are a startup business, midterm, whether you are pivoting or growing, whatever you do, cash is the commodity that will keep your business going and those business lights burning. As far as spreadsheets are concerned, it's not the preserve of the accountants, the finance people, or at least it shouldn't be.

The principles I'm outlining in this podcast will apply whether you are an Excel user, whether you are a Google Sheets user, or you have another package that you tend to use. There's very much a similarity these days, and a synergy between all spreadsheet applications, even though Microsoft XO is the leader.

Lots of spreadsheet applications, emulate them. In this podcast, I'm going to be referring to the I Hate Numbers Food Palace business, my business that prepares and delivers delicious food to corporate clients. Now, your business may not be a food business, but that's okay because the knowledge and approach is very transferable.

Now, step one is think about what you want your spreadsheet to show you. And fundamentally, in my, I Hate Numbers Food Palace business, I want to see two things. Firstly, I'd like to see the results of my planned activity. What does that mean in terms of for the next 12 months, the contribution that I will be?

From delivering and getting paid for my food, what will be the cash left over after I pay off all my costs, my overheads, all my commitments, and how much money am I left with in my bank balance at the end of each month? Am I going to be clenching my buttocks or am I going to be enjoying as the cash builds up or the cash depletes?

Now, in principle terms, when you build up your cash flow model, do not edit as you go along. Do not stop yourself and inhibit yourself in saying, I can't afford to do that. The key thing is, is to get the story, get the numbers in the spreadsheet, see what the spreadsheet is telling us. Then we can go back, revisit, and change an order.

The thing is, let your spreadsheet reflect your wishlist, your aspirations, how you see your business unfolding. If it doesn't give you the results you want financially, then you have the option and the power to change that story. The first thing we should do, open up a blank workbook, give it a file name, save it immediately, and make sure you save as you go along.

Nothing more frustrating than doing all that hard work. And then suddenly you don't save your spreadsheet, something goes wrong and you've got to start all over again. So that's the first thing we should do in terms of the mechanics of the spreadsheet. In general terms, when you build a spreadsheet, you need to differentiate between instructions that you're going to give your spreadsheet, what you might call a formula, where you are asking your spreadsheet to perform certain calculations.

So for example, the cash that I received from my customers will be based on a calculation, what I'm going to call my business Lego bricks. That cash from sales will be made up of how many food deliveries I make, how much I charge my customers, and when that money comes into my bank account. Now, those variables may change when I see the results of my forecast,

it may be I'm not receiving enough cash in, so I might want to play around with the pricing, the credit terms I give to my customer. I might be more ambitious with the level of sales that I'm likely to make, so I want the ability to change those variables. So try and minimise where you put in the results of calculations

just as data. Don't do your calculations on a back of a cigarette packet and then enter the numbers in. You want the spreadsheet to perform those computations. The second principle I want is to make sure that my spreadsheet is user-friendly. So when I first open up my workbook, I can create individual sheets, which are effectively like math class where you've got to show your workings to the teacher for the math calculations you performed.

My worksheets are where I'm going to separate out and record all my different building blocks. In Excel, you have the ability to go to the bottom of your screen, use your mouse, the ribbon bar, and insert another sheet. My first output, by the way, will be the summary, and I want it to be user-friendly. So think about, do you need to use decimals in the results of your calculation?

I would suggest not. Make the figures and the font appear the size that you want it to, because ultimately the number crunching is one side, but you need to make sure the document is user-friendly. So think about formatting how you want to present the information. Do you have a preference for a font type, a size of font?

Make it legible. Make it readable. Really important. Now, the information going into your spreadsheet will be either data and text. Text will be describing the headings that you have. Data will be the raw ingredients, excuse the pun. And then you're going to have the formula - the instructions where you tell the spreadsheet what you want to do.

Copy and paste, by the way, will be your best friend when you build a spreadsheet. A spreadsheet's fundamentally designed on the premise that when you perform a calculation, if the calculation is the same in structure, then you can copy and paste. So when we talked about money from customers, the principle is very similar for each month.

The number of deliveries multiplied by the price per delivery equals total sales made. That will be the same for September, October, November. So I can copy and paste that formula across the other months. It saves me typing it individually and your spreadsheet by the way, very, very powerfully will make the adjustments accordingly.

Now, my key Lego bricks, by the way, in this example, will be calculations to work out when the money comes in from the deliveries that I've made from my customers when I've got to pay for the food supplies. So every time I'm going to be selling food, I've got to be buying food as well, and there'll be a relationship

between those two numbers. My third Lego brick will be the wages. The wages I pay to my staff for the preparation for the driving, for the deliveries. And those will be my three key building blocks. If you want a visual representation, folks, check out the show notes. And I've got a link to a video that I've recorded that actually demonstrates it. It’s just that sometimes a face is not good for radio, demonstrating details or spreadsheets and audio may not be the best fit.

So visual representation is there for you. Now let me continue with my spreadsheet building. So I've saved it. I've opened up the blank workbook. I've created two areas, two worksheets here. I've also renamed them, and I've called one Summary and I've called the other one, Money in - Money out. Now let's go to the summary page.

And from the top of the screen, type in the month you want or forecast to begin. In this example, I'm going to choose September 21. So type in September 21, get your mouse, copy that across, and it will adjust an update for each month accordingly in Excel, and it'll go to October onwards. Underneath, you then got a visual layout as though it was a piece of paper.

I've got the money in and I'm going to put cash from deliveries. There may be other sources of cash you might have for your business, like money from other revenue sources. You then got the money going out and I want to have something called a cash contribution. Now, cash contribution will be money from deliveries, money from what you sell less all the costs associated with that.

And in my example, it'll be the wage bill of the food preparers, the people who deliver the food, and also it'll be the cost of buying in the food. That will give me a contribution. Every time I sell a product, every time I make a delivery, I get money coming in that contributes the remaining of my operating costs and other cash commitments and for my business, for my decision making,

I'd like to separate out those two. Now let's go back to the Lego brick. So I've got each month of the year and the beautiful thing about spreadsheets are, is that if you've got one area where you've typed in the date, you don't need to retype them. So in your second worksheet, go to the top. Because you've already set the dates up in your summary page, go to the area at the top. Now in Excel,

every time you want to give it an instruction, every time you want it to perform a task, you select the symbol equals, and that tells Excel that you want to perform a particular operation, a particular set of tasks. So press equal, take your mouse, go to your first worksheet where the date is put and hit return, select that hit return and that will copy the date.

So every time you update the date in the summary, it will automatically update the date on your second worksheet. Remember, what we're trying to do is to minimise the amount of repetition, minimise the level of mistakes, so therefore, where we can make bits of the spreadsheet talk to each other is brilliant.

Now go to your second worksheet tab. Use your friend, copy and paste and copy that date across. Now let's build up the Lego bricks. So as far as the cash coming in from customers, we are going to put some text, we are going to put number of deliveries underneath. We're going to put the price. We're then going to put a description called total, and then underneath we are going to type in when the money is received.

That's the text description. Now, let's actually put the numbers in. Now in the month of September, let's anticipate that we are going to make onedelivery. We're going to put in 750, and then the total, how much we make will be the products of how many deliveries multiplied by the price. Now the instruction is to go to your spreadsheet and say whatever figure is in the box where the number of deliveries are made, I want you to take that number and multiply it by the price to give me the total.

Now in Excel terminology, when we want to give it instruction, we select equals we take our mouse, we use our directional keys, go up to where the information for numberis, press the asterisk key, which is the multiplication symbol for Excel, move it to where the number is for the price, and then hit return. And that will be a calculation.

Now the beauty is that calculation will be the same for every single month, so what you need to do is when you've populated the numbers, go to where your instruction is, your formula. Take your mouse right-click, copy and paste across, and it'll copy that instruction. Now underneath, when it's money received, let's assume we've given our customers a 30-day credit term.

So underneath anything I sell in September, I'm not going to get paid until October. So move across to the October month. Press equals because it's an instruction, and move it to where the sale has been made in September and hit return. Do the same for the month of November. Equals. Move to where the October sales are.

Hit return, and then copy that instruction across. Now underneath, head it up, call it Food Purchases, and this will be, again, a number of ways you can do that. Let's assume that every a hundred pounds worth of food sales, half that cost is taken up with the cost of the ingredients and preparation. So underneath, I'm going to write food costs.

And I'm going to put in 50%. That's my relationship I've assumed to begin with. 50% of sales are made up of food costs. That percentage I can copy across every month will be 50% and underneath. Now I can write down the word cost. What I'm going to do in my first, what's called a sale under the month of September, I'm going to select an instruction.

So I select equals, and I'm going to move my directional keys, my mouse, to where the food cell value is, select the asterisk key and then move it to where the 50% figure is that you've typed in. Hit return, and that tells me now every single time the level of sales might change, it updates the food cost.

Underneath we are thinking about what credit terms do we get. So in a similar way, we are going to have the tax title payment made. Anything I buy in September, my food supplier has given me 30 days credit. So I'm going to pay that in the month of October. Now we've got this calculation of the cost of food.

Again, your friend copy and paste that up until effectively the next October 22, go to where your food is being paid out for. And there you are going to type in equals in the month of November, the October purchases, and so it continues and copy that across. Now my last building block is wages. Now wages is made up fundamentally of number of hours, rate of pay, and then the instruction is the total.

So type that text in. You've got hours, is the description, the heading underneath, write rate underneath, write total and underneath, write down payment made. Now it's very similar pattern here. So in the first box, you'll type in the number of hours, that's a number of data input. Underneath, you'll write down the rate of pay, you'll put in whatever that rate is, and then underneath is the instruction. You are saying to the spreadsheet whatever it is in the box, the sale, where the number of hours are, go up there, multiply it by the figure for rate of pay, and multiply the two numbers together.

So the instruction that you've given it equals move your cell cursor up to where the figure is for number of hours. Press select asterisk, which is the multiplication key, go to where the rate of pay is, and then hit return, and your friend comes in again, copy and paste. Copy it all across. Remember, you should be saving as you go along or auto save it is something you may want to select.

Now go to your summary sheet and here's where now the linking, the power comes in. The ball-breaking task is setting it up. But once you've done it, you'll see the power copy and paste. You'll see some commonality here, so you will save time as you get more familiar. Now in your spreadsheet summary, go to where you got cash received.

And you are going to say, okay, in the month of September, I want it to pick up whatever the figure is I've calculated as cash received in the month of September. So select equals, move your mouse. Go into the worksheet tab, where your Lego bricks are, move it up to where the cash receipt for September is and then hit return, copy and paste across.

Now the power here is you are linking now your calculations, your business Lego bricks to where you want the result to appear. What that means is when you've looked to the result of your cash flow forecast, if you don't like what it's saying, if you want to improve something, go back and revisit. Then you can change the underlying building blocks and the spreadsheet will be updated.

Many people that I see just put numbers into the spreadsheet. Therefore, they made a rod for their own back. Now, continue that with the cost of food buying and the wages. Now, in my summary, I wanted a cash contribution, and cash contribution will be made up of the money that I receive in September, minus the cost of buying in the food that I've paid for in that month

and the wages that I've paid for in that month as well. So in my instructions to my spreadsheet, I'm going to say select equals. So that tells me I want to give it an instruction, move the mouse up to where the cash received is. And then minus the figure that's the food purchases, the figure that's wages, and then press equals, and that's my cash contribution. Copy across. Underneath, I've got all my other cost overheads, my cash overheads and commitments,

everything from rents, rates, salaries of front-of-house staff, marketing costs, accounting fees, let's not forget those, loan repayments, sales taxes, and the like. Type that data in, get to the bottom. The total cost commitments, the total overheads, support costs will be the sum of all those items. Now, in Excel and other applications, there are shorthands.

So when you see that weird Greek symbol, what you can do, you can go to where your month of September is, select the add up lots of numbers. So on the formula bar, it's the Greek symbol sigma. Select that one, and you can automatically select the range of information from the start to the end. Press enter and the total of that section appears there.

Copy and paste across. Now, last couple of things, folks. The money that I have in and out will be the difference between the cash contribution, less the cash costs. That's how much the cash flow is in that particular month. Copy and paste across. The money that I have in my bank account will be made up of how much I started with, how much I finished with.

So again, another instruction, another formula that I have to type. Now it sounds daunting. It sounds arduous. Look at the visual representation. You'll find it's less so. Now, the power behind the spreadsheet is once you've created the spreadsheet, got your numbers in there, save as you go along. If you don't like the result, you can go back and reshape your story.

Now folks, I hope you've got some value out of this podcast. So remember, the key thing is think about your outcome. Think about what goes in there. Separate your Lego bricks from the overall result. Save as you go along. See the power of the spreadsheet and use it and adapt it as your accountability, buddy. Until next week, folks, have a good week.

We hope you enjoyed this episode and appreciate you taking the time to listen to the show. We hope you got some value. If you did, then we'd love it if you shared the episode. We look forward to you joining us next week for another I Hate Numbers episode.