Calculate Intersection Point of 2 Products Sales

**ClikClak** · 05-12-2010, 10:37 AM

Hello-

I have a project that I am sure where to begin. I have to calculate the point (month) that an old products decreasing orders will intersect with it's replacement products increasing orders. I don't have much data yet as the new product was only released in Sept 2009, but this is what I have so far. As you can see New products orders are consistently increasing, but the Old product is all over the place.

Old Product Sales
09/09 = 2975
10/09 = 3040
11/09 = 2550
12/09 = 2704
01/10 = 2757
02/10 = 2374
03/10 = 2740

New Product Sales
09/09 = 34
10/09 = 102
11/09 = 280
12/09 = 455
01/10 = 582
02/10 = 655
03/10 = 890

Thanks-

ClikClak

**Richard Buttrey** · 05-12-2010, 11:29 AM

Hi,

Probably about the best you can do is plot the trend lines for the 2 curves and then solve the trend line equations since where they cross will be the same value.

See the attached workbook. Solving the two equations indicates that the sales will be the same just after period 15, or sometime in November 2010

See attached.

HTH

**ClikClak** · 05-12-2010, 12:47 PM

Hi Richard-

How did you calculate the co-efficient and the constant?

**Richard Buttrey** · 05-12-2010, 01:22 PM

Hi,

You can either use formulae for the Slope & Intercept, e.g. for the old sales
Slope: =SLOPE(C2:C8,B2:B8)
Intercept: =INTERCEPT(C2:C8,B2:B8)

Alternatively you can right click the trend line, choose format trendline and in the options tab choose to display the line equation on the graph.

HTH

**ClikClak** · 05-12-2010, 03:15 PM

Hi Richard

In your example, why did you use the #'s 1 - 7 in the calculation, what do the represent? I am just trying to understand charting better and these statistical functions.. I was able to add the April data I just received and it changed the projection to the 14th month.

Thanks

CC

**Richard Buttrey** · 05-12-2010, 04:21 PM

Hi,

I used period numbers starting with 1 for September, 2 October etc. You could use the date numbers and then the calculation of the difference between the slope trend line equations would be a date number. This will be slightly different since the numbers of days in months are not equal whereas period numbers are equally spread.

HTH