Call Us 1.703.780.2044 or
16
Jun
2008
Doing Projections in SurvivalWare – Part 1: the Forecast Tool
This is a 5 part series of articles covering the fundamentals of doing financial projections using SurvivalWare and the generic financial model that comes with it.
- Getting familiar with the Forecast Tool
- Sales and Expenses
- Debt Service and Investment
- Working Capital
- Other Cash Flow
Part 1 – getting familiar with the Forecast Tool
Intro
The Forecast Tool is designed to help you verbalize your assumptions about a projected line item, and convert the assumptions to hard numbers.
Definition of “Projections”
I’d like to define a projection as your best guess about what is going to happen given a number of explicit assumptions.
You could do a sales projection and stop there. Your explicit assumption might be “I expect sales to 10% above the year ago value for the next several months.” Or “I’d like sales to continue growing as they have recently, and exhibit the same seasonality.” SurvivalWare accommodates either.
The role of the “model”
Individual line items
The model that comes with SurvivalWare combines the latest “actuals” with your projections to calculate the best possible estimate for combinations of months such as the current quarter, and the projection for the current year. (e.g., the current quarter could include one month of actuals and two months of projections).
You can either type in your projection, one number at a time, or use the Forecast Tool. The Forecast Tool helps you verbalize your assumptions and convert them to hard numbers.
The complete picture
The model also takes dozens of individual projections and assumptions of what will happen to Sales, Expenses, and key drivers such as Collection Period and Inventory turnover – and combines these into an overall projection of what happens month by month to profits, the balance sheet, and cash flow. Some refer to this as an integrated financial statement projection because the model captures the interplay between income statement, balance sheet, and cash flow. The end result, is that you have a projection of how much cash you’ll have on hand month by month based on your detailed assumptions about sales and expense levels, planned capital expenditures, etc.
Forecasting a single line item
Here for example is a projection of Organic Visits to the SurvivalWare website. Organic visits occur when someone uses a search engine to find our website (as opposed to a referral, a paid click, or direct entry of our URL). We’ve been using Google Analytics since February 2007 to track traffic to the website, so we have 15 months of data. This is not enough history to do a seasonal forecast, and I am not sure it is seasonal anyway.
Looking at the trend is easy as 1-2-3:
Total Organic visits cannot be forecast directly because it is a calculated row. We can tell it is calculated because it has a yellow background, and we cannot type a number in one of the yellow cells. What we can do is forecast the three rows that make it up all at once.
So we mark the three rows - Google, Yahoo, and Other – and click the Forecast Icon.
10% growth vs. last year
Simple trend
The simple trend uses the least squares regression technique to fit a straight line to the historical data, and extends that line into the future. Just click the Forecast Icon again, select “Simple Trend” and click the Forecast button. Here’s what you get:
Looking at Years
You can switch the view from months to years at any time by clicking on the “Years” radio button in the upper right part of the screen.
This is what our 21 month forecast looks like when summed into years.
Making a total leap of faith
Here’s what happens if we then use the forecast tool to take our 21 month forecast based on a total of 15 months of history and extend it out another 4 years. Just click on the Forecast Icon again, and hit the Forecast Button to compute the “Simple Trend” for the years view.
21
May
2008
A case study of detailed cash planning using SurvivalWare Cash Planner
Ivan Newton was kind enough to be interviewed about his usage of SurvivalWare to right his business and propel it forward. His conclusion: “SurvivalWare is the best solution we’ve found that addresses the unique ‘balancing act’ of our small business.”
Here is his story as re-told by Mary Ritley:
The Challenge
While the business always looked profitable on paper, further review of the financials uncovered notable cash flow problems – enough that Newton had trouble paying the bills necessary to keep the business going. He found himself in the situation that every small business owner dreads. He was cash poor.
Newton made it his mission to better understand cash flow. He searched the internet for information that would help him understand where the money was going and why he wasn’t retaining more profit.
He purchased a variety of software programs that promised to help him understand ratios, make projections, and improve his business. But he quickly became frustrated with expensive “solutions” with complicated applications that offered little more than monthly comparisons. Newton found that these applications didn’t address his fundamental business problem. He needed something much more granular – something that gave him a magnified view of the cash going in and out the door on a weekly, even daily basis.
The Solution
It wasn’t until Newton found an article about understanding cash flow by Rusty Luhring that he began to understand the basics of cash planning and how to better manage his resources. The article offered tips like identifying invoices that need to be paid immediately and those that can be put off without penalty, invoicing clients early, and offering incentives to pay within ten days. Newton found that Luhring was “speaking his language” and was pleased to learn that Luhring also developed a software package – SurvivalWare – that put those cash planning concepts into action.
After an online purchase and a simple installation process, Newton found himself up and running with SurvivalWare’s Cash Planner quickly. The interactive solution allowed him to assess his cash situation on a daily basis and made it easy to move numbers back and forth, see the impact of proposed spending, make changes, and smooth out his cash flow. SurvivalWare also offered the ability to convert numbers to charts and graphs that provided a visual representation of cash flow trends. This functionality also made it easy for Newton to share financial information with his operations manager and to understand the critical combination of primary factors – sales, cost of sales, and cost of labor – that drive profitability.
The Results
According to Newton, “We began using the software in earnest three years ago when we found ourselves in a serious cash crunch. Since that time, SurvivalWare Cash Planner has helped us use our cash wisely, prepare for unexpected expenses, and ultimately maintain a higher cash-on-hand balance.” Newton was also able to meet his financial obligations and avoid bouncing checks.
The ability to actually see the impact of cash adjustments and the results of anticipated decisions was essential for Newton’s business. “No other software package we found offered the ability to conduct an assessment of the business and see how things were changing on a monthly, weekly, and daily basis. With SurvivaWare, I can enter in my accounts payable, and based on accurate sales projections, chart my projected income,” says Newton. He also found the online help to be very beneficial. When he needed to speak with a support person, Luhring returned his call directly and solved the problem.
What’s Next
Newton has recently installed the beta version of SurvivalWare 2.0 scheduled for release in June. This new version has inspired Newton to experiment with features he had not previously used. SurvivalWare 2.0 has enabled him to categorize his sales vs. only seeing sales as a single number. He can now view his cost-of-sales, identify hot items, see items that are generating the most profit, and decide which items to promote. Newton anticipates this new knowledge of his sales mix will help him further improve his profitability. He concludes, “SurvivalWare is the best solution we’ve found that addresses the unique ‘balancing act’ of our small business.”
6
May
2008
How to guide – using the SurvivalWare Cash Planner
Step 1 – enter the current cash balance. And also define the planning horizon with the ending date of the first period, and the number of periods, and their units (days, weeks, months).
Step 2 – enter payments. We can copy/paste from Excel. Also, if you use QuickBooks, you can export the current payables to a file that SurvivalWare can import. (See www.survivalware.com/download/Importing_AP_into_SurvivalWare.pdf)
Step 3 – enter open invoices. Again, we can copy/paste from Excel. Also, if you use QuickBooks, you can export the open invoices to a file that SurvivalWare can import. (See www.survivalware.com/download/Importing_AR_into_SurvivalWare.pdf)
Step 4 – Enter monthly sales estimates. You have the option of making assumptions about the percentage of orders that are prepaid vs. invoiced, and you can take commissions out of the future cash flow stream as well. You can also set billing patterns if you want (e.g. End of month billing for services vs. evenly distributed throughout the month for product sales).
Step 5: Look at the results. You see them graphically, though you are at a loss as to why it takes 4 charts to convey the message. All you need is the bottom charts to see if there are any months where your cash balance dips below zero. It turns out they are identical when you do a simple estimate, but will display different measures when you do a simulation.
Here’s where SurvivalWare Cash Planner starts to shine.
Working on a payment plan
You can mark one or more payments, and then delay them by a certain amount of time, or split them into multiple pieces.
SurvivalWare makes note of the delay in the Payments tab.
Now the results tab shows there is no problem. You just have to explain the situation to your landlord and employees.
Dealing with multiple credit lines and/or credit cards
What, you actually borrow against credit cards to finance your business? Don’t you read the know-it-all financial planning columnists who tell you what a bad thing that is to do? A very costly form of borrowing?! Shame on you.
If you’re going to construct a portfolio of high coupon debt to provide the capital wherewithal to seize opportunities in your business (or borrow from your credit cards to keep from going bust) – the least you can do is keep track of the details and know where you stand and how it affects your cash flow and cash cushion.
There is a grid in the Credit Lines tab to capture all the information you need to forecast interest, fees, and minimum payments, plus available borrowing capacity in future time periods.
(see www.survivalware.com/download/CreditLines_in_SurvivalWare.pdf for a full explanation of the Credit Line feature).
When you run a simulation, you can instruct SurvivalWare on whether it is OK top tap unused credit lines to keep from running out of cash.
Introducing uncertainty
You don’t need SurvivalWare to introduce uncertainty into your life. I’m sure there’s a lot of that already. SurvivalWare helps you cope with uncertainty in your cash flow by tying many different assumptions together into a single simulation.
The computer in effect asks “what-if?” 100 times for each tiny event, including the collection of a specific invoice – and tabulates the end result of all the tiny events happening at random, but within the ranges you set.
In the settings tab, just pick “Run a SIMULATION.”
Collections of existing accounts receivable
You know damned well that not every customer pays in precisely 35 days. In fact, some receivables may go bad, and you never collect.
The collections tab allows you to set the “mean” collection period based on the age of the receivable.
Future Sales
This is where you really need a crystal ball. First, to forecast what sales will be next week, next month, or next year. Second, to forecast when you’re actually going to collect the cash for those sales.
SurvivalWare allows you to forecast up to 10 revenue streams, each with its own billing pattern and other cash flow characteristics.
For each revenue stream, you specific a low value, most likely, and high value for the sales in each month of the planning horizon. You can also specify that a certain percentage of the sales are made for cash (as opposed to invoiced), and you can take commissions out with a lag time from when the money is collected from the customer. (A negative lag time means you pay commissions before collecting).
Uncertain major events
The source of the uncertainty could be the timing (a major deal is sure to close anywhere between the 1st of June and the end of August), or the amount (e.g. penalties and interest less negotiated abatements to the IRS for past transgressions), or both.
I can never remember the format, so I click on the help icon (“?”) in the “Income / Extr Event” tab.
Tying it all together
That is what a simulation in SurvivalWare does for you. Click on the “Results” tab and the 4 quadrants tell the story of the simulation.
Here it is without the big deal:
Here it is with:
The tabs along the right hand side of the screen let you access other graphs, or dive into the numbers.
Upper left: Receipts vs. Requirements
This graph shows what happened during the simulation. Each green line represents the cumulative amount of cash received at the end of each time period for a single “trial.” The bottom red line represents the cumulative cash required to make all the payments you listed after exhausting your initial cash balance. The other red line adds to that the minimum payments required on your portfolio of credit cards if you are using the credit line feature. It is good to see green lines above the red lines – that means you have enough cash.
Upper right: Survival Score
This graph shows the probability of being able to make the payments according to your payments schedule.
Lower left: Cash – Conservative View
This graph shows your cash balance for each time period with 90% confidence. This means that during the simulation, 90% of the time your cash balance was actually higher than the value shown.
Lower right: Cash – with Luck
This graph shows what might happen with a little luck. It shows your cash balance for each time period with 50% confidence. This means that during the simulation, 50% of the time your cash balance was actually higher than the value shown.
