Monday, January 13, 2014

Load Forecasting in MS Excel: Limitations and Tricks

You may not realize that MS Excel is THE most widely used tool for business forecasting. There are many reasons that spreadsheet is the dominant player in this field, such as its simplicity, low cost and portability, etc. There are also many limitations that MS Excel has that prevent it from being a suitable tool in the forecasting areas that require advanced techniques.

Believe it or not, I did use MS Excel in my first load forecasting project. It was on long term spatial load forecasting. I then wrote a thesis based on what I have done in the project, which included human-machine interface design, nonlinear optimization, hierarchical forecasting and reconciliation, and visualization of the forecasts. For example, the picture below is a snapshot of the forecasted load drawn on a spreadsheet. The tool has been commercialized since then and is now being used by dozens of utilities in North America.

Source: Tao Hong, "Spatial Load Forecasting Using Human Machine Co-construct Intelligence Framework". Master thesis, North Carolina State University, Oct 28th, 2008

Last semester, in my course "Energy Systems Planning", I taught my students how to use MS Excel to do load forecasting. I explained some limitations and tricks of MS Excel in the class. Then the students did a good job with their load forecasts.

I would love to share with my audience here about my experience with spreadsheets. In this one-hour webinar, I won't be able to cover how to do VBA programming as what I have done in my master's thesis. However, I think there is enough time to cover several key DOs and DONTs that may be helpful to the spreadsheet users.

I would strongly recommend you to watch this 1-minute forecasting demo in Excel provided by Microsoft. If you don't know why the demo makes no sense, you will for sure find my webinar useful.

If you are interested, please visit the webinar page to register this webinar.

1 comment:

  1. That looks like a traffic map of LA County.

    I used Excel to maintain a pooled cross section, time series data history, but uploaded the data into EViews for the analysis.


Note that you may link to your LinkedIn profile if you choose Name/URL option.