A common advanced analytic requirement is “what if” scenario modeling. How can we enable our analysts to alter certain parameters within known contraints to test potential impact? Well, let me walk through a few ways this can be done. Quite honestly, many analysts use the default, out-of-the-box Excel features for “what if” scenario modeling and never realize all the options available to them.
Excel actually has five kinds of what-if analysis tools within Excel or Excel Add-Ins: 1) scenarios, 2) data tables, and 3) Goal Seek. There is also 4) Solver, an add-in to Excel that allows for more variables. Lastly the most advanced what-if analysis option is within the 5) SQL Server Data Mining Add-Ins for Excel. The SQL Server Data Mining Add-Ins for Excel forecasting/time series what-if option can cross predict patterns with many variables, scales to extreme size data sets and uses true predictive algorithms.
For basic level what-if analysis, Excel Scenarios and data tables take sets of input values and determines possible results. These options are limited to a few variables and can only have up to 32 values. Goal Seek is different from scenarios since it works backwards…it will take the end result and determines potental values to get that result. Note Goal Seek is limited to one variable.
Solver is similar to Goal Seek except it allows for more variables and enables building forecasts by using the fill handle or series commands that are built into Excel. It is limited to linear trend or growth trend calculations. For more advanced forecast complex and nonlinear data models, you can use the Analysis Pack add-in or the Data Mining Add-Ins for Excel. Solver works with a range of cells that are related to a formula in the target cell. Solver adjusts values in variable cells a.k.a. “adjustable” cells. Solver does allow constraints to restrict the variable values.
Lastly the most advanced what-if analysis option, Cross Prediction, is available via the Data Mining Add-Ins for Excel. The Data Mining Add-Ins for Excel has forecasting/time series algorithms that can be used for complex, non-linear and patterns with seasonality or periodicity. The time series anlysis cross-predicts how patterns in one time series impact other forecasts or series in the same model. This option allows you to specify future values for a series and then continue the forecasts from that point. Time series algorithms use Fast Fourier transformation to detect seasonality before training. This most advanced option is also the most scalable for large data sets that exceed Excel limits since this Add-In can reference data stored in a data warehouse or other database server. Also the Data Mining Add-Ins for Excel do not suffer from the variable limitations of other Excel-based what-if options and offers true predictive modeling capabilities thanks to the power of the Analysis Services Data Mining engine.
Cross Prediction is a query-time feature added in 2008 that’s independent of OLAP writeback. This feature allows you replace or extend the training-time time slice data as part of the prediction (forecast) query. Thus you can apply the patterns detected at training time to a new series (or to an extension of the training series). For example, if you suspect diesel prices follow the same trends as gasoline prices and gasoline data is more readily available, you can build a forecast model with gas prices but then use that model to predict diesel prices. Here is a video on time series forecasting with this Excel add-in. Here is a tutorial that covers Cross Prediction with more details: http://msdn.microsoft.com/en-us/library/cc879290.aspx. There is also cool sample application that can be used to demo this concept visually. The demo allows you to drag a forecast curve and see related series changes: http://www.sqlserverdatamining.com/ssdm/Default.aspx?tabid=97&Id=382.