LESSON WORKBOOKS: Level 3 Lesson 7 2007.xlsx
Level 3 Lesson 7A 2007.xlsx
Level 3 Lesson 7B 2007.xlsx
Level 3 Lesson 7C 2007.xlsx
Level 3 Lesson 7D 2007.xlsx
Level 3 Lesson 7E 2007.xlsx
In this lesson we are going to look at two of the very handy, but little known features of Excel. Data Tables and Consolidation. Data Tables are another one of the tools that is specifically designed for use in "What-If analysis". By now, you will be familiar with how "What-If Analysis" works and what it is used for. Data Tables are just a range of cells that are used for testing and analysing outcomes on a a larger scale. Consolidation is a powerful feature of Microsoft Excel that enables you to combine data from separate worksheets into one consolidated worksheet. It also enables you to perform many calculations on this data, including 3-D formulas, which are formulas which refer to cells on multiple Worksheets.
Data Tables
A Data Table will show you how by changing certain values in your formulas you can affect the result of your formula. The great thing about using Data Tables is that they can store the results of many scenarios for you so that you can analyse them to select which scenario is your best option. The results are then written into a table form in your Workbook in a location specified by you. Data Tables are written as array formulas, which were discussed in an earlier lesson. Array formulas perform multiple calculations in a single location.
There are two types of Data Tables available to you these are:
A one-variable table can be used to gauge the effect on one or more formulas by changing the value of one input cell.
A two-variable table can be used to gauge the effect on one formula by changing the value of two input cells.
As with the other "What-If Analysis" tools, the first thing you will need to do for either a one-variable table or a two-variable table is to create a base model. You will also have to tell your Data Table which formulas from your base model you want to test. This is easily done from within the Data Table by placing a formula to reference the formula in the base model. Lets say that we wish to purchase a new forklift for work in a warehouse. We need to know that if interest rates fluctuate we can still afford to pay for our forklift. So we need to know what our loan repayments will be, what our total repayments will be and how much interest we are paying.
Open the attached file Level 3 Lesson 7 2007 which contains the base model from which we are going to create our Data Tables.
Creating a One Variable Data Table
Click on the Worksheet tab named Base Model and spend a few moments looking over the data and clicking in the cells to have a look at the formulas. Obviously if you changed any of the dependent values in various cells you will get different results. Now click on the Worksheet tab named One Variable Table and notice that this has exactly the same data as the previous table, plus an additional area in which to create our Data Table. Let's do that now.
Click in cell E3 and type in =B15, which is the Monthly Payment.
Tab across to F3 and type in =B17 which is the Total Repayment.
Tab across to G3 and type in =B18 which is the Total Interest Paid.
Tab across to H3 and type in =B19 which is the date on which the Completion Date.
Now highlight the range of the table which is D3:H16, then select the Data tab, then under Data Tools select What If Analysis then Data Table to display the Table dialog box.
Leave the first box, Row input cell, blank. As we are only creating a one-variable table in this instance, we need nothing here.
Tab or click to the next box Column input cell and click on the collapse dialog button to the right and select cell B9 which contains the original interest rate of 6.5% Notice how once selected, $B$9 is made absolute.
Collapse back through to the dialog box and select OK.
You should now be able to see the results of the calculations given the values you placed in column D (the interest rate) that would appear in cells B15, B17, B18 and B19 of your original model pasted into the table. Note, however that you may need to use your Format Painter tool to copy the correct cell formats to your table.
Because Data Tables use array formulas they are very versatile so you can have more than one one-variable table within a Worksheet or Workbook. Let's have a look at placing another one-variable table at the end of the table we just created. This table will calculate how much our loan repayments will be if we adjust the term of the loan.
Click in cell E16 and type in =B15,
Tab across to F16 and type in =B17
Tab across to G16 and type in =B18
Tab across to H16 and type in =B19
Now highlight the range of the table which is D17:G24, then select the Data tab, then under Data Tools select What If Analysis then Data Table to display the Table dialog box.
Tab or click to the box Column input cell and click on the collapse dialog button to the right. Select cell B11 which contains the Term of the Loan in Years. Notice again how once selected, $B$11 is made absolute.
Collapse back through to your dialog box and select OK.
You should now be able to see the results of the calculations given the values you placed in column D (the term of the loan) that would appear in cells B15, B17, B18 and B19 of your original model pasted into the table.
Creating a Two Variable Data Table
If we wanted to see what our loan repayments would be with different terms of the loan, and different interest rates we can use a two-variable table. With this type of table, you can nominate to series of data that can be placed back into the original model into two different input cells. This means that we can nominate a series of interest rates to place in the original interest rate cell (B9) and a series of loan terms to place in the Term of Loan cell (B11). When creating a two-variable table, one series is entered into the first column of the table and the other into the first row of the table. As when creating a one-variable table, the formula reference that we want to test needs to be placed into the blank cell at the top of the first column of the table.
Select the Worksheet tab named Two Variable Table.
Select cell D3 and type in =B15
Now highlight the range that you wish to use for the Data Table. In this case D3:K16 then select the Data tab, then under Data Tools select What If Analysis then Data Table to display the Table dialog box.
This time we need to nominate a cell for the Row input cell: Let's hit the collapse dialog button and select cell B11, the Term of the loan in Years.
Now collapse back through to your dialog box and click in the box Column input cell: Hit the collapse dialog box again and select cell B9, which is our Interest Rate cell.
Collapse back through to your dialog box and select OK.
Now you should see presented in front of you the amount of the loan repayments, taking into consideration the various interest rates and the various terms of the loan. Note again that you may need to use the Format Painter to copy the tidy up the format of your cells.
So, now you can see how easy it is to perform a "What-if Analysis" using Data Tables. There are a couple of rules to bear in mind, however, when considering using a data table to test your analysis. These are:
You must spend some time setting up your "Base model"
You do not change the values in the "Base model"
It is a good idea to document the area around your data table, so you and other users can tell what it is you are analysing.
You can use Data Tables to change up to two variables only
You can create as many one-variable or two-variable Data Tables as you like in a Workbook.
Bearing these rules in mind should help you make your choice of whether to use a Data Table or one of the other tools from the "What-if Analysis" Toolpak when you wish to perform an analysis on data.
Consolidation
Consolidation is the process of combining values from several ranges of data. It can be used for such things as bringing together budgets from different Departments into one, inventory reports, sales forecasts etc. The great thing about Consolidation is that once your data has been pulled together into one combined worksheet you can perform a variety of calculations on the combined data. There are two types of Consolidation that can be performed, these are:
Using worksheets that have exactly the same layout, but containing different data
Using worksheets that have different layouts and containing different data
Consolidation Using the Same Layout
Performing the first type of Consolidation is the easiest and works using a layering operation. This means that once you open the workbooks you wish to consolidate, you specify the ranges to consolidate, then the values in one worksheet are overlaid on those of another worksheet until all the worksheets are overlaid. It is then that you can select the calculation to be performed on your data.
Let's work through an example.
Open the four workbooks, called Level 3 Lesson 7A 2007, Level 3 Lesson 7B 2007, Level 3 Lesson 7C 2007, Level 3 Lesson 7 2007. Three of these Workbooks contain the Income of Hawley's Brewery for the past three years. The fourth Workbook Level 3 Lesson 7 2007 is where we will perform our Consolidation and so this will need to be our active Workbook. Make sure you are clicked on the Consolidate tab.
The first thing that we need to do is select the range where we wish our Consolidation to take place. In our case it is the range B3:Q6.
Now go to the Data tab and under Data Tools select Consolidate.
What you have in front of you now is the Consolidate dialog box. The top box labelled Function: This is where you select the function that you wish your Consolidation to perform. In our case study we will select Sum.
The next box is labelled Reference: This is where we will select the ranges in our other Workbooks that we wish to Consolidate. Hit the Collapse button at the right of the Reference: box then select navigate to Level 3 Lesson 7A 2007. Now select the range B3:Q6, then collapse back through to your Consolidate dialog box and click on Add.
Click on the collapse button to the right of the Reference: box again and follow the same steps and select the same ranges in Level 3 Lesson 7B 2007 and Level 3 Lesson 7C 2007.
You should now be able to see all three ranges in the All References: box.
The Delete button directly underneath the Add button will delete references from the All References: box. The box underneath the All References: box is headed Use Labels In. We would use the Top row or Left column boxes only when you want to consolidate data with labels that are similar but arranged differently in each source area selected for consolidation. If you want to consolidate data that has different category labels but contains similar data arranged identically in each source area, or if you want to consolidate by position, do not include labels when you select source areas.
The last little checkbox titled Create links to source data when checked will update the consolidation area data automatically when the data changes in any of the source areas. In our case study we do not wish this to happen, so we will leave this checkbox unchecked.
All we need to do now is select OK.
Once you have done this you will see in the
Level 3 Lesson 7 2007 the results of your
Consolidation. Notice that if you click around on the various values
within the cells that there are NO formulas.
Consolidation Using the Different Layouts
This type of Consolidation is a little trickier to perform, but works in nearly the same way. When you select your data to consolidate, you must this time include your row and/or column headings. Excel will then examine the row and/or column headings and will be able to plot the layout of your Worksheets and Consolidate your data for you by examining the contents of the ranges to be used.
Save the final Consolidation you have on your screen, close down all the Workbooks and re-open three of the workbooks, called Level 3 Lesson 7A 2007, Level 3 Lesson 7B 2007, and Level 3 Lesson 7C 2007.
Also open Level 3 Lesson 7D 2007, noting the layout is slightly different and Level 3 Lesson 7E 2007.
Make sure that Level 3 Lesson 7E 2007 is your active Workbook, then highlight the range A3:Q7 then go to the Data tab and under Data Tools select Consolidate.
In this type of consolidation we are going to use the Average Function, so lets select Average from the list under Function:
Now add the range A3:Q7 in the Level 3 Lesson 7A 2007, Level 3 Lesson 7B 2007,Level 3 Lesson 7C 2007 and Level 3 Lesson 7D 2007 workbooks.
Once you have done this, ensure that Left column in Use labels in appears with a tick in it, then select OK.
Now that your consolidation is complete, you will see that you have performed an Average calculation on your data, and that the figures and label for the Lounge from Level 3 Lesson 7D 2007 has been inserted into your Level 3 Lesson 7E 2007. This was because we ensured that the Left Column was selected under Use Labels In.
It is very easy to change the type of calculation that you wish to do when performing a Consolidation. Once the ranges are added, all you need to do if you wish to see a different calculation result is to re-highlight your data (A3:Q8) and then go to Data Tools select Consolidate, then select the type of Function you require and click OK.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid.com is in no way associated with Microsoft.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.