executive MBA subject 07

Quantitative Analysis

Year 1, term B. 6.5 ECTS, about 162 hours, 8 parts. Part 1 is free.

task, condition, standard

What you will be able to do

Task, condition and standard

Task

Build a spreadsheet model that forecasts one business figure and tests how sensitive it is.

Condition

Given two years of monthly data and a spreadsheet tool.

Standard

The model is checked, the forecast method is justified, and the three most sensitive inputs are ranked.

Who it is for: Managers who must turn data into decisions with spreadsheets and basic statistics.

how this subject works

Free entry, voice and your learning style

Part 1 is free to try. Read it, listen to it and answer it before you decide. Join the programme, or this subject on its own, to open the other parts.

Listen. Every part has a Listen button that reads it aloud.
Speak or type. Answer each part by talking (press Speak) or by typing. Your answers stay on this device only.
Learn your way. Every part has a See, Hear, Do and Read-and-write route. Not sure which suits you? Take the eight-question learning-style check.

8 parts

The parts, with worked examples

Part 1 Spreadsheet modelling bootcamp 20 hApply: build

A good model keeps inputs, calculations and outputs apart, so anyone can follow and check it.

Inputs in one coloured block
No typed numbers inside formulas
Add a check cell that must show zero

Worked example: Worked-example figures, in your currency. Revenue = units x price: 1,200 x 25 = 30,000. Change price to 26 in the input block and revenue becomes 31,200 with no formula touched.

Activity: Build a one-sheet profit model with separate inputs, calculations and outputs.

Standard: No hard-coded numbers in formulas and a check cell that reads zero.

Learn your way

See. Sketch "spreadsheet modelling bootcamp" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Build a one-sheet profit model with separate inputs, calculations and outputs.

Part 2 Optimisation 20 hApply: optimise

Optimisation finds the best choice within limits, such as the product mix that earns most with limited machine hours.

Define the goal, the choices and the limits
Rank by profit per unit of the scarce resource
Check the answer stays inside every limit

Worked example: Worked-example figures, in your currency. Product A earns 40 and uses 2 machine hours (20 per hour); B earns 30 and uses 1 hour (30 per hour). With 100 hours and demand for B capped at 60 units: make 60 B (60 hours), then 20 A (40 hours). Profit = 1,800 + 800 = 2,600.

Activity: Find the best mix for three products with one scarce resource.

Standard: Ranking by profit per scarce unit and total profit both correct.

Learn your way

See. Sketch "optimisation" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Find the best mix for three products with one scarce resource.

Part 3 Decisions under uncertainty 20 hEvaluate: compare options

When outcomes are uncertain, compare options by expected value and by the worst case you can live with.

Expected value = sum of chance x payoff
Decision trees show choices and chance events
The value of better information is the gain it allows

Worked example: Worked-example figures, in your currency. Launch: 60% chance of +500, 40% chance of -300. EV = 300 - 120 = +180. Do not launch: 0. Launch has the higher EV, but check you can survive the -300.

Activity: Draw a decision tree for a two-option choice and calculate both EVs.

Standard: Tree complete, EVs correct, and the worst case stated.

Learn your way

See. Sketch "decisions under uncertainty" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Draw a decision tree for a two-option choice and calculate both EVs.

Part 4 Descriptive statistics 20 hUnderstand: summarise

Mean, median and spread summarise data. The median is safer when a few values are extreme.

Mean = total / count
Median = middle value
Standard deviation measures typical distance from the mean

Worked example: Worked-example figures, in your currency. Delivery days: 2, 3, 3, 4, 18. Mean = 30 / 5 = 6. Median = 3. One late delivery pulls the mean up; the median shows the normal case.

Activity: Summarise a set of 20 values with mean, median and range.

Standard: All three correct and one sentence on which is more useful here.

Learn your way

See. Sketch "descriptive statistics" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Summarise a set of 20 values with mean, median and range.

Part 5 Sampling techniques 20 hApply: sample

A sample lets you learn about many from a few, if it is chosen fairly and is large enough.

Random sampling gives everyone the same chance
Stratified sampling makes sure key groups are included
Margin of error shrinks as the sample grows

Worked example: Worked-example figures, in your currency. Staff are 70% in operations and 30% in offices. A stratified sample of 200 takes 140 from operations and 60 from offices.

Activity: Design a stratified sample of 300 for a population with three groups.

Standard: Group sizes match the population shares and add to 300.

Learn your way

See. Sketch "sampling techniques" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Design a stratified sample of 300 for a population with three groups.

Part 6 Normal distribution and prediction 20 hApply: predict

Many measures cluster around an average in a bell shape. About 68% fall within one standard deviation and 95% within two.

68% within 1 standard deviation
95% within 2
Use it to set ranges and safety stock

Worked example: Worked-example figures, in your currency. Daily demand averages 200 with a standard deviation of 20. On about 95% of days demand is between 160 and 240.

Activity: Set a 95% range for a measure from your work.

Standard: Range correct and one use of it explained.

Learn your way

See. Sketch "normal distribution and prediction" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Set a 95% range for a measure from your work.

Part 7 Sensitivity of key business variables 20 hAnalyse: test

Sensitivity analysis changes one input at a time to see which ones move the result most. Effort then goes to the inputs that matter.

Change each input by the same percentage
Rank inputs by the change in the result
Show it as a tornado chart

Worked example: Worked-example figures, in your currency. Profit is 100. A 10% fall in price cuts profit by 60; a 10% rise in costs cuts it by 40; a 10% fall in volume cuts it by 25. Price is the most sensitive input.

Activity: Run a 10% sensitivity on four inputs of your model.

Standard: Four results correct and inputs ranked.

Learn your way

See. Sketch "sensitivity of key business variables" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Run a 10% sensitivity on four inputs of your model.

Part 8 Regression and forecasting 20 hApply: forecast

Regression fits a line through data to show how one figure moves with another. Use it to forecast, but check it makes sense.

y = a + b x
R squared shows how much of the change the line explains
Do not forecast far beyond your data

Worked example: Worked-example figures, in your currency. Sales = 50 + 4 x advertising. With advertising of 30, forecast sales = 50 + 120 = 170.

Activity: Fit a line to 12 months of data and forecast the next 3 months.

Standard: Line fitted, forecasts calculated and one limit of the method stated.

Learn your way

See. Sketch "regression and forecasting" as a picture: boxes, arrows and the key number.
Hear. Press Listen, or explain the worked example aloud to someone.
Do. Do the activity with your own work or a practice case.
Read and write. Write the idea in three lines in your own words below.

Your answer. Fit a line to 12 months of data and forecast the next 3 months.

ten checks

Check yourself

Say or write your answer first, then read the model answer.

Q1. Why keep inputs apart from formulas?
Model answer: So anyone can change and check them without breaking the model. (Covered in part 1: Spreadsheet modelling bootcamp.)
Q2. Product B earns 30 per machine hour, A earns 20. Which first?
Model answer: B. (Covered in part 2: Optimisation.)
Q3. 60% of +500 and 40% of -300. EV?
Model answer: +180. (Covered in part 3: Decisions under uncertainty.)
Q4. Why check the worst case as well as EV?
Model answer: You must be able to survive the bad outcome. (Covered in part 3: Decisions under uncertainty.)
Q5. Values 2, 3, 3, 4, 18. Mean and median?
Model answer: Mean 6, median 3. (Covered in part 4: Descriptive statistics.)
Q6. 70/30 split, sample 200. How many from each group?
Model answer: 140 and 60. (Covered in part 5: Sampling techniques.)
Q7. Share of values within 2 standard deviations?
Model answer: About 95%. (Covered in part 6: Normal distribution and prediction.)
Q8. Mean 200, standard deviation 20. 95% range?
Model answer: 160 to 240. (Covered in part 6: Normal distribution and prediction.)
Q9. Which input is most sensitive in the example?
Model answer: Price. (Covered in part 7: Sensitivity of key business variables.)
Q10. Sales = 50 + 4 x advertising. Advertising 30. Forecast?
Model answer: 170. (Covered in part 8: Regression and forecasting.)

slides L1 to L7

The seven-level ladder, slide by slide

Slide 1: L1 Numbers in business. Why data matters. Speaker note: Say: put "numbers in business" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 2: L2 Models. Inputs, calculations, outputs. Speaker note: Say: put "models" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 3: L3 Choosing best. Optimisation with limits. Speaker note: Say: put "choosing best" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 4: L4 Uncertainty. Expected value and trees. Speaker note: Say: put "uncertainty" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 5: L5 Describing data. Mean, median, spread, samples. Speaker note: Say: put "describing data" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 6: L6 Predicting. Normal ranges and regression. Speaker note: Say: put "predicting" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.
Slide 7: L7 Advising. Sensitivity tested advice for the board. Speaker note: Say: put "advising" in one sentence and give one example from work. Check: ask one learner to say it back in their own words. Mistake: moving on before the numbers are clear.

how it is judged

Marking rubric

Marking rubric (Not yet, Meets, Exceeds)
Criterion	Not yet	Meets	Exceeds
Accuracy	Several figures or facts are wrong.	Main figures are correct and the working is shown.	Figures are correct and checked a second way.
Use of quantitative analysis tools	Tools are named but not used.	The right tools are used for each step.	Tools are adapted to the case and their limits are stated.
Recommendation	No clear answer.	A clear answer backed by the analysis.	A clear answer that also says what would change it.
Communication	Hard to follow.	Answer first, clear and short.	Clear, short and persuasive under questions.

jobs this leads to

Where this takes you

Business analyst. Builds models and reports.
Data analyst. Summarises and forecasts data.
Planning manager. Uses forecasts to set budgets.

language

Choose your language

This subject is in English first. Choose any language and the page is translated for you by machine, with the English kept as the official text.

Keep going

All subjects Previous: Financial Management Next: Corporate Social Responsibility

Draft for review. Not live. Credit values are aligned with the European credit system for orientation only and are not an accreditation.