Saving Excel files as CSV – beware of number formats

We recently ran into two separate cases of CSV files that appeared to have different numbers from the XLS files they were created from.  Customers were importing the CSV files into SurvivalWare, and couldn’t figure out why the Import Checks failed.  The first clue that something was amiss was the fact that some numbers just didn’t add up in the Balance Sheet.  Upon closer inspection, we noticed that there were no negative numbers at all in the CSV file we were importing.  We got the customer to send the original XLS file, which had been produced by an accounting package called Profit 21.

In the XLS version of the Balance Sheet, there were a few numbers in Red, and when we clicked on them, the minus signs appeared in the formula bar.  But sure enough, when we did a Save As to CSV file, the minus signs disappeared.

Here’s what the data looked like in Excel:

Clicking on Format / Cells in Excel revealed the number format used.  It turns out that the second format for negative numbers causes the Save As to CSV to convert them to positive numbers.  The solution is to change the number format for negative numbers to any of the other three, or to import the data directly from the XLS file.

SurvivalWare Pro 4.1 – Consolidation Wizard Documentation Available

The official release date is 4/4/2011, and we are trying to make information about version 4.1 available in advance as things come together.

Documentation for the new Consolidation Wizard feature in SurvivalWare Pro Version 4.1 is ready for download.  Click here for the full 80 page PDF file.

Here is a peek at the Table of Contents:

Data loading applet for financial statements from Infor ERP Software

We offer to map and load a customer’s data as part of our QuickStart service. Recently a customer sent us a set of files that were from the Infor ERP software, and were definitely not the same format as Quickbooks.

There were a series of Excel files (.xlsx format), one per month each for the Income Statement and the Balance Sheet. The excel files had prior year values, so there was the opportunity to load two months from each of the 11 months of 2010 that were available.

There was a minor hitch: the Cost of Goods Sold accounts (3 of them) appeared as negative numbers. SurvivalWare expects them to be positive numbers.

I decided to write a “Pre-Processor” applet to take care of 3 things all at once:

1. To automate the loading of a range of months, instead of just one month at a time
2. To change the format to QuickBooks style for ease of mapping and loading
3. To reverse the sign of the Cost of Goods line items so that they load into SurvivalWare properly

This is what the applet looks like when you run it.  The first time you run it, just process a single month (which creates a P&L and Balance Sheet CSV out of the XLXS files), and set Load CSV to “N”.  Then go to the load data module and map the rows just like you would with a set of QuickBooks generated CSV files.  After that, you can re-run the pre-processor and load several months at once.

You can download the complete source code for this applet at www.survivalware.com/applets/Infor-data-pre-processor.zip.

Time to do a Rollover

If you are on a calendar year, the time to “Rollover” your SurvivalWare files is fast approaching. This is something you should do before loading January data.

The Rollover does three things:

1. It makes an archive copy of the data file
2. It copies the “Next Year Budget” data into the “Current Year Budget” columns, and clears out the Next Year Budget for you to work on later in the year.
3. It shifts historical and projected data over by 12 months, dropping off the oldest 12 months.

Here’s how to do the rollover:
1. On the Main page of Survivalware Select Rusty’s Toolbox
2. Select Data / Roll over this file
3. Set the month to January and the year to 2011. Leave the Budget Options set to the default value “Move Next Year Budget into Current Year Budget”. Click OK.
4. Click OK to the next two windows.

Your file has been “Rolled Over”, and an archive copy saved under the name “-2010Archive.MTX”

Analyzing Franchisee Financials – Getting Management Buy-In

You have to start with a management with enlightened self-interest:  i.e. the belief that the success of the franchisor ultimately depends on the success of the franchisees.  If nothing else you can point out that failing franchisees will default on their royalty payments, while growing, successful franchisees will provide long term revenue streams that increase over time.  Plus franchises are easier to sell if you can demonstrate that they actual make money, and provide a return on the franchisee’s investment.

Also, if you do make an earnings claim in item 19 of the UFOC (Uniform Franchise Offering Circular), it is nice to have some solid backup to the claim.

If you want to make your franchisees more profitable and successful, then it is essential that you measure their profitability and success.  To do a reallygood job, you have to develop a deep understanding of not just sales and margins, but also inventory levels, collection cycles, and cash flow.  And let’s not forget the non-financial measures and Key Performance Indicators (KPI’s) that are unique to your business.  These could be things like Sales per Labor hour in a fast food franchise, Sales per Square Foot for a retailer, or Gross Margin Cents per Gallon for a convenience store operator.

It is the total financial picture of each franchisee plus the KPI’s that allow you monitor how they are doing, and separate the high performers from the laggards.  With this information you gain the ability to “smoke out” best practices from the best performers, and the ability to identify problems ahead of time with the others.

To do a really good job of this requires a lot of work and costs money.  So you really have to look at the benefits to the franchisor to make sure the effort is worthwile.  What is the benefit of preventing a franchisee from going under?  What is the lifetime value of selling a new franchise?  What is the value of avoiding a lawsuit by having good data to support your earnings claim?  The benefits quickly add up.

Benchmark studies are a help, and can be a first step - but they usually are out of date by the time they are published, and deal with annual results instead of months or weeks.  To keep your finger on the pulse of the business, you need to put in place a system to collect complete, accurate financial data and key operational measures on a monthly basis.  Even better is to feed back to the franchisees how they rank on each key performance measure, so they can spend their time on areas needing the most attention.  And to put this information in the hands of the area developers or regional managers whose job it is to help the franchisees succeed.

Often the impetus for putting an analysis system in place comes from top management.  It is critical to communicate the benefits to the entire management team to provide a foundation for a successful implementation.  Getting this management buy-in is the first step.  Once everyone agrees on the benefits, you can look at the best way to put a franchise analytics system in place.

Analyzing Franchisee Financials – an Overview

One of the most successful applications of SurvivalWare software has been in the franchising community.  SurvivalWare is a tool that franchisors can use to bring together franchisee financial data in a common format, and gain a deeper understanding of the financial health of their franchise network.  The franchisees benefit by being able to see how they rank and compare on the Financial Ratios and Key Performance Indicators most important to their profitability and growth.

This article is the first of a multi-part series that describes how such a system can be implemented for a franchise system, and provides examples of the data feeds, menus, reports, and dashboards that make up a “Franchise Analytics” system.

This overview lays out the steps and major system components based on our experience with a number of franchisors.

Here are the major steps:

1. Secure management buy-in on the need to improve franchisee profitability and cash flow

2. Define the common format

3. Generate the SurvivalWare model

4. Pilot test the model with a subset of franchisees

5.  Make the Go / No Go decision

Assuming a “Go” decision:

6. Make adjustments to the financial model and reporting formats

7. Conduct training for field and corporate staff

8. Develop the process for franchisees to submit their data either in form of CSV/XLS files or SurvivalWare zip files amd receive back comparative data and ranking reports

9. Publicize the results of the pilot test and invite other franchisees to participate

10. Conduct training webinars on a regulatr basis for franchisees and field staff

11. Periodically enhance the model, reports, and dashboards based on user feedback

In subsequent articles, these steps will be examined in detail to give you a better picture of the work involved, who does what, and what the final system looks like.

Something about tax cuts or earnings or money..

Thanks to my brother Hank for bringing this to my attention.  This is from the Onion, and is guaranteed to make you laugh out loud. He recommends reading it more than once – it gets funnier with wach read. 

http://www.theonion.com/articles/something-about-tax-cuts-or-earnings-or-money-or-s,18169/

Startup Capital for the Rest of Us

I ran across this site, http://www.antiventurecapital.com/, while exploring small business and entrepreneur groups on Linked In.   Peter Ireland is obviously an experienced entrepreneur who has learned some valuable lessons he is willing to share (and of course have you pay for if you want his full guide).  His main point is that the best way to raise capital when you are starting out is to demonstrate that you can generate cash flow.  Otherwise, you are beating your head against the wall.  He estimates you have less than 1 in 500 chance of raising venture capital, so you may as well put the effort into making your first sale and building the business, as opposed to polishing your business plan.  He also warns of the thousands of “Money Middle-men” poised to take your money.  His advice: put very little faith in these services and never pay up-front fees.

I love his graphic to illustrate his point:

http://www.antiventurecapital.com/

Have you had experience (positive or negative) in trying to raise startup capital?  I’d love to hear some real life stories.

SurvivalWare 3.1 – Shopping Cart back by Popular Demand

Shopping Cart

The shopping cart is simply a tool to collect in one place a number of variables for viewing or reporting.  When you are in the Dataviewer, Projections Module, Budget Module, or Rusty’s Toolbox, the variables are organized into tabs for viewing.  There is one tab each for the three primary financial statements: Income Statement, Balance Sheet, Cash Flow (circled below).  There are additional tab for things like Breakeven Analysis, Working Capital, and Valuation.

Since the Fort Knox model is designed to be used by a variety of businesses, chances are that you use some, but not all of the variables provided.  When you’re doing analysis or projections, it can be useful to have the 10 or 20 most important variables in your business visible on one screen.  These may be different for you when doing historical analysis vs. projections.  They may also be different for you over time.  Problems may crop up that require special monitoring, and a view of a different set of performance indicators.

We provide the shopping cart as a means to collect variables from the far reaches of the model and put them in one place.  You can quickly graph them in succession using the Next and Back button with the trend charts or Comparanator.  In the Reports module there is a selection to print out the variables in the current shopping cart with any of the column sets.

We allow you to create multiple shopping carts, and edit their contents

At any time you can Left Mouse Click the Survival Cart icon and it will bring up the rows in the current shopping cart.  Notice the “Back” icon has been activated.  Click on this to leave the shopping cart view and go back to the previous view.

Since the Fort Knox model is designed to be used by a variety of businesses, chances are that you use some, but not all of the variables provided.  When you’re doing analysis or projections, it can be useful to have the 10 or 20 most important variables in your business visible on one screen.  These may be different for you when doing historical analysis vs. projections.  They may also be different for you over time.  Problems may crop up that require special monitoring, and a view of a different set of performance indicators.

We provide the shopping cart as a means to collect variables from the far reaches of the model and put them in one place.  You can quickly graph them in succession using the Next and Back button with the trend charts or Comparanator.  In the Reports module there is a selection to print out the variables in the current shopping cart with any of the column sets.

We allow you to create multiple shopping carts, and edit their contents

Adding Rows to the Shopping Cart

This is a two step process, and we will be the first to admit it is a little clumsy.  First select the row you want to add by clicking on it or one of its cells.  Then Right Mouse click the Survival Cart Icon.  If you Left Mouse Click by mistake, the tabs will disappear and the rows contained in the current shopping cart will appear.  If this happens, you can click on the “Back” icon to restore the previous screen.

Viewing Rows in the Shopping Cart

When you move the mouse over the SurvivalWare Cart icon, the message bar tells you how many rows are in the shopping cart.

At any time you can Left Mouse Click the Survival Cart icon and it will bring up the rows in the current shopping cart.  Notice the “Back” icon has been activated.  Click on this to leave the shopping cart view and go back to the previous view.

Edit Shopping Cart

The Edit Shopping Cart feature is under the File Menu in the DataViewer, Projections Module, Budget Module, and Rusty’s Toolbox.

Here’s what it looks like.  You can delete a row, move a row up or down in the list, or empty the cart altogether.

You can also save the shopping cart under a new name, or read in a previously saved shopping cart.  Shopping carts can be stored in any folder, but preferably in the Model folder.  The file names use the extension, “.SHP”.

Reports with the Shopping Cart

Click on the Reports Icon from the Main menu to access the Reports module.

Then:

1. select the Shopping Cart report
2. Select a column set
3. Click on the Preview Report icon

SurvivalWare 3.1 – New Comparanator Options

The comparanator  has been much improved in version 3.1.  There are now a total of ten “Styles”:

• Basic Line Chart

• Dual Scale Line Chart

• Actual vs. 12 Month Average

• Actual vs. Budget vs. Last Year

• Next Year Budget

• Peer Group Members

• Scenarios

• Peer Group vs. Benchmark

• Actual vs. Comparison Group (25/50/75)

• Actual vs. Comparison Group (5/25/50/75/95)

 (right click)

To select a comparanator style, and make it the default going forward, simply Right Click on the Comparanator Icon or select “Set default Comparanator style…” from the File Menu  in the DataViewer or Projections Module.

Basic Line Chart

Graphs the focus row (and Next and Previous rows as requested) against a single comparison row.    You can browse through a list of variables in the Fort Knox model to pick a comparison row.  Once selected, it is “sticky” – i.e. it remains the default comparison row until you explicitly change it. 

The Basic Line Chart uses a single scale for the Y-Axis, which means it is useful only when comparing rows of similar magnitudes, e.g. two different expense lines. 

Here is “Total Overhead” vs. “Sales”:

Dual Scale Line Chart

 Useful for comparing variables of different magnitudes. 

Graphs the focus row (and Next and Previous rows as requested) against a single comparison row, using separate y-axis scales for each row   You can browse through a list of variables in the Fort Knox model to pick the comparison row.  Once selected, it is “sticky” – i.e. it remains the default comparison row until you explicitly change it. 

Here is “Delivery Costs” vs. “Sales” in a Dual Scale Line Chart.  The scale for “Sales” goes from zero to 250,000.  For “Delivery Costs” it goes from 0 to 15,000.

Actual vs. 12 Month Rolling Average

Graphs the focus row (and Next and Previous rows as requested) against the twelve month moving average for that row.  Note that you are not given the option to change time periods like you are with the other comparanator styles.

Actual vs. Budget vs. Last Year

Graphs the focus row (and Next and Previous rows as requested) for the twelve months of the current year.  For the same 12 months, the Budget numbers are graphed, as well as, last year’s numbers.  Note that you are not given the option to change time periods like you are with the other comparanator styles.

Note: Current Year Budget values can be entered in the Budget Module. 

Next Year Budget

Graphs the focus row (and Next and Previous rows as requested) for the twelve months of the current year.  For the same 12 months, the Budget numbers are graphed, as well as, the numbers for Next Year’s Budget.  Note that you are not given the option to change time periods like you are with the other comparanator styles.

Note: the Next  Year Budget values can be entered in the Budget Module. 

Peer Group Members

These can be separate locations if you have multiple outlets, or clients of yours who have agreed to share data files.  This style graphs the focus row (and Next and Previous rows as requested) with one line representing each group member.  It is useful for small groups. 

You can create a CSF file in the DataViewer, Projections Module, or Rusty’s ToolBox.   Select “File ‘ then “Create CSF File”.

Scenarios

This does the same really as Peer Group Members, but the group members are different scenarios for the same company as opposed to different companies.   You create the scenarios by typing in different assumptions or using the Forecast Tool to access different forecast techniques.  Then save each result under a new name (File / Save Company As…). 

You would normally select this style from the Projections Module as opposed to the DataViewer.

Peer Group vs. Benchmark

Here’s “Total Overhead” for the month of March 2010 for the group.  US201 was selected to be the benchmark. 

You can build your own  benchmark file and enter published industry stats by setting up a new company.  Check the box “Use this file for Benchmark data only.”  This allows you to enter values for calculated measures such as “Inventory Turns” or “Days of Cash.”

Actual vs. Comparison Group

In SurvivalWare a Comparison Group has special meaning:  it is a collection of company or locations which have been assembled in a central area (e.g. by a corporate analyst for a franchise company), and analyzed through the Company Stats module in Rusty’s Toolbox.  The analysis produces a set of “meta data” files than can be re-distributed to the participants without compromising anyone’s privacy. 

These two comparanator styles let you graph your performance against the percentiles of the group – either 3 at a time (25^th percentile, Median, 75^th percentile) or 5 at a time by including the 5^th and 95^th percentiles as well.

We’ve included a sample comparison group so you can see how this works. It is called “fake-meta-data” and the files are all contained in the folder ..\Survware\FortKnox\ComparisonGroups.