Oops! The input is malformed! Beginning Prescriptive Analytics with Optimization Modeling by Jen Underwood - BeyeNETWORK
We use cookies and other similar technologies (Cookies) to enhance your experience and to provide you with relevant content and ads. By using our website, you are agreeing to the use of Cookies. You can change your settings at any time. Cookie Policy.

Channel: Prescriptive Analytics - Jen Underwood RSS Feed for Prescriptive Analytics - Jen Underwood


Beginning Prescriptive Analytics with Optimization Modeling

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.

(Mouseover image to enlarge.)

Let’s begin digging into this first optimization model. What is immediately notable is the optimization model that you create for use with Solver looks no different from any other Excel spreadsheet model. What makes this model different is the impressive Solver engine that deciphers the model and runs the decision science algorithms. The Solver engine is far more advanced than the base Excel engine. Also the Solver engine Excel formulas get mapped to Excel cells in the Solver Options and Specifications task pane of the Excel Analytic Solver Platform add-in.

In the Advertising Media Mix example problem, we want to minimize advertising costs, while meeting our goals of reaching a minimum total audience for each channel, and not exceeding a budgeted number of ad impressions. To break this down into a solvable model, we first define an Objective, a formula calculating advertising costs that Solver will minimize. We then define Constraints, formulas calculating the total audience reached and the number of ad impressions generated. The Constraints will also include a lower limit for the total audience and an upper limit for ad impressions. The Inputs or known values are the target audience sizes for each channel, the cost per impression and the max impressions. The Decisions or Variables in this example are the amounts to invest in each channel. To associate the needed optimization elements to Solver format, you use the Solver task pane Model tab. Here you can enter the three needed optimization elements defined in the spreadsheet to map them to the Solver under Objective, Variables and Constraints to be able to find a solution.

After you have mapped the spreadsheet model cells to a Solver model element, you can click the green arrow icon to run the Solver algorithms to evaluate and provide optimized results. The Advertising Mix model is a linear programming problem. Solver quickly found a solution that satisfied all defined constraints and optimality conditions, using its LP/Quadratic Solver. This simply means that the Solver has found the optimal or “best” solution under the circumstances or within a given “solution space.” In this case, Solver has returned a “globally optimal solution,” meaning there are no better solutions for the given scenario. For some optimization models, finding a globally optimal solution might be impossible, or it might take hours or days using the best algorithms on the fastest computers.  Since every model is an abstraction/simplification of the real world, it’s possible to create different models for the same business situation. Part of the art of prescriptive and predictive analytics modeling is defining a model that captures the essentials of the real-world situation but is also solvable in reasonable time. 

  (Mouseover image to enlarge.)

You can review the prescriptive analytic optimization model results in the Output tab in the Solver task pane. You can also see that the Decision Variables have been automatically populated into the Excel cells for you with the optimized answers. If you want to further experiment and explore other model scenarios, you can simply edit the Solver model optimization elements and re-run the model for “what-if” analysis.

Prescriptive analytics solutions like the Solver are extremely strategic and invaluable to an organization. When applied properly, optimization tools and projects provide immediate returns on investment and can save companies millions of dollars each year. In the real world there is a bit of a learning curve to developing optimization models and understanding the mathematical solution space concepts around this specific area of advanced analytics. Analytic modeling is both an art and a science. Since prescriptive analytics is not at all simple, it often is underutilized today. Another common challenge facing advanced analytic professionals is that it may be difficult or impossible to structure a problem accurately into a model that a mathematical algorithm can resolve.

Developing the Optimization Model

To develop an optimization model, there are a few general steps that to follow. First, truly understand the problem to solve with a model. It sounds obvious but often bad models are a result of not capturing all of the needed elements. Next, identify the decision variables, what answers/decisions should the model return in the output. Then define the objective as a linear combination of the decision variables. Lastly, itemize the constraints that must be honored.

If you've made it to this point, you have successfully set up and solved a simple conventional optimization problem using the powerful Frontline Systems Solver Analytic Platform 12.5 add-ins for Excel. These types of optimization models are also referred to as deterministic optimization models since they do not include any uncertainty. If you explore Solver a little further, you will notice that we have merely touched the surface of this specific solution’s capabilities in this article.

In future articles, we will continue learning more about the field of prescriptive analytics and review more advanced modeling techniques. Topics such as Simulation and Risk Analysis will be covered using stochastic optimization models that do include uncertainty. You also learned that complex problem solving can be made easier with the right tools, but there is still a bit more to it than just having the right analytic tools.

As we continue exploring prescriptive analytics, we will also discuss additional real-world issues, common pitfalls, case studies and best practices in analytic model development and usage.

SOURCE: Beginning Prescriptive Analytics with Optimization Modeling

  • Jen UnderwoodJen Underwood
    Jen Underwood has almost 20 years of hands-on experience in the data warehousing, business intelligence, reporting and predictive analytics industry. Prior to starting Impact Analytix, LLC, she held roles as a Microsoft global business intelligence technical product manager, Microsoft enterprise data platform specialist, Tableau technology evangelist and also as a business intelligence consultant for Big 4 systems integration firms. Throughout most of her career she has been researching, designing and implementing analytic solutions across a variety of open source, niche and enterprise vendor landscapes including Microsoft, Oracle, IBM and SAP.

    As a seasoned industry presenter, author, blogger and trainer, Jen often volunteers her time and gives back to the global technical community in many ways. Recently Jen was honored with a Boulder BI Brain Trust (BBBT) membership, a 2013 Tableau Zen Master (MVP) award and a Dun & Bradstreet MVP. Jen holds a bachelor of business administration degree from the University of Wisconsin, Milwaukee and a post graduate certificate in computer science -- data mining from the University of California, San Diego.

    She may be contacted by email at jen@impactanalytix.com, and her blog can be found here.

    Editor's Note: Find more articles and resources in Jen's BeyeNETWORK Expert Channel. Be sure to visit today!

Recent articles by Jen Underwood



Want to post a comment? Login or become a member today!

Be the first to comment!