DrillDown – an essential tool for financial analysis

It’s true that you don’t really appreciate something until it is taken away from you.  I experienced that earlier today with the DrillDown feature of SurvivalWare.  I’m working with a new customer to automate the loading of financial data for 60 to 70 freestanding locations.  We are using the Simple model as a first step, and as a result, each SurvivalWare line item has several accounts mapped to it.  I am using this customer as a test case to improve the ability of SurvivalWare to accept financial data from sources other than QuickBooks.  So in the course of fixing a “design feature” that would not allow the same account number to be used twice with different descriptions, I introduced a bug that had the impact of disabling the drilldown feature for these types of imports.  At the same time, I was trying to validate and make sense of their data.  It was IMPOSSIBLE to do without a working DrillDown.

I thought this would be a good opportunity to review the purpose and operation of the DrillDown feature in SurvivalWare.  DrillDown is a term in data analysis that refers to finding the detail behind any data item.  The idea is that you are “drilling down” into the data to find out what is underneath any particular number.  Here is how it is supposed to work.

Drilldown(small)

 

 

 

 

You click on any cell in the DataViewer to make it the “Focus Cell.”    Then click on the “Drilldown” icon to show what the detail is behind that cell.  Remember that there are two types of variables in SurvivalWare:  INPUT and CALCULATED.  If you are looking at an INPUT variable, a Drilldown in SurvivalWare shows the accounts from your accounting system that were mapped to that variable.

 

If you are looking at a CALCULATED variable, the Drilldown will show you the variables used in the calculation.  These should be the same variables that show up in the Formula Bar – but along with the month by month values for each variable, not just the logic.

So back to the bug – I did roll up my sleeves and get it fixed before sending the version out to any customers.  I figured it was easier to fix the bug than to limp around without a working drilldown.  It was amazing to me how much I have come to depend on the drilldown in my day to day financial analysis.

 

SurvivalWare 2014 Officially Released

SurvivalWare 2014 Released

The customer notifications just started going out last week, and finally we can announce the new version is available to everyone!

There are two versions:

  • SurvivalWare Simple  at $495 is for those who need to analyze a single company, using either a simple or sophisticated financial model.  We think this version will appeal to business owners, entrepreneurs, and their financial helpers such as bookkeepers, accountants, and consultants.
  • SurvivalWare Pro at $895 is for those who need to analyze multiple locations or companies, or developers of custom financial models and reporting systems.  SurvivalWare Pro is great for franchisors with hundreds of franchisees to analyze in a common format, larger enterprises looking for cost-effective reporting solutions, and entrepreneur-developers who want to provide services to these enterprises, or run their own Profit Improvement Groups.

What’s New – SurvivalWare Simple

Two Models Included – Simple and Fort Knox

SurvivalWare at its core is a financial modeling tool.   The Fort Knox model is a sophisticated corporate planning model developed over many years.  It allows for 10 product lines, 50 or 60 operating expense lines, and flexible projection methods for working capital so that you can hardwire assumptions, or use a Days of Sales as a forecasting technique.  It also has the ability to fund cash  shortfalls with a credit line.

The Simple model strives for simplicity above all else.  There is just one revenue category, and 15 operating expense lines.  Mapping and loading data goes a lot faster.  There are fewer tabs to choose from in the DataViewer, and the number of reports available is about a third of what comes with the Fort Knox model.   The Simple model still does a complete Income Statement, Balance Sheet, and Cash Flow projection – it just uses fewer lines to do it in.  A side benefit to the simplicity is that the complete set of formulas for the Simple model can be exported to a spreadsheet, not just the data.

Cash Flow Focus Report

Cash Flow Focus

Philip Campbell came up with the idea about a year ago, and we collaborated on implementing the feature in SurvivalWare.  The concept is to calculatet the Top 3 Cash Flow items each month, and present them in order of importance (i.e ranked by magnitude) along with an “All Other” category.  The idea is to focus your attention on the top 3,  so that you can explain what happened to the cash last month (or last quarter, last year) in a two minute conversation with a spouse or business partner.

Cash Flow Focus Report

 

 

 

 

 

 

The Cash Flow Focus Report is available as a formal report, or as a special view (with graphing, drilldown capabilities) in the DataViewer and Projection Modules.

 

Formula Bar

We added a formula bar to show you how each row in the model is calculated in plain English.  

Formula Bar - History

 

 

 

 

 

 

 

Even better,  if a variable is calculated differently for Projected months vs. Historical months,  you see a different formula based on the focus cell.

Formula Bar - Projection

 

 

 

 

 

 

 

And if you have used the Forecast Tool to forecast a key assumption, the Forecast Technique is displayed in the Formula Bar.

Formula Bar - Forecast Technique

 

 

 

 

 

 

 

Notes

You can enter text and associate it with a specific row and time period.  The notes appear in a Notes box at the bottom of the screen as you click on any cell.

 

blog-notes-icon-pick

 

 

Entering a Note:

 

blog-notes-enter

 

 

 

 

 

 

They also appear on printed reports in two different formats:  the Actual vs. Budget Reports show the notes on the same line as each item on the report.

 

 

blog-act-bud-notes

 

 

 

 

 

 

 

 

 

You also have the option to display the text as footnotes at the end of each page, just by clicking on the “Display Notes” check box on the report screen.

 

blog-notes-checkbox

 

 

 

 

 

 

blog-notes-2

 

 

 

 

 

 

 

 

 

Notes are also displayed on the Trend Graphs if any of the time periods covered by the graph have notes associated with them.

 

 

blog-notes-barchart

 

 

 

Disposable Spreadsheets with formulas

This cool new feature is available for the Simple Model only.  The template for the Fort Knox Model is under construction.

 

blog-disp-ss-icon

 

 

blog-disp-ss-2

 

 

 

It warns you it’s going to take a long time.

 

blog-disp-ss-Yes-to-Create

 

 

 

It seems to take Forever!

blog-disp-ss-elapsed-time

 

 

 

 

 

This is what the spreadsheet looks like in Excel:

 

 

 

 

 

blog-disp-ss-xls

 

 

 

 

 

Row Mapping Improvements (and dozens of minor improvements)

Real Quick, some of the more notable ones:

  • Row Mapping Tool
    •  “omitted only” view helps your troubleshoot mapping errors.
    • ability to set flex names for the model rows during the mapping process.  Just “right-mouseclick” on the mapped-to row and type in a new nam
    • ability to deal with duplicate account numbers (but different descriptions) when mapping financial statements originating from accounting systems other than QuickBooks.  SurvivalWare really shines at importing data from weird formats produced by niche accounting software.
  • ZIP Files – the Simple Model and Fort Knox Model now create a single zip file with all relevant company files in one place – including row maps and imported financial statements.  This makes it easy to send SurvivalWare data from one person to another.  The zip files are typically 3 or 4 MB or less in size, and can be sent easily as email attachments.  Note: the AlphaGraphics Model has been doing this for years.

 

What’s New in SurvivalWare Pro

Coming soon… in Part 2 of “SurvivalWare 2014 Officially Released”

blog-power-menu

 

 

 

 

 

 

 

 

 

 

The Power Menu

 

Look before you Leap

Building a business is about constantly testing and evaluating business models to see what works best.  You often have to take a leap of faith because the outcome of a major decision – such as a project  - or  a new marketing campaign – or an acquisition –  is uncertain and unknowable.  Yet you don’t do these things blindly.  You have certain expectations about what will happen and when you’ll know if something is a success.  “Look Before You Leap” means that you state these assumptions and expectations explicitly, and analyze what might happen from a financial standpoint.

Back of a cocktail napkin is a good place to start.  But then things can get complicated as you learn and pivot and spend and invest and pivot some more.  All of a sudden you have employees and leases and inventory and receivables.  A portfolio of credit cards becomes a major source of working capital.

So how much is this project XYZ going to cost?  Month by month?

What impact will it have on sales?  Margins?  Month by month?  Also: what are the ranges of possible values?  Best case?  Worst case?

Suppose I sell licenses only and not subscriptions?  How about the other way around?

What if customers take 45 days to pay?

How much money do I need?

Look before You Leap with Financial Modeling.

Financial modeling is a tool that allows you to look at different business models “on paper” first.    Unless you have unlimited funds, you need to plan each move carefully.  Over time things get more complex.  There are many moving parts.  You can’t keep track of everything in your head.

You start with the facts. This is where you are today.  Cash, Inventory, Debt, recent sales.  A few years of monthly financial statements if you’ve been around that long.

The financial model is a structure to accumulate this data and make sense of it.  It also allows you to peek into the future

This is what SurvivalWare is all about.   It is a tool to help you Look before You Leap.

The SurvivalWare Experience

You first look at historical performance to inform your decisions about what is possible going forward.

You look at trends graphically, efficiently, with push button ease.

The provided financial models show you everything you need to make assumptions about in order to do a complete financial projection (Income Statement, Balance Sheet, and Cash Flow).

You can base your assumption on history or on judgement.  Or “back of the napkin” sketches.  Days of market research.  Coin flip.  Whatever.

You look at what happens to the cash as the end result of everything else.

The secret about financial modeling is that the income statement and the balance sheet have to be in balance.  Once you’ve got estimates for everything else, your cash balance is the plug.  It is the end result of everything that goes on in the business.  The model calculates cash for future months based on your assumptions about sales, inventory, receivables, expenses, credit cards, etc. etc.

Look before You Leap – try it out for 30 days!

SurvivalWare Simple is a great way to start, and is yours free for 30 days.

If you decide to take this step – do it right!  Call or email to arrange a free consultation.  Let us help you get your data loaded, and give you some pointers on how to peek into the future financially.

custsupport@survivalware.com

703-780-2044

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.

Proj-Exp-Excel

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.

File-Import-Proj

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

Follow

Get every new post delivered to your Inbox.

Join 70 other followers