MS Excel - Year over Year Trend Analysis - Std Deviation? 1

[BxWill]

Don't know if the following can be performed.

Have an Excel worksheet with over 30,000 rows and 25 columns - the first column is the product code and the remaining 24 columns contain the cost per unit of the product over a 2 year period.

Example, I have a year over year trend of 30% for the month ending May 2009 on an agregate basis (for all products).

This would imply that when one compares the average cost per unit for the 12 months ending May 2009 relative to the average cost per unit for the 12 months ending May 2008, there is a 30% increase in the cost per unit!

I am trying to readily assess the month(s) during the 24 prior periods that have the greatest degree of variability year over year for each product.

Format of data is such:
Code---Yr1Month1--Yr1Month2...Yr1Month12...Yr2Month12
124----1.25-------1.24--------1.50---------1.75
125----2.50-------2.64--------2.75---------3.50

Currently, I am exploring the calculation of the variance and standard deviation for Year 1 relative to Year 2 but don't know if this will resolve the objective.

Any insight as to if the objective can be accomplished is greatly appreciated!

 

[SkipVought]

I am trying to readily assess the month(s) during the 24 prior periods that have the greatest degree of variability year over year for each product.

Exactly what does that mean?

Please do not assume that we know anything about your terms.

Degree of variability?

Year over year?

How would you do this by hand?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Maybe, "variability" is not the most appropriate term.

What I am trying to accomplish is determine which product(s) has a higher average cost per service in year 2 relative to the average cost per service in year 1.

Also, a clarification is necessary. There is an additional column that is to the immediate right of the "Code" column that is titled "Company" that contains the company that sells the product.

Format of data is such:

Code--Comp---Yr1Month1--Yr1Month2...Yr1Month12...Yr2Month12
124---ABC Co--1.25-------1.24--------1.50--------1.75
124---ABD Co--2.64-------2.75--------3.50--------3.70
125---BCC Co--3.00-------3.15--------3.85--------4.00

If the average cost per service for all codes and companies for the 12 months in Year 1 (ending May 2009) is 1.70 and 1.80 for the 12 months in Year 2 (ending May 2008). Then, the 12 over 12 trend for the month ending May 2009 is 6% (1.80 - 1.70)/1.70.

One thought is to add three additional columns - first column to contain the average cost per service for each company for each code for year 1; the second column that will contain the average cost per service for each company for each code for year 2 and the third column to contain the %-age increase (or decrease) in cost per service in year 2 relative to year 1 for each company for each code.

Maybe VBA could be used to loop through each of the 30,000 records and sort in descending order on the column that contains the %-age increase (or decrease) and display the top 25 or so products and companies that are "driving" the 12 month over 12 month trend.

Any thoughts as to if this is even feasible or another approach to consider?

 

[SkipVought]

Wouldn't it be simple to add 3 columns to caculate avg cost for yr1, yr2 & % change?

Then sort decreasing on % change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BxWill]

Yes,

That is the path that I am going down.

Did try pivot tables.

I can generate the pivot table to compute the sum or variance of May 2009 vs. May 2008 but cannot yet create a pivot table to compute a standard deviation and variance for each month for a 12 month period in Year 2 relative to the same 12 month period in Year 1.