Quick Consultant: Pricing & Productivity Ratios Show Positive Signs

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

Surveys

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.