How you Build your cash flow with a spreadsheet

I'm 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 week’s episode of I Hate Numbers I'm going to look at

• your initial steps, the first thing you should do
• I'm then going to be looking at the principles or spreadsheet building
• general considerations
• the business Lego bricks approach

Cash is the commodity that keeps your business going, your business lines burning.  Spreadsheets are not the preserve of the accountants, the finance people or at least it shouldn't be.

The principles in this podcast will apply whether you use Microsoft Excel, Google Sheets, or any other package.

Using spreadsheets to tell your business and cash story

Spreadsheets are a powerful tool in your business toolbox. They take the heavy lifting out of number crunching and are a practical help in the world of finance and numbers. Above all we need to think about the look and feel of our spreadsheet. You don't need to be an IT or math’s genius to build a good workable spreadsheet, but take care with its look, design, and use.

My I Hate Numbers Food Palace business will be used to show you how you build your cash flow forecast with a spreadsheet.  The business prepares and delivers delicious food to corporate clients. And your business may not be a food business, but that's okay. Because the knowledge and approach is very transferable.  Check out my video to see a visual demonstration.

Think about what you want your spreadsheet to show you.

Above all I want to see the financial outcome of my planned activity for the next 12 months? What does that mean in terms of

• Cash contribution
• The cash left over after paying for your overheads and cash commitments
• How much money I left in your bank at the end of each month?
• Will you be clenching your buttocks or relaxing as the cash depletes or piles up

Putting information into your spreadsheet

When you build your cash forecast do not edit as you go along.  Do not stop yourself and say 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.

Your cash flow spreadsheet reflects your wish list, aspirations, and how you see your business unfolding.  It's the story of what you're trying to do with your company.  You have the option and power to change it.

Your numbers are telling a story about what you want for yourself and for your company - don't let them tell a different one !

Listen to find out more.

Conclusion

Above all, how you build your cash flow forecast with a spreadsheet is good to know.  It keeps you closer to your business numbers, and a key business tool, the spreadsheet!  This week’s podcast tells you this, plus calculations, tips, and advice.

Listen to find out more.

My mission is to inform, inspire and educate you to get closer to your numbers. You can make more profits, save tax and time, improve your well-being and your money mindset.

Help me to help you and others by subscribing and sharing this episode in your network.  .  Listen now and subscribe to I Hate Numbers, so I can send it straight to your inbox every week with all the latest updates from I Hate Numbers podcast! are

If you found this podcast useful then share this episode on social, leave a review on Apple podcast, connect with me on Instagram, You Tube, Twitter, LinkedIn and Facebook,

Links

https://podcasts.apple.com/podcast/proactiveresolutionss-podcast/id1500471288

https://open.spotify.com/show/5lKjqgbYaxnIAoTeK0zins

https://www.stitcher.com/podcast/proactiveresolutionss-podcast

https://tunein.com/podcasts/Business–Economics-Podcasts/I-Hate-Numbers-p1298505/

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.