EXERCISES: Data Table in Excel

Please sign up for the course before starting the lesson.

Data Table Example 1- Question

In row 15 we have a calculation that tells us how much must be paid in tax based on the salary in A15.

We want to re use this formula to create a table at various salary levels in cell C22 to C222. For each level we want to see the Fixed Base (cell G15), % Base (cell F14), and total (cell H15).

Data Table Example 2- Question

We have been provided with the amortisation table for some equipment we need to purchase. The formula is in C7 and the resultant loan balances (closing balance) is in E15 to I15.

In D19 to I24 we want to create a table that tells us what the closing balance will be in year 1 to 5, depending on what period is chosen in cell C5. Note that this table is different from normal in that the inputs are now in the row E19 to I19 and the Outputs have been linked in D20 to D24.

Data Table Example 3- Question

We have determined a formula to work out the value of office space over 500m2 based on the size and age. We want to create a matrix that shows the value at various combinations of age and floor space per the matrix.

Data Table Example 4- Question

A client has built a calculator to determine the water costs based on a tier structure. If you enter the units consumed in G10, you will get the cost in I24. They now want to use this calculator on a building with 100 units.

Using a Data Table, use the calculator to determine the detailed breakdown of the bill (per tier) with the totals per unit. The water consumed per unit is in C32 to C131.

To see the full course contents click here.

 

Back to: Online Financial Modelling, Budgeting and Forecasting Course > Running What Ifs