Automate scenario analysis of constraints to achieve goals.

Product management leverages numerous factors to achieve optimal success with product outcomes such as constraints (limitations upon resources) and decisions decisions on those constraints.

Optimization allows team members to influence certain decisions about constraints, delivering differing results according to the team strategy.

In this article, I will demonstrate the use of using optimization to create a strategy for a fictitious tablet manufacturer (Tablix Maker).

The spreadsheet begins with Tablix Maker’s counts of semiconductors used for each tablet, its inventory, target delivery for each model, and profits forecasted for each model along with total profit.

Tablix Maker has two models – A and B. Each model requires a certain amount of semiconductors from a fixed inventory.
Finally, each model yields a profit.

To determine the total profit achieved, I will enter an objective function which uses each product’s constraint (target delivery for each model) with each model’s profit, to determine the total profit.

Now we have the expected total profit achieved – $11,00.
However, total profit does not account for semiconductors required or inventory, nor do we know if we have achieve the optimum target delivery for the inventory.

Excel provides the Solver utility which examines different scenarios using requirements and per unit profit to determine the best scenario for optimum profit.

Using the same spreadsheet, I will start the Solver utility using the Data tab -> Solver button.
I’ll set the location of the objective function, tell Solve which cells it may change (target delivery), then add constraints when running through various scenarios no more than 150 of Tablet A models, and no more than 200 Table B may be produced respectively.

Upon pressing Solve, I see Solver determine the best strategy for Tablix Maker models is for them to produce 40 of Tablet and 200 of Tablet B, for a total profit of $11,400.