Unit 2: Electronics Spreadsheet (Advanced) using LibreOffice Calc
Chapter 4 Analyse Data using Scenarios and Goal Seek
A. Multiple-choice questions
Question 1:
Which of the following feature is not used for data analysis in spreadsheet?
(a) Consolidating data
(b) Goal Seek
(c) Subtotal
(d) Page layout
Answer: (d) Page layout
Question 2:
Which of the following office tool is known for data analysis?
(a) Writer
(b) Calc
(c) Impress
(d) Draw
Answer: (b) Calc
Question 3:
Which of the following operations cannot be performed using LibreOffice Calc?
(a) Store and manipulate data
(b) Create graphical representation of data
(c) Analysis of data
(d) Mail merge
Answer: (d) Mail merge
Question 4:
What is the extension of spreadsheet file in Calc?
(a) .odb
(b) .odt
(c) .odg
(d) .ods
Answer: (d) .ods
Question 5:
The default function while using Consolidate is ________.
(a) Average
(b) Sum
(c) Max
(d) Count
Answer: (b) Sum
Question 6:
Group by is used in ______ tool to apply summary functions on columns.
(a) Consolidate function
(b) Group and Outline
(c) What-if scenario
(d) Subtotal tool
Answer: (d) Subtotal tool
Question 7:
Which tool is used to predict the output while changing the input?
(a) Consolidate function
(b) What-if scenario
(c) Goal seek
(d) Fine and Replace
Answer: (b) What-if scenario
Question 8:
Which of the following is an example for absolute cell referencing?
(a) C5
(b) $C$5
(c) $C
(d) #C
Answer: (b) $C$5
Question 9:
_______ analysis tool works in reverse order, finding input based on the output.
(a) Consolidate function
(b) Goal seek
(c) What-if analysis
(d) Scenario
Answer: (b) Goal seek
B. State whether the following statements are True or False
Question 1:
Consolidate function is used to combine information from two or more sheets into one.
Answer: True
Question 2:
The Consolidate function cannot be used to view and compare data.
Answer: False
Question 3:
Link to source data is checked updates the target sheet if any changes made in the source data.
Answer: True
Question 4:
Using subtotal in Calc needs to use filter data for sorting.
Answer: False
Question 5:
Subtotal tool can use only one type of summary function for all columns.
Answer: False
Question 6:
Only one scenario can be created for one sheet.
Answer: False
Question 7:
What-if analysis tool uses one array of cells.
Answer: False
Question 8:
Goal seek analysis tool is used while calculating the output depending on the input.
Answer: False
Question 9:
The output of What-if tool is displayed in the same cell.
Answer: False
C. Fill in the blanks
Question 1:
Consolidate function is used to combine information from multiple sheets to ___________ the information.
Answer: summarize
Question 2:
Data can be viewed and compared in a single sheet for identifying trends and relationships using _______ function.
Answer: consolidate
Question 3:
_______ under Data menu can be used to combine information from multiple sheets into one sheet to compare data.
Answer: Subtotal
Question 4:
The _____ tool in Calc creates the group automatically and applies functions on the grouped data.
Answer: Subtotal
Question 5: ______ scenario is used to explore and compare various alternatives depending on changing conditions.
Answer: What-if
Question 6:
________ is a planning tool for what-if questions.
Answer: What-if tool
Question 7:
What-if analysis tool uses _______ array of cells, one array contains input values and the second uses the ________.
Answer: two, formula and display output
Question 8:
_________ helps in finding out the input for the specific output.
Answer: Goal seek
D. Answer the following questions
Question 1.
Define the terms:
- (a) Consolidate function
- (b) What-if analysis
- (c) Goal seek
Answer:
(a) Consolidate Functions:
Consolidate is a function used to combine information from multiple sheets of the spreadsheet into one place to summarize the information. It is used to view and compare variety of data in a single spreadsheet for identifying trends and relationships
(b) What-if analysis
What-if tool uses Data > Multiple Operations and is a planning tool for what-if questions.
In this, the output is not shown in the same cells, whereas it uses a drop-down list to display the output depending upon the input.
The what-if analysis tool uses two arrays of cells, one array contains the
input values and the second array uses the formula and
display the result.
(c) Goal Seek
Goal Seek helps in finding out the input for the specific output. It means you can discover what values will produce the result that you want.
Question 2:
Give one point of difference between
- (a) Subtotal and What-if
- (b) What-if scenario and What-if tool
Answer:
(a) Subtotal and What-if :
The Subtotal tool in Calc creates the group automatically and applies common functions like sum, and average on the grouped data. One can use any type of summary function for each column as per the requirement of data analysis.
What-if tool uses Data > Multiple Operations and is a planning tool for what-if questions. This tool uses two arrays of cells, one array contains the input values and the second array uses the formula and
displays the result. A what-if analysis tool is very helpful when we want to know how much profit we earn for a particular product for a series of selling units.
(b) What-if Scenario and What-if tool
What-if scenario is used to explore and compare various alternatives depending on changing conditions. It can be used in the beginning of any project to optimize the output. This tool is used to predict the output while changing the inputs which reflects the output and thus one can choose the best plan of action based on it.
What-if tool uses Data > Multiple Operations and is a planning tool for what-if questions. This tool uses two arrays of cells, one array contains the input values and the second array uses the formula and
displays the result. A what-if analysis tool is very helpful when we want to know how much profit we earn for a particular product for a series of selling units.
Question 3:
Give any two advantages of data analysis tools.
Answer: Advantages of data analysis tools:
- Data analysis tool is used to retrieve, correlate, explore, and visualize the data.
- Data analysis tool is used to identify patterns, trends, and relationships.
- Data analysis tool is used to analyse the data and interpret the result from it.
Question 4:
Name any two tools for data analysis.
Answer: Tools used for data analysis are
- Consolidating Data
- Groups and Subtotals
- What-if Scenarios
- Goal Seek
- What-if Analysis Tool
Question 5:
What are the criteria for consolidating sheets?
Answer: Criteria for Consolidation Sheets are
- Open each sheet in the spreadsheet and check that the data types match which you want to consolidate.
- Match the labels from all the sheets which are used for consolidating.
- Enter the first column as the primary column based on which the data is to be consolidated.
Question 6:
Which tool is used to create an outline for the selected data?
Answer: The Group and Outline tool in Calc is used to create an outline of the selected data and can group rows and columns together so that one can collapse (-) to hide it or expand (+) it using a single click on it
Lab Exercise
1. Mr Gurdeep has to take a loan of Rs. 10 lakhs to buy a house. After assessing his situation, he has realized that he can pay the loan in 15 years by paying out an EMI of 20,000.
- (a) Use Goal seek to find out the interest rate at which he can borrow the loan.
- (b) Use a What-If Scenario to depict the payment of the loan in 25 years by paying out an EMI of 10,000.
- (c) Use Scenario Manager to find the best case.
2. Power Motors has 3 branches all over Bhopal. Each branch maintains monthly sales of different models of electric scooters and at the end of the month mails it to the State Head. Prepare a consolidated sheet that shows total and average sales made for each model of the electric scooter with respect to the spreadsheet sent by the branches to the head office. You are required to identify the column headings for the various branches, enter data in three different spreadsheets indicating different branches, and consolidate data to find total sales and average sales for each model.
