A small shop that loses money every month usually knows the loss, not the fix. The question is simple: how many more sales would stop it? Most people answer it by typing guesses into the sales cell until the profit cell turns positive, which works, but takes a while and lands on a round number that is a little too high. Goal Seek turns the question around. You say which cell should reach which value, and which cell it may change, and the spreadsheet does the guessing. The Spreadsheet Editor in the osFoundry desktop app has it under Data, in the What-If Analysis menu. The numbers below are example figures for one product, made up for this walkthrough.
Put the numbers in one column
Four inputs and one result are enough:
- B1 Price per item: $20
- B2 Cost per item: $8
- B3 Monthly costs: $3,600 (rent, software, anything you pay whether you sell or not)
- B4 Items sold: 200
- B5 Profit:
=B4*(B1-B2)-B3
With these values the profit is -$1,200. The profit has to be a formula that uses the other cells, because Goal Seek changes one input and reads the result through that formula. A profit typed in by hand gives it nothing to work with.
Click the profit cell B5, open the Data tab, then What-If Analysis and Goal Seek.
Ask for a profit of zero
The dialog has three boxes. Set cell is already B5, because the editor fills in the cell that was selected. To value is the number that cell should reach: 0, the point where the shop neither loses nor makes money. By changing cell is the input Goal Seek may move: B4, items sold.
Click OK. Items sold becomes 300, the profit becomes $0, and a message at the bottom says "Goal Seeking found a solution: 300.0".
Goal Seek tries a value in B4, recalculates the sheet, sees how far the profit is from the target, and uses that to pick a better value, until the profit is close enough. On a sheet this simple you can check the result by hand: each item leaves $12 after its cost, and $3,600 divided by $12 is 300. The value of the tool shows up when the profit formula is longer, with fees, discounts or tax, and dividing by hand is no longer obvious.
If the answer comes out as a fraction, round items sold up, not down. 287.4 items is not something a shop can sell, and 287 would still leave a small loss.
Ask for the price that reaches a goal
Break-even only stops the loss. Say the shop wants $1,200 a month. With items sold now at 300, open Goal Seek again (Data is still the active tab, so What-If Analysis and Goal Seek are enough). Keep Set cell at B5, type 1200 in To value, and this time put B1, the price, in By changing cell.
OK sets the price to $24, and the profit reads $1,200. Four dollars more per item, on the same 300 sales, is $1,200 a month.
Goal Seek answers the formula it is given, and this formula assumes the shop still sells 300 items at the higher price. Whether customers would accept $24 is a question for the shop, not the sheet. A useful habit is to run it both ways, as above: once on volume and once on price, and compare the two answers with what the shop can realistically change.
Goal Seek changes one cell at a time. For several inputs with limits (a price no higher than a competitor's, stock no more than the shelf holds), the same Data tab has Solver.
The Spreadsheet Editor is part of the free osFoundry desktop app, and the workbook stays on your own computer, as described on the local-first platform page.
Top comments (0)