Cash Flow Dashboard – How Many Months Before You Run Out of Cash?

Have you ever wondered “Can we cover payroll this month” or “What if that big deal doesn’t come in? Will we be ok to make payroll?” How many months cash do you have in time of crisis or seasonally slow sales times? To put together a Cash Flow Runway is an easy way to predict these answers. We look at this weekly, it is a regular KPI in our weekly meetings and everyone on the Executive Team knows the answer to all the above questions. Here I will show you how to put together a Cash Flow Runway so that you will know the answers as well for all your planning needs.

1 – Determine the best time period to look at your cash. Is that monthly, weekly, bi-weekly, etc? We use the 15th and 31st because we have a lot of payments at the beginning of the month and also again at the end of the month. This helps separate each item quickly and keeps the payments to different time periods of the month.

2 – In Excel create a Summary tab, a Receivables tab, and a Payables tab. This step is helpful if you have a lot of customers and/or payables. Create the time periods you have determined will work best for your company across the top of the sheet. In addition, add a column for Paid or Collected on both the Receivables and Payables sheets.

3 – Next, determine your Receivables or Cash Ins for the company for each time period. In other words, what customer payments do you expect to receive in each time period? On the Receivables tab in Excel, you can be very detailed and list every customer out or use a summary of current receivables. If using a summary of receivables simply determine the percentage of receivables that will come in each time period. For example, 50% of AR may come in by 30days, 25% in 40days, etc. Plot this info accordingly on your sheet. Whenever you receive a new customer or contract for service, you can simply add the new info into the appropriate time period when you believe you will receive their payment. If you have recurring revenue this can be a copied into each period in which you receive the recurring deposit. Here is an example of what the Receivables Tab would look like using our time periods.

Receivables or Cash Ins Tab Dashboard

4 – The next step is to list out all your expenses into the appropriate time periods? Using the Payables tab, enter your Rent, Salaries, Utilities, Credit card payments, loan payments, etc. Enter all of your expenses that will come out of your bank account. Then repeat any recurring payments into all the future periods. We like to look at about a year of activity but you may want to start with 6 months. Therefore, plot 6 months of rent payments into each appropriate period.

Payables Tab Dashboard

5– Once all items have been entered total each column and return to the Summary tab. Here you will begin with a Cash Balance from your last Bank Reconciliation. Then link the summary tab to the Receivables and Payables tab to get an ending balance by period. Carry the ending balance back up to the beginning balance of the next period like in the below example.

Summary Tab Dashboard

6– Each week or when payments come in or go out, update the appropriate sheets by moving the amounts into the PAID column and tie it out to the current bank account. This Cash Flow Runway should always tie out to the GL bank account for the current week so that you are forecasting the future with an accurate starting point. Once you have all your items plotted you can project cash out as far as you can predict.   On the summary sheet, when you see the Ending Balance shows RED (or negative depending on formatting) that time period is when your cash will run out. Count the number of months away from today. We are Green if = 6 months and Red if = 3 months. However, you can determine what your Red, Yellow, Green will be.

7– After you get this all setup and begin using it regularly, you can then add your Sales Plan numbers to project what will happen when Q4 sales come in, for example.   For decision making you can project what will happen if you need to move and Rent increases or how much will the new hire impact cash. Simply load in the new info and see what happens on the Summary sheet. This is a great planning tool for growth or if crisis hits and the questions begin arising, you will be ready for everyone’s WHAT IF... scenarios!