US Economic Overview Dashboard

This Excel dashboard shows current US Economic data from online sources. And it's based on a dynamic Excel dashboard template.

by Charley Kyd, MBA Microsoft Excel MVP

This dashboard illustrates several significant reasons that Excel makes such a great dashboard environment.

First, you can display your Excel dashboards easily. You can...

• Print them to paper.
• Print them to PDF, as I have done with this dashboard, which you can download here.
• Display them online, as you can see here.

(I gained respect for online delivery of Excel reports in the 1990s. The Chief Technology Officer of a very large media company used my first Excel add-in product to create and save roughly 5,000 Excel standard reports as gif files each month. Users viewed the files on their Intranet. The solution was popular and very inexpensive. It cost hundreds of dollars to implement, rather than millions.)

Second, Excel gives you the ability to use data from virtually any source. Here, for example, I'm using data I found online and retrieved from the Federal Reserve Bank of St. Louis, using my KydWeb add-in.

Third, good Excel dashboards are a continual work in progress. And that's the case here.

So if a professional economist or statistician writes to explain why one of my charts is based on incorrect data or assumptions, I can change it quickly, and republish. Or, if I find a more interesting way to look at data, I can update the less-interesting charts quickly.

Plan to take the same approach with your own Exce dashboards.

After all, these dashboards don't use complicated software. They just use Excel.

Fourth, you can include as much sophisticated analysis as you want in your Excel dashboards. Here, for example, I started with a template from IncSight DB. Next, I added a worksheet and formulas to transform and summarize the FRB data into multiple series that I could display in the chart.

(I designed this new worksheet to be dynamic, by the way. That is, after the FRB adds new data online, the chart will display it automatically when I open and recalculate the dashboard workbook.)

Because I needed the top section of the dashboard for the big chart, I deleted the table and charts in the top 40% of the dashboard. Then I added the Camera object to the first figure, an object that returns the chart's enhanced legend from another worksheet.

I also deleted several of the smaller charts in the original template, and stretched the others to twice their original width. And I changed two line plots to column plots using standard Excel methods.

This dashboard took all weekend to prepare--a lot longer than I expected. The first three figures, which use the same data, took most of that time.

Because I rushed, I'm not ready to share this workbook. But I plan to clean it up document it as quickly as I can.

The Contents of the Economic Overview

Here's an explanation of each figure:

Figure 1--Job Gains (Losses) from the Start of Each Post-WWII Recession, Until Job Recovery

From a jobs perspective, a recession cycle follows this four-stage process:

1. Recession starts
2. Jobs decline
3. Recession ends
4. Jobs recover to their level at the start of the recession.

Figure 1 tracks this four-stage process for each of the 11 recessions the US has had since 1948.  

We’re now in the fourth stage of the 2007 recession. When the recession began in December, 2007, we had 146,273,000 jobs in the US. This month (June, 2012), the number was 142,287,000. So we must recover another 3,986,000 jobs before Stage 4 can end.

In those ten other recessions, the four stages took between 13 and 27 months to complete. But as I write this, the 2007 recession began 54 months ago. In other words, this full 2007 recession cycle has taken twice as long as the previous high so far, and we still need to add another 4 million jobs to complete the four-stage cycle.

This figure is particularly interesting from an Excel perspective. To organize the data for this chart, I used INDEX-MATCH, SUMPRODUCT, OFFSET, and array formulas. Honestly, I had a lot of fun with those formulas.

Watch for a future training program that discusses this chart and its data in detail.

Figure 2--Average Monthly Population Gains (Line) and Job Gains (Area)

News reports about the economy occasionally compare our deteriorating jobs picture to the number of jobs needed to satisfy our growing population. This chart provides a similar perspective.

The line shows the growth rate calculated by the 60-month trend in monthly population for each month shown. And the area plot provides the equivalent information about job creation. As the figure shows, our economy started to lose jobs in 2008, and the current five-year trend is negative.

From an Excel perspective, this chart relies heavily on the data used for the first chart.

Figure 3--Lost Job-Months During Recession

In this figure, I wanted to summarize the cluttered results shown in Figure 1. Each line of that figure represents a certain number of jobs lost each month. One way to summarize the total effect of each of those recessions is to find the total of all job-months lost from the beginning of Stage 1 of the recession through the end of Stage 4.

Lost job-months are important numbers, from several perspectives. From the perspective of the people who lost those jobs, it represents their months of lost income. From the perspective of the businesses who sell products and services to those people, it indicates the degree of lost buying power within their customer base. And from the perspective of the government, it represents lost tax revenue and the cost of increased public assistance.

As you can see, the number of lost job-months since the beginning of the 2007 recession makes all other lost job-month results insignificant by comparison. In fact, all recessions from 1948 through 2001 lost a grand total of 120 million job-months; but since the 2007 recession began, the US has lost 276 million job-months...more than twice the total for the prior 50 years. And we're still counting.

From a financial perspective, the most-recent data available from the Bureau of Labor Statistics puts the average hourly wage of all US workers at $22.77 per hour, which comes to $3946 per month. Therefore, out-of-work US employees have lost a total of (276 million x $3,946 =) nearly $1.1 trillion dollars in lost wages since the recession began. And we're currently losing (4 million jobs x $3,946 =) nearly $16 billion in unpaid wages every month.

Figure 4--Unemployment Rate

This chart shows the trend in the traditional unemployment rate. As you can see from Figures 1-3, this widely followed rate doesn't offer a complete story about the US employment data.

Figure 5--Crowding Out: Ratio of Government Debt to Business + Consumer Debt

This figure uses a term I heard a lot during the 1980 and 1981 recessions: crowding out. The fear then was that government was borrowing so much money from banks, driving the interest rate so high, that few businesses and consumers could afford to borrow. These days, we have a different reason for limited business and consumer credit, but the result is much the same.

This chart tests the crowding-out effect by comparing total business and consumer loans with total US government securities at all banks.

As you can see, banks have increased the proportion of US government loans significantly since 2008. This ignores any increase in the cash that state and local governments have borrowed.

Figure 6--12-Mo Change: GDP Implicit Price Deflator

The GDP Implicit Price Deflator is a common measure of inflation in the US. The deflator is an index value that was set to 100 in 2005. For any month, the difference between the current value of the index and the value 12 months ago represents the average inflation percentage over that time.

This figure shows the results of those monthly calculations. As you can see, this display shows that we're hovering around 2% inflation on average.

Figure 7--Median Price of Houses Sold

This figure shows that half the houses in the US have sold for more than about $230,000 during the past two years, and half have sold for less than that number.

Figure 8--Real Personal Consumption per Capita

Real consumer spending per capita has risen by a tiny amount over the past two years. A significant increase in this number in the future could indicate that the economy finally is improving. But a decrease would bring news of another kind.

Figure 9--Investor Confidence Ratio: AAA to CCC Bond Rates

Companies rated CCC pay much higher interest rates on their bonds than companies rated AAA. Investors demand higher interest rates from CCC companies because the investors realize that the CCC companies are more likely to fail, particularly in a bad economy.

Therefore, the ratio of the average AAA interest rates to the average CCC interest rates is a measure of the degree of optimism that knowledgeable investors show about our economy's future. As you can see, investor confidence has fallen significantly since the beginning of the recession in 2007.

Figures 10-12--Real Trade Weighted U.S. Dollar Index

These three figures show the dollar compared with a variety of currencies. Since 2005, the US Dollar has been slowly climbing against all three currency averages.

Keep in mind, however, that these are relative measures. That is, if all major currencies happen to be deteriorating, the US Dollar will rise if it's deteriorating more slowly than the others.