Should you make more of your most profitable product?
Four products, three scarce resources and a sales ceiling on each. Making twenty of everything earns $5,900 a week. The optimizer finds $6,890 from the same workshop, and it does it by making fewer of the product with the biggest profit per unit.
Operations Starter Optimization free
After you install, this is the model to open.
What Should We Make This Week?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
What it does
A small furniture workshop makes four products from the same machine time, labor and timber, and can only sell so many of each in a week. The sheet opens on the plan most people start from, twenty of everything, which earns $5,900 and fits inside every limit. Click Run and the optimizer returns $6,890, which is $990 a week more from the same workshop: 40 bookshelves, 30 coffee tables, 45 stools and 5 desks.
Look at what it cut. The desk earns $120 a unit, the most on the sheet, and the run takes it from twenty down to five. The reason is in the labor column. A desk takes 5 labor hours, so it earns $24 for each hour, the least of the four. A bookshelf earns $31 an hour, which is why the run makes all 40 the market will take. Profit per unit tells you what a sale is worth, and profit per hour of the scarce resource tells you what to make.
The result in the panel names the limits that came back Binding, which are the ones that stopped the profit going higher. Here machine time and labor are both used to the last hour, and bookshelves and coffee tables both reach their sales ceiling. Timber has 45 kg left over, so buying more timber would change nothing, and an extra hour of machine or labor time is where the next dollar is.
The whole-number rule on the Units to make cells is what keeps the answer to complete pieces of furniture. To use your own numbers, overwrite the products, the profit and usage columns and the three Available figures, keep the formulas in the Used column, and run again.
The model
It arrives on a tab called Template: What Should We Make This Week, carrying these columns:
- Units to make (count)
- Profit per unit ($)
- Machine hours per unit
- Labor hours per unit
- Timber per unit (kg)
- Most we can sell (units)
with the model computed beside the data:
| Machine time (hours) | 140 |
| Labor time (hours) | 220 |
| Timber (kg) | 1,260 |
| Total profit this week ($) | 5,900 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- How few people can cover a seven-day week?How Few People Can Staff the Week?
- How many units does one delivery wait really need on the shelf?How many units to stock for 90% odds of no stockout?
- How many hires does a support week really need, in odds rather than averages?How many hires keep 85% odds of hitting the support target?
- Is a second shift enough for the peak week, or does it take a second line?What capacity gives 95% odds of covering peak demand?
- How much should you order when demand is uncertain?Inventory Order Quantity (Newsvendor)
- Should you hold your price, or cut first?Price War: Hold or Cut?
Every model like this one, and the method behind them: Optimization in Google Sheets.