Pivot table and time series

My desired result is generating dashboards for various macroeconomic data (time series statistics). This is my first attempt to master pivot tables/charts so I have many questions:

As an example I will use consumer price index breakdown by main item groups (food, health, transportation etc.)

1. Making a database:

Looks like the usual way of how macroeconomists are used to see the data won't cut it. I want to make sure I got it right and if I really am forced to make database in following order:

Year	Month	Item_name	Index_value
2000	Jan	Food	100
2000	Feb	Food	100.3
2000	Jan	Transport	100
2000	Feb	Transport	99.8

Instead of having dates as rows and item groups as columns (or vice versa).
My concern here is the way I update the database with recent number - instead of just writing in new values in last row/column I will have to write in all information on new data (as data comes in I have to fill in year, month, category, subcategory, features, etc. and ofcourse, value itself)?

2. Pivot table itself

As we are looking on monthly indexes, most likely we would like to see such results as monthly growth rates (%dif of t and t-1), yearly growth rates (%dif of t and t-12) and yearly average growth rates (%dif of average(t ; t-12) and average(t-13 ; t-24). With possible modification of item weights and further grouped weighted growth rates to form new categories etc. Macrodata has plenty of information in it and numberous ways to play around.

So, this is where I got stuck. Pivot table doesn't give any obvious ways to calculate time series equations with leads/lags (and I'm not sure if Pivot table actually understands "time series" concept).

Here's what I got so far:

If I make table of Year/Month in Rows, Item_name in Columns and Index_value in Values, I get my very habitual form of seeing macrodata. I can make subtotals for averages to see yearly indexes right away, which is a good thing. But when I start to apply some calculations to it - things go terribly wrong.

a) If I try %difference from previous month, table will show me all the growth rates except january data for all years (where only first one should be blank). I partially solved the problem by introducing new variable "Date" in mm.yyyy form instead of separate "Month" and "Year". That ofcourse eliminated all yearly subtotals but monthly growth rates are fine.

b) And if I want to see growth rates of subtotals, which I switched back on (yearly averages index), I have to make %dif of previous year and collapse (or take out) monthly data. There is no way I could figure out of calculating years averages for months, only for subtotals.

c) Doing yearly differences give me a new headache - when presenting material I will surely like to play around with time periods, filtering out some years etc. It does calculate yearly growth rates however, only within filtered range. What I mean, if I filter out last 3 years - calculation will occur only for last 2, since apparently pivot table doesn't search for data beyond filtered values. And showing last year AND unnecesary blank previous year is quite annoying.

So the general question : is pivot table simply not fit for time series ? Or I have to make plenty of macros for each specific case for material to be any good ? Or, well, I just got the whole thing wrong ?

Adding a datafile.

I created a few macros to make yearly and monthly growth rates (and according reset buttons). I'm not really happy with neither of my solutions because for monthly I have to add special date variable and for yearly I have to observe first blank year on both table and graph.

So my main question is open - is pivot table a viable option for presenting time series?

And... quite inspiring dashboard:

http://epp.eurostat.ec.europa.eu/inflation_dashboard/

Is it even made in excel?