Weekly Cash-Flow Model: When to Use One, and How?
by Prateek Gupta, on May 31, 2018
Who needs a weekly cash-flow model?
You own a fast-scaling company, and you’re growing your customer base steadily, but you’re still nervous. You almost didn’t make payroll last week. You had to call your landlord and ask to pay rent a few days late. Your projections say you’re going to make a small profit this year, but your margins are razor-thin, and you don’t know how much cash is flowing in or out any given month, let alone any given week. It’s keeping you up at night.
While you may have been able to skate by without an accurate cash-flow analysis, there comes a point where your lack of insight becomes a problem. It is even more problematic if your sales are flat, or you are confronted with a large unexpected expense like an equipment repair, or you are overburdened with debt that causes stress each month when the payment is due.
If any of the above scenarios sound like your business, you need to create a weekly cash-flow model.
How to model your weekly inflows and outflows
Tracking cash flow is different from tracking profit & loss, and requires a model that can predict with reasonable accuracy how much cash you will have on hand at the end of each week, enabling you to adjust your business decisions accordingly.
Companies tracking weekly cash flow typically use a 13-week (quarterly) model.
Set up an Excel spreadsheet with one column for each week, leaving space at the left.
You’ll need a line at the upper left for “Beginning Cash Balance,” and the last line on the bottom left will be “Ending Cash Balance.” In between, you should list “Cash Inflows” with a line for each category (cash sales, accounts receivable receipts, loan or line-of-credit proceeds, etc.), and a line for the total.
After that, you have “Cash Outflows” (rent, utilities, payroll, health insurance, loan payments, inventory purchases, deliveries, office expenses, owner draws, etc.).
Good cash-flow analysis can help you understand and improve many aspects of your business, and manage it proactively rather than reactively.
Note that both loan proceeds and monthly principal repayments, which do not hit your profit & loss statements, need to be recorded in the cash-flow model, as they represent real cash flowing in and out of your accounts. Only the interest expenses hit your profit & loss statements. On top of that, the accounting gets trickier when you pay expenses on a credit card, since that payment is not a cash disbursement. (However, when you pay OFF the credit card, that would be a cash disbursement)
Say you pay a supplier with a credit card on the first of the month. That expense hits the P&L but not the cash-flow model (no cash has left the house); conversely, when you make a payment on the credit card weeks later, that goes into the cash-flow model but not the P&L.
If you are not used to working with numbers and spreadsheets, setting up the model and determining the values to enter can seem daunting at first. It’s OK if your initial assumptions are off; you can correct them as you go forward once you gain a better understanding of the numbers coming in and out. If this seems confusing, that’s because it is. Alternatively, you can contract with a Financial Planning & Analysis expert to set up and implement the model, and train you to use it going forward.
Each week, the model will add the beginning balance and cash inflows, subtracting cash outflows to arrive at an ending cash balance. The model will also show you beginning and ending quarterly balances for a longer view, and as each new iteration of the model more closely matches the outputs of your accounting platform, you can easily perform more variance analyses. Once you have a handle on the weekly cash-flow, you can start to think about a monthly cash-flow model.
Where do the numbers come from?
Of course, the model’s accuracy in predicting ending balances depends on the quality of data that was inputted. Some of these are straightforward: you know how much your rent and loan payments will be, and when they are due. The same goes for the weekly or biweekly payroll—as long as salaries and/or hours worked are consistent from week to week. However, it gets more complicated if salespeople receive commissions. If you can predict sales, you can write a formula to arrive at commissions.
Not that predicting sales is simple. It helps if you have customers on contract; they have already committed to what they will spend, and you can plug those numbers in with confidence. The less you rely on contracts, the more you have to rely on your own seasonal sales history, your recent sales trends, and what your salespeople are telling you. This means that you needed to have kept accurate records of historical data.
The other wrinkle with sales is that they don’t get plugged into the cash-flow model anyway—collections do. And here’s where you need to get a handle on your company’s accounts receivable turnover, which will allow you to predict how much AR you are likely to collect in any given week.
Once you nail down weekly cash-flow, you can start building a monthly model
Weekly cash-flow models are used as a hail Mary—it’s a quick bandage for those that don’t have the insight for a monthly cash-flow model yet. However, once you’ve gotten your feet beneath you, consider the benefits of implementing a monthly model for proactive business management.
Whether you are working directly from the cash-flow model or using its data to create graphic dashboards that highlight key performance indicators, good cash-flow analysis can help you understand and improve many aspects of your business, and manage it proactively rather than reactively.
For example, if your cash flow is tight but your model predicts improvements several weeks or months ahead, you can adjust the timing of a new hire or piece of equipment accordingly. You can also use seasonal ups and downs to your advantage if your cash forecasts are good; for example, plan to spend that extra cash from the busy season on marketing that will help improve sales in the coming slower months. On the other hand, if your dashboard shows many months that are cash negative, then you may be bleeding cash, and this should prompt a conversation with a CFO on what you can do to turn it around.
A good cash-flow model can help you make adjustments during a difficult period. For example, if you are digging out from a period of losses, or sales are flat, and you foresee problems ahead when several payments come do the same week, you can plan ahead to build up cash for that week by drawing on a credit line, seeking an investor contribution, or finding accounts payable that can be delayed. Knowing what to expect from cash flow can help you manage a short-term challenge while you work on your strategies to build reserves.
How to utilize your weekly cash-flow model?
When you have a good working cash-flow model, you can also use it to analyze strategies to make better decisions going forward on such things as:
- When would be an ideal time to hire a part-time employee?
- Am I getting more for my money from salaried employees with benefits, or freelance contractors?
- Am I spending more than I need to on, say, accounting, and what would be the impact of shifting a portion of that spend into marketing?
- As owner, when would be the best time to take a draw?
Cash flow is the lifeblood of any business. And it is not something that is out of your control. But before you can turn it into an asset and adjust it to your advantage, you have to know what’s coming in, what’s going out, when, and why. A good cash-flow model will give you that.
Prateek Gupta is a financial analyst that has build hundreds of models that have been used for pricing strategy, cash-flow analyses, and other financial analyses. Prateek has owned his own startup and has several years of consulting experiences helping small to medium business owners achieve a better understanding of their financials. Prateek has a a PhD in engineering from the University of Illinois and an MBA from the University of Chicago. During his time off, Prateek will usually be doing a Sudoku while trying different places to find the ultimate deep dish pizza.