U.S. President John F. Kennedy once said of a growing economy, “A rising tide lifts all boats.”
However, a rising inflationary tide does not lift all boats! Nor does it lift them all at the same rate.
For example, in March 1980, the annual rate of inflation—as measured by the Consumer Price Index—was at 14.6%. And the interest rates for 30-year mortgages averaged 15.7%—compared to about 3% in the first half of 2021.
On the other hand, Gasoline was inflating at 69% per year. And the average price of farm products was deflating at an annual rate of 1.5%!
To make sense of it all, we must monitor three types of inflation.
First, we should track the general rate of inflation, because it can affect the health of our economy significantly.
To illustrate, in March 2021, CNBC posted an article titled, “The Fed can fight inflation, but it may come at the cost of future growth.”
“Hiking interest rates,” they wrote, “is the most common way the Fed controls inflation.
“It’s not the only weapon in the central bank’s arsenal, with adjustments to asset purchases and strong policy guidance also at its disposal, but it is the most potent.
“It’s also a very effective way of stopping a growing economy in its tracks.”
Or, as Ben Bernanke—the Federal Reserve chair from 2006 to 2014—once said in a speech, “Regarding the Great Depression, … we did it. We’re very sorry. … We won’t do it again.”
Second, we should track our personal rate of inflation—because that’s the rate that most affects our personal well-being.
In 1980, for example, if you had a long commute, your personal rate of inflation would have been much higher than the average rate of 14.6%!
Third, we should track the rates of inflation for the goods and services we buy. That will help us—as much as possible—to adjust our purchases to reduce our personal rates of inflation.
That’s why I created the Inflation Tracker workbook. With this workbook, we can track all three types: the general rate of inflation, our personal rate of inflation, and the inflation of the key products and services that are components of the other two rates.
Together, the two pages of the dashboard display 44 measures of inflation, measures that you easily can choose to track from a total of 119 available measures. And you can update all 119 measures monthly, with one command, without using macros.
The dashboard includes the most-common measures and indicators of inflation, including:
- Consumer Price Index (CPI),
- Core CPI, which is All Items Less Food and Energy,
- Sticky Price CPI,
- Personal Consumption Expenditures Price Index (PCE), which is the Fed’s favorite measure, and usually tends to produce the lowest rate,
- Trimmed Mean PCE,
- Producer Price Index (PPI),
- The 5-, 10-, and 30-Year Breakeven Inflation Rates, and,
- Median value of the University of Michigan’s Survey of Inflation Expectations.
The other measures provide trends in the prices of specific goods and services, including:
- S&P/Case-Shiller U.S. National Home Price Index
- Rent of Shelter
- Electricity
- Gasoline
- Telephone Services
- Meats, Poultry, Fish, & Eggs
- Hospital and Related Services
- Computers, Peripherals, & Smart Home Assistants
- Pet Food
- Candy & Chewing Gum
- Etc.
And also, this dashboard has five unusual features—particularly for an Excel dashboard:
1. Your Personal Inflation Rate
Both the Consumer Price Index and the Personal Consumption Expenditures Price Index are based on average market baskets of goods. Those market baskets are as accurate as the U.S. Bureau of Labor Statistics (BLS) can make them—on average.
But your own market basket probably looks nothing like the BLS market basket! And therefore, your personal rate of inflation could be significantly different than any general measure of inflation.
That’s why the Inflation Tracker workbook includes a table that allows you to define your own market basket of average monthly spending. And then, in Figure 1, the Inflation Tracker shows you the trend in your personal, weighted-average inflation rate.
From an Excel Perspective
In case you’re wondering what the gray and red shaded bars in the chart represent, I put the legend for each page of charts in the top-right corner of the page, as you can see here.
Those red and gray columns in the charts provide context for each measure. In the Figure 1 chart above, for example, we see how the example person’s inflation rate fell during the three-month recession the US experienced when the Covid virus first struck.
Do the periods of presidential transition add useful context? Possibly. They certainly seem to do so in that chart and the next one.
2. The Calculation Span
The inflation rate is defined as this year’s price divided by last year’s price, minus 1. That is, it’s defined as an annual rate of inflation.
However, as this slicer suggests, we can calculate an annual rate of inflation by comparing prices over many different time spans.
For example, as of July, 2021, the Consumer Price Index grew at a compound-average annual rate of…
- 2.0% over the prior seven years.
- 2.7% over the prior three years.
- 5.4% over the prior one year.
- 8.9% over the prior six months, as this chart illustrates.
Why is that information useful?
The Federal Reserve Chair Jerome Powell insisted in July 2021 that US inflation is “transitory.” By tracking recent inflationary trends, we’ll be able to see when inflation begins to fall—if it actually does fall any time soon.
He never did define what he meant by “transitory,” by the way.
And Powell aside, when the rate of inflation is changing rapidly, we can reveal recent inflationary spikes if we calculate inflation rates over the span of several months. (However, if we calculate inflation over too short a span, the rate becomes very volatile and tells us little.)
The most-recent data is a case in point. The Tracker gives us a strong indication that the rate of inflation had been increasing at an ever-increasing rate—at least in the first half of 2021.
From an Excel Perspective
Did you notice the underlying purpose of the slicer? It tells Excel which of 18 different calculation methods to use. That’s a type of Excel interactivity that I’ve started to add to many of my dashboards. I’ve found that charting trends calculated in different ways can reveal hidden patterns in my underlying data.
That’s something you might want to remember when you report and analyze your company’s data.
3. The Top-20 Measures
The first page of the dashboard includes a table with the 20 items that have the highest rate of inflation. The first table you see here shows the first five of those items over a span of one year.
The second table shows the same type of information. But its rates are based on an annual inflation rate calculated over a span of four months—as its title explains.
When you compare the rates in these two tables, you’ll see that our results can change significantly when we change the time span of our calculations!
From an Excel Perspective
First, when you view the table, you’ll see that it’s columns don’t line up with the columns in the report’s worksheet. See if you can figure out how I achieved that result.
Second, the table updates automatically when you change the time span with a slicer. You can use several different methods to sort your measures like this by using formulas. If you’re going to be productive in Excel, you never should set up a top-10, top-20, or top-whatever report that requires manual sorting.
Using manual sorting methods for your Excel reports and analyses acts like a massive traffic jam on your highway to Excel productivity!
4. Changing Measures to Report
Earlier, I said that the dashboard allows you to report 44 measures quickly from a selection of 119 of them. This figure illustrates how you’ll be able to do that.
This is the second dashboard I’ve created that uses this method. I like it because when we build dashboard reports, we’re not always sure which measures will be useful in the future.
And also, when I was preparing dashboard reports for managers, I often didn’t know which measures the managers would like to see in the dashboard. And that meant I often had to spend extra time adding more data to my workbook and then shoe-horning the results into my dashboard.
But now, by setting up a variety of datasets in our workbooks—consisting of more datasets than we can display in our reports—we make it quick and easy to tweak what data we do display in our dashboards, and where in the dashboard we display the data.
From an Excel Perspective
To change a measure in a figure, you just select the title cell for any figure, other than the personal inflation rate in Figure 1. Doing so reveals a control for an Excel validation list. When you click on the control, you see the list of all items from which you can choose to display in the figure.
To be clear, I created this method to be an agile way to set up or modify the dashboard. But I did not intend for it to serve as a user-friendly way to work interactively with your dashboard.
That’s what slicers are for.
5. Multiple Versions of Excel
Originally, I set up the Inflation Tracker using Excel 365. But because many Excel users don’t yet have 365, I wondered whether I could transform it to work with Excel 2010.
Excel 2010 is the earliest version I could use because that’s the earliest version that supports Power Query, SUMIFS, and tabular Pivot Tables—all of which I knew would be necessary to make the converted workbook easy to use. But the conversion was going to be a challenge, because the Excel 365 version of my dashboard uses the FILTER, SORT, LET, and XLOOKUP functions—and also functions introduced in Excel 2016 and 2019.
But I found ways to make it all work. So I now have two workbooks that produce the same results: InflationTracker2010.xlsx and InflationTracker365.xlsx.
How to Get Both Workbooks
The pair of workbooks include eight pages of instruction, including an introduction to the source of the inflation data.
- Both workbooks give you the power to set up your own market basket of goods and services, so you can track your unique rate of inflation.
- Both workbooks use cutting-edge methods with best practices, which allow you to update your data monthly, with only one command.
- Both workbooks support the two pages of dashboard reports shown below.
- Both workbooks give you the easy ability to track 44 different measures of inflation from a selection of 119 measures.
- Both workbooks are unprotected, so you can see exactly how I created them.
You can get both Inflation Tracker workbooks, along with eight pages of instruction, for only $87.
Get these workbooks now and start tracking the measures of inflation that are important to you!
Get these workbooks now to learn how it’s possible to be significantly more productive in Excel!
