Recent Articles

Feb 9Dave Lemon

Using Excel Pivot Tables to Analyze Sales and Customers

Feb 9Dave Lemon

I know it sounds odd to say “sales-oriented CFO”, but I know we CFOs have no beans to count until something is sold. I’ve used terms like “sales monkey” occasionally over a beer with my buddy who’s a salesperson, but I grew up in a house with 10 kids (I’m 9th), and there wasn’t food on the table unless my dad’s agents sold something.

I ask myself with any potential client, how can I help them sell more? One tool I want to share today is using excel pivot tables to analyze sales and customers. This is not a “how to” (see link below to a great tutorial). My goal is to share how you can use pivot tables to better analyze your sales and customers, and then you can decide how to use it in your business.

First, here are a few ways I’ve used pivot tables:

  • Sales forecasting, including combining invoiced sales with pipeline info from salesforce.com.
  • Sales analysis by:
    1.  Industry sales/gross margin mix by manufacturer and customer;
    2.  Domestic v. international sales;
    3.  Customer concentration, which I use with every valuation model I do for an owner selling a business or helping a buyer with due diligence;
    4.  Seasonality;
    5.  Financial budgeting & forecasting;
    6.  Inventory analysis: product mix and slow moving identification.

 

Here are three client examples:

  • Client #1: We use it to analyze customers,vendors, sales quotas, industry, US v. international. I also worked with their accounting manager to set up process to update the information by just right clicking and hitting “refresh”;
  • Client #2: We’re using it to do sales analysis by manufacturer, and we’ve saved time by eliminating multiple tracking databases;
  • Client #3: I analyzed 3 years worth of invoices (17,000) in 2 hours. I understood their business much better after trending by customer, product and salesperson.

 

Benefits and a Tutorial

Now that I’ve explained to you how you might be able to use it, I want to share a great tutorial to show you how easy it is to:

I found the tutorial on YouTube, and I viewed the 20-minute video while also watching a Yankees game. I’ve read a number of very smart data analytic people say pivot tables are the most powerful feature in excel (versions 2007 and above).

This knowledge is useless if you don’t take action, so:

  1. Watch the tutorial;
  2. Think of a test project and try it and more ideas will flow once you see how easy it is, and;
  3. If you have questions, feel free to call me at (402) 679-9680.

photo credit: ExcelPVT via photopin (license)

B2B CFO®

Free Discovery AnalysisTM

Fill out the form to receive your
Free Discovery AnalysisTM (a $1600 value)