Excel Dashboards >
US Economic Overview Dashboard
This Excel dashboard shows current US Economic
data from online sources. And it's based on a dynamic Excel
dashboard illustrates several significant reasons that Excel
makes such a great dashboard environment.
First, 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, using my
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
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
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
- Recession starts
- Jobs decline
- Recession ends
- Jobs recover to their level at the start of the
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
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
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
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
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
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
Therefore, the ratio of the average AAA interest rates to the
average CCC interest rates is a measure of the degree of
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
Again, you can
download the PDF file here, and
full-screen image of the dashboard here.