Advanced Spreadsheet II SS2 Digital Technologies Lesson Note

Have you ever wondered how a big company like MTN or a small bakery down the street decides 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’s 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.

What is Financial Modeling?

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

The Three Main Parts:

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., TotalSales=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 (Inputs) is wrong, the whole house (The Result) will fall down!

Scenario Analysis: Planning for “Rainy Days”

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.

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. High costs and very few customers.

How to do it in Excel (The Scenario Manager):

Instead of manually changing every number, Excel has a tool called Scenario Manager (found under the Data tab > What-If Analysis). It lets you save different sets of numbers and switch between them with one click.

Goal Seek: Working Backward

Usually, we enter numbers to see the result. But what if you already know the result you want, but you don’t know how to get there?

Example: Imagine you are selling meat pies. You know 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.”

The Steps:

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!

Data Tables: The “Comparison” Tool

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’s like looking at a multiplication table, but for your business profits!

Summary Checklist for Success

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.

Scenario: What happens to our profit if the price of sugar doubles? (Worst Case).