Perform what-if analysis and goal seek

9 Modelling – Objective: Perform What‑If Analysis and Goal Seek

9.1 Overview of Modelling in IT 9626

• Modelling creates a simplified, mathematical representation of a real‑world situation using a spreadsheet or specialised simulation software.
• Key purposes: predict outcomes, test alternatives, support decision‑making, and communicate findings clearly.
• Core techniques required for the Cambridge A‑Level syllabus:
  • What‑If analysis – explore many possible scenarios or sensitivities.
  • Goal Seek – determine the exact input needed to achieve a single target result.

9.2 Characteristics of Modelling Software

Feature	Spreadsheet (e.g., Excel, Google Sheets)	Dedicated Simulation Tools (e.g., @RISK, Arena, Simul8)
Calculation speed	Fast for moderate data sets; may slow with very large tables.	Optimised for large‑scale iterative calculations and stochastic runs.
User interface	Grid‑based, familiar to most students; rich visualisation options.	Diagram‑oriented (flow‑charts, block diagrams); steeper learning curve.
Built‑in analysis tools	What‑If (Scenario Manager, Data Tables), Goal Seek, Solver, add‑ins for Monte‑Carlo.	Monte‑Carlo engines, discrete‑event simulation, queuing theory, built‑in validation reports.
Validation & audit trail	Formula auditing, precedents/dependents, error‑checking, file‑history.	Model verification reports, change logs, version control within the project.
Scalability	Excellent for small‑to‑medium models; can become unwieldy with thousands of variables.	Designed for very large, complex systems (e.g., traffic‑flow, supply‑chain networks).

9.3 Using Dedicated Simulation Tools (Real‑World Modelling)

• @RISK (Excel add‑in) – adds Monte‑Carlo capability directly to a spreadsheet; you define probability distributions, set the number of iterations, and generate output statistics.
• Arena / Simul8 – stand‑alone packages for discrete‑event simulation; you build a flow‑chart of entities, resources, and queues, then run the model to obtain performance measures such as waiting time or utilisation.
• Typical exam‑style tasks:
  • Natural‑disaster planning – model the spread of a flood across zones and test evacuation strategies.
  • Traffic‑flow modelling – simulate vehicle arrivals at a junction to determine the optimal signal timing.
  • Manufacturing line – use a discrete‑event model to find the bottleneck and evaluate the effect of adding an extra machine.

9.4 What‑If Analysis

What‑If analysis investigates how changes to input values affect model outputs. It is a form of sensitivity analysis and can be extended to stochastic (Monte‑Carlo) modelling.

9.4.1 Core Tools in a Spreadsheet

• Scenario Manager – stores complete sets of input values (scenarios) and lets you switch between them instantly.
• Data Tables
  • One‑variable data table – varies a single input across a row or column and records the resulting output.
  • Two‑variable data table – varies two inputs simultaneously (one across the top, one down the side) and produces a matrix of results.
• Sensitivity analysis – a systematic one‑variable data table that shows how a KPI responds to incremental changes.
• Monte‑Carlo simulation (optional A‑Level extension) – uses random numbers to model uncertainty; usually performed via an add‑in such as @RISK.

9.4.2 General Steps for a What‑If Analysis

1. Define the objective (e.g., maximise profit, minimise cost, assess risk).
2. Identify all input variables that could reasonably change.
3. Choose the appropriate tool:
   • Scenario Manager for a few distinct “what‑if” stories.
   • One‑ or two‑variable data tables for systematic range testing.
   • Monte‑Carlo for modelling uncertainty.
4. Set the range of values (minimum, maximum, step size) for each variable.
5. Run the analysis and record the resulting KPI values.
6. Interpret the outcomes, highlight the most sensitive inputs, and document all assumptions.

9.4.3 Example – Two‑Variable Data Table

Scenario: A small retailer wants to see how profit changes with different selling prices and quantities.

Price per unit (£)	30	35	40
Quantity sold (units)	800	850	900
Profit (£)	7 200	9 250	11 400

Using Data → What‑If Analysis → Data Table the student creates the matrix, then identifies the combination that meets a profit target or maximises profit.

9.4.4 Monte‑Carlo Extension (A‑Level optional)

1. Define uncertain inputs and assign probability distributions (e.g., demand ~ Normal(μ=1 200, σ=150)).
2. Set the number of iterations – at least 1 000 for a reliable estimate; more iterations give smoother output distributions.
3. Run the simulation via @RISK:
   • Insert the @RISK function (e.g., =RiskNormal(1200,150)) in the demand cell.
   • Open the @RISK toolbar, choose “Run Simulation”, and specify iterations.
4. Analyse the output:
   • Mean, median and standard deviation of the KPI (e.g., profit).
   • Percentiles – 5 % and 95 % confidence interval.
   • Probability of meeting a target (e.g., “Probability profit ≥ £10 000 = 78 %”).
5. Document the assumptions – distribution choice, parameters, and number of iterations.

9.5 Goal Seek (A‑Level Requirement)

Goal Seek finds the exact value of a single input that makes a formula return a specified result. It is an iterative, single‑variable optimisation tool.

9.5.1 When to Use Goal Seek

• You know the desired outcome (e.g., target profit, break‑even revenue).
• You need to determine the required level of one controllable variable (e.g., sales volume, price, cost).

9.5.2 Limitations

• Only **one** changing cell can be used at a time.
• The algorithm may not converge if the relationship is non‑monotonic or the target lies outside the feasible range.
• Goal Seek does not consider constraints; for multi‑variable optimisation use the Solver add‑in.

9.5.3 Steps to Use Goal Seek

1. Enter the formula that produces the result you wish to control (e.g., =Revenue‑Cost).
2. Select Data → What‑If Analysis → Goal Seek.
3. In the dialog box:
   • Set cell: the cell containing the result formula.
   • To value: the target result (e.g., 15 000).
   • By changing cell: the input you want Goal Seek to adjust.
4. Click OK. Excel iterates and returns the required input value.
5. Validate the solution by recalculating the model manually or with a quick “what‑if” check.

9.5.4 Example – Finding Required Quantity

Profit formula:

\[ P = (p \times q) - C_f - (c_v \times q) \]

• Given: p = £30, C_f = £5 000, c_v = £10, target P = £15 000.
• Goal Seek set‑up:
  • Set cell: Profit (e.g., E10).
  • To value: 15000.
  • By changing cell: Quantity (q) (e.g., B5).
• Result: q ≈ 950 units (rounded to the nearest whole unit).

9.6 Comparison of What‑If Analysis and Goal Seek

Aspect	What‑If Analysis	Goal Seek
Primary purpose	Explore many alternative scenarios or sensitivities.	Find a single input that meets a specific target.
Number of variables changed	One or many (via scenarios, data tables, or Monte‑Carlo).	Only one variable at a time.
Typical output	Table or set of results for each scenario; probability distributions for Monte‑Carlo.	Exact value of the chosen input.
Complexity	Can become large with many combinations; Monte‑Carlo adds stochastic depth.	Simple and fast, but limited to linear or monotonic relationships.
Common exam use	Strategic planning, risk assessment, sensitivity questions.	Break‑even, required sales, required price questions.

9.7 Effectiveness of Spreadsheet Models

• Advantages
  • Highly flexible – can be adapted quickly to new data or assumptions.
  • Transparent – formulas are visible; audit tools help locate errors.
  • Integrated with charts, pivot tables, and What‑If tools.
  • Widely available and familiar to most users.
• Disadvantages
  • Prone to human error – incorrect references, copy‑paste mistakes.
  • Scalability limits – performance degrades with very large data sets.
  • Limited built‑in stochastic modelling (requires add‑ins such as @RISK).
  • Version‑control can be weak without external file‑management systems.

9.8 Cross‑Topic Links (Validation, Documentation & Communication)

• Validation (Topic 8 – Spreadsheets)
  • Use Data Validation to restrict input ranges (e.g., only positive numbers).
  • Protect cells that contain formulas before running What‑If or Goal Seek.
  • Employ formula‑auditing tools (Trace Precedents/Dependents, Error Checking) to ensure the model behaves as expected.
• Documentation
  • Maintain a separate “Assumptions” sheet or a comment block that records:
    • Source of data and any estimates used.
    • Explanation of each input variable.
    • How scenarios or probability distributions were constructed.
  • Number each scenario (e.g., Scenario 1 – Optimistic, Scenario 2 – Pessimistic).
• Communication
  • Present findings with clear headings, labelled charts, and a concise executive summary that answers the original business question.
  • Include a “Key Take‑aways” box that highlights the most influential variables and any recommendations.

9.9 Modelling Process – Flow‑Chart (textual diagram)

Define Objective → Identify Inputs & Assumptions → Build Spreadsheet Model
      ↓                         ↓                         ↓
Validate Formulas & Protect Cells → Choose Analysis Tool
      ↓                         ↓                         ↓
What‑If (Scenario Manager / Data Table)   OR   Goal Seek
      ↓                         ↓
Run Analysis → Record Results → Interpret & Document
      ↓
Communicate Findings (charts, summary, recommendations)

9.10 Summary Checklist for the Exam

• Identify the key input(s) and the KPI you need to analyse.
• Choose the appropriate technique:
  • What‑If Analysis for multiple scenarios, sensitivity work, or risk assessment.
  • Goal Seek when a single target result is given.
• Set up the model correctly – use absolute/relative references wisely, apply data validation, and protect formula cells.
• Document all assumptions, ranges, and the rationale for each scenario or distribution.
• Validate results by checking a sample calculation manually or with a quick “what‑if” test.
• Interpret the outcomes, highlight the most influential variables, and make clear, concise recommendations.