TECHNOLOGY
The information presented in this book comes from public sources. Where possible, the data was pulled directly from the government agency (e.g., the Bureau of Public Debt, the Bureau of Labor Statistics, etc.) responsible for collecting and producing a given data set.
The charts and tables were created in Excel 2010. I used a new Excel add-in, called PowerPivot (www.powerpivot.com), to
- gather and store data from multiple sources
- create relationships among different data sets
- define custom calculations (e.g., last non-empty, yearly changes, etc.)
- analyze the data using Excel PivotTables and PivotCharts
An Excel workbook, which contains much of the data I present in this book, is available below. If you want to dive further into a particular subject area, you’ll need a copy of Office 2010 Professional (the PowerPivot add-in is free). If you want to get up to speed on PowerPivot, take a look at my August 2010 Article in SQL Server Magazine (see the author page for a link).
There is a good chance you’ll find a number in my book that differs from a number you find elsewhere. Those who work in a large company know the challenges of trying to get to “one version of the truth” when analyzing business metrics like profitability, retention, or market share. Every organization I’ve worked with stores data across multiple systems. The data is often duplicated. Certain reports require extraction of data from multiple source systems in order to first transform and then harmonize the data. Some metrics may differ depending on the requestor – consider sales revenue. A financial analyst may want billed revenue. A sales manager may want revenue credited to his region. A services manager may only want to see revenue from service operations within a certain geographical area, which may or may not align with the sales manager’s regional structure.
In this respect the U.S. government is similar to a large organization. Debt and economic data is stored across multiple agencies. Debt numbers are maintained by the U.S. Bureau of the Public Debt, an agency within the U.S. Treasury Department. Gross Domestic Product (GDP) is defined by the U.S. Department of Commerce Bureau of Economic Analysis (BEA). Government receipts and outlays are prepared by the Financial Management Service, part of the U.S. Treasury Department, though a comprehensive and download friendly historical summary is available from the Presidential Office of Management and Budget (OMB) or the U.S. Census Bureau. Data is sometimes changed or restated after it is initially reported. Some metrics have multiple definitions. For example, GDP is reported quarterly. The quarterly number is adjusted for the season and multiplied by 4 to produce a seasonally adjusted annual value . GDP is also reported annually. The annual value is an average of its 4 quarterly values. So if I want to calculate debt as a percent of GDP for a given year, do I use the GDP for the last quarter or the annual average? Am I calculating debt as a percentage of GDP for a calendar year or a fiscal year? Is the calculation for the beginning of the year, or the end of the year?
In writing this book, I compared my numbers with other sources to look for glaring errors and to make sure there was a general consensus in calculations. So, if I tell you a given ratio is 83.62% and you find someone else saying it is 85.83%, don’t be alarmed. Most of the numbers and trends in this book are so large that a small deviation makes little difference.
Update - May 01, 2012. I have updated the underlying tables for Outlays and Receipts to include a much richer set of details (e.g., you can now download to subfunction and account levels of spending). The workbook has been updated to reflect these changes.
Update - March 18, 2012. A version of the workbook based on the new PowerPivot 2012 add-in - download here.
A sample Power View report here (this is zip file, you'll need to extract the file(s) ).
Note - check back often as I while be periodically updating the workbook and posting new Power View reports.
Update - November 23, 2011. I have posted a new version of the workbook that is
1) Refreshable. The source data is now published as an OData feed, so the workbook can be refreshed on-demand to pull in recent data.
2) Optimized for Excel Services (some of the design elements in the original workbook would not display in Excel Services).
To download the new Excel workbook, click here.
To download the original Excel workbook - click here.
