Cash Flow is a practical way of showing whether you have enough money coming in to cover the money going out. It helps us to gauge how much extra cash we might need over time, and whether we are at risk of running out of funds. In theory it’s easy, but when we actually do it, maybe not so much.

How Do You Calculate Cash Flow in Excel?

1. Write the cash inflows in columns on the left and the cash outflows on the right. You might want to split the balance sheet into two parts: one for sale of your own products, and another for money coming from customers.

2. Add up all the totals, so that you have an overall total of cash flow. Some companies might analyse their cash flow as a percentage of revenue. Others will use a different measure, like sales growth or EBITDA (earnings before interest, tax, depreciation and amortisation).

3. Divide your total annual cash outflow by 12 to get your average monthly figure (for example $12,000 divided by 12 gives $600). This will then be your annual average.

4. Now divide the monthly average by 30 to get a monthly minimum cash outflow ($600 divided by 30 gives $20). This is the minimum level you need to be able to pay every month. Obviously if you are in business for only one month rather than 12 months, then this figure would be one-twelfth of the 12-month minimum (or $6).

5. Divide your total annual cash inflow by 12 and multiply it by your monthly minimum (total annual inflow times monthly minimum gives you the total amount of money you need to cover this each month).

6. What is left over after subtracting the monthly minimum from the total cash inflows is your extra cash. This is very useful when it comes to forecasting and planning for the future, as well as monitoring and reviewing your cash flow.

Here are some additional tips for analysing your cash flow.

7. The more sales you have (or the more your products cost to make), the more cash you will be receiving and therefore the less cash you will need also. So consider these figures as a guide only, not a rule of thumb.

8. As you grow your business, your cash flows will increase too, because you will be selling more and earning more money in the process. Have a look at your cash flow in several periods of the business, like when you are first starting and then compare it to further down the line.

9. If you are planning for a period of time where you might not have enough money coming in, it’s worth building that into your cash flow plan by including a buffer fund. This will help you to overspend but hopefully only just enough.

10. If a large payment is due to come in at some point, try to support this by thinking about the cash flow earlier on so that the balance won’t be too low during this time period (and therefore unable to meet all of your other bills).

11. Jot down any large, unexpected payments that you need to make, especially if these are related to your business, like paying for new equipment or covering a VAT bill.

The annual cash flow calculation is not difficult. All you need to do is add up all your sales figures, subtract any payments or expenses, and then divide your cash income by 12 to get your average monthly amount.Each of the cells in this example is covered in more detail below. We have added some extra information on the right-hand side too – for example, we have added year, month, date and time.

This template is designed to be a very simple cash flow analysis for a one-person company with annual turnover of $2,000 and monthly sales of $5,000. You should complete the cash flow analysis for similar sized businesses if you want to use it as a starting point. We obviously make no representations or promises that this will be suitable for your business; it’s just our experience that you need to start somewhere.

To survive, you are going to have to make some tough decisions about how much you can spend and how much you need to put aside for the future.

We’ve tried to make this template as simple as possible. Most of it is completed for you already. The only columns that are left blank are the ones relating to your sales totals, so that you can fill in your own figures and totals in these cells as required (for example, if your sales completely vary from month to month).

Conclusion

This should give you a good basis for calculating your cash flow for your business. In the next chapter we will look at how to calculate profit and loss, and how to prepare your monthly cash flow forecast.