ETX1100/ETX5900 Business Statistics

Week 7: Spreadsheet modelling and optimisation

19 January 2027

Today’s journey

  1. Translate a business decision into a spreadsheet model.
  2. Separate inputs, decisions, calculations, objectives, and constraints.
  3. Explore sensitivity with one- and two-way Data Tables.
  4. Work backwards with Goal Seek.
  5. Optimise constrained decisions with Solver.

Model formulation

Retrieval check

  • How is prediction different from optimisation?
  • What does a regression coefficient estimate?
  • Why should inputs be separated from formulas?
  • What makes a recommendation auditable?

What is a business model?

A spreadsheet model is a simplified, explicit representation of a decision problem.

It links:

  • known or assumed inputs;
  • controllable decisions;
  • calculation logic;
  • a performance objective; and
  • restrictions or constraints.

Model anatomy

Component Question
Inputs What is known or assumed?
Decisions What can the business control?
Calculations How do inputs and decisions combine?
Objective What should be maximised, minimised, or targeted?
Constraints What limits feasible decisions?

Watch me: formulate before opening Solver

A firm sells a product with unit cost AUD 40 and demand q=800-5p.

  • Input: unit cost.
  • Decision: price p.
  • Calculation: demand and profit (p-40)q.
  • Objective: maximise profit.
  • Constraints: non-negative price and demand.

Write this logic in words before entering formulas.

Now you try: map the decision problem

A café chooses how many staff to roster. More staff increase wage cost but may increase customers served and reduce waiting.

Identify:

  1. inputs;
  2. decision variable;
  3. calculations;
  4. objective; and
  5. at least three constraints or business rules.

Spreadsheet design principles

  • Place each input in one labelled cell.
  • Reference cells instead of burying numbers in formulas.
  • Use units consistently.
  • Colour or style inputs and decisions distinctly.
  • Add reasonableness checks.
  • Keep the objective cell and constraint calculations visible.
  • Document assumptions beside the model.

Watch me: build the price model

Open week7-modelling-data.xlsx, sheet Book Example.

Trace the cells for:

  1. price and unit cost;
  2. demand formula;
  3. revenue and total cost;
  4. profit; and
  5. checks for negative demand.

Change one input and verify the calculation chain.

Now you try: audit the model

  1. Identify any hard-coded assumptions inside formulas.
  2. Add labels and units where needed.
  3. Test a low and high price.
  4. Add a check that flags negative demand.
  5. Explain which cells a decision-maker may safely change.

Sensitivity with Data Tables

What-if analysis

Sensitivity analysis asks how an output changes when assumptions or decisions change.

A Data Table:

  • repeatedly substitutes trial values;
  • records the resulting formula output; and
  • reveals thresholds, nonlinearities, and robust regions.

It explores scenarios; it does not prove assumptions are correct.

One-way Data Table

For a list of trial prices:

  1. reference the profit cell above the result column;
  2. select the entire table range;
  3. choose Data → What-If Analysis → Data Table;
  4. use the column input cell when trial values run down a column.

Watch me: profit by price

Create a one-way table for prices from AUD 40 to AUD 140.

Check:

  • where profit becomes positive;
  • where profit reaches its largest listed value;
  • whether profit changes linearly; and
  • whether any trial produces impossible demand.

Two-way Data Table

A two-way table varies one input across columns and another down rows.

  • Row input cell: corresponds to values across the top row.
  • Column input cell: corresponds to values down the first column.
  • The upper-left corner references the output formula.

Watch me: price and cost sensitivity

Vary price across columns and unit cost down rows.

Before running the table, predict:

  • how higher costs affect profit;
  • whether the best price must change; and
  • which combinations could produce a loss.

Then use conditional formatting to reveal the pattern.

Now you try: two-way sensitivity

Build the two-way price-by-cost table.

  1. Identify the correct row and column input cells.
  2. Find the highest profit in each cost scenario.
  3. Describe whether the recommended price is robust.
  4. State one assumption that deserves further testing.

Goal Seek

Work backwards from a target

Goal Seek changes one input until a formula reaches a specified target.

Use it when:

  • there is one target output;
  • one adjustable input; and
  • the relationship is represented by a formula.

It finds a solution, not necessarily a global optimum.

Watch me: investment break-even

On the Investment sheet:

  1. calculate net present value from the cash flows and discount rate;
  2. choose Data → What-If Analysis → Goal Seek;
  3. set the NPV cell to 0 by changing the discount-rate cell; and
  4. interpret the result as the internal rate of return.

Now you try: target profit

Use Goal Seek on the price model to find a price that produces a chosen target profit.

  1. Record the starting value and solution.
  2. Try a different starting value.
  3. Check demand and other business rules.
  4. Explain why multiple solutions may exist for a nonlinear profit curve.

Constrained optimisation with Solver

When Solver is needed

Solver can:

  • maximise, minimise, or target an objective;
  • change one or more decision cells; and
  • enforce constraints.

The mathematical model still comes first. Solver cannot rescue incorrect formulas or missing business rules.

Product-mix case

A factory produces Products A and B.

Resource or value Product A Product B Available
Profit per unit AUD 30 AUD 50 —
Assembly hours 2 4 240
Finishing hours 3 2 180

Choose units of A and B to maximise total profit.

Watch me: formulate the product mix

Decision cells: A and B units.

Objective:

\max\ 30A+50B.

Constraints:

2A+4B\le240, 3A+2B\le180, A,B\ge0.

Add integer constraints only if partial units are infeasible.

Watch me: configure Solver

  1. Set objective to the total-profit cell and choose Max.
  2. Select the A and B decision cells.
  3. Add both capacity constraints and non-negativity.
  4. Choose Simplex LP for this linear model.
  5. Solve, keep the solution, and inspect used versus available resources.

Now you try: solve and validate

Build and solve the product-mix model.

  1. Report optimal A, B, and profit.
  2. Identify binding constraints.
  3. Substitute the decisions back into every constraint.
  4. Explain why a feasible solution is not automatically optimal.
  5. State whether integer restrictions are needed.

Sensitivity after optimisation

An optimal decision depends on assumptions.

Test:

  • profit per unit;
  • resource availability;
  • minimum commitments;
  • maximum demand; and
  • integer requirements.

Record whether the recommended decisions change materially.

Now you try: stress-test the recommendation

Change one assumption at a time:

  1. reduce assembly capacity by 10%;
  2. increase Product A profit to AUD 40;
  3. impose a minimum of 20 units of B; and
  4. impose integer decisions.

For each scenario, record the new decision and profit. Identify the most decision-sensitive assumption.

Communicating a model

From optimum to recommendation

A useful modelling brief explains:

  • recommended decisions and objective value;
  • which constraints bind;
  • key assumptions and their sources;
  • sensitivity to plausible changes;
  • implementation considerations; and
  • what the model omits.

An optimum inside the spreadsheet may still be unsuitable in practice.

Now you try: decision memo

Write a four-sentence memo for the factory manager:

  1. recommendation;
  2. expected profit;
  3. limiting resources and sensitivity; and
  4. one operational limitation requiring judgement.

Common traps

  • Opening Solver before formulating the problem.
  • Hard-coding assumptions inside formulas.
  • Swapping row and column input cells in a Data Table.
  • Treating Goal Seek as an optimiser.
  • Omitting non-negativity or capacity constraints.
  • Choosing the wrong Solver method.
  • Reporting a solution without checking feasibility and sensitivity.

Closing check

Can you:

  • identify inputs, decisions, objective, and constraints?
  • build an auditable spreadsheet model?
  • create one- and two-way Data Tables?
  • use Goal Seek for a target?
  • configure and validate a Solver optimisation?
  • communicate a decision with sensitivity and limitations?