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.

Code Conventions: Microsoft Access SQL

1. Use original column name as much as possible until last presentative SELECT. This is to help for visual tracing back to where the data come from originally.

2. Never use format [something] as alias for the column, it would cause the visual confusion when you changed idea to reuse it, or extent to next level of expansion. You may use it at last presentative SELECT.

3. Always please comma (,) in front rather than at end of a line. It would enable you to easily conduct visual check, since all lines are started from a command or comma. For instance:

SELECT Col_1
, Col_2
FROM Tab
;

4. Always use your notepad or SQL editor to edit and save file. When complete, copy it onto Microsoft Access. This is because Microsoft Access will rewrite your scripts to make your life much harder. Every time when you need to edit it, open your text file to edit it and then paste it onto Access.

5. Doing your formatting job as much as possible in Access rather than at later stage at Excel or other presentation layer. It is even more important when you make your report automation.