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

Other Current Assets

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

(Incr)/Decr Other Current Assets

(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

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

Summary of Cash Flow

This is how it looks graphically:

Projected Cash Balance

Projected Cash Balance

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

 

 

Balance Check

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

Months or years

 

Projecting 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

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:

 

 

 

April

May

June

(1) A/R Balance

Balance Sheet

 

 

  $ 45,660  

(2) Sales each Month

Income Statement

36,602

29,183

   $ 45,603

(3) Sales last 3 months

Sum of Apr, May, June

 

 

$ 111,388

 

(4) Annualized Sales

(3) times 4 

 

 

$ 445,552

 

(5) Average Daily Sales

(4) divided by 365

 

 

$   1,221

 

(6) A/R Days

(1) divided by (5)

 

 

37

 

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

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

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

A/R assumption in the SurvivalWare grid

If at first you don’t succeed, you’re in excellent company

This was originally posted in a separate blog on 4/30/2008.  I decided to discontinue that blog and combine it with this one for SurvivalWare.

There was a great article in yesterday’s (4/29/2008) Wall Street Journal (page D1) with the same title.  It was the the Health Journal column by Melinda Beck.  The tie-in to health was an examination of something psychologists call self-efficacy, “the unshakable belief some people have that they have what it takes to succeed.”

There were several nuggets that provide inspiration to any entrepreneur struggling to build a business.  Did you know:

  • J.K. Rowling was rejected by 12 publishers before a small London house picked up “Harry Potter and the Philosopher’s Stone.”
  • Walt Disney was fired by a newspaper editor who said he “lacked imagination.”
  • Michael Jordan was cut from his high-school varsity baksetball team sophomore year.
  • It took Thomas Edison 1,000 tried before he invented the light bulb. (“I didn’t fail 1,000 times,” he told a reporter.  “The light bulb was an invention with 1,000 steps.”)

I’m not sure if you have to be a WSJ subscriber to view the article, but here is the link if you want to try:

http://online.wsj.com/public/article/SB120940892966150319.html?mod=2_1566_leftbox

I’ve been an entrepreneur since 1979 (OK maybe since the late 60’s if you count selling selling vacuum cleaners and operating bubblegum machines).  I’ve noticed that the ones who succeed are the ones with perserverance and determination, the ones who never give up.  I put myself in that category.

I am in the midst of a new product launch with my second startup, Luhring SurvivalWare, Inc. (www.survivalware.com).  It has been a long painful process, not made any easier by my desire to retain 100% of the equity and grow without raising outside capital.  I’m trying to explain to my wife how the cash flow problems of the past should not be viewed as failing 1,000 times, but rather the 1,000 steps necessary for our ultimate financial success.