Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Finding solutions with Excel: Part 2 - Data Tables

by
12th Feb 2009
Save content
Have you found this content useful? Use the button above to save it to your profile.

SCIENTIFIC CALCULATORA recent Any Answers question on finding the optimum balance of salary and dividends to minimise tax and NIC for a company director suggested that Excel's solver function might be the answer. Simon Hurst experiments with Excel 2007 Data Tables to see if they can help.

The story so far

This is the second part of a three part series looking at some of the Excel tools that can help with this sort of problem. In the first part we set up a model to calculate tax on the profit shares of two partners and exposed it to the AccountingWEB audience for peer review, before demonstrating the use of Goal Seek to work backwards from the answer to the question. This time we'll use Excel data tables as an alternative method of identifying our answer.

A one-way data table

An Excel data table displays the results of a calculation for a series of possible input values. A one-way table displays a list of different results for one changing input and a two-way table shows a matrix of results for two changing inputs. We'll start with the simpler one-way table.
In cell H8 we'll enter a reference to our 'answer' cell, the deductions total in D18.

Then we'll enter our Partner A Share input percentages in cells G9 to G19. There are several ways of doing this but one quick way is to enter 0% in G9, 5% in G10 then select G9 to G10 and use the fill handle to drag down to G19. Because in G9 we have the start of our series and in the difference between G9 and G10 the 'step', Excel can calculate and fill our series for us.

Next we select G8 to H19 and choose the option Data, Table . We then need to enter the reference to the 'input' cell that the data table function will substitute our series of values into. In our case, because we have entered our series of values in a column we need the 'Column input cell' and we need to set it to refer to our Partner A Share cell, B3.


Excel now enters a series of array formulae in cells H9 to H19 that calculate our total deductions value for each of the profit shares in the adjacent cells:


Adding conditional formatting

We could now look down this list of values to find the minimum value. We could use conditional formatting to make it easier to spot the minimum value. In Excel versions prior to 2007 we could do this by selecting H9 to H19 and selecting Format-Conditional Formatting and then entering a 'Formula Is' condition as follows:


The formula is set up as:

=(H9=MIN($H$9:$H$19))

The first reference is relative so it will check each cell in our range in turn, whilst the reference to the column of values is absolute so it will refer to the correct block of cells, whichever cell it is in. The formula compares the value in each cell to the minimum value in the range to highlight the minimum deduction amounts. If you wanted to highlight the highest deductions, you could use the MAX() function instead.

In Excel 2007 the whole area of conditional formatting has been dramatically improved and we could use Conditional Formatting, Colour Scales to create a 'heat map' type effect.

Here are the two results side by side:


Of course this approach is far from foolproof, there might be some strange set of circumstances that means the minimum actually falls at some odd percentage such as 23.45% - the data table could well not provide a clue to this. In this case, it does seem that the minimum value falls somewhere between 40% and 50% so we could change our test values to 40%, 41% … 50%:


Getting closer. If we then chose a step of 0.20% and covered 41.00% to 43.00% we would find the minimum lay between 41.40% and 41.80%, so we could use a step of 0.04% between these two figures and carry on reducing the step until the difference was no longer significant – here we are down to 0.01%:


Automating the input value list

It's tedious having to keep entering all the test percentages, so to make this a bit easier, we can add some further cells to enable our test percentages to be calculated from a couple of entries. We will have one cell to hold the 'middle' percentage share figure (H3 – named DTShare) and another to hold the step between each test value (H4 – named DStep).

We can then calculate the first test value as the Share minus (Step*5) - there being 5 cells either side of the middle cell in our list of input cell values:

=DTShare-(DTStep*5)

The cell below would contain a reference to the cell immediately above, plus the step:

=G9+DTStep

This could then be copied down to the other cells in the list. The result of this should be that the value entered as the share value becomes the middle value in the data table, with 5 values either side, as shown below:


There is an issue to be aware of here. If you try and use the original share percentage cell – B3 - as the basis for the test values, rather than a different one that just contains a value, you will, without warning, get some very odd results. I think this is because a hidden circular reference is created. However, B3 could refer to H3 so that we would only need to type the value in once to keep our data tables and calculation area synchronised.
Finally we could create a two-way data table with the percentage share set up as before, but with the 'row input cell' referring to the total profit amount:


Note that in a two-way table the formula is entered in to the top left-hand corner cell – G9 in this case. We have amended the conditional format formula to make the column references relative so we can copy the format to the other columns:

=(H9=MIN(H$9:H$19))

As promised, in the next part of the series we'll introduce the Excel Solver Add-in and apply it to our simple problem before looking at the original salary versus dividend issue.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.