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, using a add-in called PowerPivot, which is now a built-in component of Excel 2013. 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).
An Excel 2010 workbook, which contains much of the data I present in this book, is available here.
An Excel 2013 version of the workbook is available here.
An Excel Add-in that I use for misc. utilities (e.g., de-pivoting data, generating a date table) is available here.
The underlying data used in the Workbooks is available as an OData feed, which can be accessed at http://www.understandingtheusdebt.com/odata/WcfDataSvcUSDebtv2.svc. Note this data is maintained as new updates become available.
A Note about data quality
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.