Advertisement

Cash Forecast Excel Tool: In Tough Times, Cash is King

By John Carvalho
Published: April 9, 2020 | Last updated: April 9, 2020
Key Takeaways

We’re witnessing a global economic shut down that has never been experienced in human history. Business owners will unfortunately suffer but past recessions can provide guidance on how to survive.

istock-913589156

Needless to say, these are challenging times for everyone across the globe. This is an unprecedented health crisis, so the safety and health of our readers are at the forefront of our thoughts.

Advertisement

From an economic perspective, it is not over-exaggerating to say that we are also witnessing a global economic shutdown on a scale that's never been experienced in human history.

Download Free Excel Tool: Weekly Cash Flow Forecast

Advertisement

Business owners will unfortunately suffer but past recessions can provide guidance on how to survive.

In 2008, while attending a "state of the economy" conference, I heard sage advice from a seasoned and successful entrepreneur that has always resonated with me: “I never regretted cutting too deep in times like these”.

I took that to mean that adopting an overly conservative approach of preserving cash using severe and swift measures will see a business through times of uncertainty.

Advertisement

I also added my own context to his message in that once the danger has passed, there is an opportunity to rebuild.

Recession Survival Checklist

Sequoia Capital, a preeminent venture capital firm, recently shared a similar message to its portfolio companies on March 5, 2020:

Advertisement

“Having weathered every business downturn for nearly fifty years, we’ve learned an important lesson — nobody ever regrets making fast and decisive adjustments to changing circumstances. In downturns, revenue and cash levels always fall faster than expenses. In some ways, business mirrors biology. As Darwin surmised, those who survive 'are not the strongest or the most intelligent, but the most adaptable to change.'”

Also back in 2008, Sequoia Capital shared with its portfolio companies some tips for surviving the economic downturn that I think are still relevant:

  • Perform situational analysis.

  • Adapt quickly.

  • Use a zero-based budgeting approach.

  • Make cuts.

  • Review salaries.

  • Employ a heavily commissioned sales structure.

  • Bolster balance sheet.

  • Become cash flow positive as soon as possible.

  • Spend every dollar as if it was your last.

The overall common thread in Sequoia’s advice is preservation of cash and an important tool in achieving this is the ability to forecast cash.

In more conventional and certain times, preparing a monthly or quarterly forecast might suffice. In these times of uncertainty, a much shorter forecast period (i.e. weekly) should be employed.

Cash Is King (A Tool to Forecast Cash)

In the hopes of helping business owners through these tough circumstances, we have built a weekly cash flow tool in excel that can be downloaded here.

Let’s walk through the steps and thought process needed when using this tool:

Step 1

Enter in your company’s balance sheet as at a current date in column D on the “Balance Sheet” worksheet. Also enter the start date for the forecast in cell D2. It is important to have your exact amount of cash on hand as at the start date.

Step 2

Forecast weekly revenues and expenses on the “Income Statement” worksheet. The financial model allows you to enter in estimates of annual revenue, direct costs as a percentage of revenue, and annual G&A expenses in column C.

The financial model will then automatically calculate weekly revenue and expenses. The other option would be to enter in revenues, direct expenses and G&A expenses manually on a weekly basis. As a note of caution, be conservative when forecasting revenue.

The purpose of this exercise is to estimate a realistic cash position or potential shortfalls in this time of significant economic retraction.

Step 3

On the “Assets” worksheet, estimate the timing of cash receipts from customers. This should be a manual exercise and based on review of your aged accounts receivable listing.

Remember that it’s likely your customers will delay payment in these times of uncertainty. Also on this worksheet, enter in the timing of capital asset additions and changes to other asset accounts, if any.

Step 4

On the “Liabilities” worksheet, estimate the cash out flows paid for payroll and to supplier on rows 7 and 8. Similar to cash receipts, this should be a manual exercise.

The timing and amounts of payments to payroll is relatively easy to calculate based on the number of employees. Payment to supplier can be based on review of essential supplier and aged accounts payable listing.

Step 5

Also on the “Liabilities” worksheet, enter in the total principal repayment on long term debts (if any) on row 22. Consider if there is an opportunity to contact your financial institutions for loan payment deferral or access to additional financing.

Review all other liability accounts and input cash inflows or outflows that are applicable.

Overall Analysis of Cash

Now that you have entered the financial information, take a step back and analysis the forecast results.

Ask yourself the following questions:

  • Where is the forecasted cash balance on the “Balance Sheet” worksheet in four weeks, eight weeks and 12 weeks?

  • Do you run out of cash and enter a short-term borrowing position? If so, where can you find cash? Can you obtain additional financing from your financial institution? Can you inject cash as a shareholder loan? Can you ask your bank for debt payment deferrals? Are there government subsidies that can be accessed?

  • What are your biggest weekly expenses on the “Income Statement” worksheet? Can you reduce expenses by deferring nonessential or discretionary items?

  • In most mid sized businesses, payroll represents the largest company expense. As difficult as it may be, can your staff compliment (and payroll expense) be reduced to reflect reduced activity levels?

  • What happens if revenue and cash collection get significantly worse? What are plans B, C, D, E and F to find cash or reduce expenses?

Final Thoughts

As an optimist, I am confident we will get through these times of difficulty but how we come out the other end will depend on the work we put in now.

As the proverb says: “May you live in interesting times”.

Let’s stay safe, healthy and determined to grow through this adversity.

Share This Article

  • Facebook
  • LinkedIn
  • Twitter
Advertisement

Written by John Carvalho | President, Divestopedia Inc.

John Carvalho

John is president and founder of Stone Oak Capital Inc., an M&A advisory firm, as well as a co-founder of Divestopedia. For more than 20 years, John has served his clients on numerous valuation, acquisition and divestiture assignments in a wide variety of industries. John holds the Corporate Finance designation, is a Chartered Business Valuator and a Chartered Accountant. He has made it his life's mission to help entrepreneurs build valuable businesses and Divestopedia serves as an avenue for this cause.

Related Articles

Go back to top