Forecast Monthly Spreads with Partial Year Results
Below are a series of formulas to help forecast the remainder of a year given partial annual results and total annual forecast number. In this first example, two years of history are provided (2006 and 2007). The numbers for January through June are known. A 4% annual growth is projected for 2007 to 2008. The known data is in black font and the predicted data is in red.
The known data is in black font and the predicted data is in red. The formula in cell C9 is:
=SUM(C9:D9)/SUM($C$9:$D$14)*($E$15-SUM($E$3:$E$8))
It is copied through the range C9:C14. To predict 2009, the formula in cell F3 is:
=AVERAGE(C3:E3)/AVERAGE($C$15:$E$15)*$F$15
It is copied through the range F3:F14.
The same kind of logic can be used to predict monthly averages based on an annual average. As with the first example, two years of history are provided (2006 and 2007). The numbers for January through June are known. A 3% annual growth is projected for 2007 to 2008. The known data is in black font and the predicted data is in red.
The known data is in black font and the predicted data is in red. The formula in cell C9 is:
=SUM(C9:D9)/SUM($C$9:$D$14)*($E$15*12-SUM($E$3:$E$8))
It is copied through the range C9:C14. As with the first example, to predict 2009 the formula in cell F3 is:
=AVERAGE(C3:E3)/AVERAGE($C$15:$E$15)*$F$15
