You know you’re getting old and becoming a poor insurance risk when you get a nice letter from your insurance company thanking you for your business, and letting you know, “Your current premium is guaranteed to remain the same for the first 10 years of your policy—through 6/17/2011.” However, “…if you choose to renew your policy in year 11 the premium will increase to $56,655!”You would think one of the actuarial accountants had suddenly burst into the accounting office waving a sheaf of papers with my name at the top and yelling to everyone within earshot that we’ve got to get rid of this client ASAP. I can imagine him saying, “So far, we’ve dodged the bullet with this policy holder, but it’s only a matter of time, maybe just weeks, before he kicks the bucket or kills himself in that stupid plane. We should never have offered him a policy.”
I would love to look at the specific spreadsheet used to develop my actuarial table. Does it note that my father died at age 43 of a massive heart attack? Does it mention that my mother died young as well from lung cancer? What about me? What does it say? “Client is mentally unstable, and easily driven to temper tantrums. He continues to lie on his renewal applications by indicating ‘brown hair’ when every photo we have shows him with a solid head of gray.”
Exactly how did they come up with precisely $56,655, I keep wondering? Why not round it to $57,000? Does it go to $95,000 at age 68? What about age 70? What would the premium be at that age? Of course, the worst date to spot on the table would be when the premium for my $1 million dollar policy reaches $1 million. That would be the date and time when their computers forecast I will die. Come to think of it, maybe I don’t want to see that printout.
Spreadsheets and Pricing
Talking about printouts, I am in the very early stages of analyzing the data submitted for the 2011-2012 Quick Printing Industry Study. Unfortunately, I am writing this April column in late February in order to meet publication deadlines. The deadline for the study has long passed, but as I write this column we still have a bit more than three weeks to go before reaching the March 7 deadline.
I must admit that I love working on the biennial pricing study. The spreadsheet I am currently working on contains more than 300 columns, most of which represent the answers for specific questions, while the remaining columns have been inserted in order to perform some secondary calculation like sales per employee, sales per DTP employee, sales per press operator, plus other columns used to measure increases or decreases in pricing for various products.
Half the fun of working on these types of studies is the challenge they present in being able to take a myriad of data that includes many outliers and inadvertent errors and converting that information into an accurate and useful document on industry pricing. It ain’t easy, but it is fun.
I used a common sense formula this time to help me spot surveys in which participants completed only small portions of the survey in order to qualify for a free copy of the study. Using the “CountA” function, I totaled the number of cells in each row that contained either text or numbers and compared that against the number of cells left blank. If a company failed to provide answers for 40% or more of the questions we asked, they didn’t qualify for a free study.
Another function I use is “conditional formatting.” This function allows me to apply a specific background color to a cell or highlight the entry itself, based upon one or more conditions I establish. As an example, I used this formatting tool to highlight any price within a column that was less than or greater than a percent of the calculated median for that question. By highlighting those questionable cells, I can then examine the entries more closely and determine whether that entry needs to be deleted.
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.
The final study will feature average, median and “high-low” prices for more than 100 different products, services, and quantities, including pricing for digital color printing, digital black-and-white printing, 1-C and 2-C offset printing. Also covered in this study are basic bindery services for both standard offset products and digital color printing, offset press rates for eight different presses, and markup practices for both paper and outside (brokered) services.
Pricing Practices 1993 – 2011
We have been tracking pricing practices for dozens of common services and products since the early 1990’s. Interestingly enough, pricing practices and increases on those products vary, based upon the specific product or service.
As an example, prices have increased at a rate of 5.9% every two years for NCR, but owners have been conservative or cautious when it comes to DTP charges. In the prepress department, the average price increase every two years has been only 3.4%.
If you want to survive and prosper in this industry it is critical that you monitor the pricing practices of your peers. It is also important to look at the prices being charged for individual products and services, not just a select few. If your costs are slowly increasing, especially for labor and materials, you must address the issue of pricing, even at the risk of losing some customers.
If you wonder how you are supposed to find and quantify this type of information, ordering the new pricing study would be a great place to start.
Senior contributing columnist John Stewart is president of Q.P. Consulting Inc. Contact him at 2110 S. Dairy Road, West Melbourne, FL 32904, call 321-727-2444, email firstname.lastname@example.org. Be sure to check out John’s blog on his website at www.quickconsultant.com.