Home » Conditional Formatting » Seasonality

Depending on the industry you are in, the seasons will likely play some part in your bottom-line. Back at the credit union, we knew that our deposit flows were affected by the time of year, holidays, and the makeup of our membership, many of whom were teachers.

For my example spreadsheet, I used a set of data I knew was highly sensitive to the weather. I downloaded U.S. Housing Starts, not seasonally adjusted (NSA). The U.S. Department of Commerce: Census Bureau keeps data on housing starts from 1959 on a monthly bases. The units are in thousands. I used 1959 through 2013. I inserted the data into a table in Excel 2013. Columns A & B are downloaded data. Column C & D are the years and months extracted from column A using table formula nomenclature :

=YEAR([@[Mon-Yr]])  & =MONTH([@[Mon-Yr]])

I used conditional formatting to differentiate years with a red line.

seasonal1

The next step was to calculate a linear regression for each year. I used the Forecast function making sure that each forecast used only the months within each year. That way I ended up with 55 separate regressions, one for each year.

That was accomplished using this array formula in column E (Regression):

{=FORECAST([@Months],[Units],IF([@Years]=[Years],[Months]))}

Column F (Seasonal) was simply the units divided by the results of forecast:

=[@Units]/[@Regression]

The Seasonal column uses conditional formatting to show colors for the higher and lower months, within each year:

 

 Now I averaged the 55 years by month:

=AVERAGEIF(Table1[Months],J5,Table1[Seasonal])

seasonal3

Now I chart the results with three charts. A radar chart, a line chart, and a heat map (not actually a chart). It is no surprise that not many housing starts are in the winter months:

Download workbook “Seasonal” from:

http://www.pistulka.com/Excel_Shared/ 

Downloads Written in Excel 2013

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*