How to Do a Cash Flow Analysis

A cash flow analysis is done by mapping out incoming money versus outgoing expenses on a calendar, then identifying where there are gaps so a business owner or manager is not caught off guard with a $0 bank balance.  

This gives the decision-makers in a business the confidence they can handle upcoming problems including equipment breaking or needing to replace inventory and allows them to plan for short-term funding options to cover any shortfalls so they don’t miss payroll, default on a debt payment, or short a supplier. It can sometimes also be considered a short-term liquidity analysis. 

Here’s what you need to do a cash flow analysis followed by the process for how to do it and an example with a spreadsheet so you can follow along while building your own. 

What You Need to Do Cash Flow Analysis  

To do a cash flow analysis you will need: 

• Business bank account balances  

• Issued checks that have not cleared or pending drafts on the bank accounts  

• Expected Inflows including invoices, projected daily sales, estimated future sales 

• Fixed expenses like rent, insurance, and loan payments 

• Variable expenses like travel, subscriptions, and snacks for the office 

 When you have all the information, the next step is to lay it out on a spreadsheet with the columns set up as days, weeks, or months like a calendar. 

Doing the Cash Flow Analysis 

The first thing to do for your cash flow analysis is to determine your true starting balance by subtracting un-cleared checks and pending charges.  

Next, set up your spreadsheet where you put all of your cash inflows and outflows in separate rows and make each column a day, a week, or a month depending on your business. Retailers will focus on daily cash flows while business-to-business services operate mostly on monthly flows. 

Now that your spreadsheet is set up and you have your true starting balance, follow this five-step process to do your cash flow analysis: 

1. Map when money is coming in. 
2. Map when money is going out. 
3. Add up the columns to see weekly flows. 
4. Calculate the running balance. 
5. Identify risks and what you can cut. 

Map When Money is Coming In 

To map where money is coming in, match your invoice or sales amounts to the spreadsheet based on when the money will actually be in your bank account, not when the sale occurs. The example spreadsheet below for a retailer shows the first 5 weeks of sales for cash and credit cards and you’ll see how it gets filled in for the rest of the steps:  

Make sure to adjust for the time difference from when you receive a check versus when it clears, or when you make credit card sales versus when the funds settle and hit your account. If your credit card processor settles funds in 2 days, then count Wednesday’s sales as Friday’s cash flow. Also make sure to account for any reserves your payment processor withholds in case of chargebacks.  

Formatting options like different cell colors, bold fonts, or border outlines in the spreadsheet helps to signal things like different customers that have a history of not paying on time or estimated seasonal sales that vary greatly from year to year so you can easily identify them in the later steps of the analysis.  

Map When Money is Going Out 

Map the dates on which cash will leave your bank account for fixed payments and any variable expenses, each on their own row. Enter these amounts as negative values on the spreadsheet so that the math is easy when you do the final calculation for each column. 

Sum up the columns to see daily/weekly/monthly flows 

Use your spreadsheet sum function to add the values in each column to get your cash flow for that day/week/month and determine whether it is adding to your bank balance or taking away from it.   

Calculate the running balance 

Your running balance is what you have in the bank each week after adding / subtracting the flows from the previous step and is the key number that tells you when you have a cash flow gap to plan for.  

For the first day/week/month, calculate your running balance by adding the flow from the previous step to your true starting bank balance. Then add each day/week/month’s flows to this number to see how your running balance changes over time.  

If you see a negative number in your running balance it means you are running into a cash crunch. By knowing about it in advance, you can cut some expenses, delay some purchases, or use a short-term business loan to cover the gap.  

Identify Risks and What You Can Cut 

The final step is to identify where you have risks and what you can do about it to protect yourself. Start by looking for weeks where your running balance drops below the level you’re comfortable with and look for risks around that time like customers you worry might pay late.  

Watch how your running balance changes as you move payments from these customers later on your timeline. If you’re a retailer, adjust your sales numbers for seasonal or economic risks like lowering sales numbers around the winter months in case of major snow storms or depressed holiday shopping. 

If your running balance drops too low or goes below zero, start identifying expenses you can cut. If you can’t cut enough expenses to get your running balance up to the level you want it, then you can start planning now for funding options to cover the cash flow gap. 

Now that you know the steps to do a cash flow analysis, here is an example for a retail store doing a cash flow analysis over the next 12 weeks. 

Retail Cash Flow Analysis Example 

This example retail cash flow starts with its true bank balance at the top of week one and then follows the cash flow analysis process steps: 

• Inflows from cash and credit card sales are mapped to each week. 

• Fixed and variable expenses are mapped to their respective weeks. 

• The weekly cash flow line sums up each week’s sales and subtracts the expenses.  

• The running balance row adds the weekly cash flow to the starting balance. This then becomes the beginning balance for the following week. 

• The retailer would then identify where they had big risks, for this example it’s chargebacks. 

Notice that the retail store is living on a tight margin of only $10 free cash flow at the start. 2 weeks of high chargebacks in weeks 4 and 5 will drop their running balance to a dangerous level where one more high charge back week could put their account near $0. 

With this analysis in hand, the retailer can get plans in place right now just in case this happens like setting up a line of credit they can draw on when needed, being ready to cancel technology or other subscriptions and general fees in SG&A expenses (salaries, marketing, office expenses, etc…), or investing in advertising to increase their top line.  

Doing a cash flow analysis helps you see where you either currently have cash flow gaps or have a chance of running into them if things change and let you plan ahead, so you don’t have a funding emergency at the last minute. Gather the information and follow the process listed above, then use your spreadsheet to stress test where you think there are risks of late payments. 

National Funding does not provide tax, legal or accounting advice. This material has been prepared for informational purposes only. You should consult your own tax, legal and accounting advisors.