A Simple Tool to Calculate and Track Cash Flow

Last week, in the posting It’s Cash That Counts, I wrote about the difference between net profit in a Profit and Loss Statement and the reality of cash in the bank. Business owners get into trouble when they really on Net Profit to understand where their cash is. This month, I would like to present you with a simple tool to help you calculate future cash flows as well as to track current cash flows.

To better follow this posting, I have created an Excel spreadsheet, which can be seen in Google Docs by clicking here. (You may want to open the spreadsheet in a separate window or tab. Not only will the spreadsheet help you with this explanation, it can also be extended to a full year to use in your business.

On the left hand side of the spreadsheet, you will see a summary of the January Profit and Loss Statement, indicating a profit before taxes of $750. The business owner may look at this and scratch his head, asking, “My bank account is overdrawn, what gives?” The reality is that due to the timing of cash flows, he will only have incoming cash of $3,000, based on the percentage of cash sales along with collections of his accounts that pay in Net 30, 60, 90 and more.

When the business owner calculates cash flow out, first for Cost of Sales, it turns out to be $6,250 because not only does he pay some of his accounts in cash, but he also has payables of Net, 30, 60, 90 and more. In addition, he has his regular expenses, or overhead, that cost him $3,950 a month.

Therefore, the business owner’s total cash outflow is calculated by adding together Cost of Sales Cash Out and Total Expenses, which equals $10,200. Since his Total Cash in equals only $3,000, the owner has a Net Cash Flow of -$7,200. As you look across the months sales gradually increases to the point where cash flow is no longer negative. However, if the business owner does not have either an equity investment or a loan to cover the shortfall, he may be out of business in 3 months.

Below, you will find detailed instruction on how to gather information to calculate your cash flow. Using this same tool, you can also forecast your cash flow into the future. Forecasting is a useful exercise that will help you understand in advance when you will need the working capital to stay afloat.

Calculate Cash Inflow

The first task is to calculate your actual cash inflow. Follow these steps.

Cash Inflow for the Month
o Using the Income and Expense statement for the current month, and any other available information (invoices or contracts) to calculate cash inflow for the current month
o Using the Income and Expense statement for the previous month, and any other available information to calculate Net 30 cash inflow for the previous month
o Using the Income and Expense statement for the month before last, and any other available information to calculate Net 60 cash inflow for the previous month
o Using the Income and Expense statement for months prior to month before last, and any other available information to calculate Net 90+ cash inflow. All income that is more than Net 60 is usually added together into Net 90.
o Total Cash Inflow = Cash + Net 30 + Net 60 + Net 90

Cash Outflow for Cost of Goods Sold in the Month
o Using the Income and Expense statement for the current month, and any other available information (invoices or contracts) to calculate cash outflow for the current month, calculate Cost of Goods Cash outflow for the month.
o Using the Income and Expense statement for the previous month, and any other available information to calculate Net 30 cash outflow for the previous month.
o Using the Income and Expense statement for the month before last, and any other available information to calculate Net 60 cash outflow for the previous month.
o Using the Income and Expense statement for months prior to month before last, and any other available information to calculate Net 90+ cash outflow. All income that is more than Net 60 is usually added together into Net 90+.
o Cash Outflow for Cost of Goods Sold the month =
Cash + Net 30 + Net 60 + Net 90+

Monthly Expenses
Use the total of monthly expenses from the Income and Expense Statement

Total Cash Outflow:
To calculate the total cash outflow for the month, add cash outflow for cost of goods to monthly expenses.
Total Cash Outflow = Cost of Goods Cash Outflow + Expenses Outflow

Calculate Net Cash Flow:
Subtract cash outflow for the month from cash inflow for the month to calculate Net Cash Flow.
Net Cash Flow = Cash Inflow – Cash Outflow (could be negative number)

• Add the beginning cash balance to the monthly net cash flow to determine current cash.
Current Cash balance = Beginning Cash Balance + Net Cashflow (could be negative number)

3 thoughts on “A Simple Tool to Calculate and Track Cash Flow

  1. Chad Triebwasser

    Great Post!! I would have to agree looking an Income Statement with comparison to a bank statement one would scratch their head. I remember when I had started my first business, I thought the same. The information you have provided would be useful in educating small business owners and start-ups the value of having the correct amount of working capital and how to calculated it before speaking to investors.

  2. […] A Simple Tool to Calculate and Track Cash Flow (via The COO's Bulldog) Last week, in the posting It’s Cash That Counts, I wrote about the difference between net profit in a Profit and Loss Statement and the reality of cash in the bank. Business owners get into trouble when they really on Net Profit to understand where their cash is. This month, I would like to present you with a simple tool to help you calculate future cash flows as well as to track current cash flows. To better follow this posting, I have created a … Read More […]

  3. […] because the business grows more quickly than their cash flow allows (see Its Cash That Counts and A Simple Tool to Calculate and Track Cash Flow). Adequate cash flow is vital to the success of any business, and it is possible to analyze your […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s