If you ever find yourself in a situation where your life depends on solving a complex optimization model in Excel, here's what to do: 1) stay calm, 2) activate the Solver add-in, and 3) effortlessly optimize your way to freedom. In this tip, you can learn to use Solver to determine the optimal shipping strategy to minimize costs based on a number of decision variables and real-world constraints.
to share this next Pro Tip with you. … This is a five-star, truly expert level analytics tip. … We're going to talk about optimizing … complex models using Solver. … Now, what Solver's designed to do is work with … real world, complex optimization problems … that require multiple inputs or decision variables … subject to a given set of constraints. … Miami, Dallas, Seattle, and Baltimore, … and we're going to be walking through this … in depth when we jump into Excel, … but the bottom line, the objective here, … is to figure out how many units of product … to ship from each factory to each distribution center … in order to minimize our total shipping cost. … Now, it's not quite that simple … because in the real world we have … constraints and limitations as well. … For one, we need to fulfill all of the orders demanded, … in this case 6,000 total orders, … and two, we can't exceed the inventory … available at each specific factory. … So, for a problem like this we really … can't use simpler tools like Goal Seek, …
- Use the Analysis ToolPak to explore data.
- Interpret the effect of a fence when using outlier detection.
- Explain data modeling basics in Excel.
- Use CUBE functions to explore data models.
- Use Monte Carlo simulations to predict outcome probability.
- Recognize the actions Solver does to optimize complex data models.