Excel for Excellence

Excel for Excellence

Here’s a really fun topic… Excel (heavy groan).  But let’s take a closer look. As much as we talk about collecting data and information about your business, Excel can be an incredibly powerful tool.  Analyzing business data due to its flexibility and various functions makes it the tool of choice. Although many Business Owners are using Excel to collect data, few harness the real potential of this powerful application.

I have long had a problem with creating management systems that front-line employees must spend time providing the data that takes them away from producing billable goods or services. I mention this because Business Owners often create simple spreadsheets that the front-line employees must input data so that the Owner can later analyze.  Over time, one of two things usually happens.  [1] The employees slack off on entering the data, or [2] the Owner rarely uses that data for analysis.  What a waste of everyone’s time!

When possible, it is always better to take exported data from an accounting system, POS systems, ERP systems, or inventory systems, bring it into your spreadsheet and then manipulate it to learn what you need to know.

I find it best to segregate my spreadsheets into 3 areas, often 3 different tabs.

Input:

Input can sometimes be manually entered, but if you do this be sure to make the inputs simple, minimal, and obvious as to where to put the data and lock out all the other cells so that something important doesn’t get overwritten.  Most companies I work with send me exported data from one of the previous mentioned systems. I create a paste-in tab to put this report in and on a different tab I parse or extract the data I need to perform the analysis.

Even better is to build in a SQL (Structured Query Language) into the spreadsheet that will always provide the user with live data without pasting or inputting anything.  But this is more advanced than manual or paste-in input data and not always possible.

Calculations:

Now that the input data is in the spreadsheet, we can begin to query it by date(s), types, amounts, etc.  This should usually be on a different tab than the input data so that future paste-ins do not corrupt these formulas.  Lookup formulas come in very handy here.

Pro Tip: “Index Match” is almost always a better and more bullet proof method than “Vlookup”, an Excel function.

Many spreadsheet gurus will advocate using Pivot Tables at this step.  While this is one valid way of looking at the data, I prefer other methods so that I can paste and immediately go to my analysis rather than have manipulate the data with a new Pivot Table each time.

When done properly, the user of the spreadsheet will never see these calculations as they will sit in the background and often hidden from the user.

Output:

Now that we have our data, and our calculations have it well organized for us, we can begin to build the “output” portion.  The Output is simply a dashboard of sorts that allows the user to learn important information about his/her company.  Usually with simple table data and charts, and often with conditional formatting.

Simplify and automate as much of the spreadsheet process as possible so that the person who inputs as well as the analyst does as little as possible to gain the results needed.

A spreadsheet is a word processor, a powerful calculator, and a graphics tool all in one.  It is designed to tell an important story about the data.  So, use it to make the data compelling.  Make sure it gets to the root of why you are measuring in the first place.  Make the inputs easy and obvious and make the outputs tell the story.

You can take this new analysis tool that you’ve created to the next level by building a “what if” section that allows you to change some simple variables to help determine best course of future action.  Example: Take this data and suppose that we could reduce our “cost per dollar” by 10%.  How would that affect our bottom line?  This is easily possible once you have the benchmark obtained with the present data set that you’ve already inputted.

I have also used Excel to help me take information from two or more systems that do not talk to each other.  Example: I get production numbers from an ERP (Enterprise Resource Planning) system and labor cost numbers from a payroll system and then I can calculate cost per widget.

Conclusion:

Since the early 1980s starting with Lotus 1-2-3, I have spent most of my life trying to perfect my spreadsheet skills.  Even though the technology makes it easier each year, I have only scratched the surface of harnessing this powerful tool.  Having said that, it remains my favorite software and it should be to every business owner what a hammer is to a carpenter… an essential tool to analyze their business with.

By following these steps, you can effectively use Excel to analyze your business data and gain valuable insights to drive decision-making and improve performance.  If you have trouble, reach out to me any time because I love, love, love to build spreadsheets.  The more complex the better!

882