This discussion, however, concerns comparing EO and Excel’s scenario management capability as it relates to tactical and strategic business planning.
What is Scenario Management?
Somewhat loosely defined for this purpose, consider scenario management as the process of applying a prescriptive analytics technique (e.g., optimization) to determine “what-if” outcomes from a particular set of varying inputs. Normally, alternative solutions generated are then compared to some starting point — or base case — to which objective criteria can be measured, such as increased (or decreased) profitability or inventory turnover.
Enterprise Optimizer® Versus Excel’s Scenario Manager for Business Planning
The criteria that I’m using to evaluate each product is:
- How easy is it to define a scenario?
- How easy is it to automate the process of analyzing a set of scenarios?
Both criteria are equally important: effective and useful scenario management does not rely only on manual data input; and some amount of automation must be available to minimize time and effort.
But here’s the big question: Can Excel’s Scenario Manager feature match or exceed EO’s scenario management capability, or is it the other way around?
A Chemicals Co. Example Model
To illustrate the comparison, I’m using an EO model of a hypothetical chemical company. This model has for many years been installed with the product files, so everyone with a valid EO license has access to it. Here is the flow diagram, which includes a variety of raw material sources, three manufacturing plants, inventories, and four product plus one by-product sales markets.
The focus of the comparison will be relatively simple: to determine how what-if scenarios can be defined by varying product sales prices. Each set of input values constitutes a new scenario. All procurement, production, inventory, transportation and other data held constant.
To begin, I built a query in EO that I named SalesActivityMTP, which extracted all necessary sales data — e.g., market, product, price, min/max limits — as well as solution results — e.g., optimal solution, opportunity values, average sales/unit, average cost/unit and average margin/unit.
Then, by exporting query results from inside EO, a new Excel 2013 workbook is automatically created. I’ve highlighted the sales prices in yellow — this is the data that I intend to modify.
My Expectations for Excel's Scenario Manager
According to Excel’s Help, “a scenario is a set of values that Microsoft Office Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios on a worksheet and then switch between these scenarios to view the different results.”
From a prescriptive analytics standpoint, just defining and saving multiple groups of input data values doesn’t add much benefit. What would be the point?
Any measurable value from using Excel’s Scenario Manager must come from applying the different data groups to a specific problem (a model) in order to calculate various outcomes. This can be accomplished by using Excel’s built-in features (e.g., cell calculations) or via extensions like the Solver Add-in.
Although this might seem obvious, what appears simple can sometimes be challenging to meet expectations and requirements, especially given the typical demands that advanced business planning requires.
For example, most Internet tutorials on this feature show how, by varying a few input values, calculations in Result cells change. A common example is for a personal budget, where totals change when spending on rent, food, utilities, etc., changes. Is that prescriptive analytics? No, it’s not.
Can this approach be useful for Integrated Business Planning (IBP) optimization modeling? Let’s find out.
Creating a Scenario
With my new Excel workbook already open, I clicked the Data tab and then the What-If Analysis button.
Note, this menu also includes Goal Seek and Data Table, two other features that Microsoft has grouped together under What-If Analysis. Goal Seek is used to determine input value(s) when the solution is known beforehand. Data Table is used to determine solution values based on a one- or two-variable data table. Scenario Manager and Data Table are somewhat similar, so it helps to clearly understand each feature’s purpose.
After selecting Scenario Manager from the drop-down menu, a window popped up.
I clicked the Add button, which allowed me to define a scenario name (“Base case”) and select the cells to be included in my scenarios. For the base case, I choose all the cells containing product prices. I could also modify the “Comment” if I wanted, as well as two other options. Clicking OK closed the dialog.
So far, this process seems straightforward, but now it’s time to add a new scenario and vary some of the prices. For my scenario, I want to find out what happens if all Trade product prices were to increase by 10%. So, repeating the same process, I created a new scenario (“Trade product prices + 10%”), referenced only those cells related to the Trade market, and then clicked OK.
Another window popped up for me to edit the default values.
This was my first twinge of disappointment.
For very simple cases this dialog might have sufficed, but it’s too simplistic to be very useful. The dialog only allows for statically defined values, which must be calculated separately and input manually. There’s not even a control that would allow me to add/subtract/multiply all the values by some amount.
Since the dialog was modal, I could not click on the worksheet to use Excel’s cell calculation abilities. I would have to close the dialog, do my calculations somewhere outside the range, then reopen the dialog and enter the numbers. Instead, I used my old HP calculator to multiply each product’s price by 1.1.
After entering each value and clicking OK, the new values appeared in the worksheet. If I had any calculated cells they would automatically display the new values.
Then, if the cells containing these new prices were referenced in a linear programming (LP) model using Solver (or some other algebraic modeling software) I could then solve for and answer the what-if questions.
Comparing Scenarios
Skipping the advanced prescriptive analytics details, which is largely outside the scope of this comparison, I’m now left with these fairly simple decisions:
- Display saved values by selecting one of the two defined scenarios and clicking Show.
- Create more new scenarios by repeating the same, simple steps.
- Choose Summary…
I choose Summary…, primarily because, even after just a single what-if scenario, creating another scenario using the same steps is no longer very interesting.
Just to see what would happen, even though I had no calculated (Result) cells in my worksheet, I attempted to define a reasonably large area for Result cells. The error message that appeared was actually quite insightful — if using the Scenario Manager for what-if analysis, be prepared to have no more than 32 calculated cells on which to base the results!
Keep in mind that many prescriptive analytics models have millions of decision variables being solved for. So, this limit — even though it is strictly for cell calculations — also means its value for large and complex workbooks might be quite limiting.
Since I didn’t have any calculated cells, I clicked OK on the message, de-selected the Result cells, and clicked OK again. Excel automatically created a new worksheet named Scenario Summary and displayed this output.
Automating Scenarios
Next, I wanted to discover if a programmatic approach can be used to define and iterate through scenarios. It only took a Google search and a look inside Excel’s online Help to find out.
According to Help, a Worksheet.Scenarios Method exists that “returns an object that represents either a single scenario (a Scenario object) or a collection of scenarios (a Scenarios object) on the worksheet.”
Using a language like C# or Visual Basic and the API, one could (conceivably) create scenarios programmatically, and then loop through scenarios to conduct prescriptive analytics techniques. Of course, anyone wanting to do this must know how to program with the Excel API or pay someone to do it for them. Either way, this approach will result in a highly custom solution that, in my experience, frequently results in a “black box” that few people understand.
The Enterprise Optimizer® Approach
Enterprise Optimizer® (EO) is an optimization modeling and prescriptive analytics platform. Although its user interface includes a table editor and dashboard builder with some Excel-like features, it is most definitely not a spreadsheet. However, since both Excel and EO can be used to build and solve for optimization problems, comparing how each manages scenarios remains relevant and fair.
Unlike Excel, EO does not have a toolbar option labeled “Scenario Manager”. Depending on the model’s edition (Developer, Runtime, Research); purpose (e.g., consulting project, solution, etc.); and technology approach (e.g., desktop, SaaS solution, etc.), users have multiple options available to manage scenarios, including: EO’s built-in, code-free stochastic definition feature, including tables for currency exchange rates, procurement, inventories, resource downtime and capacities, production rates and yields, transportation transit-times, and sales prices.
The table displayed below is where stochastic definitions would be applied for the same sales prices used in the Excel example. Here, each price is defined as a Normal distribution with mean equal to the base price and standard deviation equal to 10% of the mean.
Solving and Analyzing in EO
Each time the EO model is solved, a new data value for each row will be randomly generated prior to the matrix generated and passed to the solver. Options exist to generate different data for each time period or the same across all periods, etc.
The EO model can be solved ‘n’ times, either manually with EO’s built-in-dashboards or automated using either the included Visual Basic for Applications (VBA) environment; the EO Server job management component — which is also code-free to use; the API inside a custom application (e.g., Visual C++, .NET); or from a browser using web services. Hundreds, even thousands, of scenarios that can be generated, solved for, and saved in a single batch.
Solution results can be analyzed immediately inside the EO model using its native dashboards and reports. Additionally, data can be exported to any common SQL database platform and then analyzed using Business Intelligence products like Microsoft Power BI, Tableau, Qlik, MicroStrategy or SAS.
Going Deeper with EO
To learn more about EO’s capabilities, I suggest reading these informative blogs:
- Monte Carlo Optimization for Beginners – a good primer on how to stochastically define data in EO models and create an EO Server job to solve thousands of scenarios in a single batch.
- Why Optimization Modeling Should Move Beyond Using Scripts – a useful summary of how automating what-if scenarios using EO Server eliminates the need to write code.
- Stop Using Excel for Business Optimization Modeling – a good summary of why relying on Excel for optimization modeling is not scalable and sustainable over the long-term.
Concluding Thoughts
Prior to writing this blog, I had never used Excel’s Scenario Manager — and there’s a possibility that I will never again. With hundreds of millions of Excel users, statistically speaking, some percentage must be benefiting from it, so I’m not totaling discounting it either.
That being said, for serious Integrated Business Planning — where decisions involve thousands, even millions, of complex, interrelated constraints — it’s difficult to see Excel’s Scenario Manager as widely used as it is for two main reasons:
- Unless using the API, only deterministic data can be input (no stochastic), which requires the tedious task of manually creating separate data sets. Even a simple tool for applying adjustments to values would have been quite helpful.
- Unless using the API, there is no way to automate the process of cycling through scenarios in a batch process. To use multiple scenarios in an advanced analysis, such as a linear programming model, the user must select each scenario group manually prior to each analysis. Repeating this process over and over again would be even more tedious than reason #1 above.
Even just one of these restrictions would be difficult enough to work around. The combination of the two makes using this feature for prescriptive analytics a non-starter unless the time, effort and funding to undertake the programmatic approach using the API are available. Even then, there are no guarantees it will work depending on the user requirements.
Enterprise Optimizer®, on the other hand, lets the user define stochastic data and automate batch scenario runs code-free. Only if EO is to be embedded inside a custom solution might coding be required, and even then it typically takes just a few lines of code to execute the batch process.
If you have used Excel’s Scenario Manager for anything besides simple calculations, especially for advanced business planning/prescriptive analytics, and especially if using the API, I am extremely interested in reading your comments. I sincerely want to better understand how companies are making decisions using Excel’s Scenario Manager, so please send me an email message (ekelso at riverlogic dot com) or get in touch with us on social media!
