Oops! The input is malformed!
Originally published November 19, 2013
In my previous article, I introduced prescriptive analytics, the most advanced area of decision support on the analytics maturity spectrum. Prescriptive analytics provides the best outcomes for modeled situations based on the powerful concepts of optimization. Optimization entails deciding how to best leverage limited assets, time and resources in situations with varying levels of uncertainty. Literally everyone applies prescriptive analytics in their daily life and may not realize it or may not be using a mathematical model. Common prescriptive applications include staffing projects, determining budget usage, setting prices, deciding to pursue a proposal, managing a financial portfolio or running a business.
If you went to business school, optimization models were most likely covered in your operations or management science courses. In this article, I will walk through a simple optimization example using mathematical programming (MP) techniques with two of the most popular prescriptive analytic modeling tools on the planet, Excel and an Excel add-in called Frontline Systems Solver.
This first example will be fairly easy to start our journey and introduce solution space concepts. In the real, world it is not uncommon to find optimization models that are mathematically simple but very large – with up to millions of decision variables – as well as models that are mathematically complex (non-linear, non-smooth, non-convex) and very difficult to solve. Other prescriptive analytic tools that could be used for simulation and risk analysis, and some optimization problems include Crystal Ball Professional, @RISK Industrial, Tibco Spotfire or Tableau.
Prescriptive analytic optimization models combine historical data, business rules, variables, constraints and desired outcomes. All optimization problems have three key elements: an Objective, Decisions and Constraints. An objective is a measure to optimize, such as profit. The decisions to be made are called decision variables and are typically amounts of inputs or outputs. For example, a decision may be how many hours to staff personnel or how much money to invest. Constraints are logical restrictions or limits, such as available hours in a day or budget. To begin solving an optimization problem, you structure the optimization problem as a mathematical model with these three optimization element types in an Excel spreadsheet.
In this first example, I will reference the Solver’s Advertising Media Mix optimization model. If you want to follow along, feel free to download and install a free trial of Frontline Systems Solver Analytic Platform 12.5 add-in for Excel. The add-in is compatible with Excel versions 2003 to 2013. If you are not familiar with Frontline Systems Solver, it is an evolution of the original, free Excel Solver add-in that has expanded to include comprehensive forecasting, data mining, simulation/risk analysis, decision trees, and conventional and stochastic optimization. The for-fee version of Solver that I am showcasing in this article is much more feature rich than the free Solver add-in that comes installed with Excel.
The Analytic Solver Platform truly provides best-in-class Excel add-in tools for solving prescriptive analytic problems. The Analytic Solver Platform trial includes a wonderful array of optimization, simulation and stochastic model examples. There are also video tutorials and white papers available on the Frontline Systems website for further reference. After downloading and installing the trial version, navigate to the added Analytic Solver Platform menu item in Excel, choose Help, Examples and a spreadsheet containing links to all of the available example models will be displayed. From there, choose Optimization Examples and Advertising Media Mix on line 30.
Recent articles by Jen Underwood