Advanced Spreadsheet II - SSS 2 note

ADVANCED SPREADSHEET II

Subject: Digital Technologies
Class: SSS 2
Term: First Term
Week: 4
Topic: Advanced Spreadsheet II
Duration: 1 Week

LEARNING OBJECTIVES

By the end of the lesson, students should be able to:

1. Develop simple financial models.
2. Perform scenario analysis using spreadsheet tools.
3. Interpret results from different financial scenarios.
4. Apply spreadsheet tools to simple financial planning tasks.

1. INTRODUCTION TO FINANCIAL SPREADSHEETS

Financial spreadsheet: A spreadsheet used to organize, calculate and analyse financial information such as income, expenses, savings, costs and projected results.

Spreadsheet applications such as Microsoft Excel can be used to create simple financial models. A financial model allows a user to represent a financial situation using data, formulas and assumptions.

For example, a student may create a spreadsheet to estimate monthly income and expenses, while a small business may use one to estimate sales, costs and expected profit.

2. FINANCIAL MODELS

Financial model: A structured spreadsheet that represents a financial situation using data, assumptions, formulas and calculations to estimate possible results.

Examples of Simple Financial Models

• Personal monthly budget.
• School event budget.
• Small business sales and expense model.
• Savings plan.
• Cost and profit estimate.

Basic Components of a Financial Model

Component	Meaning
Income	Money received or expected to be received.
Expenses	Money spent or expected to be spent.
Assumptions	Values or conditions used as the basis for calculations.
Formulas	Calculations used to produce results.
Outputs	The results produced by the model.

3. CREATING A SIMPLE FINANCIAL MODEL

A simple financial model can be created by entering financial information into a spreadsheet and using formulas to calculate the required results.

Example: Monthly Budget

Item	Amount (₦)
Monthly Income	100,000
Food	25,000
Transport	15,000
Education	10,000
Other Expenses	10,000

The total expenses can be calculated using a formula such as:

Formula: =SUM(B3:B6)

The remaining amount can then be calculated by subtracting total expenses from income:

Formula: =B2-B7

The model can therefore show how much money remains after the listed expenses have been deducted.

4. FINANCIAL PLANNING WITH SPREADSHEETS

Financial planning is the process of organizing financial information and deciding how available money may be used to achieve planned objectives.

A spreadsheet can help a person or organization plan how money will be received and spent. It can also make it easier to compare expected income with expected expenses.

Uses of Spreadsheets in Financial Planning

• Preparing budgets.
• Recording income and expenses.
• Planning savings.
• Estimating future costs.
• Comparing planned and actual expenses.
• Estimating possible financial outcomes.

5. SCENARIO ANALYSIS

Scenario analysis is the process of examining how different sets of assumptions or conditions can affect the result of a financial model.

Instead of considering only one possible situation, scenario analysis allows the user to examine different possibilities.

For example, a business may want to know what could happen if sales are high, normal or low. Each situation represents a different scenario.

Examples of Scenarios

Scenario	Possible Situation
Best Case	Higher sales and lower expenses.
Expected Case	Normal or expected sales and expenses.
Worst Case	Lower sales and higher expenses.

Important: Scenario analysis does not guarantee what will happen in the future. It allows users to examine possible results based on different assumptions.

6. WHAT-IF ANALYSIS

What-if analysis is the process of changing one or more input values in a spreadsheet to see how the changes affect the calculated results.

For example, if a business model estimates profit based on sales and expenses, the user can change the expected sales figure and observe how the estimated profit changes.

Simple What-If Example

Input	Original Value	Changed Value
Sales	₦200,000	₦250,000
Expenses	₦120,000	₦120,000
Estimated Profit	₦80,000	₦130,000

The example shows that when sales increase while expenses remain unchanged, the estimated profit also increases.

7. SPREADSHEET TOOLS FOR WHAT-IF ANALYSIS

Spreadsheet applications provide tools that can help users examine different possible outcomes. One commonly used group of tools is the What-If Analysis feature in Microsoft Excel.

Examples of What-If Analysis Tools

Tool	Purpose
Scenario Manager	Stores and compares different sets of input values.
Goal Seek	Finds an input value required to achieve a specified result.
Data Table	Shows how changes in one or more inputs affect calculated results.

8. SCENARIO MANAGER

Scenario Manager is a spreadsheet tool used to create, store and compare different sets of values in a worksheet.

For example, a business can create separate scenarios for low, normal and high sales. The scenarios can then be compared to see their possible effects on profit.

General Steps for Creating Scenarios

1. Create a financial model with input cells and formulas.
2. Identify the cells whose values may change.
3. Open the spreadsheet's What-If Analysis tools.
4. Create a scenario and enter the relevant values.
5. Create additional scenarios with different values.
6. Compare the results produced by the scenarios.

9. GOAL SEEK

Goal Seek is a spreadsheet tool used to determine the input value needed to produce a specified result in a formula.

For example, if a student wants to know the sales amount required to achieve a particular profit, Goal Seek can be used to find the required sales value when the other relevant values are provided.

10. COMPARING POSSIBLE OUTCOMES

One important purpose of scenario analysis is to compare different possible outcomes. A spreadsheet can place the results of different scenarios side by side for easier comparison.

Scenario	Sales	Expenses	Estimated Profit
Low Sales	₦150,000	₦120,000	₦30,000
Expected Sales	₦200,000	₦120,000	₦80,000
High Sales	₦250,000	₦120,000	₦130,000

From the table, students can compare the estimated profit under each set of assumptions. The comparison is based on the values entered into the financial model.

11. CLASS PRACTICAL ACTIVITY

Students should create a simple spreadsheet model for a small school event. Assume that the expected income and expenses are as follows:

Item	Amount (₦)
Expected Income	200,000
Venue	40,000
Food	50,000
Decoration	20,000
Transport	15,000

Students should:

1. Enter the information into a spreadsheet.
2. Calculate total expenses.
3. Calculate the expected balance.
4. Create a different scenario by increasing the expected income.
5. Create another scenario by increasing one or more expenses.
6. Compare the resulting balances.
7. Explain how the changes affected the financial outcome.

12. ADVANTAGES OF USING SPREADSHEETS FOR FINANCIAL PLANNING

• They make calculations faster.
• They reduce repetitive manual calculations.
• They make financial information easier to organize.
• They allow different scenarios to be compared.
• They make it easier to identify the effect of changing assumptions.
• They help users present financial information clearly.

13. SUMMARY

A financial model uses spreadsheet data, assumptions and formulas to represent a financial situation. Financial models can be used for budgeting, savings planning, cost estimation and other financial planning activities.

Scenario analysis examines different possible situations, while what-if analysis changes input values to observe their effects on results. Spreadsheet tools such as Scenario Manager, Goal Seek and Data Tables can assist in analysing and comparing possible outcomes.

14. EVALUATION

1. What is a financial model?
2. State four examples of financial models.
3. What is scenario analysis?
4. What is meant by what-if analysis?
5. State three spreadsheet tools that can be used for what-if analysis.
6. What is the purpose of Scenario Manager?
7. What is Goal Seek used for?
8. State four uses of spreadsheets in financial planning.
9. Why is it useful to compare different financial scenarios?
10. Explain how changing an input value can affect the result of a financial model.

15. ASSIGNMENT

1. Define a financial model and explain four of its components.
2. Differentiate between scenario analysis and what-if analysis.
3. Explain the functions of Scenario Manager, Goal Seek and Data Table.
4. Create a simple monthly budget spreadsheet showing income, expenses and balance.
5. Using your budget, create two different scenarios and explain how the results differ.

16. QUICK QUIZ

1. What is a financial model?

A. A computer game B. A spreadsheet representing a financial situation C. A word-processing document D. A web browser

2. What does scenario analysis examine?

A. Different possible situations B. Computer hardware only C. Internet speed D. Printer settings

3. Which tool can find an input value needed to achieve a specified result?

A. Goal Seek B. Spell Check C. Mail Merge D. Word Count

4. Which tool is used to store and compare different sets of input values?

A. Scenario Manager B. Page Layout C. WordArt D. Format Painter

5. What is the main purpose of financial planning with spreadsheets?

A. To organize and analyse financial information B. To play games C. To design computer hardware D. To browse social media