Advanced Spreadsheet II – SS2 Digital Technologies Lesson Note (First Term)
Lesson note at a glance
- Class
- SS2
- Subject
- Digital Technologies
- Term
- First Term
- Reading time
- 6 min
A financial model is a spreadsheet that summarises a business’s income and expenses to help with big decisions.
Behavioural objectives
By the end of the lesson, students should be able to:
- explain what a financial model is and name its three main parts;
- describe scenario analysis and the three standard scenarios;
- find Scenario Manager and Goal Seek in Excel;
- use Goal Seek to work backward from a target;
- explain what a Data Table shows.
What is financial modelling?
Financial modelling is the use of a spreadsheet to test whether a business plan will make a profit or a loss.
- Who uses it: a big company like MTN or a small bakery deciding whether to open a new branch.
- The “Digital Crystal Ball”: you enter current costs and expected sales to see how the business may do over the next few years.
- Main benefit: decisions rest on calculated figures, not on guesses.
- Main tools in Excel: the What-If Analysis tools, which you will meet in this lesson.
What are the three main parts of a financial model?
A financial model has three main parts: inputs, calculations and outputs.
- Inputs (the “What Ifs”): numbers you can change, such as the price of one loaf of bread or the cost of flour.
- Calculations: the formulas that link everything together, for example Total Sales = Price × Quantity.
- Outputs (the results): the final answer, which shows whether the business makes a profit or a loss.
- Teacher’s side-note: a good model is like a building. If the foundation (the inputs) is wrong, the whole house (the result) will fall down.
What is What-If Analysis in Excel?
What-If Analysis is a group of Excel tools that show how the result changes when you change the inputs.
- Location: Data tab > What-If Analysis.
- Scenario Manager: saves different sets of inputs and switches between them.
- Goal Seek: finds the input needed to reach a target result.
- Data Table: shows many results in one grid.
What is scenario analysis?
Scenario analysis is the process of changing your inputs to see what happens in different situations.
- Why it is needed: things do not always go as planned.
- Examples of changes: the price of fuel goes up, or a competitor opens a shop next door.
What are the three standard scenarios?
There are three standard scenarios: best case, base case and worst case.
- Best case: everything goes perfectly. Sales are high and costs are low.
- Base case: the most likely outcome. It is business as usual.
- Worst case: everything goes wrong. Costs are high and there are very few customers.
How do you do scenario analysis in Excel?
You use Scenario Manager, which saves different sets of numbers and lets you switch between them with one click.
- Click the Data tab > What-If Analysis > Scenario Manager.
- Click Add and type a name, such as Best case.
- In Changing cells, select the input cells you want to vary.
- Click OK and type the values for that scenario.
- Repeat for the base case and the worst case.
- Select a scenario and click Show to see its results in the model.
What is Goal Seek?
Goal Seek is an Excel tool that works backward from a target result to find the input needed to reach it.
- Normal use of a spreadsheet: you enter numbers and read the result.
- Goal Seek: you already know the result you want, and Excel finds the number that gives it.
- Rule 1: the Set cell must contain a formula, such as the profit cell.
- Rule 2: the Changing cell must hold a typed number, not a formula.
What is an example of Goal Seek?
A meat pie seller uses Goal Seek to find how many pies must be sold to make ₦50,000 profit.
- Cost: ₦200 per pie.
- Target: ₦50,000 profit this month.
- Assumed selling price: ₦300 per pie, so profit per pie is ₦100.
- Profit formula: (Price − Cost) × Quantity.
- Question to Excel: “Find out how many pies I must sell to reach exactly ₦50,000.”
- Answer: 500 pies, because ₦100 × 500 = ₦50,000.
What are the steps for using Goal Seek?
- Go to Data > What-If Analysis > Goal Seek.
- Set Cell: select your profit cell.
- To Value: type 50000.
- By Changing Cell: select your sales quantity cell.
- Click OK, and Excel will “count” until it finds the answer.
What is a Data Table?
A Data Table is a grid that shows many results at once for different values of an input.
- Instead of: trying one price at a time.
- You get: one table with a result for every price.
- Comparison: it is like a multiplication table, but for business profits.
A Data Table can show results like these:
- Price of ₦100: the profit at that price.
- Price of ₦150: the profit at that price.
- Price of ₦200: the profit at that price.
- Negative result: a figure below zero means a loss.
What are the types of Data Table?
There are two types of Data Table: one-variable and two-variable.
- One-variable: changes one input, such as price, and shows the profit for each value.
- Two-variable: changes two inputs, such as price and quantity, in a grid of rows and columns.
How do you make a one-variable Data Table?
- Type the input values (for example the prices) down one column.
- In the cell one row above and one column to the right, link to the result cell, such as profit.
- Select the whole range, including the values and the formula cell.
- Click Data > What-If Analysis > Data Table.
- In Column input cell, select the price cell and click OK.
Which tool should you use?
Each tool answers a different question, as the table shows.
| Tool | Simple definition | When to use it |
|---|---|---|
| Financial Model | A digital version of a business plan. | When starting a new business. |
| Scenario Manager | Preparing for “best” or “worst” luck. | When the economy is unstable. |
| Goal Seek | Working backward from a target. | When you have a specific profit goal. |
| Data Table | A grid of results for many input values. | When you want to compare many prices at once. |
| What-If Analysis | The “umbrella” term for all these tools. | Any time you want to predict the future. |
Class activity: the popcorn business
The class sets up a popcorn stand for Inter-House Sports and builds a model for it.
- Inputs: cost of corn, cost of sugar, price per bag.
- The goal: use Goal Seek to find how many bags must be sold to pay back the ₦5,000 borrowed for the machine.
- Scenario: find the profit if the price of sugar doubles (worst case).
Evaluation
- What is a financial model? Name its three main parts.
- What is scenario analysis? Name the three standard scenarios.
- Where do you find Scenario Manager and Goal Seek in Excel?
- State the steps for using Goal Seek.
- What does a Data Table show?
Assignment
A bakery sells loaves of bread. List two inputs, one calculation and one output for its financial model. Then describe a best case, a base case and a worst case for the bakery.
This term's Digital Technologies topics
SS2 First Term · 7 topics

