Analyse Data using Scenarios and Goal Seek
This chapter deals with analysing spreadsheet data using tools such as Consolidate, Subtotal, Scenarios, Multiple Operations and Goal Seek.
✍️ NOTES ONLY • NO TOP 50 TESTSource: NCERT Domestic Data Entry Operator — Class X, 2023–24, Part B, Unit 2, Chapter 4.
🗺️ Chapter Roadmap
- Analysing Data
- Consolidating Data
- Subtotal
- What-if Analysis
- Scenarios
- Creating Scenarios
- Multiple Operations
- Goal Seek
A spreadsheet is often used to store a large amount of data. However, simply storing data is not enough. The data must be analysed to obtain useful information for making effective decisions.
Analysing Data means examining and processing data to obtain useful information that can support decision-making.
Why Analyse Data?
- To obtain useful information from data.
- To summarise large amounts of data.
- To compare information.
- To make effective decisions.
Data analysis helps in obtaining useful information for making effective decisions.
When data is available in different worksheets or different ranges, it may be necessary to combine the data into one summary.
Consolidate is used to combine data from multiple ranges/sources into a summary.
Purpose of Consolidation
- Combine data from different ranges.
- Summarise information.
- Make comparison and analysis easier.
Consolidate → Combine/Summarise Data
The Subtotal feature is used to calculate subtotals for groups of related data.
Purpose of Subtotal
- Organise data into groups.
- Calculate subtotals for each group.
- Make large data sets easier to analyse.
Subtotal is an important data-analysis feature of Calc.
Meaning
Sometimes the input values in a spreadsheet are not fixed. We may want to change the input values and observe how the output changes. This type of analysis is called What-if Analysis.
What-if Analysis is used to examine possible results by changing input values.
What-if Tool
LibreOffice Calc provides a What-if tool to analyse different possibilities.
What-if analysis is used to determine how changing input values affects the result.
What is a Scenario?
A Scenario is a set of values that can be used to analyse different possible situations in a spreadsheet.
Different scenarios can be created by changing the values of selected cells.
Scenario = Different possible set of input values used for What-if Analysis.
Why Use Scenarios?
- To compare different possibilities.
- To study the effect of different inputs.
- To support decision-making.
A scenario is created by selecting the cells whose values will change and assigning a name to the scenario.
Basic Process
- Enter the data and formulas in the worksheet.
- Select the cells containing the values that are to be changed.
- Open the Scenario option from the Data menu.
- Enter a suitable name for the scenario.
- Set the required options.
- Create the scenario.
Scenario analysis allows different sets of values to be stored and compared.
Once scenarios have been created, they can be selected and used to view different results.
Important Concept
A scenario does not permanently change the original set of values. Instead, it allows different possible sets of values to be examined.
Scenarios are associated with What-if Analysis.
The Multiple Operations feature is used when we want to calculate multiple possible results by using different values for variables in a formula.
Multiple Operations can generate different outputs from a formula by using different input values.
Important Terms
- Formula Cell — cell containing the formula.
- Variable/Input Cell — cell whose value is changed.
- Output — calculated result produced by the formula.
Example Concept
Suppose a formula calculates profit based on the number of items sold. If different numbers of items are entered, the corresponding profit can be calculated using Multiple Operations.
The textbook demonstrates Multiple Operations using a formula cell and a variable/input cell.
- Create the worksheet and enter the required values.
- Create the required formula.
- Enter the different possible input values.
- Select: Data → Multiple Operations
- The Multiple Operations dialog box appears.
- Enter the cell address containing the formula in the Formulas box.
- Enter the cell address of the variable used in the formula in the Column input cell box.
- Click OK.
- Calc generates the possible outputs based on the formula.
Data → Multiple Operations
The textbook specifically demonstrates entering the formula cell and the variable cell before clicking OK.
Formula Cell → Formula
Input Cell → Variable
Result → Multiple Outputs
When the same formula cell address needs to be referred to repeatedly, absolute cell referencing can be used.
Example
Here, the cell reference remains fixed because both the column and row are preceded by the $ symbol.
The textbook example uses $B$5 as an absolute cell reference for the formula cell.
What is Goal Seek?
Goal Seek is used to find the input value required to obtain a specific desired output.
Normally, we enter input values and use a formula to calculate the output. But sometimes we already know the desired output and want to find the input value that will produce it. That is where Goal Seek is useful.
Simple Example
Suppose a student wants to achieve an average of 70 and has marks for four subjects. Goal Seek can be used to determine the marks required in the fifth subject to achieve the desired average.
Goal Seek finds the input value required to achieve a specified output.
| Component | Meaning |
|---|---|
| Formula Cell | The cell containing the formula whose result needs to reach the desired value. |
| Variable Cell | The cell whose value is changed by Goal Seek. |
| Target Value | The desired result/output. |
Formula Cell + Variable Cell + Target Value → Goal Seek
The textbook gives the following procedure for performing Goal Seek.
- Enter the required values in the worksheet.
- Write the formula in the cell where the calculation is required.
- Place the cursor in the formula cell.
- Select: Tools → Goal Seek
- The Goal Seek dialog box appears.
- The Formula cell box contains the formula cell.
- Select the Variable cell — the cell whose value needs to be changed.
- Enter the desired result in the Target value box.
- Click OK.
Tools → Goal Seek
Problem
A student has marks in four subjects and wants to find the marks required in the fifth subject to obtain a target average of 70.
Formula
The textbook example uses the Average function in cell B7.
Goal Seek can then be used to find the value in the variable cell that makes the formula result equal to the desired target value.
Known: Desired average
Find: Required input marks
Tool: Goal Seek
| Feature | Scenarios | Goal Seek |
|---|---|---|
| Purpose | Compare different possible sets of values. | Find an input required for a desired output. |
| Type of analysis | What-if analysis. | Backward calculation. |
| Input | Different sets of values. | Variable cell value is determined. |
| Output | Results for different scenarios. | Required input for target output. |
Scenario → “What happens if inputs change?”
Goal Seek → “What input is needed for this desired result?”
| Multiple Operations | Goal Seek |
|---|---|
| Generates multiple possible outputs. | Finds an input for a specific desired output. |
| Uses different values for a variable/input. | Changes a variable cell to reach target value. |
| Menu: Data → Multiple Operations | Menu: Tools → Goal Seek |
| Task | Important Command / Keyword |
|---|---|
| Data Analysis | Summarise and analyse data |
| Combine Data | Consolidate |
| Group Calculations | Subtotal |
| What-if Analysis | What-if Tool |
| Different Possible Sets | Scenarios |
| Multiple Results | Data → Multiple Operations |
| Formula Cell | Cell containing formula |
| Variable Cell | Cell whose value changes |
| Desired Result | Target Value |
| Backward Calculation | Goal Seek |
| Goal Seek Menu | Tools → Goal Seek |
Consolidate
Used to combine/summarise data from different ranges.
Subtotal
Used to calculate subtotals for groups of data.
What-if Analysis
Analysis performed by changing input values to observe their effect on results.
Scenario
A set of possible values used for analysing different situations.
Multiple Operations
A tool used to calculate multiple possible outputs using different values for a variable.
Goal Seek
A tool used to find an input value required to obtain a specified output.
- Data analysis is used to obtain useful information for effective decision-making.
- Consolidate is used to combine/summarise data.
- Subtotal is used for grouped calculations.
- What-if Analysis studies the effect of changing input values.
- Scenario represents a possible set of values.
- Multiple Operations generates multiple outputs using different input values.
- Formula Cell contains the formula.
- Variable Cell contains the value that can change.
- Target Value is the desired result.
- Goal Seek performs backward calculation.
- Goal Seek menu: Tools → Goal Seek
- Multiple Operations menu: Data → Multiple Operations
CHAPTER 4 — RAPID REVISION
- Analyse Data → Useful information + decisions.
- Consolidate → Combine/summarise data.
- Subtotal → Calculate subtotals for groups.
- What-if → Change inputs → observe outputs.
- Scenario → Possible set of input values.
- Multiple Operations → Multiple possible outputs.
- Formula Cell → Contains formula.
- Variable Cell → Value changed during analysis.
- Target Value → Desired output.
- Goal Seek → Find input for desired output.
- Multiple Operations → Data → Multiple Operations.
- Goal Seek → Tools → Goal Seek.
- Absolute Reference Example → $B$5.
- Average Example → =AVERAGE(B2:B6).