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.

Decimal Place in T-SQL

Sometime decimal place can drive you into mad in T-SQL because different machine can show different result.

In SELECT statement, a formula would generate a result without decimal place, because it had been automatically rounded to integer:

Value_Is_200/3 would generate 67

The easiest way to control decimal place is to place decimal point in one of the constants in your formula:

Value_Is_200/3.0 would generate 66.7
Value_Is_200/3.00 would generate 66.67

If it does not work, you can try:

ROUND(Value_Is_200/3.0, 1) would generate 66.7
ROUND(Value_Is_200/3.0, 2) would generate 66.67

It may not work sometimes, because ROUND() is not for the purpose of decimal control. If it does not work, or if you need to truncate rather than round, try:

ROUND(Value_Is_200/3.0, 2, 1) would generate 66.6
ROUND(Value_Is_200/3.0, 3, 2) would generate 66.66

Sometimes, it still does not work. Then try:

CONVERT(DECIMAL(12,1), Value_Is_200/3.0) would generate 66.7
CONVERT(DECIMAL(12,2), Value_Is_200/3.0) would generate 66.67
or
CONVERT(DECIMAL(12,1), ROUND(Value_Is_200/3.0, 2, 1)) would generate 66.6
CONVERT(DECIMAL(12,2), ROUND(Value_Is_200/3.0, 3, 2)) would generate 66.66