CBSE 10th class Unit 2: Chapter 1 ANALYSE DATA USING SCENARIOS AND GOAL SEEK

 

Unit 2: 

Chapter 1

ANALYSE DATA USING SCENARIOS AND GOAL SEEK


1. Which of the following features is not used for data analysis in a spreadsheet?

  • e) Page layout

Page layout is related to formatting and presentation, not data analysis.

2. Which of the following office tools is known for data analysis?

  • b) Calc

Calc is the spreadsheet component of LibreOffice, specifically designed for data analysis and manipulation.

3. Which of the following operations cannot be performed using LibreOffice Calc?

  • d) Mail merge

Mail merge is a function typically associated with word processors like LibreOffice Writer.

4. What is the extension of a spreadsheet file in Calc?

  • d) .ods

.ods is the standard file format for spreadsheets in LibreOffice Calc.

5. The default function while using Consolidate is to be

  • a) Average

By default, the Consolidate function calculates the average of the selected data.

6. Group by is used in ________ tool to apply summary functions on columns.

  • b) Group and Outline

A. Multiple Choice Questions

  1. (b) What-if Scenario
  2. (b) $C$5
  3. (b) Goal Seek

B. True or False

  1. True
  2. False
  3. True
  4. False
  5. False
  6. False
  7. False
  8. False
  9. True

C. Fill in the blanks

  1. summarize
  2. Consolidate
  3. Consolidate
  4. Group and Outline

Explanation

A. Multiple Choice Questions

  1. What-if Scenario allows you to change input values to see how they affect the output.
  2. $C$5 uses absolute cell referencing, where both the column and row references remain fixed.
  3. Goal Seek works in reverse order, finding the input value that produces a desired output.

B. True or False

  1. Consolidate function can be used to view and compare data by consolidating information from multiple sheets into a single sheet.
  2. Subtotal tool can be used on unsorted data.
  3. Subtotal tool can use multiple summary functions for different columns.
  4. Multiple scenarios can be created for a single sheet.
  5. What-if analysis tool can use multiple arrays of cells.
  6. Goal Seek analysis tool is used to find the input value that produces a desired output.

C. Fill in the blanks

  1. Consolidate function summarizes information from multiple sheets.
  2. Data can be viewed and compared using the Consolidate function.
  3. Consolidate under Data menu can be used to combine information.
  4. The Group and Outline tool automatically creates groups and applies functions.

A. Answer the following questions

  1. Define the terms:

(a) Consolidate function: This function combines data from multiple worksheets into a single worksheet, allowing for easier analysis and comparison.

(b) What-if analysis: This tool helps you explore different scenarios by changing input values and observing the impact on output values.

(c) Goal Seek: This tool helps you find the input value needed to achieve a specific output value.

  1. Give one point of difference between:

(a) Subtotal and What-if: Subtotal is used to calculate subtotals within a range of data, while What-if analysis is used to explore different scenarios by changing input values.

(b) What-if scenario and What-if tool: A What-if scenario is a specific set of input values that you want to test, while the What-if tool is a general tool that can be used to create and manage multiple scenarios.

  1. Give any two advantages of data analysis tools:
    • Improved decision-making: Data analysis tools can help you make more informed decisions by providing insights into data trends and patterns.
    • Increased efficiency: Data analysis tools can automate repetitive tasks, saving time and effort.
  2. Name any two tools for data analysis:
    • PivotTables: A powerful tool for summarizing and analyzing data.
    • Data Analysis ToolPak: A collection of tools for statistical analysis and data mining.
  3. What are the criteria for consolidating sheets?
    • The sheets must have the same column headings.
    • The data in the columns must be of the same data type (e.g., all numbers or all text).
  4. Which tool is used to create an outline for the selected data?
    • Group and Outline

B. 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.

    • Set up a formula to calculate the present value of the loan using the given EMI, number of payments, and an initial guess for the interest rate.
    • Use Goal Seek to find the interest rate that results in a present value of Rs. 10 lakhs.

(b) Use What-If Scenario to depict payment of loan in 25 years by paying out an EMI of 10,000.

    • Create a scenario where the number of payments is 25 and the EMI is 10,000.
    • Use the Scenario Manager to view and compare the results of this scenario with other scenarios.

(c) Use Scenario Manager to find the best case.

    • Create multiple scenarios with different combinations of interest rates, EMIs, and loan terms.
    • Use the Scenario Manager to compare the results of each scenario and identify the best case based on your criteria (e.g., lowest total interest paid, shortest loan term).
  1. Power Motors has 3 branches all over Bhopal. Each branch maintains monthly sales of different models of electric scooter and at the end of 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.  

Steps:

    1. Identify column headings: Ensure that all three spreadsheets have the same column headings for the electric scooter models.
    2. Enter data: Input the monthly sales data for each model in each of the three spreadsheets.
    3. Consolidate data: Use the Consolidate function to combine the data from the three spreadsheets into a single sheet.
      • Select a cell in the destination worksheet where you want to place the consolidated data.
      • Go to the Data tab and click on Consolidate.
      • In the Function box, select "Sum" to calculate total sales or "Average" to calculate average sales.
      • In the Reference box, select the range of cells containing the data from each spreadsheet.
      • Click OK.

 


No comments:

Post a Comment

Subscribe to: Posts (Atom)