Earlier we were discussing risks of cashflow. The argument is that they are mostly symptomatic of other problems, and the real risk arises from not modeling and managing cash behaviour. This leads of course, to – how exactly do we do this?
When we first started our operation , there weren’t that many transactions, and an envelope pretty much covered our needs. Now, we might have 100 customers, 10 employees, and 35 suppliers. This is a bit tougher. Guess what – you need a tool. That tool is the dreaded spreadsheet – spawn of the demon, imp of the underworld of illusionists and contortionists, and other bizarre creatures.
It takes about ten minutes to learn the basics of spread-sheeting – because you already do similar things on the envelope. If you have an aversion reaction to it, find some fourteen year old, and have them type the keys and so on. Nor does it need to cost you money – there are open source spreadsheet programs available such as Open Office’s Calc.
Lets do a three month model. Open a spreadsheet. You will want at least two tabs (sheets). The first is for assumptions, the second for the running calculations. In the assumptions page, start putting in your information for standard recurring inflows and outflows
Cash Outflows
Net payroll, every two weeks, Friday a.m. - $ 2,000
Payroll gov’t remittance 15th mo follow - $ 750
Premises lease payment, 1st month - $ 1,000
Accounts Payable, trade, 23rd mo follow - $ 3,000
Sales tax remittance, 25th mo follow - $ 1,800
Utilities, A/P non-trade, 7th mo follow - $ 500
Now go to the second tab, and label the following columns : Date, Description, Inflow, Outflow, Balance. Enter seven dates in the date column , one for each of the Friday net payrolls over the three month period. In the outflow column for each date, enter the cell on the assumption page with the amount of the net payroll, and make it an absolute reference (usually by making a cell, say B24 read $B$24…).Put in a description that references net payroll. Next add the three government remittances over the period, date the 15th of each month. Enter the outflow amount similarly with your absolute reference link to the assumptions sheet. Continue this process for items like premises lease. The accounts payable can either be treated as if they were one bill (meaning on line on your sheet, or you can break out certain bills that might need special handling – your choice. You should end up with about 22 dated entries – seven net payrolls, and three for each of the others. Done? Good – move on.
Cash Inflows
Do you have accounts receivable, or just cash equivalent sales, or both. If accounts receivable, if there are specific large accounts that are worth modeling, list them, separately in your assumptions page. Take the rest of the receivables, and put the total in your assumptions page. Below that enter a row for that balance, converted to a weekly equivalent. Do this by dividing the receivables balance by the number of weeks on average, it takes you to collect from your customers. This will give you an estimated amount of cash collected per week. Watch out for seasonal fluctuations in sales or collections.
Enter this on your calculation sheet by creating a series of Fridays over the three month period. Why Friday – because it is more conservative to show cash inflows at the end of the week.
(Hint: enter the first Friday in the period, move to the cell below, and add the above cell plus 7. Copy this down to fill 13 cells. Then convert the formula to a value with copy, paste, special, values . . .)
In the inflow column, make an absolute reference link to the cell on the assumption page where you have the estimated weekly inflow.
Next, on the assumptions page, enter on a row the amount (if any) of the tax included cash sales that you experience each week. Be a little conservative here; consider using only 90% of your expectation. Move to your calculation sheet, add a block of thirteen dates starting with the first Monday in the period. Under inflow make an absolute reference link to the appropriate cell on the assumption page, for each of your thirteen rows.
Let’s assume that these are your only sources of cash flow.
The Assumptions Page :
Calculating the Model
On the calculation page, at the top of your block find the column called balance. Move the text ‘balance’ up one cell, and right on the title row, in line with the words Date, Description, Inflow, Outflow, put the starting balance of your cash position.
Now, in the first detail row under the titles, enter the following formula in the balance column, where the words in curly brackets are the locations of the cells you need to enter;
‘=+{balance just above} + {the inflow column cell in the row} – {the outflow column cell in the row }
and press enter to finish. Now copy that formula cell all the way down and past the list of details that you have already entered.
The last step is to hi-light select all of the detail rows that you have entered, then do a data, sort, sort on {whatever column you have the date in}, and press enter. The spread sheet will then provide you with a running balance of what you expect your cash balance to be for the net three months, based on the assumptions on the assumptions page.
The Calculation Sheet
Reading the Tea Leaves of the Cash Model
Look over your running cash balance. Note at what points you start running into negative positions, and don’t be too surprised to discover that cash goes negative just after major remittances such as trade suppliers, or government sales and payroll taxes.
Look for areas which offer you a bit of flexibility – certainly any amount you entered for your own cash draws, possibly a bit of maneuvering room on trade payables. Do not mess around with dates and amounts for government remittances, and premises leases is not a great item for manipulation. However, do look closely at your assumptions for cash inflow from customer accounts. That is one area that needs constant attention.
Massaging
Often this is not the final step. There may be other significant items that you want to add, from time to time. Some dates may need to be adjusted, to keep your bank balance above water. Be careful not to move too far off your governing assumptions – this is often a sign that you need to take stronger management steps to deal with an underlying problem. Playing with the cash model does not actually fix problems – it simply helps make you aware that the problem is there, and possibly buy you some time and space to deal with it.
Maintenance
We have built a model that covers three months. Two points.
The first is that I typically set up the model to cover a full year, since it reduces my time to generate updates. The validity does not extend over 12 months, but it means that I do not have to continually be adding items – I just have to adjust the assumption variables.
The second is that the cash flow needs to be reviewed every week, and updated for cash balance, bills deferred and not paid. Adjusted amount for payroll and remittances that differ from the assumptions, and slightly changed dates.
Sunday is a good day for updating your cash model. Every Monday morning, it is best to start your week knowing what the cash position is likely to be, so that when the question comes up, you can give an informed answer to a supplier or a customer request for credit. If you have someone in charge of a food budget, that person is likely to need answers, too, and lord help you if you don’t have one!
12 Comments
Pilot, got your e-mail, and had a look. Yeah, that’s the kind of thing I started with. It got me thinking about the problem a little bit more, but at least I had a constructive way of controlling the problem. This was a huge leap forward for my peace of mind.
Now, as long as I remember to add new details to my sheet as I make promises, I know a week ahead of time exactly where I should stand at the bank. This is great stuff, and so simple. However, I still don’t much care for the computer, and I bully the kid into doing the major updates while I watch and hassle him from the sidelines., OK, so I am easily amused.
So, is this about ready for me? I think this would be a good addition, right?
Hey Pilot, you got me started on this 18 months ago, and I used to do it before our Sunday a.m. get togethers - force of habit, still do it Sunday morning! Of course, I don’t make a heavy night of it on Saturday, so it may be easier for me than for some others I could mention but who will remain nameless (Drinksomethingorother).
My biggest challenges are the heavy seasonality I encounter, and the insanely unpredictable payment process and timing of the government contract side of the business. Incredibly frustrating that is, although better than it was now that I offer those steep early payment discounts. Sometimes government estimators and contract negotiation types are not overly, ummm, aware?
Looked at your sample sheets, Pilot. I get that you were just using these simple ones for explanation, but for Dewey, you might want one that is a little more sophisticated, right? Offer is I could run one up for you with a set of instructions, betweenh us we’ve done enough of these that I caould do this, as my little contribution to the conversion effort. Pretty sure I have three of four kicking around that I could turn into a template. Let me know - might take me three or four days depending on the feature set, but that would get Dewey off your back!
I use sight lines of credit a fair bit because of my importing. This adds a certain amount of complication to my life, because of the tie-up in funds, the somewhat unpredictable delivery habits of a few of my off-shore suppliers, and the various turn-around times to move product into the distribution chain. So I have had to use behavior models a lot, I get bulges and sags freqeutnyl, and need to make sure that I can ride them out. Getting squeezed when I need a LOC is a real irritant to me, and my suppliers really don’t deal with ti at all well. So - yeah, this is a daily tool for me.
You complicated guys. Look at me ! the keg comes in the door in the morning - I pay for it cash on the barrel head ( well cheque anyway). By the next daya I have turned that keg right back into cash in my safe. End of complication. Done. Finished. Lesson for the day : Pay cash, sell for cash - no inventory, no payables, no receivables and NO behaviour model.
Drinkwater, we all worship at the altar of the master. Sadly, not all of us can run a bar in your estimable style. Some of us are simply not up to the challenge, my man. We must be neurotics - going out and deliberately complicating our lives to drive ourselves nutty. Please live up to your name - Drink Water.
snerk
ahh, yur jes jealous . . .
hiyuck
Nice blog. Umm, seems to be a long time with no posts, like March and April, but I like the posts themselves. You mentioned that you had about 30 or more posts on the original subject of risks, are they still coming?
Are your commenters people who know you, because some of them seem a little unusual.
I am interested in the spreadsheet model that you are talking about. I do something a bit like this, but I don’t use this default assumptions approach that you suggest. Why do you do that?
Welcome farmgate. Hmmm, well, the blog is a part of a group of use who got together starting a few years back to share information and advice and some other things. The idfea was that we could barter knowledge and training as a form of co-operative, like a farming co-op. So we had accounting guys, a corporate tax guy, one or two from the financial/investment sector, IT types, MBAs, and the rest of us were diverse independent business people. The membership restriction was that you needed to be involved as an entrepreneur, and you needed to be able to respect confidentiality.
We met on Sunday mornings mostly, because that was the one time of the week where business demands were relatively quiet. We started off meeting for coffee, and later, when the group got a little bit bigger, as other business acquaintances joined, we met at Drinkwater’s bar, since it was central, and we had the place to ourselves Sunday since he would unlock it just for the group. We still referred to it as the coffee-house for a bunch of reasons, even though what was poured out of the teapot wasn’t actually tea. Anyway we went online about a year ago, because we couldn’t get together in person as much. The blog is actually a conversion of a series of discussions that the group had, with its original comments. Of course, where information was more sensitive or not for the public, I have had to prune, so not all of the discussions/comments flow the way they did originally, and of course, each participant has veto right over what is posted in the conversion. So this is kind of a conversion. There are still missing posts in the March and April months, but I am working on back-filling them as I have time to rewrite them. In a few more weeks, I hope to have the rest of them migrated to this Wordpress blog.
We are doing this because the group decided that they got enough value from the group’s discussions that it would be a sensible idea to expand it to other members of the business community that we haven’t met in person. We figure that it goes around and comes around, and makes life easier for all of us. On top of that, we can talk to people who have at least been there, done that, and get it. Sloppy text perhaps, but that’s the essence of it. If you are interested in what we are up to, follow the link on the blog’s frontpage under BlogRoll to the Harbour Forum pages and take the Tour over there - it should fill you in.
So that is likely what you are picking up on. Drinkwater doesn’t take himself too seriously in real life, and is one of our top contributors. We know him, you see.So . . .
As to the model : The reason I use the assumptions page is because it lets me tie into the statements to a degree, which keeps the assumptions grounded. It also lets me project further out than my hard data would permit, which in many cases expires out about six weeks. Lastly it lets me identify where I have made detail changes that differ from the assumptions, and this lets me track the impacts of my decisions - contracts that permit longer payment cycles for instance. I can very quickly put my thumb on the sore spots. Oh, and I find it is much less work for little sacrifice in accuracy and usefulness. I wanted a quick tool that consumed about ten minutes in a typical week, yet gave me about 90% of the desired accuracy. The remaining 10% is just a little bit of a dance, and I can live with that. I put the extra time into making money instead.
Anyway, welcome to the blog.
Got it, thanks, I’ll have a look at the site. I find it hard to come up with decent data for my assumptions page, to be honest. My payroll fluctuates a fair bit, because a lot of my people are independent subcontractors, and the work volume and costs vary a fair bit. My product sells for cash, except for two outlets that I sell organics to, and the sales tend to vary with the weather a lot, and with traffic patterns. So the inflow is hard for me to establish with much certainty. And I don’t have the same kind of overheads that a lot of businesses have, so the outflow is more detail oriented. Still I try. I’ll give it some thought though - maybe I can make some improvements to what I’m doing.
farmgate - this tool isn’t for everyone. If what you are doing works for you, stick with it. There may be some tweaks that will make it more effective for you, but it has to work for you.
Post a Comment