How to Report Your Company’s Private Rate of Inflation in Excel

by Charley Kyd on October 20, 2011

These days, it’s important to keep a close watch on the rate of inflation. This is because there’s a strong likelihood that inflation will sky-rocket when the economy starts to improve.

This chart tracks a company's unique rate of inflation. Because Excel formulas are linked to data maintained by the Bureau of Labor Statistics, the chart updates automatically after the data changes and the workbook is opened.

But which inflation rate should you monitor? The one for consumer purchases? The one for producer purchases? Perhaps you should monitor both.

But also, you certainly should monitor your own company’s private rate of inflation.

That’s right. Because your company buys different stuff than most other companies, and in different proportions, it has a unique rate of inflation. And that rate could be significantly higher or lower than the average rates of inflation discussed in the news.

It therefore makes sense to use a chart like this to track the Private Rate of Inflation (PRI) for your company, or even for your division or department.

In fact, a version of this chart should be included in every manager’s monthly dashboard report, for at least three reasons:

First, the chart offers early warning. Without that warning, your managers could be surprised by large price increases that destroy your margins and make your budget obsolete. This is particularly true if your Purchasing Department negotiates long-term contracts that tend to hide much larger costs you’ll experience when those contracts expire.

Second, the chart suggests changes you should consider both for the prices you charge and for the products you purchase.

Third, you’ll do a much better job of budgeting and planning if you really understand how inflation is affecting your company’s costs.

Introducing the Private Rate of Inflation Schedule

This figure shows the PRI schedule that supports the chart. I’ve hidden many rows and columns in it so you can see all four corners.

This worksheet uses data from the Bureau of Labor Statistics to calculate your average rate of inflation each month. Each row of data comes from a different BLS web page. Click on the figure to download the workbook.

The schedule relies on cost data generated by the extensive surveys that the Bureau of Labor Statistics performs each month.

Here’s what the schedule contains:

• Column A: The Series IDs from the Bureau of Labor Statistics (BLS) for the major items your company buys.
• Column B: KydWeb formulas that return a description for each item.
• Column C: Your company’s annual budget for each item listed.
• Column D: The weighting factor, which is equal to the associated value in column C divided by the total of column C.
• Columns F-AP: The weighting factor multiplied by the KydWeb formula that returns the current inflation rate for each item.

Notice the negative values in row 2. The value in cell AP2 tells the KydWeb formulas in its column to return the most-recent value for the inflation rate for any line item. The value in cell AO2 specifies the value one month earlier, and so on.

The Key Formulas in the Schedule

The formulas in this schedule are simple:

F3:    =KydWeb_Actual(“BLS”,”WPU0571″,F$2,2)

Cell F3 uses a representative BLS Series ID to find the date specified by the backindex value in cell F2. (The final argument of this formula, which has a value of 2, specifies that a date should be returned. In contrast, using a value of 1 would cause the formula to return the Series ID’s actual value for the specified period.)

D5:    =C5/C$22

Cell D5 merely returns the percentage share of the market basket that the value in cell C5 represents.

F5:    =(KydWeb_SUM(“BLS”,$A5,F$2-2,F$2)/
KydWeb_SUM(“BLS”,$A5,F$2-14,F$2-12)-1)*$D5

In cell F5, the first KydWeb formula sums the price index values for the specified month and for the two preceding months, then divides that sum by the equivalent sum from the previous year. It subtracts 1 to find the average rate of inflation for that three-month period. Finally, it multiplies that rate by its share of the market basket in cell D5.

F22:   =SUM(F4:F21)

The sum of all the shares of inflation in the column produces the total average inflation for the month.

F23:    =TEXT(F$3,”mmm”)&CHAR(13)&TEXT(F3,”yyyy”)

This formula formats the date for display in the chart. It does so by inserting a carriage return character between the month and the year, which labels in Excel charts recognize.

How to Find the BLS Series IDs

Once the PIR report is set up, it updates automatically whenever you open the workbook. Initially, however, you will need to spend the time to match the items your company buys with the industries and products that the BLS surveys.

At www.bls.gov/data/, the first section is titled Inflation and Prices. This is divided into groups for Consumer, Producer, and International prices. Most of what your company buys probably will be found in the Producer group, but check the other groups as well.

The Producer group includes categories for both Industry and Commodity data. To get started with the Industry data, choose the  button for One-Screen Data Search in the Industry row. In the applet that follows, you select an industry from which you buy stuff, and the product category you buy from that industry. Then you choose Add to Your Selection to add that item to your cart. When you’re done, choose Get Data to load all the data for all the selected items into a browser.

To create the workbook above, I used that final web page as an easy source from which I could copy the Series IDs to paste into my worksheet. But each item has some additional information that you might find useful.

You’ll probably need to switch back and forth between your budget reports and the BLS information, entering Series IDs in column A and the equivalent budget amounts in column C. And honestly, you’ll probably need to make some estimates and compromises. But when you’re done, you’ll have a fairly good approximation of your company’s market basket in BLS terms. But even the worst estimates will produce a PIR schedule that’s far closer to your actual inflation rate than any other source you can find.

Possible Ways to Modify the Chart

This chart reflects more power than may be obvious. One line combines internal company data with data from 16 web pages offered by the BLS. It also displays data from two web pages offered by the FRB.

The formula for cell F5, discussed above, uses the KydWeb_SUM function to find a rolling three-month average inflation rate. Using the approach tends to smooth out random fluctuations in the rate.

To eliminate the smoothing, change KydWeb_SUM to KydWeb_Actual, which returns just one value.

Also, you can compare your Private Inflation Rate to both the Producer and Consumer rates from the FRB, as shown here. This can add perspective to your inflation report.

Download the PRI Workbook

You can download the PRI workbook here. If you don’t have KydWeb, you won’t be able to use it. But you might find it interesting in any case.

Tagged as: backindex, BLS, Bureau of Labor Statistics, Consumer Price Index, Federal Reserve Bank, FRB, inflation, inflation report, KydWeb, Private Inflation Rate, Producer Price Index

{ 2 comments }