Optimize a pricing decision

What you will build

A pricing desk that solves three different decisions: the price required to hit a profit target, the price that maximizes profit, and the production run size that minimizes batch cost.

The model remains reactive. Changing market assumptions causes new solutions, but solver results never mutate the candidate input cells used to describe the problem.

Use the starter download to write the solves yourself, or open the worked pricing desk to run the complete experiment. In the worked model, BaselineDemand feeds A3; the Pricing tab displays the results together. Change BaselineDemand through the I/O view, then return to Pricing to compare the answers. The named input and layout expose the same equations taught below.

Watch the worked case

The price finds the target follows the same pricing desk from baseline demand to three new recommendations. The film uses Grid 0.66.2; the downloadable worked model contains the exact fixture exercised in its verified capture.

Open the film page and transcript.

1. Read the economics

Open the Goal-Seek and Optimization example. Its inputs describe unit cost, fixed cost, baseline demand, price sensitivity, a candidate price, and a candidate run size.

A1 IS currency = 38
A2 IS currency = 24000
A3 = 2200
A4 = 9
A5 IS currency = 120
A6 = 900

The candidate economics are:

B1 = MAX(A3 - A4 * A5, 0)
B2 IS currency = A5 * B1
B3 IS currency = B2 - (A1 * B1 + A2)

Checkpoint: at price 120, demand is 1,120, revenue is 134,400, and profit is 67,840.

2. Solve for a target

SOLVE C1 = A5 IN [40, 140] GOAL B3 = 30000

Grid treats A5 as the variable inside the bounded solve and writes the answer to C1. It does not overwrite A5.

Checkpoint: the target price is approximately 72.996. The candidate price remains 120.

The bounded interval matters because the same profit target can have more than one mathematical root. The model makes the intended decision region explicit.

3. Maximize profit

SOLVE C2 = A5 IN [40, 240] MAXIMIZE B3

Checkpoint: the profit-maximizing price is approximately 141.22, producing demand of 929 and profit of approximately 71,893.44.

The model calculates those economics separately in D1 and D2, so the recommended price and its consequence remain inspectable.

4. Minimize the batch-cost objective

B4 IS currency = (B1 / A6) * 1800 + 0.65 * A6 / 2
SOLVE C3 = A6 IN [100, 4000] MINIMIZE B4

This objective balances more frequent setups against carrying a larger run.

Checkpoint: for the candidate demand of 1,120, the best run size is approximately 2,490.60.

5. Compare statement and function forms

The example also uses GOALSEEK and MINIMIZE with lambdas:

E1 = GOALSEEK(p => (p - A1) * MAX(A3 - A4 * p, 0) - A2, 0, 40, 140, 0.0001, 200)
E2 = MINIMIZE(q => (D1 / q) * 1800 + 0.65 * q / 2, 100, 4000, 0.001, 200)

Use SOLVE when the authored decision is naturally expressed through workbook cells. Use the function form when a self-contained lambda is clearer.

Checkpoint: E1 solves for zero profit (break-even), while C1 solves for profit of 30,000. They are different decisions. The batch optimizer over demand at the best price returns approximately 2,268.31.

6. Change an assumption

Raise baseline demand A3 from 2,200 to 2,500. All three solved recommendations change, while candidate price A5 = 120 and candidate run size A6 = 900 stay fixed.

These are approximate numerical solutions within the authored bounds. More demand lets a lower price reach the same profit target, while the maximum-profit price and economical run size increase. Substitute C1 into the profit formula to check that it still yields 30,000.

Changing unit cost is a different experiment: it changes the profit solves, but leaves C3 unchanged because its batch-cost objective depends on candidate demand, setup cost, and carrying cost.

No rule or imperative callback is needed. The solver statements participate in the dependency structure just like other derivations.

Try it yourself

Add an editable minimum-margin requirement and a status cell that compares maximum profit with that threshold. Then change fixed cost and explain which solver result moves most and why.

For an additional constraint, narrow the maximizing price interval to [80, 130]. The answer should land on the upper boundary when the unconstrained optimum lies outside the permitted range.

You are done when

• Target, maximum, and minimum solves produce independent results.
• Solver outputs do not mutate their variable inputs.
• Changing one assumption recomputes every affected recommendation.
• You can explain why bounds are part of the decision specification.