Disposable Spreadsheets

SurvivalWare is a great conglomerator of data: financial, cash flow, and other.  It also houses a good solid financial model.  You can be pretty sure that the numbers coming out of SurvivalWare are calculated correctly.  What SurvivalWare lacks is the immediacy and autonomy of a spreadsheet – to do stuff like take in crazy assumptions or do one-off KPI calculations. (You didn’t hear me say that!)  The structure of SurvivalWare is nice – until it isn’t.

Introducing the Disposable Spreadsheet – to give you the best of both worlds.  What is it?  A spreadsheet created on the fly, whenever needed.  It includes not just numbers, but the formulas underlying the SurvivalWare financial model.  A complete financial model in Excel, populated with 12 months or more of history, and 24 months or more of projections.  The inputs are clearly marked, as are the calculated cells.  Note that the calculations for a given row can be – often is – different for projected months vs. historical  months.

You can send the spreadsheet to the business owner or to a controller or CFO. A Tax planner.  Consultant on a project that impacts cash flow.  They can do their own what-if?’s in Excel.  If you have a boss,  you can load the marked up spreadsheet back into SurvivalWare.  This is called “Recycling” the spreadsheet.  The updated assumptions become part of the master data file in SurvivalWare, and available for analysis and reporting.  These assumptions are the ones used next time a Disposable Spreadsheet is created.

Otherwise, toss the file in an archive bin in case you ever want to mine it, and move on to next month’s edition of the Disposable Spreadsheet.

Here’s what the disposable spreadsheet looks looks like for a sample data file from the SIMPLE model.

Sample DIsposable Spreadsheet

Sample DIsposable Spreadsheet

Currently, this (the Simple Model) is the only model it is available for.  We are working on upgrading the Fort Knox model to do the same.  As you might expect, the more complex the model, the harder to get it to work reliably in Excel.  The Fort Knox Disposable Spreadsheet Feature should be available in March when we release SurvivalWare 2014. (Luckily nobody reads this blog, so I might have a little wiggle room on the date).

Right now, the way you create a spreadsheet with formulas is to go to the Projections Module and select the “Export Formulas” tab.  You probably want more than a single month of history, so click “More” in the upper right to display more history months.  This is what you see in SurvivalWare prior to the export.


It takes about 30 seconds or so to complete after you click the Export to Excel button.

The neat thing is that it is a piece a cake to read an updated Excel file back in.  Just select File / Import from XLS File…  Then select the file with the updated projections.  SurvivalWare takes care not to change any history numbers during this process.


Excel Ruining the World?

One of the problems with spreadsheets is that it is so easy to introduce errors, especially as the size and complexity increase. We feel that SurvivalWare, with its English-like modeling language underpinning the calculations, is less prone to errors and more transparent. So of course I was mesmerized by the recent brouhaha over the Reinhart / Rogoff study, which tied high levels of government debt to low growth in the global economy. What I didn’t realize until I read this article on CNN Money, is that there are several other high profile examples of spreadsheets going wild in recent history. Spreadsheet mischief had a role in the London Whale epsiode, MF Global’s demise, and Fannie Mae overstating its results.

I love the title of the article: Damn Excel! The ‘most important software application of all time’ is runing the world.

Transaction Models

Transaction Modeling

For some people, a transaction model is a financial model of a complicated transaction, for example a real estate deal or an acquisition.  Transaction in this sense really means “deal.”

However, the type of Transaction Models I’m going to write about in this article refer to transactions as accounting type transactions – e.g. a sale that generates an invoice;  or a payment to a vendor.

Your accountant will tell you that each transaction consists of a debit and a credit.  But don’t let that scare you away!  You don’t have to be an accountant to do transaction modeling.  Rather you have to roll your sleeves up, and pay attention to the details that affect cash.  What you do is forecast all the cash-affecting transactions you expect to occur over some planning horizon (usually 3 or 4 months), and then tabulate them in such a way that reveals your cash balance week by week (or day by day) within that planning horizon.

Here are the most common types of transactions that impact cash:

  1. Transactions with Customers
  2. Transactions with Vendors
  3. Transactions with Employees
  4. Transactions with credit card companies
  5. Transactions with banks (loans, interest, repayment)
  6. Transactions with governments (withholding taxes, sales taxes, property taxes, etc.)

Transaction modeling is used for doing detailed cash planning.   If you can determine all the transactions that will occur for your company for a specific time period  in the future, you can determine the net change in cash for that time period.  So, if you know what your cash balance is to start with (I hope you do!),  you can estimate for each time period in the future how much cash you’ll have on hand.

Because for any time period:

Ending Cash = Beginning Cash + Net Change in Cash

Net Change in Cash = Cash InCash Out

Cash In = The sum of all transactions bringing in cash

Cash Out = The sum of all transactions sending out cash

Here’s how each of these looks:

Transactions with customers

Your Company  ==> Products or Services ==> Customers

Customers ==> Cash  [Delay based on terms] ==> Your company

Customers ==> Credit Cards [Delay based on processing] ==> Cash ==> Your Company

Transactions with Vendors

Vendors ==> Products and Services ==> Your Company

Your Company ==> Cash [Delay based on terms granted] ==> Vendors

Transactions with Employees

Employees ==> Work ==> Your Company

Your Company ==> Cash [Delay based on company policy] ==> Employees

Transactions with Credit Card Companies

Your company ==> Uses credit card for products or services ==> Increases balance owed to Credit Card company

Your company ==> Cash (for interest, fees, and principal) ==> Decreases balance owed to Credit Card Company

Transactions with Banks

Bank ==> Agrees to  loan Cash ==> Your Company

Your company ==> Cash (for principal and interest) ==> Bank

Transactions with Governments

Your Company ==> Cash  (for sales tax, withholding tax, income tax) ==> Government

Back to Transaction Modeling

So the idea is to forecast all the transactions for some period of time, and see how the cash shakes out week  by week.  Many of the transactions are easy to forecast – such as the rent payment, payroll, etc.  These are under your cotrol.   Others are not so easy to forecast:  what will sales be next week?  When will company ABC pay their invoice?  Will the bank agree to my loan request?

In upcoming articles we’ll examine different approaches to developing a Transaction Model and using it to do detailed cash projections for your business.  The same approach can be used for your personal finances as well.

What is Financial Modeling?

Definition of Financial Modeling

 Wikipedia defines financial modeling as the task of building an abstract representation (a model) of a financial decision making situation. 

I prefer a more inclusive definition:  financial modeling is the task of building a financial model, or the process of using a financial model for financial decision making and analysis.  I would agree that a financial model is an abstract representation of a financial decision making situation.  By abstract representation, we really mean a mathematical model, and to be practical, a computer based mathematical model.  The model usually represents an ongoing business, or a project that requires investment.  Financial models are not limited to profit making entities.  Non-profits, governments, personal finances – all can be represented by financial models. 

What is Financial Modeling used for?

 Financial modeling is used to do historical analysis of a company’s performance, and to do projections of its financial performance into the future.  Project finance is another area that lends itself to financial models.  A project (such as a real estate investment, or a new factory) can be analyzed using a financial model.  It does not have to be complete business.

 Financial Modeling is not just for the accountant or financial consultant, who are called upon to develop financial projections, but also for business owners and managers.  With improved user interfaces and heavy use of graphics, it is now feasible for non-technical people to use a financial model to test options and make decisions based on the projected impact on profits and cash flow.

 How does a financial model work?

Some financial models are “black boxes” with their logic hidden or poorly understood by the users of the model.  Most spreadsheet models fall into this category, because the time it takes to find and understand the relevant formulas is daunting for most users.

However, the more you understand about how a financial model works, the more confident you can be in using its results.  In the next four blog posts, I’m going to explain how financial models work for each of the four main kinds of financial models:

1. Transaction based models

2. Discounted Cash Flow models

3. Financial Statement models

4. Consolidation Models

But first (you can skip this part)..

My Introduction to Financial Modeling

Financial modeling is my passion.  I was introduced to it 35 years ago when I went to work for a company called Comshare, which sold computer timesharing services to corporate customers.  We targeted financial analysts, controllers, and CFO’s who were frustrated at having to go through their IT departments for management reports and analysis.   Some things never change.

Back then a new class of higher level software deemed “third generation” was all the rage.  (The first generation was assembly language, second generation was general purpose programming languages such as Fortran and Cobol.  Who knows what generation we are in now!)  Part of this new wave of software was the genre known as the financial modeling language.

These languages let you construct financial models using English like statements instead of obscure lines of code.  Today, some of us old timers consider spreadsheets – with their obscure cell formulas – to be a step backward, at least when it comes to constructing financial models that are maintainable and understandable.

In the first 3 or 4 years after getting a degree in Industrial and Operations Engineering (a fake engineer as my wife likes to refer to it), I bounced around from job to job trying to find my niche.  When I interviewed for the job in Comshare’sArlington,Va.sales office, I told them up front my goal was to start my own company.  (Good boy – correct answer, shows initiative).

I started in the Fall of 1977 as a customer support representative.  I learned FCS, the financial modeling language sold by Comshare that was growing by gangbusters in the late 70’s. Some of the other popular financial modeling languages at the time were Prophit II and IFPS.

As I settled in, I thought I had died and gone to heaven. It seemed the perfect confluence of interest and passion for me:  computers, business, and customer support.  (Some say it is a character defect, but I genuinely enjoy helping people).

One of our customers back then was the Marriott Corporation – who used financial models to do cash flow projections for hotel projects.  They had a great formula for growth.  They would find investors to put up the money to build a hotel, and then take a management fee for running it and including it in the Marriott network.  Marriott had a dynamic young Treasurer at the time named Al Checchi, who pushed for financial discipline and fact based analysis.  Financial modeling was a great tool for crunching the numbers and analyzing the returns to both Marriott as well as the investors.

Another customer was the First American Bank ofMaryland.  They used a financial model for overall corporate planning.  They put in assumptions about interest rates, average loan balances and deposits by category and by branch – and computed a complete income statement and balance sheet for each branch and the consolidated bank.  The model had to handle the simultaneous equation involved in computing the bank’s cash position each month.  In banking, since cash is also inventory, they have accounts called Fed Funds Purchased or Fed Funds sold depending on whether or not they needed to borrow from the Fed.  I could never remember which account meant you had excess funds.  I seem to remember it was counterintuitive.  The financial model sorted it all out.

Yet another customer was Fairchild Industries, a Fortune 500 aircraft manufacturer and NASA contractor.  They had several independent divisions, and used financial modeling to consolidate the financial reports on a monthly basis, and to prepare budgets.

In addition to helping the customers learn the modeling language and developing models for them, I worked with Comshare’s sales representatives to go out and find new customers.  I’ll never forget a sales call we made at McCormick, the spice company inHunt Valley,Md.  They asked if FCS could interface with their General Ledger (i.e. accounting software) that ran on their mainframe computer.  My answer (with a straight face) was yes, of course it interfaces.  You print out the financial reports from the general ledger, and type in the numbers on the computer terminal. I seem to remember that we did not get the business.  That was the state of the art of automated interfaces at the time.

The disadvantage of financial modeling back then was something we referred to as the “ouch point.”  Computer timesharing was priced based on usage.  In Comshare’s case, there was a charge for the length of time you were “logged on” to the computer, plus some measure of CPU seconds.  These charges included a fee to go to the software company that developed the financial modeling language.

Since financial models gave the finance department freedom from the internal IT department, things got done quickly, and usage soared.  The “ouch point” occurred when the monthly timesharing bill was big enough to be noticed by senior management.  Any IT manager worth his pocket protector would try to use the expense as an excuse to bring the application in-house, and the renegade users back onto the IT reservation.  This was a pre-cursor to the Personal Computer vs. Mainframe battles soon to come.

Comshare made a big mistake and promoted me to branch technical manager in early 1979.  All of a sudden I had all the headaches of being a manager, but not much difference in the amount of pay.  I figured if I was going to put up with the BS, I may as well do it on my own.  So I put in my notice, and to make it a smooth transition, I wound down my involvement from 5 to 4 to 3 to 2 days a week in the waning months of 1979, and was fully on my own by January, 1980.  This was just in time for the steepest recession since World War II, the 2nd Oil Price shock, and a prime rate that peaked just shy of 20%.  Timing has never been my forte.

The company I started was called Ferox Microsystems, Inc.  Ferox was short for ferrous oxide.  Iron oxide.  Rust.  My name is Rusty.  So yeah – it was my ego trip: I wanted to get my name into the company without sounding like a one man band.

My original plan was to develop small business accounting software or something else simple enough to fit on the Apple II computer I had bought that summer.  With a second mortgage, a working wife, and no kids – I had about a 6 month horizon to make it work.

To make a long story short, I found that the Apple computer was much more powerful than I anticipated, especially when they released Apple Pascal, a programming language that allowed you to write programs that were bigger than the computer’s memory.  It did all the swapping for you behind the scenes.  At the same time I had some consulting customers who were reaching their “ouch points” and pushed me to find a lower cost way to do financial modeling.

The result was RCS – The Micromodeler, a financial modeling language that ran on the Apple II computer, and sold for $1,500 a pop in mid 1980.  RCS was short for “Rusty’s Computer System.”  Entrepreneurial ego knows no bounds.

A year or so later I sold the North American rights to a book publisher, who renamed the product DSS/Finance or DSS/F for short. Yet another example of my proclivity for poor timing, or just plain poor judgment.

Fast forward to 2002, and I decided to start Luhring SurvivalWare, Inc. to adapt big company financial modeling technology to small company cash flow problems.  I had gone through some roller coaster years with Ferox, and wanted to help other entrepreneurs avoid some of the mistakes I had made.  By this time, I had shed the second mortgage, my wife was working part-time, and we had 4 kids, the first of which had started college.

My original thought was to focus on detailed cash planning to help entrepreneurs survive a cash crisis – but I quickly found this market segment not particularly receptive to parting with their scarce, hard-earned cash.

SurvivalWare has evolved quite a bit since those humble beginnings to include the full range of financial modeling power to do financial statement projections, consolidations, and benchmarking analysis.

Coming up next: Transaction Based Financial Models

Financial Modeling gets cheaper and cheaper

Cheap is Good

 How is it that Financial Modeling is finally within the reach of Small Business?  It is because SurvivalWare makes it cheaper – an order of magnitude cheaper – for a company to install and use a financial model that fits.  SurvivalWare provides the infrastructure that makes the model inexpensive to feed with data and easy to use.   With the new SurvivalWare Pro (coming in December), we have enabled consultants and accountants and bright analysts with the on-the-ground knowledge of a specific company or groups of companies to provide the final “fit and finish” on top of the solid Fort Knox model structure. 

Our Model Generator technology – set to be released shortly thereafter - promises to reduce the cost of developing a completely customized financial model.  You might want to have a highly specialized financial model for say for a franchise system, or a specific industry like convenience stores – to a fraction of what it was four years ago when we first applied Survivalware to such a setting.

Moore’s Law

People in the computer industry are familiar with the concept of Moore’s law.  It states that computer power roughly doubles every two years, and has done so for almost four decades. 

Below is a graph of transistor counts found in the Wikipedia entry on Moore’s law. 

Moore's law - transistor counts

Since transistor count is an approximation for power per unit of cost, we have seen dramatic declines in the cost over the years of processing power and memory.

 You may have seen this quote making the rounds on the internet, attributed to Bill Gates at Comdex in 1999 (there is some doubt he really said it):

“If GM had kept up with technology like the computer industry has, we would all be driving $25 cars that get 1,000 to the gallon.”

The rejoinder of course is that GM issues a press release that says if GM had developed technology like Microsoft,  cars would have the following characteristics: 

  1. For no reason at all, your car would crash twice a day.
  2. Every time they repainted the lines on the road, you’d have to buy a new car
  3. etc., etc.

But you get the point.  The cost of computing continues to get smaller and smaller while the power continues to grow.

Financial Modeling Power

 So what has happened to Financial Modeling power over the years and its cost per unit of power?

Financial Modeling Power per unit of cost - last 30 years

I’ve been in the financial modeling field for over over 30 years and can provide some insight.

Back in the late 1970’s, most financial modeling was done through commercial timesharing services who not only sold “raw cycles” but added value to their offerings by charging a premium to use 3rd generation software such as financial modeling languages.

Back then, I serviced the Marriott account for Comshare, which sold access to a really good financial modeling package called FCS.  They spent about $10,000 per month to do cash flow projections for Hotel development projects in the course of structuring deals and attracting investors to put up the money to actually build the hotels.  Marriott was great at getting hotels built with OPM – Other People’s Money.

I’m intimately familiar with those models and their complexity because I was the one that developed the models for them.  My estimate is that they were moderately complex financial models with a limited number of time periods analyzed, equivalent to about 2 SMU’s of power.  An SMU is a SurvivalWare Modeling Unit, and is equal to a standard 200 variable model applied to 12 months or 10 years for a month’s worth of usage.  So we can say that Marriott was paying a monthly fee of $5,000 per SMU.  Expressed as power per dollar of cost, we get 0.2 SMUs per $1,000.  This was in 1978 and 1979.

I started my first company in 1979 just to get out on my own.  I quickly realized that the microcomputers being introduced then were more powerful than most people gave them credit for.  I set out to drive down the cost of financial modeling by figuring out which features were absolutely critical, and which were not – and providing 90% of the functionality at less than 10% of the cost.  The first product was developed for the Apple II computer in 1980 and called RCS (which stood for Rusty’s Computer System – the ego thing started long ago).  I believe it was the first financial modeling software to appear on a personal computer.

RCS Brochure from Sept, 1980

RCS lowered the cost to Marriott from $5,000 per SMU to $500.  Power rose to 2 SMUs per $1,000.

ENCORE! Plus - 1988

ENCORE! Plus was introduced for MSDOS in 1988 with a power rating of 20 SMUs per $1,000.

ENCORE! for Windows - 1996

ENCORE! for Windows appeared in 1996 continuing the increase in power while holding steady on the cost.  We estimate the cost per SMU at about $20, equivalent to a power rating of 50 SMUs per $1,000.

SurvivalWare 3.0 - 2009

The combination of SurvivalWare 3.0 and the Fort Knox model as currently sold delivers about 10 SMU’s of modeling power.  If we look at the life cycle cost over 5 years, we can convert it to a monthly by dividing by 60 months.  Then divide the monthly cost by 10 to get the cost per SMU which turns out to be about $3 – equivalent to 333 SMUs per $1,000 of cost. 

That turns out to be a 1,700 fold improvement over 30 years. 

And when you look at applying SurvivalWare to a GROUP of small companies, the improvement is even more dramatic – another threefold increase is power per unit of cost.

Computer Modeling in the News

It really is amazing how important computer modeling has become.  This is where the computer really plays to its strengths.

The latest Scientific American (July, 2009) has a cover story article entitled “Grassoline at the Pump” and goes on to paint a very optimistic picture of using non-food plant material to provide 50% of our liquid fueld needs.

From page 55,” “Scientists.  have recently enjoyed an explosion of progress.  Powerful tools such as quantum-chemical computational models allow chemical engineers to build structures that can control reactions at the atomic level.” (Emphasis on models was mine).

Then there is an article I clipped from the June 24, 2009 Wall Street Journal: “Dreamliner’s Delay points to difficulties with composites.”

“Independent structural expers said Tuesday that even the most advanced computer models sometimes have difficulty accurately predicting how stress will affect the composite parts, or where they attach to aluminum or titanium.”

This was a companion article to the surprise announcement from Boeing that they were delaying the flight test for the 787 Dreamliner in order to reinforce the attaching of the wings to the fuselage.

SurvivalWare is, of course, a form of computer modeling.  It actually does two kinds: 

1) basic Financial Modeling, which allows you to do full financial statement projections – i.e. the Income Statement, Balance Sheet, and Cash Flow Statement – based on your analysis of history (conveniently stored in the same model), and assumptions about Sales, Headcount, Expenses, Capital Expenditures, Loans, etc.  Building financial models takes a special skill, and that is part of what SurvivalWare customers get to tap into:  my 30+ years developing financial models for a broad range of industries and company sizes.  An immediate use of this kind of modeling is producing the two years of cash flow projections most lenders ask for when you apply for a loan.  The recently announced ARC Loan program run by the SBA has this requirement.

2) Monte Carlo simulation – used by SurvivalWare’s Cash Planner.  Each and every cash disbursement and receipt is simulated over a 13 – 26 week period (or longer if you want.  Typcially the focus is on the short term).  Measurements are taken at various points to see how much cash is in the till. You can also include credit cards in the simulation.  You have to go to a fair amount of trouble to set up one of these simulations, but it can be a life saver when your back is against the wall, and survival at risk.

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.




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:







(1) A/R Balance

Balance Sheet



  $ 45,660  

(2) Sales each Month

Income Statement



   $ 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)





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.



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

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.

Entering credit card repayment

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.”

Line items affected by Credit Card repayment

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.

Entering Interest Rate assumptions

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.

Result of Interest Rate applied to last month\'s balance


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.

Entering Capital Expenditures

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.




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.

Two years of sales history

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

Selecting the \" width=

And this is what the seasonal forecast looks like:


Graph of Seasonal forecast for Sales



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%.


Use the same values as last year


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


  • Benefits
  • Commissions
  • Payroll Taxes
  • Royalties




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.




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.




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.



Get every new post delivered to your Inbox.

Join 71 other followers