Chapter 4 Analyse Data using Scenarios and Goal Seek PDF

Chapter 4 Analyse Data using Scenarios and Goal Seek Analysing data is the process to extract useful information for making effective decisions. The spreadsheet is one of the best software used for data analysis. It is used to retrieve, correlate, explore and visualise...

Chapter 4 Analyse Data using Scenarios and Goal Seek Analysing data is the process to extract useful information for making effective decisions. The spreadsheet is one of the best software used for data analysis. It is used to retrieve, correlate, explore and visualise data to identify patterns, trends and relationships. The spreadsheet component in LibreOffice is known as Calc. Consolidating Data 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. You need to check the following before consolidating data. Open each sheet in the spreadsheet and check that the data types must 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 on the basis of which the data is to be consolidated. Steps to consolidate the data are as follows: Step 1. Open the spreadsheet which has the data to be consolidated. Step 2. Create a new sheet where the data has to be consolidated. Step 3. Choose Data > Consolidate option that will open Consolidate dialog. Step 4. The default function “Sum” is seen in the Function drop- down. List of functions will be displayed by clicking on the Function drop-down. Step 5. Choose the required function from the drop-down list. Step 6. The sources data range list contains existing named ranges to quickly select from that. But if the source range is undefined, then click and select the range from the sheet which is to be consolidated. Step 7. Click on Add to add this range under the ‘Consolidation ranges’ of the Consolidation dialog. Step 8. Repeat steps 6 and 7 to add more sheets to be consolidated. Step 9. Check the target range specified under Use ‘Copy results to’. If it is not mentioned, then click on the cell of sheet where the final data has to be produced. Step 10. Click on Options that will list two checkbox under Consolidate by “Row Labels” and “Column labels” and “Link to source data” under Options. Link to source data is checked to make the modification automatically in the consolidated sheet while making any changes in the source data. Step 11.Finally click on OK button. Keyboard shortcut for consolidate is ALT+D. The consolidated sheet will have all the consolidated data along with the original data. The original data of the sheets are visible by clicking on the ‘+’ sign in front of the consolidated row. Groups and Subtotals Group and Outline 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. Select the data to be grouped, click on Data>Group and Outline. Then choose Rows to group the data on the basis of rows or columns to group it on the basis of columns. The Subtotal tool in Calc creates the group automatically and applies common functions like sum, average on the grouped data. One can use any type of summary function for each column as per the requirement of data analysis. It can group subtotals by using category and sorts them in ascending or descending order so that one need not to use filters. Follow the following steps to apply Subtotal tool. Step 1. The sheet where this is to be applied must have labels to the column Step 2. Click on Data menu and choose Subtotals (Data>Subtotals) Step 3. Choose the column in the Group by list in the subtotal dialog which is to be used for grouping the data in the sheet Step 4. Select the column by clicking the checkbox under Calculate subtotals for to create subtotals for. Step 5. Select the desired function by clicking the function under Use function. Step 6. You can use the 2nd Group and 3rd Group tabs to group the data in further levels. Step 7. Click on OK button. The outline to the left of the row numbers shows the hierarchical structure which can be used to show or hide different levels by clicking on the group indicators ‘+’ sign to expand and ‘—’ sign to collapse the data. You can hide the low-level details and just look at the final totals and grand totals. If you want to remove the outline feature from the sheet at any point of time then it is possible by just clicking on Data > Group and Outline > Remove Outline. What-if Scenarios What-if scenario is a set of values that can be used within the calculations in the spreadsheet. A name is given to the scenario and several scenarios can be created on the same sheet. It is used to explore and compare various alternatives depending on changing conditions. It can be used in the beginning of any project to optimise 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. For example, a person who is taking car loan has to decide on certain factors as given below: The number of years for which the car loan is taken. The total amount of car loan (Principal amount) The above two factors can display the EMI to be paid monthly. Follow the following steps to create scenario. Step 1. Select the cells which contains values in the dialog sheet that needs to be changed. To select multiple cells, hold Ctrl key and click on the cell to be selected. Step 2. Choose Tools>Scenarios will open scenario dialog window Step 3. Enter a name for the new scenario and leave the other fields unchanged. Step 4. Click on OK button. This will create a new scenario which is activated automatically. What-if Analysis Tool 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 Multiple Operations tool creates a formula array to display the list of results applying the formula on a list of alternative values used in the formula. This tool uses two arrays of cells, one array contains the input values and the second array uses the formula and display the result. It is useful to check in the beginning to understand from the output for the efficiency. 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. Following steps are need for what-if analysis tool: (calculate with 1 formula and 1 variable) Step 1. Enter the data in the cells and then enter a formula to calculate a result from values in other cells. Step 2. Create an array of input values on the basis of which the output is to be generated using the formula. Step 3. Select the cell range of input array and output array. Step 4. Click on Data+Multiple Operations will display the multiple operations dialog window Step 5. Enter the cell address in the Formulas box from the sheet which contains the formula Step 6. Now enter the cell address of the cell which is a variable and is used in the formula in column input cell box. Step 7. Click on OK will generate all the possible outputs based on the formula. Goal Seek It general we fill in the values in the cells and then create formula on these values to get the required result. To predict the output, we keep on changing all the input values to obtain the desired output. Goal seek helps in finding out the input for the specific output. For example, if you want to know the number of units produced to get the desired output then use Goal seek analysis tool. Follow the following steps to use Goal seek tool. Step 1. Enter the values in the worksheet. Step 2. Write the formula in the cell where the calculation has to be used. Step 3. Place the cursor in the formula cell, choose Tools > Goal Seek Step 4. The Goal seek dialog window will appear and the Formula cell box will have the correct formula Step 5. Place the cursor on the Variable cell box and click on the cell that contains the value to be changed. Step 6. Enter the desired result in the Target value box. Step 7. Click on OK button