SESSION 1-ANALYSE DATA USING SCENARIOS AND GOAL SEEK

Hello friends, Here you will find the important notes of CBSE Class 10 Information Technology Code 402. In this post you will find UNIT 2 ELECTRONIC SPREADSHEET (ADVANCED) Notes. In it Session 1-ANALYSE DATA USING SCENARIOS AND GOAL SEEK is defined for good understanding the concepts of Scenarios and Goal Seek.

ANALYSE DATA USING SCENARIOS AND GOAL SEEK

Consolidating data:

Data Consolidation allows you to gather together your data from separate worksheets into a master worksheet. In other words, the Data Consolidation function takes data from a series of worksheets or workbooks and summaries it into a single worksheet that you can update easily.

Steps for Data Consolidating:

1 Open the worksheet that contains the cell ranges to be consolidated.

2 Choose the Consolidate option under the Data menu.

3. Select Source data range and Click Add. The selected range now appears on the Consolidation ranges list.

4. Select additional ranges and click Add after each selection.

5. Specify where you want to display the result by selecting a target range from the Copy results to box.

6. Select a function from the Function list. The Sum function is the default setting.

7. Optionally click More in the Consolidate dialog to display additional settings. Select Link to source data to insert the formulas. This generates the results in the target range instead of the actual results.

8. Under Consolidate by setting, select either Row labels or Column labels, To consolidate by row labels or column labels, the label must be contained in the selected source ranges. The text in the labels must be identical, so that rows or columns can be accurately matched.

9. Click OK to consolidate the ranges.

Note: Data > Define Range to give a name to a range.

Creating Subtotals:

SUBTOTAL, totals/adds data arranged in an array—that is, a group of cells with labels for columns and/or rows. Using the Subtotals dialog, you can select arrays, and then choose a statistical function to apply to them. It is accessible from Data menu.

Steps to insert subtotal values into a sheet:

1. Ensure that the columns have labels.
2. Select the range of cells that you want to calculate subtotals for, and then choose Data -> Subtotals.
3. In the Subtotals dialog, in the Group by box, select the column that you want to add the subtotals to.
4. In the Calculate subtotals for box, select the columns containing the values that you want to subtotal.
5. In the Use function box, select the function that you want to use to calculate the subtotals.
6. Click OK.

Using “What If” Scenarios:

Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. A scenario is essentially a saved set of cell values for your calculations. You can easily switch between these sets using the Navigator

Creating Scenarios:

1. Select the cells that contain the values that will change between scenarios.
2. Choose Tools > Scenarios.
3. On the Create Scenario dialog, enter a name for the new scenario. This name is displayed in the Navigator and on the title bar of the scenario.
4. Optionally select or deselect the options in the Settings section.
5. Click OK to close the dialog.

Goal Seek:

Usually, you run a formula to calculate a result based upon existing values. By contrast, using Goal Seek option under Tools menu, you can discover what values will produce the result that you want.

For example, Chief Financial Officer has a good idea of the company’s income in the first three quarters, because of the contracts that are already signed. For the fourth quarter, however, no definite income is available. So how much must the company earn in Q4 to reach its goal? The CFO can enter the projected earnings for each of the other three quarters along with a formula that totals all four quarters. Then she runs a goal seek on the empty cell for Q4 sales, and receives her answer.

Solver:

Solver option under Tools menu amounts to a more elaborate form of Goal Seek. The difference is that the Solver deals with equations with multiple unknown variables. It is specifically designed to minimize or maximize the result according to a set of rules that you define.