Operating Cash Flow Calculator

• Version
• 5'136 Downloads
• 240 KB File Size
• January 3, 2022 Updated
• 0 Number of comments
• ★★★★★

  ★★★★★
  Rating

As a business owner, you need to understand every aspect of your business’s operation, and the effect its activities have on the health of the business.

Tools that provide such information are invaluable, and there are many out there.

Traditional financial accounting reports provide some of the information a business owner needs. Balance sheets give levels of assets and liabilities, accounts receivable, accounts payable, owner’s investment (or capital) in the business, financing activities, and much more. Accounting income statements give information on gross income (or revenue), depreciation expense, net income, income tax, general expenditures, and overall profitability. And the Reconciliation of Net Worth pulls information from each to report the Owner’s Equity position.

However, as informative as they are, financial statements provide only some of the information necessary for the successful operation of a business. Something more is needed.

Importance of Operating Cash Flow Calculator

Only business cash flow statements succinctly detail from where a business’s cash is derived and where the business’s cash is used.

Our Operating Cash Flow Calculator is a critical tool that will help you track, gauge the effectiveness of, and understand the impact of your business’s core activities.

We stress “core” here because nearly all legitimate business activities use or generate cash. But only a subset of those activities relates to the core operation of the business.

Bill payments, funds available for capital expenditures, tax payments, and reduction in loans and or accounts payable also come from cash. These other activities may be important to the business’s success, but may not be involved in the core operation that drives the business.

This template reflects the activities of only those processes necessary to drive the business’s core operation (hence the description “cash flow from operation”). Other tools are available that include these ‘non-core’ activities. Cash flow calculators focus on core activities because, if they consume more cash than they provide, the business is very likely to be in real jeopardy.

Operating Cash Flow Calculator Models

The template provides two models, the Yearly and Monthly, and is built in Excel, so you probably already have everything you need to run it.

Yearly Model

The Yearly model looks like this:

Note that this form is blank, and highly customizable. Rows and columns can be easily edited with your company’s name, specific categories you might want to include, or color schemes to give it the look you want.

For instance, you can – and should – insert your own company’s name in the “Company Name” cell. Just go to the cell, delete the contents and type your own name in. More customizations are available via the Toolbar at the top of the screen.

You can see below where we’ve constructed examples using a fictional company name with fictional operating results.

Also, the form references 2019 and 2020, but it can be used for any two consecutive years. You could use the first column to reflect the previous year’s activities and the second column to project this year’s.

It is not, however, required that you fill in more than one year. The form is equally useful for a single year. Conversely, another column for a third year could be added.

To do this, right-click on the column D. Resize it to match the width of column C by right-clicking on the column header. Choose “Column Width” from the drop-down menu next to the 2020 column and type in “16” as your selected width. If you’ve done it correctly, it should look like this:

Then, highlight and copy C6:C11 to Column D.

Your last step is to change the date in Column D to “2021”. Simply highlight the “1901” cell and change it to 2021. Just like that, you’ve added another column. You can replicate this procedure to generate as many additional columns as you want. Note that there is more than one way to insert a new column, but doing it as we have shown will replicate our formulas and formatting.

Here’s the completed task.

It’s your form; configure it however you prefer.

This model is set to print in “Portrait” mode, so it will easily fit on a single sheet of paper and is easily read on your computer screen. Of course, any Excel customization option is available, should you choose to personalize the form to your liking.

For instance you can resize the page, change the fonts, or add images of your company, or change the back-ground color. It’s up to you – just follow this link for more information on customizing Excel templates.

This form does one thing. It provides you with a report that tracks the cash your core business activities generate and how that cash is used during the period in question.

For this template, all entries need to be in the positive (or”+”) form. The model automatically adjusts entries in the Cash Out categories as minuses (or “-“). If you make entries in the minus (“-”) format, the calculations will be incorrect.

After the cells are filled in, the model will automatically calculate the net result of your activities as entered in the Net Cash Flow from Operating Activities row.

Yearly Model Example

Here’s an example of the Yearly form that’s been filled in.

For the purposes of this example we’ve named the company “Best Operating Company, Inc.” And, we’re assuming that the company’s operating year is a full calendar year beginning on January 1st and ending on December 31st.

Also, note that this business operates using U.S. Dollars. But the model works equally well with other currencies. If you’d like to change that, simply go to the toolbar and click the drop-down arrow next to the $ sign. Then you will be able to select from a range of currencies. The model will convert to that currency and assume that selection for all calculations.

The model tells us that our business netted $140,000 from our core business activities in 2019. Also, our salesmen have told us that they expect sales to increase by 25% for the upcoming year. Savvy business owners that we are, we know better than to rely exclusively on their optimism. However, we’ve plugged their expectations in to see what the result would be.

The above model shows those expectations accordingly under the 2020 column.

Note that the model refers to Net Sales Revenue. This means that these numbers are after accounting for customer returns and/or bad debt.

Similarly, Inventory Purchases will be net of credit for inventory returns.

This example shows that our business generated $140,000 in free cash flow from operations during 2019. And we can also see we’ve increased the 2020 numbers by the amount our sales staff has suggested.

The $140,000 generated in 2019 can be used to increase working capital, fund equipment purchases, reduce or pay down debt or accounts payable, pay dividends to shareholders, or any other non-operating use that you may elect. These other items do affect overall cash flow but are not included in the model because the template addresses the question of how much cash your core business activities generate. Other models that incorporate these items are available but may not provide as clear a picture of the value of your core operation as this template does.

And, of course, being able to project expected revenue in future years will allow us to prepare in important ways. If we know that we’ve recently added a big contract, and perhaps our sales staff’s estimate of a 25% increase is reasonable, then we might need to think about expanding our operations by investing in new equipment to prepare for the increased sales. Conversely, if we know we’re losing contracts or that the economy is weakening, we can adjust our numbers, see where we hope to come out, and plan accordingly.

There are numerous articles available to help guide you to manage your cash flow. Two links that seem valuable to us are Ten Tips for Better Managing Cash Flow from Quickbooks, and Twelve Easy Ways to Successfully Manage Your Cash Flow from American Express.

Monthly Model

Now, let’s move on to the Monthly Cash Flow Calculation in Tab 2. Below is a blank copy to familiarize you with the template.

This model provides the same information as the Yearly template but breaks it into monthly results. The model can be used in different ways.

First, it can be used to track monthly cash flow generation/use over the course of a year. As the monthly cells are populated with data, they are summed in the TOTAL column. This way, you can see the results on the business's cash flow each month, and cumulatively as your year progresses.

Second, it can be used to project your expected monthly cash results for the ensuing year.

Note that this form is built in the “Landscape” mode, meaning that its information runs left-to-right rather than up-and-down as we had in the Yearly model. This format facilitates viewing an entire year’s worth of information at once without having to refer to multiple sheets, or cutting-and-pasting printed sheets together in order to view an entire year. This example looks a bit small in the article but is easy to read on your computer screen or when printed.

Let’s look at an example of each of the uses detailed above.

Monthly Cash Flow Tracking Example

In this example, we’ll look at an entire year’s worth of data. It will compare to the 2019 numbers in the Yearly example above.

Again, remember that this model is set to print/display in the Landscape mode (from left-to-right). The model will be easily read when viewed on your computer screen or printed.

In our example, we made some important assumptions. These include:

• Sales (and therefore revenue) activity is somewhat seasonal, peaking during June, July, and August. Revenue ratchets up before that period and down afterward.
• Other Operating Revenue (such as recovered shipping fees) follows the same pattern as sales/revenue.
• Expenses related to sales/revenue volume (Inventory purchases, Office Expenses, Travel, Advertising, and Other) fluctuate seasonally also.
• Wages, Rent, Interest, and Maintenance are expenses assumed to be spread evenly throughout the year.
• Taxes are paid one time during the year.

In our example, we can see that the net cash flow of our business fluctuates during the year. All months produce positive cash flow except for March, April, May, and June when we are building our inventory. This tells us that we will need either to have sufficient beginning cash in the bank to cover these temporary losses, that we may need to increase borrowing during these months, or that owners may need to make temporary cash injections to cover the shortfalls.

However, by July our net operating cash flow returns to the positive, and we make strong progress in the last four months of the year.

Projected Monthly Cash Flow Example

Now let’s assume that it’s November 2019, and that we are are considering what 2020 may look like.

As mentioned above, the sales team has assured us that sales/revenue will improve by at least 25% in 2020 (you may be dubious of this optimistic number, but let’s not get lost in the example!). Accordingly, the 2020 model has been adjusted for this increase. All revenue and expense numbers follow those incurred in 2019, except that they are 25% larger.

Here is what the 2020 Projected Operating Cash Flow Calculation would look like with these assumptions.

Remember that this will be easier to read when printed out or read on your computer screen.

Our example shows that we expect our Net Cash Flow from Operating Activities in 2020 to be $175,000. That is terrific!

Importance of Examples

These examples demonstrate how important it is for you, the business owner, to be aware of your cash position, and to gauge how effective your core operating practices are. It is not unusual for businesses to suffer cash shortfalls during certain times of the year. The owner/manager must remain aware of this possibility, anticipate them, and make plans to adjust for them.

Typically prepared financial statements (i.e., balance sheet, income statement, and reconciliation of net worth) do not provide the owner with this kind of critical information.

For this reason, we recommend that you use Operating Cash Flow templates to monitor both on-going and anticipated future business activities. Knowing when cash will be needed and where it might come from is just as important as deciding when and where to spend it.

In the example above, you might use the cash flow projections to leverage a more accommodative payment schedule for your payables. Or, you might decide to cut discretionary expenses in months where a cash shortfall might be indicated. Of course, only you can know the best uses for your business’s cash flow – but in order to make the best decisions, you have to have the best information, and our Operating Cash Flow Calculator will help give you that.

Summary

Our Operating Cash Flow Calculator template will provide valuable information when managing your business. We wish you the best of luck with your company and hope that you will rely upon us to provide you with other useful tools as well.