For now, let’s look at the data we do have. Let’s set it up to answer questions that will allow us to better understand what is happening in our business and what we can do better to improve and gain market share.
Start with asking your ERP system to create the following spreadsheet for up to the last five years. It will be a big file, but full of data to help you and your team better understand what is actually happening in business.
I suggest the following 10 headers for each line item invoiced:
- Invoice Date
- Customer
- Qty shipped
- Vendor
- Vendor Product Number
- Description
- Finish (if you can)
- Margin (if you can’t pull this information directly, you can create a simple formula with information below)
- Total line cost
- Total line sale
- Showroom salesperson
Once you have this data, make sure each cell in a column is formatted uniformly. If they are not, the data will be off when combined for a table or report.
Find your best and brightest spreadsheet expert and ask them to set up a pivot table and pivot chart with this data. If you cannot find such a person, ask your accountant, myself or YouTube. Click here for a simple overview of pivot tables to get you started.
From this data you will be able to ask the following questions:
- What am I actually selling by:
- Vendor
- Finish
- Product category
- Are my customers:
- Purchasing everything they need from me?
- At what margin?
- What product categories that I sell that they are not buying from me?
- What are my salespeople selling:
- By brand
- By function
- By finish
- What are they not selling?
- What are my margins by:
- Brand
- Customer
- Vendor
- Salesperson
- Product category
Once you have these answers, and any other questions you deem important to your business, you can focus on the opportunities you see that offer the largest upside and are the easiest to address.
After you set this up, you can run the data output and create your pivot table reports at the end of each month. You will now have actual, black and white data to allow you and your team see what is actually going on and plot a course to improve sales, margins and team performance. Not to mention getting the right products on display and building a more dynamic inventory.
Great write-up, Jeff! The tid-bits you share are wonderful! Keep up the solid work and value-added research and development for us :)
ReplyDelete~ John Gosselin