ADVANCED SPREADSHEET II: FINANCIAL MODELS AND SCENARIO ANALYSIS — NERDC Curriculum Aligned
INTRODUCTION
Imagine you are running a small business selling zobo drinks or phone accessories near your school. Every week, you spend money on raw materials, transportation, and packaging. At the end of the month, you want to know: did I make a profit? And what if the price of zobo leaves goes up next month — will you still break even? These are exactly the kinds of questions that financial models and scenario analysis in spreadsheets are designed to help you answer.
In today's world, major Nigerian companies like Dangote Group, MTN Nigeria, and Access Bank all rely on spreadsheet-based financial models to plan their finances, track performance, and prepare for the future. Even small traders in Onitsha Main Market use simple versions of these tools. As an SSS 2 student, learning how to build financial models and run scenario analysis puts you ahead of your peers and prepares you for rewarding careers in accounting, business, engineering, and data analysis.
This lesson is a continuation of Advanced Spreadsheet I. Here, we move beyond basic formulas into the world of financial modelling — the process of using numbers, formulas, and logic to represent real financial situations — and scenario analysis, which helps you compare different outcomes based on changing conditions.
LEARNING OBJECTIVES
By the end of this lesson, students should be able to:
- Define a financial model and explain its purpose in business and personal finance.
- Identify the key components of a simple financial model in a spreadsheet.
- Describe what scenario analysis means and explain how it is used in decision-making.
- Demonstrate how to use Excel's What-If Analysis tools, including Goal Seek and Scenario Manager.
- Construct a simple budget model and apply scenario analysis to forecast outcomes.
- Evaluate the ethical and responsible use of financial data in spreadsheets.
WHAT IS A FINANCIAL MODEL?
A financial model is a spreadsheet built to represent how money flows in a business, project, or personal situation. It uses formulas, numbers, and logic to show income, expenses, profit, and losses — and to help predict what might happen in the future.
Think of it this way: when your school principal wants to plan the school's annual budget — how much to spend on new desks, teachers' salaries, and textbooks — they need a financial model. It helps them see at a glance whether the money available will be enough to cover all their plans.
Key Definition: A financial model is a structured spreadsheet that uses data and formulas to simulate a real-life financial situation, helping users to plan, forecast, and make informed decisions.
Core Components of a Financial Model
Every good financial model contains the following parts:
Input Section — Where you enter your raw data: prices, quantities, salaries, interest rates, and so on. These are the figures that can change.
Calculation Section — Where formulas process the inputs. For example: Total Revenue = Price × Quantity Sold.
Output Section — Where the results are displayed, such as total profit, net loss, or projected savings.
Assumptions Section — A written list of the conditions the model is based on, such as "fuel price remains constant" or "school fees increase by 5% per term."
A good financial model is clean, well-labelled, and easy to understand — even by someone who did not build it.
BUILDING A SIMPLE FINANCIAL MODEL IN EXCEL
Let us build a practical example. Imagine Chidi runs a small printing business near his school in Enugu. He prints flyers, handouts, and posters for students and small businesses. Here is how Chidi's simple income model looks in a spreadsheet:
Item | Value | Formula Used Price per flyer (₦) | 50 | Input Number of flyers sold | 200 | Input Total Revenue (₦) | 10,000 | =B2*B3 Cost of paper and ink (₦) | 3,500 | Input Transport (₦) | 500 | Input Total Expenses (₦) | 4,000 | =B5+B6 Net Profit (₦) | 6,000 | =B4-B7
This is a basic but real financial model. Notice how changing the price per flyer or the number sold immediately changes the net profit. That is the power of a well-built model — it is dynamic and responds instantly to change.
Important Spreadsheet Functions for Financial Modelling
The following functions are commonly used when building financial models:
SUM — Adds a range of numbers. Example: =SUM(B5:B10)
IF — Returns different results based on a condition. Example: =IF(B8>0, "Profit", "Loss")
PMT — Calculates loan or mortgage payments. Useful for financial planning.
NPV (Net Present Value) — Evaluates the value of an investment over time.
AVERAGE — Computes the average of a set of numbers, useful for monthly expense tracking.
VLOOKUP / XLOOKUP — Searches for data across a table, useful in larger financial models.
WHAT IS SCENARIO ANALYSIS?
Scenario analysis is the process of changing certain input values in your financial model to see how the output changes. In simple terms, it answers the question: "What will happen if...?"
For example, Chidi might ask: "What will my profit look like if the price of printing paper goes up by 20% next month?" Or: "What if I sell 300 flyers instead of 200?" Scenario analysis lets him test these different situations — called scenarios — without touching or destroying his original model.
Real-Life Connection: The Nigerian government uses scenario analysis when preparing the national budget. They model situations like: "What if crude oil prices fall to $50 per barrel?" or "What if we increase VAT by 2%?" This helps them prepare for both favourable and difficult economic conditions.
Types of Scenarios
Best-Case Scenario — The most favourable outcome. Everything works perfectly: sales are high and costs are low.
Worst-Case Scenario — The most negative outcome. Sales drop, costs rise, and profits disappear.
Base-Case Scenario — The most realistic and likely outcome. Neither too optimistic nor too pessimistic. This is where your main model normally starts.
Every smart business person or student preparing a business plan considers all three scenarios. It is not about being fearful — it is about being prepared.
WHAT-IF ANALYSIS TOOLS IN MICROSOFT EXCEL
Microsoft Excel provides three built-in What-If Analysis tools. You will find them under the Data tab, then click What-If Analysis.
- Scenario Manager
This tool allows you to create, save, and switch between different named scenarios within the same spreadsheet. Here is how to use it:
Step 1 — Go to Data, click What-If Analysis, then select Scenario Manager. Step 2 — Click Add to create a new scenario. Give it a name like "Best Case." Step 3 — Select the cells that will change (your input cells). Step 4 — Enter the new values for that scenario and click OK. Step 5 — Repeat the process for "Base Case" and "Worst Case." Step 6 — Click Show to apply any scenario and instantly see the result in your spreadsheet. Step 7 — Click Summary to generate a side-by-side comparison table of all your scenarios.
- Goal Seek
Goal Seek works in reverse. Instead of asking "what is the profit if I sell 200 flyers?", it asks: "How many flyers must I sell to earn a profit of ₦10,000?"
Step 1 — Go to Data, click What-If Analysis, then select Goal Seek. Step 2 — In the Set Cell box, select your output cell (e.g., Net Profit). Step 3 — In the To Value box, enter your target (e.g., 10000). Step 4 — In the By Changing Cell box, select the input you want Excel to adjust (e.g., Quantity Sold). Step 5 — Click OK. Excel automatically calculates the input value needed to reach your target.
- Data Table
A Data Table shows how one or two changing variables affect a formula's result. It creates a grid of values so you can see multiple outcomes at once. For example, you can see the profit for different combinations of price (₦40, ₦50, ₦60) and quantity sold (150, 200, 250) — all displayed in a single table.
PRACTICAL APPLICATIONS IN A NIGERIAN CONTEXT
Financial modelling and scenario analysis are not just for bank managers in Abuja or company executives in Lagos. They are useful in everyday Nigerian life.
Student Entrepreneurs — Students who sell chin-chin, zobo, or phone accessories can track monthly costs and profits, and plan ahead for seasonal changes using a simple Excel model.
School Budget Planning — School administrators model teacher salaries, maintenance costs, and school fees to ensure smooth running of the school throughout the academic year.
Agricultural Business — Farmers in Kano, Ogun, or Benue State can model what happens to their income if rainfall is poor, fertiliser prices rise, or the market price for yam or tomatoes falls.
Government Policy Planning — State and federal governments build financial models when preparing annual budgets, using scenario analysis to plan for low and high oil revenue situations.
Personal Finance — Young Nigerians planning to save for JAMB registration, university fees, or a new device can build a simple savings model to track progress toward their goal.
ADVANTAGES AND DISADVANTAGES OF FINANCIAL MODELLING
Advantages
It helps businesses and individuals make better, data-driven decisions rather than relying purely on guessing.
It saves time by automating complex calculations. Change one input number and everything else updates instantly.
It allows you to test multiple scenarios without risk — you can experiment freely in a spreadsheet without touching real money.
It makes financial information easy to present and share with others, including teachers, employers, or investors.
It builds critical thinking and analytical skills that are valuable across many careers.
Disadvantages
A model is only as good as its data. If you enter wrong figures, you get wrong results. This principle is often called "garbage in, garbage out."
Over-reliance on models can lead to overconfidence. Real life does not always follow predictions, and unexpected events can change everything.
Complex models can be difficult to understand and maintain, especially when the person who originally built them is no longer available.
Financial models can be manipulated to produce misleading results if the person building them is dishonest.
SAFETY AND ETHICAL CONSIDERATIONS
As you develop skills in financial modelling, it is important to use them responsibly. Here are the key ethical guidelines every student and professional must observe:
Accuracy and Honesty — Always use real, accurate data. Manipulating a financial model to show false profits or mislead others is a form of fraud and can carry serious legal consequences.
Data Privacy — When a model contains sensitive information such as someone's salary or a business's income, protect the file with a password and do not share it carelessly.
Transparency — Document your assumptions clearly so that anyone reviewing the model understands exactly what it is based on. Hidden assumptions lead to mistrust and bad decisions.
Digital Security — Save your work regularly, use strong passwords for files containing sensitive data, and always keep backup copies. Losing your file without a backup can mean hours of work gone in seconds.
Intellectual Honesty — If you use a model originally built by someone else, give proper credit. Do not copy and present another person's financial model as your own original work.
CLASSROOM AND HOME ACTIVITIES
Activity 1 — Build a School Canteen Budget Model
Open Microsoft Excel or LibreOffice Calc. Create a simple income and expense model for a school canteen. Include items such as food items sold, selling prices, daily quantities, ingredient costs, and monthly rent. Use SUM and IF formulas to calculate daily profit and monthly profit. Add a column that automatically displays "Profit" or "Loss" using an IF formula.
Activity 2 — Run a Three-Scenario Analysis
Using the canteen model from Activity 1, open the Scenario Manager and create three scenarios: Best Case (20% increase in sales), Base Case (current figures), and Worst Case (30% drop in sales with a 10% rise in costs). Generate a scenario summary table and discuss in class what the canteen owner should do to prepare for the worst-case outcome.
Activity 3 — Goal Seek Exercise
Using a simple sales model, use the Goal Seek tool to set a Net Profit target of ₦50,000. Adjust only the "Units Sold" cell. Record the result. Discuss with your class whether this target is realistic and what other steps a business owner could take to reach that profit figure.
Activity 4 — Home Assignment: Personal Savings Model
Build a personal savings tracker at home. Include your monthly pocket money, planned weekly savings, total savings after three months, and a specific goal — for example, buying a scientific calculator or paying for a JAMB mock examination. Use Goal Seek to find out how much you need to save each week to reach your goal in exactly two months.
ASSESSMENT QUESTIONS
Section A — Objective Questions
-
What is the primary purpose of a financial model in a spreadsheet? A. To create colourful charts B. To simulate and plan financial situations C. To send emails D. To store photographs
-
Which of the following is NOT a recognised type of scenario in scenario analysis? A. Best Case B. Worst Case C. Base Case D. Random Case
-
The Excel tool that works in reverse — finding the required input to achieve a specific result — is called: A. Scenario Manager B. VLOOKUP C. Goal Seek D. Data Validation
-
In financial modelling, the phrase "garbage in, garbage out" means: A. The spreadsheet produces random results B. Wrong input data leads to wrong output results C. The computer crashes when you make errors D. Financial models only work for large companies
-
Under which Excel tab will you find the What-If Analysis tools? A. Home B. Insert C. View D. Data
Section B — Theory Questions
-
Define a financial model. Using a Nigerian example, explain how financial modelling can help a small business owner make better decisions. (6 marks)
-
Explain the difference between Goal Seek and Scenario Manager in Microsoft Excel. Describe a step-by-step process for using either tool to solve a simple financial problem. (8 marks)
-
Discuss three ethical considerations a student or professional must observe when building and sharing financial models. Why is accuracy in financial data important in real-world decision making? (6 marks)
SUMMARY
A financial model is a spreadsheet that represents real financial situations using data and formulas to support planning and decision-making.
Every financial model contains an input section, a calculation section, an output section, and a set of assumptions.
Scenario analysis tests different possible outcomes — best case, worst case, and base case — by changing input values in the model.
Microsoft Excel provides three What-If Analysis tools: Scenario Manager, Goal Seek, and Data Tables.
Goal Seek finds the input value needed to achieve a specific output, while Scenario Manager saves and compares multiple named scenarios side by side.
Financial modelling is widely used in Nigeria — from school budgets and agribusiness planning to personal savings goals and government policy analysis.
Ethical use of financial models requires accuracy, transparency, data privacy, and intellectual honesty.
CONCLUSION
Financial modelling and scenario analysis may sound like complex, advanced terms, but at their core they are simply about using a spreadsheet to think clearly about money — past, present, and future. Every Nigerian student who masters these skills gains a genuine advantage both in school and in life beyond the classroom.
Whether you go on to study economics, engineering, agriculture, education, or start your own business one day, knowing how to build a financial model and analyse scenarios will always be useful. The world today rewards people who can make decisions based on data rather than pure guesswork. And it all starts right here, in your SSS 2 Computer Studies class.
Keep practising with real-life examples around you — your school's tuck shop, a family business, or your personal savings goal. The more you apply what you learn, the more natural and powerful these skills will become.
FREQUENTLY ASKED QUESTIONS (FAQ)
Q1. What is the difference between financial modelling and accounting?
Accounting records what has already happened — past income and expenses. Financial modelling is forward-looking — it uses historical data and assumptions to predict or plan future financial outcomes. Both are important, but modelling is more focused on planning and decision-making.
Q2. Do I need advanced Excel skills to build a financial model?
No. Even a beginner can build a useful financial model using basic functions like SUM, IF, and simple multiplication and subtraction formulas. As your skills grow, you can add more complex functions. What matters most at the start is having a clear structure and accurate data.
Q3. Can I use LibreOffice Calc instead of Microsoft Excel for financial modelling?
Yes, absolutely. LibreOffice Calc is a free alternative to Microsoft Excel and supports most of the same functions, including What-If Analysis tools. For Nigerian students who may not have access to a licensed copy of Microsoft Office, LibreOffice Calc is an excellent option and produces the same quality results.
Q4. Is scenario analysis only useful for businesses?
Not at all. Scenario analysis is useful for anyone who faces decisions involving uncertainty. Students can use it to plan savings goals, families can use it to budget for events like weddings or school fees, and individuals can use it to evaluate whether to take a loan or make an investment.
Q5. How many scenarios should I create in a financial model?
For beginners, three scenarios — best case, base case, and worst case — are usually enough to demonstrate a solid understanding. In professional settings, more detailed scenarios may be required, but at SSS 2 level, three well-considered scenarios are the standard expectation.
Q6. What careers in Nigeria require financial modelling skills?
Financial modelling skills are used in banking, investment analysis, chartered accountancy, business management, project management, government budget planning, agricultural economics, and technology startups. It is one of the most transferable and in-demand skills in the modern Nigerian economy.
Advanced Spreadsheet II, financial models SSS 2, scenario analysis Nigeria, What-If Analysis Excel, Goal Seek Excel, Scenario Manager, NERDC Computer Studies, SSS 2 lesson note, spreadsheet skills Nigeria, financial modelling for students
.jpg)
0 Comments