I also calculate the percent difference between the average and median for each question and if the difference exceeds 25%, that entry is highlighted in a specific color, warning me that I need to check the entire column of data more carefully before moving forward.
The Miracle of Advanced Filtering
One of the very best functions offered by Excel is also one of the least used or understood, and that is Advanced Filtering. This function is not to be confused with simple “filtering.” I have a collection of books explaining Excel and very few explain how the Advanced Filtering function actually works.
Advanced Filtering allows me to take a relatively large block of data consisting of more than 100,000 cells (Example: 350 rows by 300 columns), establish a criteria row beneath this data, then filter all the data that matches my criteria and place that data lower down on the spreadsheet. It is this filtered data—not the raw data—that I use to distinguish one sorted group from the next.
For example, I can ask Excel to filter my database and pull out only “franchisees” with sales equal to or greater than $1 million in annual sales. Once the data has been sorted, it is easier to determine whether or not there are significant differences in pricing practices between large independents and large franchisees.
Interestingly enough, while Excel has offered Advanced Filtering for many years, up until 2007 it limited to 256 the maximum number of columns available on any single worksheet. Since 2007 that limitation has been lifted, and it didn’t come too soon, since my surveys often result in spreadsheets with 300 or more columns.
Preliminary Ratios and Prices
While I want to avoid giving away the store and negatively impacting the sale of this study when it is released during the first week of May, I can still provide readers with some interesting data as well as some “teaser” prices.
The average participant, based upon early returns, is 58 years old, runs or owns a business founded in 1985, and had average sales in 2010 at $1,225,000. The median 2010 sales were $850,000, indicating that within our initial database we have a significant number of very large firms, some with sales in the $4 million range. No surprises there.
We also asked survey participants to estimate their 2011 sales, and we then calculated the estimated sales growth (if any) for 2011. Our early data indicates that most firms project an average growth rate of 4.8%. The median reported growth rate was significantly higher at 8%. Both numbers seem to indicate that printers are somewhat positive in their outlook for the near future.
What about plant size? Well the average number of square feet for the typical facility was 6,228 square feet, while the median size was 5,300.
SPE and Other Key Ratios
Recognizing that the data I am reporting is preliminary in nature, I can tell you that the average SPE is $129,920 and the median SPE is $125,448.
What about sales per desk top publishing (SDTP) employee? The average in this department is $73,307, while the median for this same ratio is $57,215.
We also track sales per press operator (SPPO), by dividing total retail value of “printing sales” and dividing that by the number FT/PT press operators used to produce those sales. Based upon our early data, the average SPPO is $256,506 and the median SPPO is $217,500.
If your company’s ratios fall close to the numbers above, you probably don’t have any major problems. On the other hand, if your numbers for one or more of these ratios are lower than these averages by 5% or more, it may be a cause for alarm.
Product Pricing As Well
What about some data on the pricing of some standard products and services? The chart on page 35 shows some of our early findings along with a comparison to similar data reported in 2009.
Just a word of caution: The pricing data in this chart is preliminary in nature. Nonetheless, I suspect that these sample prices will fall within +/-7% of what we will report in the 2011-2012 Quick Printing Industry Pricing Study when it is released early in May 2011.