How to avoid making a mess of your cash flow forecasts in excel

How to avoid making a mess of your cash flow forecasts in Excel

Spreadsheets are a great tool for simple cash flow forecasts

Excel, in general, is a really great tool.

It’s an open sandbox where you can build whatever you want. 

This freeform nature makes them great for quickly testing out simple ideas in isolation. 

Because of this, spreadsheets are great for simple cash flow forecasting – cash in, cash out, total. Simple.

In fact, we have an Excel or Google Sheets cash flow template for you to use here.

cash flow forecast template projection operating activities

Related: A beginners guide to forecasting business cash flow for startups

This is Excel at it’s best.  A short term forecast can be made without lots of formulas or linked sheets. It’s really valuable for any business looking to make better decisions today by planning ahead

It’s also why many people use Excel for their own personal finances. The calculations they are doing are simple. They don’t use linked sheets or forecast in the long term.

But you have to avoid it turning into a monster

Drawing from personal experience, you can start a spreadsheet with the intention of doing something really simple. 

Once you’ve achieved your goal, it can get very tempting to continue adding to the spreadsheet – with the idea of adding more value. 

In a forecasting world, this might be exploring different scenarios. Your simple vision of the future immediately makes you start asking more questions. You might start adding more detail to your spreadsheet. More formulas, more charts, different versions. 

This starts out well but, in reality, you’re making the sheet more complex and increasing your chances of making an error.

It’s really easy to continue adding to a spreadsheet. However once it becomes too complex, problems start occurring. 

These problems may be difficult to solve due to Excel’s open-ended nature. 

If you have a break between building your spreadsheet and then coming back to it later, it can be really hard to see what you made initially. You may even end up just scrapping it and starting from scratch.  

I am sure many people reading this will have had this experience!

To avoid this happening, you need to understand the limitations of Excel.

Understanding the limitations of Excel & spreadsheets

Trying to do more complex forecasting can be…complicated.

As Excel is a tool designed for many different purposes (not just forecasting) it will let you do anything. It’s this lack of specific guidance that will cause you trouble eventually.

Let’s take a look at the issues.

Excel can be difficult to use

Let’s say you’re looking at going to an exhibition to promote your product. There are a number of relevant Exhibitions you could go to throughout the year. They have different costs, different timings and could have different impacts on your sales and awareness. 

You can look at all the components for each exhibition in isolation but it would be really useful to roll all of these elements into your cash flow forecast to see the effect on your entire business. 

With this one scenario, you’ve got numerous moving parts and potential factors. You might attempt to map out the costs and exhibition sales in different sheets and then link them back into your main cash flow sheet. 

Related: New to event marketing? Here’s a 5 step guide for a successful exhibit

It is when building something more intricate like this, that the problems that Excel has begin to rear their ugly heads. Understanding the complex formulas and the ways in which Excel operates is something that takes quite a lot of skill and understanding of the program.

You might not even know how to go about setting up this example in Excel. Or you might simplify the scenario so much that it’s not realistic enough for making a decision around.

Either way, it’s not ideal. 

Part of the problem when setting up complex situations like this is that formulas are not easily readable.

When looking at the formula for a cell, it is not clear that =B2-B3 is =sales-costs, you have to look at the target cells in order to establish this. What if you got these cells around the wrong way? =costs-sales is much easier to spot than B3-B2.

This is a simple example which would be clear to see in our simple cash flow forecast. However, the more formulas you add, the easier it is to make a simple mistake like this. 

Adding complexity can cause errors

Because Excel can be difficult to use, it is easy to make errors. Understanding the root of problems in Excel requires a deep understanding of its formulas and the way it works.

One of the most common reasons why errors occur in your sheets is formulas are too loosely connected to their inputs. 

For example, when entering a simple formula like profit=sales-costs what you get is =B2-B3. If you move either of the sales or costs figures from B2 or B3 the formula won’t automatically track these values to their new locations.

Similarly, if you move the calculation to a different cell this can also lead to the wrong input cells being accessed.

Spotting errors is also harder the less you know about the expected results. If a range of cells all should calculate somewhere between 0 and 1, then 42 cropping up in one of them is a good indication of an error somewhere. 

The more complex the calculations become, and the more diverse the range of expected results can be, the harder it is to realise if there is an error in the calculations.

To make things harder, ranges of cells can’t be linked to the same formula. Sure you can copy the same formula to a range of cells, but if you change the formula in one of those cells it doesn’t automatically update the rest. 

So you have to meticulously recopy the formula to all affected cells. Miss just one and it doesn’t stick out like a sore thumb. You’d have to click on each cell in the range, in turn, to check they are all using the same formula.

It is really easy to make a mistake in Excel and the chances of making one increase the more complex your forecasting. 

There are ways to avoid creating problems. You just need to understand what you are trying to create and not push Excel beyond its limitations.

Collaboration issues

If you are using the standard office version of Excel, then sheets cannot be worked on collaboratively. The workaround that many organisations use is to have the spreadsheet saved in a shared folder, accessible by those who need it.

This is an acceptable solution to what might seem like a minor issue. Only one person can access the spreadsheet at a time. 

Excel also doesn’t track changes unless you set it up to. This means anyone with access could make a mistake in your sheet. You most likely won’t be able to tell who made said mistake either or restore it to a previous version. Unless you’re happy saving multiple back-up versions.

If you aren’t careful, it’s very easy to generate lots of different Excel documents. It turns into a jumble of files, edited at different times, by different people, for different purposes.

Tips to make your forecasting in Excel stress-free

Be disciplined and don’t let it go beyond what it’s capable of (or you’ll waste more time trying to fix problems rather than benefiting from the original exercise you started out with)

If you add complexity follow these guidelines:

Keep calculations on separate sheets to the outputs

Doing this means your formulas and their links are in the same place. This means finding errors is easier. But really, just try to avoid creating lots of cells that reference each other.

Use the comment system on Excel

Do this to remind yourself or other users of the sheet what certain formulas do, or how to use them or not break them.

Collaborate better with Office 365 or Google Sheets

If collaboration is what you’re after, try out Google Sheets or use Excel Online (Office 365). Users can be online all at the same time and the sheet is stored in the cloud meaning it can be accessed from one place –  no more sending spreadsheets via email. Changes are also tracked by user and you can restore to previous versions. It is understandable how corporations are concerned with security when it comes to storing files in the cloud though, where both these options store their files.

Overall, be diligent and careful with additions and changes you make. If you want to move beyond a simple cash flow forecast, ensure that what you create is well documented and clear.

This is both for other team members who might want to use it and your future self when you return back to it after a break (trust me, you’ll thank yourself later for that formula explanation you wrote at the time)! 

Don’t let Excel hold you back from exploring the future of your business

Financial forecasting is at its most useful when you can explore the future effects of today’s decisions. To help you get a feel for their ramifications on your bottom line. 

Simulating hypothetical ‘what if’ scenarios can bring incredible clarity to key business decisions you need to make right now. 

Small businesses often don’t do it because it can be time-consuming and frustrating, despite the fact that some extra time taken now can save you money in the future by helping to avoid a bad choice. 

Some more advanced roles of financial forecasting:

  1. Creating the base, best and worst-case scenarios
  2. Exploring the effect of new business projects (like going to an Exhibition, launching a new product, moving to new premises etc.)
  3. Stress testing your business. For example, how does my cash flow cope with an increase in my supplier costs?

What we’re getting into is the realms of financial ‘modelling’. Which sounds scary but is simple in principle. 

If you’d like to learn more about this, we’ve written a beginner’s guide to financial modelling. This article goes through:

  • What financial modelling is all about
  • How it’s used by professional financial analysts
  • How you can use this professional technique to great effect in your small business
  • An alternative tool for financial forecasting and modelling to Excel

Remember, if you want a starting point for your simple cash flow forecast, check out our free cash flow template here.

Thanks for reading and good luck keeping your spreadsheets under control. Remember, don’t let them turn into a monster! 

 

Tim Room 3rd October 2019 By
 

Take the tour of Brixx