For business users of Microsoft Excel Free guides and templates

Analytical Charting

What Inflation? How Excel Charts Can Help You Avoid 'Anecdotal Economics'

On Valentine's Day, 2018, tens of thousands of major sites warned their readers that inflation is rising. But that might not be true. These analytical Excel charts suggest a different conclusion.

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

The business news on Valentine's Day, 2018, was dominated by talk of rising inflation. A search of Google News showed 67,100 results for the phrase "rising inflation." Highly ranked search results include headlines like these:

  • Rising inflation rekindles a big market fear from the 1970s (CNBC)
  • Don't be scared by weak retail sales and rising inflation (MarketWatch)
  • TREASURIES-Yields advance after rising US inflation (Reuters)
  • Fresh Dose of Inflation Fails to Cure Dollar's Deep-Rooted Ills (Bloomberg)
  • Etc.

But is it true? What's the evidence that inflation actually is rising? Well, let's take a look.

But before we do so, I need to give you two warnings, and a suggestion. First, I'm not an economist. I'm merely an old MBA who's been interested in the economic environment since my first job as a CFO years ago.

Second, I threw these charts together in about two hours on Valentine's night. I've double-checked my data and calculations, but blunders do happen. So don't bet your farm on the strength of the following analyses.

My suggestion is that even if you don't care about measures of US inflation, you might find the following discussion interesting. This is because so much that's written about Excel charting tells you which buttons to push. But here, I'll explain ways of looking at data that you might find useful...for reasons that could have nothing to do with the economy.

With that said, let's look at the data...

Figure 1 shows the Rate of Change (ROC) in the Consumer Price Index (CPI) over the past 18 years. Each point on the chart is calculated by dividing the sum of the most-recent three months of CPI results by the equivalent values one year earlier, and then subtracting 1.

(Using rolling three-month sums removes many of the jaggies that we would get if we divided each month's value by the same month one year ago, and then subtracted 1.)

As the chart shows, there's been no sharp increase in annual inflation during the past year. In fact, according to this chart, inflation is about half a point lower than it was a year ago.

The CPI in this Excel Chart Shows No Recent Increase in Year-Over-Year Inflation

You can view the underlying data for Consumer Price Index for All Urban Consumers: All Items for yourself. The web page is on the Federal Reserve Economic Database (FRED) site, maintained by the FRB, St. Louis. You can download the free data by clicking the page's Download button.

When you look at the page at the FRED site, notice that the last part of the URL is CPIAUCNS. This is the FRED ID for the underlying data for Figure 1. If you look at the Sources near the bottom of Figure 1, you'll see that ID. (The second ID is for the light blue indicator of recessions and slowdowns.)

Therefore, to see the underlying data for any of the following charts, just replace CPIAUCNS in the URL with another Source ID for data you want to download.

Moving on, economists also pay close attention to Consumer Price Index for All Urban Consumers: All Items Less Food and Energy. Here's the ROC chart for this series:

This Excel chart of the CPI, Less Food and Energy, Shows a Minor Increase in Inflation

In this chart we do see a slight increase in inflation over the past few months, but inflation—as indicated by this measure—is about half a point below what it was one year ago.

So far, therefore, I see no indication of "rising inflation," as thousands of publications warned us of on Valentine's Day.

Notice that Figure 1 and 2 use data that's not seasonally adjusted. Many economists rely on a sophisticated statistical process called ARIMA to deseasonalize economic data. But I didn't use FRED's deasonalized version of the data for two reasons.

First, because the ROC method automatically deseasonalizes the data, there's no need to use it with deseasonalized data. And second, where possible, I prefer to use the original data, rather than data that's been massaged by a statistical process that could distort clean data.

However, because many economists do rely on deseasonalized data, let's see what it could tell us.

I found each month-to-month percentage change in the seasonally adjusted CPI—which is the method that many news reports about our "inflation" discussed on Valentine's . I subtracted the average of these percentages from each of them, and then divided by the standard deviation of those percentages.

You can see the result of these calculations in Figure 3, which shows the number of standard deviations represented by each monthly percentage change in the CPI.

As you can see in this chart, the only significant change—of more than six standard deviations—occurred during the Great Recession. But in recent months, the variations have been insignificant.

Excel chart of the Changes in the CPI Show that they're Within Their Recent Normal Range

But when we take the same approach with the CPI, less food and energy, we see a huge surprise:

An Excel chart of Changes in the CPI, Less Food and Energy, which might show a Concern

Notice that spike in January, 2018. As the caption explains, at 2.3 standard deviations, that's the largest one-month spike in this measure since March of 2005. Was there a significant change in January's inflation, which only this measure could reveal?

Luckily, we can do a reasonability test. Figures 1 and 2 rely on one way to measure inflation...the ROC, a year-over-year measure of seasonal data. Figures 3 and 4 rely on a different way to measure inflation...a month-over-month measure of deseasonalized data.

And in Figure 4, we charted the number of standard deviations represented by each measure of monthly inflation of the deseasonalized data.

So, finally, let's do something similar with the Rate of Change data. That is, let's normalize the ROC calculations so we can see how the growth rates change each month in terms of standard deviations:

This View of CPI, Less Food and Energy, Is Remarkably Unremarkable

Compare the results for January, 2018, in Figures 4 and 5. When compared to Figure 4, Figure 5 seems remarkably unremarkable. That is, the chart shows no indication of the massive spike for January, 2018, that Figure 4 displayed.

However, Figure 4 is enough of a concern that I'll keep my eye on the data for the next few months. Luckily, I created these charts using my Excel-Friendly Database strategy. I could create them quickly because I started with an Excel template that included a chart and had much of my "plumbing" in place. It also allows me to update the workbook in about five seconds, without using macros.

Could the 67,100 pages that Google News found about "rising inflation" all be wrong? These charts seem to indicate that they are. But if they're right, those publications have done a terrible job of proving their case.

In his book, Ahead of the Curve, published in 2005, Joseph H. Ellis urged businesses to overcome "anecdotal economics." By "anecdotal," he meant responding ad hoc to the deluge of data released by the government, trade groups, newspapers, television, and so on.

"Month by month," he wrote, "businesspeople absorb these reports with varying levels of credulity. Although we usually receive the disclosed economic data at face value, we lack a framework or context provided by the government bureaus that release the data and the journalists who report it."

"To assess the latest economic developments in their proper context, we must pull current data into charts large enough to encompass several decades of data in a highly readable fashion. There is no excuse for not doing so."

It requires only an ordinary personal computer, he went on to say, Excel, and access to free web sites where the data is available.

Perhaps this journey through the data and its sources will encourage you to follow Ellis's advice.


3 Simple Tricks to Improve Analytical Charting in Excel

Introducing the Power of Year-Over-Year Performance Charts in Excel

The Excel-Friendly Database Strategy

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.