Backfilling monthly volume forecast based on annual forecast

[Steve]

Hi, I have an interesting one here

A1:AD1 cells are used as lables, first 12 columns for each month, Column M is first 6 months of 2004, Column N is last 6 months of 2003 and Column O is full-year 2004, followed by identical labels for 2004 through Column AD.

A2:U2 consists of my volume data through June 2004.
AB2 is a sum formula for first 6 months of 2004.
AC2 is an input forecast for the last 6 months of 2004.
AD2 = AB2+AC2

I have come up with the following formula in Cells V2:AA2 that works in most cases to backfill montlly data for 2004 based on the mix of volume for 2003. Lets look at Y2 (October) for example:

=IF($AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1))<0,0,$AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1)))

In most cases the total of P2:AA2 gets me to 100% of my input forecast in cell AC2.

However, this formula fails if new data is much greater than historical last year's volume. For example, if in updating July 2004 Cell V2 with a large number, the total of my cells P2:AA2 is more than 100% of my input forecast in cell AC2.

I am kind of stuck here. Any advice would be appreciated.

Thanks.









=IF($AD2*((I2/$O2)+(SUM($A2:H2)/$O2-SUM($P2:W2)/$AD2)/COUNT(I1:$L1))<0,0.00000000001,$AD2*((I2/$O2)+(SUM($A2:H2)/$O2-SUM($P2:W2)/$AD2)/COUNT(I1:$L1)))

 

[Steve]

I changed cell Y2 to

=IF(SUM($P2:X2)/$AD2-SUM($A2:I2)/$O2>0.1,IF($AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1)*2)<0,0,$AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1)*2)),IF($AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1))<0,0,$AD2*((J2/$O2)+(SUM($A2:I2)/$O2-SUM($P2:X2)/$AD2)/COUNT(J1:$L1))))

and it works. Basically, it says if 2004 cumulative volume mix is more than 10% of cumulative volume in 2003, then use a divisor twice as big as what was used in the previous cells. Its obviously a little clunky. I know one of you has got to have a more statistically inclined solution... Thanks!

 

[Steve]

I ran it on my actual data and it smooths the data out well in all instances if I use *10 after the count function rather than *2. I know there must be a better way though.