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

Follow

Get every new post delivered to your Inbox.

Join 70 other followers