Last time, we discussed how to document simple inflation adjustments. We are going to take that approach one-step further this time. Rather than using the simple calculator, this time we will begin by looking up inflation data from http://www.bls.gov/data.

excel post 1

Inflation is the first data set, but as you can see, there is many useful economic data maintained by the Bureau of Labor Statistics. Furthermore, there are multiple measures of inflation. All urban consumers consumer price index (CPI) is generally the one you want to use, but if you have specific needs to look at changes in a particular basket of goods or particular region of the country there may be a better dataset out there. This example of how to calculate inflation adjustments may not meet your needs depending on the specific circumstances of your work.

The BLS provides several different interfaces for accessing the data you need. My preference is the multi-screen interface highlighted below. Try each one to see what you like best.

excel post 2

Step 1 – Select whether you want seasonally adjusted data.

excel post 3

Step 2 – Select the region or firm size you want (note: seasonally adjusted data only has 1 option – U.S. City Average).

excel post 4

Step 3 – Choose the base year you want.

excel post 5

Step 4 – Select what type of prices you want data for. Sometimes you want to exclude certain items, like energy prices, due to their volatility. For example, below series SA0LE

excel post 6

Step 5 – Choose the frequency of your data.

excel post 7

Step 6 – Hit retrieve data.

Now we have our detailed table of data we can use in Excel. Click the download button and copy the information into the spreadsheet you are working with.

excel post 8

In the following example, we take annual CPI measures and convert them to biennial inflation adjustments. In Oregon, our state fiscal years run from July 1 to June 30. Our budget period is 2 fiscal years. For example, the 2015-17 biennium runs from 7/1/2015 to 6/30/2017.

To calculate a inflation adjustment, take your current year CPI (e.g. 237.952 for 2014). Then subtract the index value for the prior year (e.g. 198.7 for 2005). Divide the difference by the prior year index value and you get your adjustment. To get a biennial adjustment, we weighted the first and last year of the biennium 25% and the even middle year 50%. This is not the most precise way to do the adjustment, but it was close enough for our purposes.

excel post 9

Therefore, the calculation for 2005-07 is as follows: (.1975*25%) + (.1681*50%) + (.1389*25%) = 16.82%. This tells you that a dollar in 2005 is equivalent to $1.26 in 2014. Multiply the adjustment by the actuals dollars from that year and you can make apples-to-apples comparisons of spending over time.

Here are a couple real world examples of inflation adjustments from Oregon Audits Division Report No. 2015-09 (Oregon Department of Fish and Wildlife Financial Condition Review):

excel post 10

excel post 11