 |
|
|
|
|
Download the associated
Workbook
for this lesson
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 are 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.
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 WBDLesson6Level3-Consolidation1 which contains the base model from which we are going to create our Data Tables.
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 depending values in various cells you will get different results. Now click on the Worksheet tab named OneVariable 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 Loan Repayment that we have worked out. 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 Amount of Interest Paid. Tab across to H3 and type in =B19 which is the date on which the loan will be repaid in full. This is the basis for our Data Table to be worked out on.
We now need to highlight the range of the table which is D3:H16, then select Data>Table to display the Tale dialog box. The first thing we need to do is to 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 c lick 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, B9 is made absolute. Collapse back through to your Table 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, which is the Monthly Loan Repayment that we have worked out. Tab across to F16 and type in =B17 which is the Total Repayment. Tab across to G16 and type in =B18 which is the Total Amount of Interest Paid. Tab across to H16 and type in =B19 which is the date on which the loan will be repaid in full.
Select Data>Table to display the Tale dialog box, then 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% Notice again how once selected, B11 is made absolute. Collapse back through to your Table 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.
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 TwoVariable Table. Select cell D3 and type in =B15 which again the the loan repayment amount. Now highlight the range that you wish to use for the Data Table. In this case D3:J16 and select 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, which is the Term of the loan in Years. Now collapse back through to your Table 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 Table 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.