Using excel spreadsheets to project cash flow seems antiquated. But it works and here's how.
“Cash is king!” It’s a mantra shouted by many business owners—and understood by few.
Most small business owners don’t forecast their cash flows, and they end up overextending their credit lines.
In some cases, they know cash is king, but they don’t really know how much cash to have on-hand. They just know when they don’t have it, they have to scramble around for alternative ways to pay.
How much cash flow should you have? Does anyone seriously follow the rules when it comes to cash? Let’s answer these questions and more by taking an in-depth look at cash flow.
Related Article: Cash Flow Management: What Do Credit Cards Have to Do With It?
Timing Is Everything
Regulating cash flow isn’t just about quantity. It’s about having enough cash at the right times. Having enough cash in Week Four doesn’t help when you don’t have enough cash to cover payroll in Week Three.
One easy way to forecast cash flow is to set up an Excel spreadsheet with one column for each week in your fiscal month. Add the inflows and outflows you expect each week.
Excel screenshots by Jacqueline Lee (Fair Use)
In this example, this business owner makes a thin profit of $625 for the month but has negative cash flow during Week Three. Instead of failing to make payroll or incurring late fees on bills, the business owner can contact Pinardi’s Paddles and offer a 2 percent discount for paying an invoice early.
Some Tips for Your Spreadsheet:
- Use formulas wherever possible. Use formulas to add inflows, add outflows, and determine cash on hand [(Last week’s balance + Inflows) – Outflows = Cash on hand]. Also, instead of typing your cash on hand into the “Last week’s balance” totals for the following week, just type a formula (e.g., type “=B21” in cell D4). Then, color-code the cells that contain formulas. By coloring the cell, you make it less likely that you’ll change the entry and alter your formula.
- Update frequently. Daily or weekly, update any information that differs from your forecast. For example, if Hailey’s Comics makes a larger-than-expected order in Week Two, change their total as soon as you find out. Because you’ve used formulas, your spreadsheet will adjust automatically as soon as you type in the changes, giving you an up-to-date snapshot of your cash flow.
- Forecast cash flow quarterly or bi-annually. You’ll never be caught off guard again by cash shortfalls, and you’ll see how decisions made in January can affect cash flow in March.
Related Article: Top Tips to Ensure Clients Pay on Time and Increase Your Cash Flow
How Much Cash Do You Really Need?
Business owners often answer this question with rules of thumb: “I keep six months worth of operating expenses on-hand.” In the real world, the answer isn’t the same for every type of business. Find smart ways to increase inflows—punctual invoicing—and ways to decrease outflows by cutting expenses:
- Take advantage of payables discounts by paying bills early. Pay your suppliers early to take advantage of their discounts.
- Slash credit card processing fees by finding a low-cost payment processor. Stop paying nearly three percent per transaction by switching to a low-cost provider once you determine what kind of merchant account you need.
- Lower variable costs. You can’t lower your rent, but you can manage variable costs like payroll, utilities expenses, supplies ordering, and inventory ordering.
- Managing inventory ordering closely. Stop re-ordering items that aren’t selling. Focus on ordering items that have high turnover rates.
Finding the Amount
Put your cash into two separate accounts: an operating account to cover you during months in which you earn the lowest revenue, and a contingency account to cover major hiccups in business as well as times in which you’d have to fill a large order before getting paid by a major client.
- Operating account. Focus on costs, including both fixed and variable expenses, that you’d have to cover if no revenue came in.
- Contingency account. Add up your total assets, and then subtract your total liabilities. Divide that number by 365 to get your daily operating capital. Then, decide how long you could survive if your business faced a major financial crisis. Keep three to six months of daily operating capital on-hand if you have a history and a solid credit option. If you’re a startup, aim for closer to a year’s worth of daily operating capital.
If you’re a business with a lot of tangible assets, like real estate, oil wells, or receivables, it’s easier to get credit in a time of need. When your assets are intangible, such as unlaunched products, intellectual property, or R&D, it’s tougher to value them for credit purposes. Make sure you keep enough cash to fund your intangible assets in addition to your contingency cushion.
Those Are the Rules. Does Anyone Follow Them?
The more you neglect managing your cash, the more likely your business is to fail. Twenty-nine percent of startups, according to research from CB Insights, fail because of insufficient cash flow. Secure credit before you find yourself in a crisis, and be smart about keeping cash reserves. It could mean the difference between weathering a crisis and going belly-up.