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.

Summation of Hour Numbers in T-SQL

This blog article is nothing but to record a standard script how to calculate the summation of hour numbers in report, for purpose not to think again in future.

Hour number need to be accumulated in minutes before present the final result. Else, it might be not accurate enough.

In inner query:

, SUM(DATEDIFF(MI, Departure, Arrival)) AS Mins

Note: DATEDIFF to work out every leg/trip's minutes. SUM() is to work out summation in this category togethere with GROUP BY.

In presentation outer query:

, SUM(CASE WHEN ... Mins ... END)/60 AS ...

Note: SUM() together with CASE statment is to format the presentation of the report.

In case wanting to keep decimal places, use:

, SUM(CASE WHEN ... Mins ... END)/60.0 AS ...
or
, SUM(CASE WHEN ... Mins ... END)/60.00 AS ...

For more about decimal place, please refer to http://koncordpartners.blogspot.com/2010/09/decimal-place-in-t-sql.html.