For business users of Microsoft Excel Free guides and templates

Management Reporting with Excel

US Economic-Overview Excel Dashboard for Managers

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, 2005-2014
The Father of Spreadsheet Dashboard Reports

US Economic Overview DashboardThis dashboard illustrates several significant reasons that Excel makes such a great dashboard environment.

The first reason Excel makes a great dashboard environment is that you can display your Excel dashboards easily. You can...

(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.

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 Excel 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 Kyd War Room. 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. By March, 2013, the number was only 143,286,000. So we must recover another 2,987,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 64 months ago. In other words, this full 2007 recession cycle has taken more than twice as long as the previous high so far, and we still need to add another 3 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.

Figure 2--Atlas Rate

In Greek mythology, Atlas carried the world on his shoulders. In a country, the non-government (private) workforce serves the same purpose. Their taxes pay for government services, for the welfare of people who don't work, for interest on government debt, etc.

So the ratio of private workers to the rest of the population is a useful measure of the burden that our "Atlas" carries. As the rate declines, the burden grows heavier.

(The "Atlas Rate" is my term, by the way. I've not seen it used elsewhere.)

From a business perspective, a low or declining Atlas Rate tends to indicate tough economic times ahead, for two reasons. First, because fewer people have jobs, there's less money for consumers to spend in the economy.

Second, tax collections decline. This tends to cause politicians to raise tax rates, which leaves even less money for working people to invest and spend.

Figure 3--Labor Force Participation Rate

This is the ratio of the "workforce" to the people of working age. People are in the workforce if they either are working or are looking for work.

The traditional unemployment rate is calculated by dividing the number of people looking for work by the number of people in the workforce. Therefore, when the Labor Force Participation Rate falls--that is, when the labor force shrinks--the unemployment rate also falls.

To understand why this is so, consider a small town with a workforce of 10 people, three of whom can't find work. The unemployment rate is therefore 30%. Now suppose that one of them stops looking for work, and is therefore no longer "unemployed" as economists define the term. This change reduces the workforce to 9 people and the number of unemployed to 2 people. Therefore, the unemployment rate falls from 3/10, or 30%, to 2/9, or 22%. 

From a business perspective, a declining Labor Force Participation Rate also is an indicator of tough economic times ahead.

Figure 4--The U6 Unemployment Rate

If a PhD can't find work in her field and therefore takes a part-time job bussing tables, she would no longer be "unemployed" according to how the traditional unemployment rate is calculated.

But the U6 rate is calculated by adding "marginal and part-time" workers to the number of unemployed, so it provides a more-complete indication of the economic health of the workforce...and of your customers.

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 their proportion of US government loans significantly since 2008, leaving less available for business and consumers. This calculation ignores any increase in the cash that state and local governments have borrowed, which likely leaves even less cash available for the private economy.

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.

Again, you can see a full-screen image of the dashboard here.


 
Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.