Share this content

Inflationary Spreadsheet Tactics

Mar 19th 2008
Share this content

By Chris Wood, CPA - The stock markets are plunging, subprime woes are drying up the credit market and manufacturing jobs are still leaving this country in droves. How do we keep on top of all these events during the day while we putter around on our computers hoping not to become part of these dreary statistics?

Well the information highway is chock full of all sorts of information and tables just waiting to be accessed and analyzed in Excel. It’s easy and here’s an example. First open a blank sheet in Excel. Select “From Web” in the “Get External Data” group under the “Data” tab.

This will open up a browser window in the internet. I’ve gone to the U.S. Department of labor statistics. There’s plenty to get depressed about here.

Unfortunately, all the data was imported into one row for every individual year. Use the “Test to Columns” feature in the “Data Tools” group under the “Data” tab to clean this up.

Much can be done in Excel using various reference functions to acquire data in the table above. I use a link to the web to import interest rates based on prime and the daily LIBOR to calculate my month end accrual. These tables don’t have to manipulated, so I can just do a refresh on the worksheet with a link to the internet to update the rates for the month.

Back to our example, you can see that 2007 was not a particularly good year. It had the highest increase (8.24) December to December since 1980.

Things aren’t that bad. The percentage increase in 1990 was much worse than 2007 (6.1% vs. 4.1%).

Hopefully, we can all look forward to a better 2008. So far we have an increase of 1.65 through February (211.69- 210.04). That is an annualized change of 9.9 (1.65 /2*12). Hmm, with consumer confidence declining, uncertainty in Iraq and oil prices surging well over $100 per barrel maybe 1990 wasn’t so bad.