Year End Rollover

Your SurvivalWare data file (.MTX) holds a limited amount of historical data.  At the end of each year, there is a process you go through called a “Rollover” to archive the oldest 12 months of data, and make room for the new year.

The time to do the rollover is when you are ready to load January data – which is probably some time in February.  You can do it right AFTER loading December data.  There is no harm in doing it then.

NOTE: If you are on a fiscal year that ends in a month other than December, you would do the rollover after loading the first month of the new fiscal year, not January.

To do the rollover, go to Rusty’s Toolbox from SurvivalWare’s main screen.

As a way of preventing accidental rollovers, SurvivalWare requires the “Last Actual Month” be set to December (the last month of the Fiscal Year) before you are allowed to do a rollover.  From Rusty’s Toolbox you can set the Last Actual Month yourself – select Data / Set Last Actual Month.

When you are ready to do the Rollover, select Data / “Rollover this file”.

The first box controls what the settings are to be AFTER the rollover.  Set the month to January (or the first month of the Fiscal Year if you are on a fiscal year).  Set the year to the new fiscal year.  (2009 for those rolling over from 2008).

You have four options for dealing with the Budget:

1. Clear budget columns – just sets all the budget values to zeroes, for both the 2009 Budget, and the 2010 budget.
2. Preserve budget data – leaves the 2008 budget in the 2009 budget columns.
3. Move projected values into Budget Columns – the monthly projections you made for 2009 are moved into the 2009 Budget columns.
4. Move Next Year budget into Current Year Budget.  During 2008, you are allowed to work on the 2008 budget (Current Year) or the 2009 budget (Next Year).  All actual to budget reporting uses the Current Year.  If you’ve already prepared the 2009 Budget,  you can move it into the Current Year budget columns during Rollover. (This is the default).

When you click OK, SurvivalWare saves a copy of the file pre-rollover in the Archive directory, and tells you its name.  Your current file will now show a current fiscal year of 2009.  You can select File / Properties, and click on “This Data File” tab to check.

Time periods stored in a SurvivalWare MTX file

2008 file prior to rollover:

Months:  Dec 2005 to Dec 2009

Years: 2010 to 2019

Current Budget: 2008

Next Year Budget: 2009

2009 file after rolling over the 2008 file:

Months:  Dec 2006 to Dec 2010

Years: 2011 to 2020

Current Budget: 2009

Next Year Budget: 2010 

Note:  The time periods stored may vary from model to model.  The example above is for the generic FM2008 model that comes with SurvivalWare.        

Doing Projections in SurvivalWare – Part 5: Other Cash Flow

This is part 5 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

1. Getting familiar with the Forecast Tool
2. Sales and Expenses
3. Debt Service and Investment
4. Working Capital
5. Other Cash Flow

Other Cash Flow

What happens in a financial projection model is that you use various techniques to forecast each and every item on the Income Statement and Balance Sheet (except for cash). The model figures out what the level of cash has to be each month in order for the balance sheet to be in balance. The integrity of the model is kept intact by calculating two checks:

(1) Is the balance sheet in balance? (i.e. Total Assets = Total Liabilities and Equity)

(2) Is your ending cash balance each month consistent with the beginning cash balance and Net Cash Flow?

In the previous sections, we have forecasted all the income and expense items for the Income Statement, and the major items on the balance sheet (Accounts Receivable, Inventory, Accounts Payable, Debt, and Fixed Assets). This last section ties up the loose ends by giving us an opportunity to forecast what happens to the minor players on the balance sheet.

Most of the items in this section are of the type “Incr/(Decr)” or “(Incr)/Decr.” Most accountants will recognize these terms from traditional Cash Flow statements.

Incr is an abbreviation for “Increase.”

Decr is an abbreviation for “Decrease.”

The parentheses are used to indicate what type of cash flow occurs when there is an Increase or Decrease.

“Incr/(Decr)” means that this line item is calculated as a POSITIVE number where there is an increase in the underlying account from one month to the next, and a NEGATIVE number when there is a decrease. Cash flow items associated with Liabilities fall into this category. Suppose for example that the balance of Customer Deposits jumps from $5,000 one month to $8,000 the following month. This is an Increase in a liability account of $3,000. “Incr/(Decr) – Customer Deposits” would show a positive value of $3,000 for that month. In real life this means that a customer has put down a deposit of $3,000 that month, and you now have the cash.

“(Incr)/Decr” means that this line item is calculated as a NEGATIVE number where there is an increase in the underlying account from one month to the next, and a POSTIVE number when there is a decrease. Cash flow items associated with Assets fall into this category. Say you have a Notes Receivable from a business partner of $12,000 as of the end of June. This means the business partner owes your company $12,000. If you advance another $5,000 in July, the balance owed will increase to $17,000. But because this consumes your cash, the $5,000 shows up as a NEGATIVE number when you look at a cash flow statement.

For the “Other Cash Flow” section of the cash flow projection, you enter the CHANGES to these balance sheet items as the way of forecasting the balance each month into the future. It is important to get the sign right. Positive cash flows (an increase in a Liability or a decrease in an Asset) are entered as positive numbers. Negative cash flows (a decrease in a Liability account or increase in an Asset) are entered as negative numbers.

When you enter numbers in this tab, you can quickly flip to the Balance Sheet tab to see the impact of your assumptions.

Of course, the first step is to eyeball what happened in the past.

In the sample data , notice that the account “Other Current Assets” just bounces around a bit, and you might see a change of $2,000 or $3,000 from one month to the next. Here is what it looks like on the Balance Sheet:

Other Current Assets

And here is the (Incr)/Decr calculated in the “Other Cash Flow” tab.

(Incr)/Decr Other Current Assets

It seems reasonable to forecast no change in the account going forward unless you have some specific knowledge of some activity that will affect this account in the future.

There are times when accounts play a more prominent role in your total Cash Flow Projection.

If you are a software vendor who sells annual support contracts, or a publisher who sells prepaid subscriptions, you might find a lot of activity in the Deferred Revenue Liability account. If you sell a $12,000 support contract that covers a 12 month period, you would set up a liability (deferred revenue) of $12,000 – and reduce that amount by $1,000 per month as you perform the service. The corresponding cash flow item – “Incr/(Decr) Deferred Revenue” would show a positive change of $11,000 the first month (the $12,000 sales less the $1,000 recognized as revenue the first month), and then minus $1,000 per month for the following 11 months.

For the sample data we’ve been using in this projection, here are the items we forecast in the “Other Cash Flow” tab:

Other Cash Flow tab

We’re planning a “Distribution to Owner” of $10,000 in August 2008. This is a payment not considered to be Salary, but is more like a dividend. Depending on your ownership structure and how you capitalized the business, you might have an “Owners Draw/Distribution” account in the equity section on the balance sheet. You can put money in or draw it out without tax consequences.

Separately, there is an asset account called “Loans to Shareholders.” By prior agreement, we’re lending an additional $2,000 each month to a key shareholder. This is a negative cash flow and is entered as -$2,000 in the line “(Incr)/Decr Loans to Shareholders” in each month.

Summary of Cash Flow

This is the combination of all the assumptions made in the other sections. Changes you make are instantly reflected here. The summary starts with the beginning cash balance, and adds each of the four major cash flows to get to the Ending Cash Balance. Note that Ending Cash is projected to be negative in July and August. And of course, Beginning Cash Balance is equal to the prior month’s Ending Cash Balance.

Note: EBITDA is Earnings before Interest, Taxes, Depreciation, and Amortization – and is basically the same as Operating Income.

Summary of Cash Flow

This is how it looks graphically:

Projected Cash Balance

There is also a “Cash Flow Balance Check” section to make sure the model is in balance.

Balance Check

When you see “POM” in the variable names, it stands for “Peace of Mind” – the cash flow schedule that Philip Campbell talks about in his book, Never Run Out of Cash. The SurvivalWare model calculates two cash flow formats:

· The “Peace of Mind” Schedule

· A Traditional Cash Flow statement

The line item “Ending Cash Balance – POM” is the ending cash balance calculated when you add all the cash flows from the Peace of Mind schedule to the beginning cash balance. This should equal the Ending Cash Balance that appears on the Balance Sheet, and if it does the “Cash Flow Check” line will have all zeroes. If not, it probably means the balance sheet was out of balance in the last historical month.

At this point, you can go back to any prior tab, and make changes – then come back to this tab to see the impact on you cash balance. You can also look at tabs that contain the projected “Income Statement” and “Balance Sheet” in the same format used for the historical statements. Use the menu item “File / Save” to save these projections. You can save them under a different file name if you want, and keep multiple scenarios.

Changing the planning horizon

The model allows for a total of 5 projected years beyond the current year. Within that planning horizon, you have the option of breaking next year into months, or forecasting it in total.

To switch the view to years, click on the “Years” radio button in the upper right.

Months or years

Projecting Years

Then you can use the “File” menu item to switch back and forth between forecasting next year (2009 in this case) as Months or Years.

File menu

Doing Projections in SurvivalWare – Part 4: Working Capital

Fluctuations in Working Capital can be the source of a major disconnect between profits and cash flow. This article covers how to analyze what has happened in the past, and how to use that knowledge to forecast Working Capital in the future.

This is part 4 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

1. Getting familiar with the Forecast Tool
2. Sales and Expenses
3. Debt Service and Investment
4. Working Capital
5. Other Cash Flow

Working Capital

In this section of the model, you’ll find out just how sensitive your cash is to your ability to collect receivables and control inventory turnover.

You can forecast Accounts Receivable (A/R) based on Days Sales Outstanding, or override the forecast in any month with a dollar amount.

The same is true for Inventory and Accounts Payable.

The first step is to see how you have performed in the past.

History

A/R (Accounts Receivable) Days

You might see the term DSO (Days Sales Outstanding), or even Collection Period used interchangeably with A/R Days.

DSO is easy to calculate based on the A/R balance and recent sales history. A true collection period would be very difficult because it would require an analysis of all invoiced sales and how long it took for the customers to pay. What makes that calculation even more difficult is having to decide how to treat open invoices. Does excluding them bias the calculation? If you include them, what value do you assign for how long it took to collect them? Also, do you weight them by invoice amount?

DSO is an approximation of collection period, and works well in a projection model. Here is how the monthly DSO is calculated in SurvivalWare:

1. For each month, take the ending A/R Balance

2. Divide by average daily Sales

You compute the average daily sales based on the last 3 months of sales to smooth things out. To do this, take the last 3 months of sales, multiply by 4 to get an annualized number, then divide by 365 to get a daily number.

Example – numbers needed to calculate A/R Days for June:

This is how it is presented in SurvivalWare’s DataViewer after you do a drilldown on “Days of Sales in A/R”:

Drilldown on A/R Days

Doing a barchart showing the trend helps you figure out what a reasonable assumption for the future might be. It looks like 35 to 40 days will be about right assuming no major changes in collection policies or the mix of customers.

Barchart of A/R Days

If you have inventory in your business, you analyze days of inventory in much the same way as Days of Sales in A/R. For a given month, you take the ending Inventory Balance. Then divide by the daily average “Cost of Sales.”

“Days of Expenses in A/P” is calculated in a similar fashion. For a given month, you take the ending balance of Accounts Payable. Then divide by daily average “Expenses.” These expenses exclude ones that never enter the payables system, such as Payroll, Depreciation, and Amortization. “Days of Expenses in A/P” is an approximation for how long you take to pay your vendors. If you see the number rising, it could be a sign that cash flow is deteriorating, and you are depending on your vendors to carry you.

Projections

The reason for analyzing the three key measures of working capital in the past is to come up with reasonable estimates for these measures in the future. The balances for A/R, Inventory, and A/P are calculated as a function of your projections for Sales, Cost of Sales, and Expenses.

Then as you change your projections for sales and expenses, the working capital balances are updated automatically. The model needs to know how much you will have tied up in Receivables and Inventory, and how much you defer in Payables before it can figure out how much cash you will end up with for any given month.

.

SurvivalWare gives you the option of entering these balances directly by typing numbers into “override” cells, or by entering the number of “days” and letting the model calculate the balances.

When you enter days, this is the formula for calculating projected Accounts Receivable:

A/R Balance = Avg Daily Sales * A/R Days

The example below shows how you can enter $50,000 as the Accounts Receivable balance in July, 2008 (“A/R Override”), and then assume 40 days of Accounts Receivable thereafter. The model calculates the resulting balances to use in the balance sheet.

A/R assumption in the SurvivalWare grid

Doing Projections in SurvivalWare – Part 3: Debt Service and Investment

Two key components of your cash flow are 1) what happens to your debt (new borrowings, repayments, interest) and 2) spending money on stuff that doesn’t show up on the income statement right away (Investment / Capital Expenditures).

This is part 3 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

1. Getting familiar with the Forecast Tool
2. Sales and Expenses
3. Debt Service and Investment
4. Working Capital
5. Other Cash Flow

Debt Service

There is where you put assumptions about new borrowings, principal repayments, and interest payments.

You may not separate the interest expense out on your books by category of debt. I’ve seen a lot of companies with one interest expense account on the Income Statement, but then maybe 5 or 6 loan accounts on the Balance Sheet. If this is the case, the model won’t calculate a good historical interest rate for you. You’ll have to estimate what it is for each category of debt; or just calculate one rate overall and use that same rate for all categories.

Borrowings are entered as POSITIVE NUMBERS.

Principal repayments are entered as NEGATIVE NUMBERS.

Interest is entered as an ANNUAL INTEREST RATE for each category of debt.

Example – Credit Card Debt

If you plan to pay off $5,000 in credit card debt in July, 2008: click on that cell, enter -5000.

After you hit ENTER, the value is accepted by the grid, and the model updates the calculated values such as “(Princ Repay) – Total”, “Credit Card Debt – Balance”, and “Total Debt.”

Scroll down a little more to enter the “Interest Rate” for your credit card debt. Typing an asterisk (*) before the number indicates you want to use the same number for all remaining months.

The Interest Rate is entered as an annual percentage rate. You should use a whole number (e.g. 23.99 not 0.2399 to indicate 23.99%). The model divides by 12, and applies this monthly rate to the prior month’s ending balance to compute Interest Expense for each month.

Investment / Capital Expenditures

This is where you enter planned capital expenditures such as Equipment or Vehicles. You also need to enter the depreciation roll-off for your existing fixed assets.

Here is an example of a $20,000 equipment purchase in July 2008. We type the number “20,000” in the “5 Yr Life” category. The model calculates the “Cash Flow from Investments” as a negative number (a cash outflow), and shows the impact on Depreciation and Net Fixed Assets as you scroll down.

If you are leasing the equipment, you would skip this section and just enter the lease payments in one of the Expense categories. (There are 5 “Other Expense” line items at the send of the expense section for this purpose).

If you are borrowing some or all of the money, you would enter the purchase price in this section in the month of purchase, and then enter the amount borrowed as a positive number in the Debt Service section.

Doing Projections in SurvivalWare – Part 2: Sales and Expenses

The first step in putting together a complete financial projection is to forecast Sales and Expenses.

This is part 2 of a 5 part series on how to create a cash flow projection using SurvivalWare and the generic financial model that comes with it.

1. Getting familiar with the Forecast Tool
2. Sales and Expenses
3. Debt Service and Investment
4. Working Capital
5. Other Cash Flow

You can forecast Sales by product line, or just Sales in total – whichever you feel more comfortable with.

For other line items, sometimes you will be forecasting rates or percentages instead of dollar amounts. Gross margin is an example of this, as is Payroll tax %. You’ll find that the rate has been calculated for you in the history time periods, so you can see what it has been before deciding what to use going forward.

Sales

If you’ve got two full years of history, and your sales are the least bit seasonal, I would start with a seasonal forecast. You have the option of forecasting each of 10 product lines separately, or entering one overall sales forecast. Unless you have good records on the gross margins of each of the separate product lines, I would just forecast the total.

Here is some actual sales history from a lawn mowing business based in the Northeast U.S. As you might expect, it is highly seasonal.

This is what it takes to do a seasonal forecast using the Forecast Tool:

And this is what the seasonal forecast looks like:

Expenses

Some overhead items such as rent or salaries you’ll know the number and want to enter it by hand. Others you’ll want to use the forecast tool to project a percentage increase over last year, or just use the year to date average.

Here’s a way to say “use the same values as last year.” Mark a bunch of expense lines, and click on the Forecast Tool icon. Then click “Last Year + X%” and leave X at 0%.

There are four special line items in the list of operating expenses that are calculated based on percentages:

• Benefits
• Commissions
• Payroll Taxes
• Royalties

Benefits

Sometimes benefits are fixed (e.g. health insurance), and sometimes they are better estimated as a percentage of total salaries and wages – e.g. 401k contributions. If you enter a percentage on the Benefits % line, the percentage takes precedence. The percentage is applied to Total Wages and Salaries, which is the sum of Salaries (Officers and Other), Commissions, and Bonuses.

You can override that by entering zero for the percentage, and keying in dollar amounts (or applying the Forecast Tool) directly on the Benefits line.

Commissions

The percentage is applied to total sales to compute commission dollars.

Payroll taxes

This is the employer share of FICA in the U.S., and in theory should be 7.65% of total salaries and wages (the sum of Salaries, Commissions, and Bonuses). Toward the end of the year, this percentage may come down if you have employees surpass the Social Security limit of $100,000 in earnings for 2008.

Royalties

This is for franchise businesses who must pay a percentage of sales to a franchisor. The percentage is applied to Total Sales. If you don’t pay royalties, just leave the percentage at zero.

The result of all this is a projection of Operating Income, which is where most people stop, but which is just one of four major components of cash flow.

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.

1. Getting familiar with the Forecast Tool
2. Sales and Expenses
3. Debt Service and Investment
4. Working Capital
5. 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.

How to guide – using the SurvivalWare Cash Planner

Tech Note – Setting up a new company in SurvivalWare

One thing nice about being a small software company is that you can make design decisions quickly without having to get approval or communicate with other departments.

This is especially nice during the “home stretch” phase just before release of a product when you are trying to develop the help files and documentation to explain how to use the software, while at the same time getting feedback from alpha and beta testers who use it in real life.  Having to explain how or why to do something can expose clumsiness in the user interface.

We had one of those moments this weekend.  We are putting a whole lot of effort into making it simple to set up a new company in SurvivalWare and do the initial mapping and loading of financial data exported from their accounting software.  I have found that if you can get someone to look at their own data in SurvivalWare, there is about a 90% chance they will buy the software.  The fewer barriers to getting to this point, the better.

Thinking like a programmer, I had put in a “Company Setup” icon on the main screen – not to set up a new company, but to let you edit the “Setup info” for an existing company.  Setup info consists of a company ID and a long company name, a company logo, and details about where the financial data is coming from.  Anne, my technical writer, was given the task to explain to a new user how to set up a new company file in SurvivalWare, and load financial data.  She kept going to the “Company Setup” to accomplish this task, and really got tripped up when I took out the “<Add New>” feature of the Company Selector.

The solution was to make the whole “File / New, Open, Save, Save As” menu structure available in “Company Setup.”  As I write this, I realize that it really makes sense to take the next logical step and make the “Load Data” icon available on the company setup screen.  Instead of telling the user to “Go back one level, and then click Load Data” after setting up a new company, it’s just “Next, click the Load Data icon.”

Tech Note: Company Selector

Warning: I have my programmer’s hat on.  Language may not be suitable for non-techies.

8 a.m. Saturday morning.  I’ve spent the last hour and a half trying to track down a bug in the “Add New” option of the Company Selector.  This is on top of the last hour and a half of the workday yesterday.  To refresh your memory, the Company Selector is available in the Data Viewer and a few other places.  It lets you select a company from a pull down list.  It builds the list by looking at all MTX files in the current DATA directory.  (There is also a way  to control what appears in the list via a setting in FinMod.SMD, the model control file for a given model).

Right now, I insert the text “<Add New>” as the first item in the list.  If you select that, and click OK, you are prompted for a Company ID, which SurvivalWare uses as the file name by adding “.MTX” to the end.  It seems to correctly set up the new file – because I can come back open one that I’ve created.

But for some reason, right after it sets up the new file, and on its way back to the DataViewer, it bombs.  Unfortunately, it is one of those catastrophic errors that practically bring down the entire PC, and leaves you clueless where to look.

And then after I click “Don’t Send”:

Here’s a portion of the “Error Report”.    I wonder if someone actually reviews this if I click the “Send Error Report” button.  I always figured it was like the “Close Door” button on elevators – something to make you FEEL like you have some control, or that you’re doing something about the problem.

I finally decided to wave the white flag.  You no longer will see “<Add New>” as an option in the Company Selector.  That feature will be kicked onto the Version 2.1 list, to be analyzed and prioritized at a later date.

I thought it was going to be an easy fix – just find where I put the item <Add New> in the list (it turned out to be in 4 places) and comment out that line.  I’m being optimistic that I’ll be able to come back and fix it later.  I just tried running it, and now have to search for the 4 or 5 debug messages that now appear since it no longer bombs.

You can set up a new file easy enough: just select File / New from the main screen, and you are done.  File / New is also available in Rusty’s Toolbox.