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.