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.

 

Advertisements

Setting up SurvivalWare to Run on a Network – Part 1: Sharing Data

Many customers have asked how to setup SurvivalWare to run on a network so that a common data file can be shared.  This is desirable when you have a controller updating monthly data for an owner or CFO, and both want access to the data.   Keeping the data on the network is nice for a single user as well.   That way, the data is backed up regularly whenever the network is backed up.

This blog article shows you how to point SurvivalWare to access data in a shared folder.   We will publish Part 2:  Sharing custom logic and reports in a later article.

Here’s how to share SurvivalWare data on a network:

1. Set up a common data folder on the network (you may need to get a network administrator to do this for you)

2. Copy the files from the data folder on your local PC to the network folder.

NOTE: If you are using the Simple model, the data folder is called c:\survware\simple\data\.  If you are running the FortKnox model, it is called  c:\survware\FortKnox\data\.

Then from each PC running SurvivalWare:

3. In User Settings, set the default data path to this new network folder

4. Open your MTX file on the network by selecting File / Open Existing Company

NOTE:   If two people open a SurvivalWare file at the same time, the person who saves last “wins” – i.e. any changes made by that person prior to saving will be the changes saved in that file.

Example:

The contents of the local data folder have already been copied to the S: drive on the network.

In SurvivalWare,  select Company Setup from the Main Menu:

Company Setup

 

 

 

 

Then select User Settings:
blog-sharedata-3

 

 

 

Set the Default Data Path to the shared folder on the S: drive:

Default Data Path

 

 

 

 

 

 

Click “Save” and you are done.

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

Excel Sucks at Financial Modeling

I don’t see how you all put up with it.  I’d pull my hair out.  I’m talking about using Excel (or any spreadsheet tool) for financial modeling.  To build and maintain financial models, that is.  It’s fine if someone else built it for you , and all you have to do is put in a few numbers in well-marked locations, and view the results.  And as long as you don’t have to trace back a formula so see how something was calculated.  As long as there is no need to make sure the same calculation is used for each column in the model. Yikes, have you seen those formulas?!  If anything is referenced outside your viewing area you are screwed.

Now don’t get me wrong.  I love Excel.  I use it every day.  I import and store lists.  I sort things.  I maintain small databases.  I keep a log of how many miles I ride my bike each day.  I have another spreadsheet I use to keep track of my daily calorie consumption, exercise, and weight.  It helped me lose 40 pounds, and keep it off still 5 years later.  Like I said, I LOVE EXCEL!  Just not as a financial modeling tool.

I’ve been spoiled.  I’ve used an English like financial modeling language of one sort or another since 1977.  (I’ve created two of them myself).  My tool of choice nowadays is ENCORE! for the financial modeling language, and SurvivalWare for the infrastructure – i.e. getting data in, analyzing, reporting, graphing, exporting data to the dashboard tool of your choice for mobile and web access.  Fortunately, you get these as a bundle when you buy SurvivalWare Pro.  But I’d advise you to wait until the release of SurvivalWare Pro 2014.  It is available in Beta test form now, but the formal release is scheduled for July 15, 2014 to accommodate a major refresh of documentation and training videos.  There is a lot of new stuff including a Model Generator to make you incredibly efficient at developing models, without sacrificing the ability to fine tune calculations and report formats.  Transparency is another big thing. Click on the new formula bar to see the English-like logic for any row in the grid.  I could go on and on.

But back to my rant: Excel sucks as a financial modeling tool.  There is just no other way to say it.  I’m sitting here having to update the Simple Model Excel template file.  I made some changes to the simple model based on suggestions from Philip Campbell after he used the model in a real live client engagement.  The new “Disposable Spreadsheet” feature in SurvivalWare depends on a working Excel model that mimics the calculations of the Simple model.  So I’ve been updating the Excel spreadsheet this morning, trying to figure out the cell references, and looking for any excuse to do something else.  SurvivalWare Simple 2014 isn’t scheduled to be released until May 15th – so I have lots of leeway to procrastinate – over 2 weeks to update the formulas in a measly 150 row model.  

Back to the grind: let’s see, here’s my formula for Annualized Sales down at row 49: =(B5+C5+D5)*4.  Let me scroll up to the top to see what Row 5 is.  And what columns B,C, and D represent.  Now is this formula for October the same? =(I5+J5+K5)*4.   Is that an I or a 1?  So far so good.  And here is Cash Flow From Operations for October: =K111+K112+K113+K119+K126.   It doesn’t exactly roll off the tongue.  You get my drift.  This is the simple stuff.  Imagine a complicated model.

I picked a hell of a time to stop drinking!

 

 

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

The Power of Transparency and the Value of Information

On a plane trip back home from Omaha a couple of years ago, I read an article in the Wall Street Journal called “The Summer to Go on a Power Diet” discussing products and techniques for reducing power usage.  In the body of the article, they talk about the impact of  just making cost information available:

A study by the Envirnmental Change Institute at the University of Oxford showed a 5% to 15% reduction in power consumption just by providing energy information to users.

( Article link: http://online.wsj.com/article/SB10001424052748704009804575308791120750332.html)

Kevin Cushing, former CEO of AlphaGraphics,  talks about a similar benefit from sharing financial information with employees.
In a recent video testimonial, he explains:

And something I think that’s understated in SurvivalWare, and in sharing financial information as a whole, is how can you keep your employees informed about the financial health of your business?  A lot of small business owners think that their employees don’t know, or don’t care, or it’s way over their head.  Other ones want to hold it all in, because they feel like that’s private information for them and they shouldn’t be sharing that with their franchisees.  My perspective: the more your employees know about the health of your business, the more that they can do to improve the performance of the business.

 

It reminded me of my early days as an industrial engineering student when I learned about the Hawthorne Effect.  Look it up on Wikipedia if you want the details – but in a nutshell some industrial engineers were trying to improve productivity at this huge GE Plant in the early 1900’s, and kept meticulous production records to help them study the impact of certain things.  They turned up the lights – and sure enough productivity increased.  They turned the lights back down – and guess what – productivity increased again!  They concluded it was the attention from management, knowing what was being watched, that led to the improved performance.
My advice is to shine the spotlight on those metrics and measures that are most important to your business, and share the information with employees so they know how they can best improve the performance of the business.  And I guess now I’ll have to practice what I preach!

SurvivalWare is a tool that can help you do this.   You can check it out by downloading the free trial: http://www.survivalware.com/free_download.html

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