Annualized Projection Formula in Excel

Follow formula can be used in Excel when conduct the annual projection to avoid possible lack of sufficient data which may cause various format errors, such as divided by 0.

Assume annualized project is based on year to date information, in comparison to last year’s year to date number together with the last year’s summation, it can achieve the seasonal adjustment by using simple strait-line method:

A1-A12: Monthly numbers of last year
B1-B12: Monthly numbers of current year
C1: The current month number within a year, for instance, June is 6 in 12 months.
A13: Last year’s Year-to-date =SUM(A1:INDIRECT(ADDRESS(ROW(A1)+C1-1,COLUMN(A1))))
A14: Last year’s summation = SUM(A1:A12)
B13: Current year’s Year-to-date.
B14: Current year’s seasonal adjusted annualized projection = ISNUMBER(ROUNDUP(B13/A13*A14,-2),0).

The formula is: =MAX(ISNUMBER(ROUNDUP(A13/C1*12,-2),0),B14)

Can also use ROUND() or ROUNDDOWN() function.

Basically, it is to determine if seasonal adjustment should be used based on the validity of the data. The formula can guarantee a valid projection number will be generated.