Advanced Spreadsheet II – SS2 Digital Technologies Lesson Note (First Term)

A financial model is a spreadsheet that summarises a business’s income and expenses to help with big decisions. It has inputs you can change, calculations that link them, and outputs that show profit or loss. Excel’s What-If Analysis tools, Scenario Manager, Goal Seek and Data Tables, help you test different situations.

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?

How does a big company like MTN, or a small bakery down the street, decide whether to open a new branch? They don’t just “guess”. They use financial models.

Think of a financial model as a “Digital Crystal Ball”. It is a spreadsheet where you plug in your current costs and expected sales to see if you will be a millionaire or go broke in five years.

A financial model is a summary of a business’s income and expenses used to make big decisions.

What are the three main parts of a financial model?

1. Inputs (the “What Ifs”): These are things you can change, like the price of one loaf of bread or the cost of flour.
2. Calculations: The formulas that link everything together (e.g. Total Sales = Price × Quantity).
3. Outputs (the results): The final answer. Does the business make 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 scenario analysis?

In real life, things don’t always go as planned. The price of fuel might go up, or a competitor might open a shop next door. Scenario analysis is the process of changing your inputs to see what happens in different situations.

What are the three standard scenarios?

• Best case: Everything goes perfectly. Sales are high, and costs are low.
• Base case: The most likely outcome. 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?

Instead of changing every number by hand, use the Scenario Manager. You will find it under the Data tab > What-If Analysis. It lets you save different sets of numbers and switch between them with one click.

What is Goal Seek?

Usually, we enter numbers to see the result. But what if you already know the result you want, and you don’t know how to get there? Goal Seek works backward from a target.

Example: Imagine you are selling meat pies. Your cost is ₦200 per pie. Your goal is to make ₦50,000 profit this month. You can use Goal Seek to tell Excel: “Find out how many pies I must sell to reach exactly ₦50,000.”

What are the steps for using Goal Seek?

1. Go to Data > What-If Analysis > Goal Seek.
2. Set Cell: Select your profit cell.
3. To Value: Type 50000.
4. By Changing Cell: Select your sales quantity cell.
5. Click OK, and Excel will “count” until it finds the answer.

What is a Data Table?

A Data Table is a grid that shows you many results at once. Instead of trying one price at a time, you can create a table that shows:

• Profit if price is ₦100
• Profit if price is ₦150
• Profit if price is ₦200

It is like looking at a multiplication table, but for your business profits.

Quick reference: which tool should you use?

Class activity: the popcorn business

Imagine we are setting up a popcorn stand for Inter-House Sports.

1. Inputs: Cost of corn, cost of sugar, price per bag.
2. The goal: Use Goal Seek to find out how many bags we must sell to pay back the ₦5,000 we borrowed for the machine.
3. Scenario: What happens to our profit if the price of sugar doubles? (Worst case).

Evaluation

1. What is a financial model? Name its three main parts.
2. What is scenario analysis? Name the three standard scenarios.
3. Where do you find Scenario Manager and Goal Seek in Excel?
4. State the steps for using Goal Seek.
5. 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.