You make three products. You have limited machine time, labor hours, and raw materials. The question isn’t which product is most profitable per unit — it’s which combination of products squeezes the most profit out of what you actually have. That’s a product mix problem, and it’s one of the most common optimization decisions in manufacturing, production planning, and operations.
This tutorial walks through the exact model shown in the video using SolveSheet, the optimization solver for Google Sheets.
Copy the example spreadsheet to follow along.
The Problem
Apex Manufacturing produces three products: Brackets, Hinges, and Frames. Each product uses a different amount of machine hours, labor hours, and steel. The factory has 240 machine hours, 180 labor hours, and 300 kilograms of steel available each week. The profit per unit is $25 for Brackets, $15 for Hinges, and $40 for Frames.
The goal: decide how many units of each product to make each week to maximize total profit without exceeding any resource limit.
Setting Up the Model
Every optimization model has three parts. Set these up in your spreadsheet before touching SolveSheet.
1. Decision variables — Three cells, one for each product, representing units to produce. Start them at zero. These are the cells the solver will change.
2. Objective — A formula that multiplies units × profit per unit and sums across all three products. This is the cell you want to maximize.
3. Constraints — For each resource, a formula that calculates total usage across all products (e.g., machine hours used = 2×Brackets + 1×Hinges + 3×Frames). Each usage formula must stay ≤ its limit.
The spreadsheet uses color coding to keep things clear: yellow cells are the decision variables you’re solving for, green is the objective to maximize, and orange shows the constraint totals being monitored.
Running SolveSheet
- Open Extensions → SolveSheet in the sidebar.
- Set the objective cell to your total profit formula and choose Max.
- Set variable cells to the three unit cells.
- Add three constraints: each resource usage cell ≤ its limit.
- Add a non-negativity constraint: variable cells ≥ 0.
- Click Solve.
SolveSheet uses the Simplex LP method for this problem, which finds the exact optimal solution in under a second.
Reading the Results
SolveSheet writes the optimal production quantities back into your decision variable cells and generates an Answer Report on a new sheet.
The Answer Report tells you which constraints are binding — meaning the resource was fully used up. Those are your bottlenecks. If machine hours are binding but labor hours aren’t, adding more machine time would increase profit; adding more labor wouldn’t.
Upgrade to SolveSheet Pro to unlock the Sensitivity Report, which shows exactly how much more profit you’d gain per additional unit of each resource — useful for deciding where to invest next.
What to Try Next
- Change the profit per unit for one product and re-solve to see how the optimal mix shifts.
- Add a minimum production requirement (e.g., “make at least 10 Brackets”) as an additional constraint.
- Add a maximum order constraint if a customer can only take so many units per week.
The model handles all of these with one more constraint row — no formula changes required.
Get SolveSheet
Free tier available — up to 10 variables and 5 constraints, no credit card required. This example runs on the free tier.