Goal Seek

Data Analysis - Goal Seek

Logarithms

We can use Goal Seek to calculate logarithms. You may find it easier to use your calculator but this gives you some idea of the mechanism. In base 10 the logarithm of 100 is 2 because 10^2 = 100; similarly the logarithm of 1000 is 3, and so on. The logarithm of 50 is the power by which 10 is raised to get 50 - in this case 1.69897. To calculate a logarithm in Excel proceed as follows:

Put a number 2 in a cell, say A1 - this is the first approximation of the logarithm
Set the next cell, B1, to =10^A1
Select cell B1 and choose Tools/Goal Seek
enter ‘50’ in the second box down (To value)
Click on cell A1 to get the cell address into the third box (By changing cell)
Press OK and the result will appear in the output box and A1

Finding The Price Before VAT

As another example, imagine that you buy something for £999.99, including VAT - how much VAT did you pay? One way to find out (if it wasn't included in the price information) would be to use Goal Seek. Set up a spreadsheet as follows:

Enter the labels in A1, B1 and C1 as shown
Enter a sample value in A2 e.g. 1000
Enter the formula in B2 =A2*1.175 - this finds the total price plus VAT at 17.5%
Enter the formula in C2 =B2-A2 - this calculates the VAT on the base amount

Select cell B2 and choose Tools/Goal Seek

Enter the total price in the 'To Value' field (such as 999.99)
Click in the 'By changing cell' field and click on A2 to set this to $A$2
Click OK and the base amount will be calculated; click Enter to set cell A2 to this base amount.

For example, a base price of £851.06 produces VAT of £148.94 (851.06 * 1.175). If the total amount is £117.50 then the VAT will be calculated as £17.50.

Modelling Income By Changing Cost, Selling Price and Sales

The Goal Seek tool provides Excel with the ability to find a target value by changing the values in existing cells. The value in the target cell should be a formula made up of cell references and you should ask the Goal Seek tool to change one of the cells in the formula to find the new value. For example, imagine that you have conducted this simple analysis of the costs of producing some item and the income thus generated:

The formula in C3 is =A3*B3; the formula in F3 is =D3*E3; the formula in G3 is =F3-C3. Set the cells with money values to currency format.

We use Goal Seek to find out the level at which we should set Production Cost, Sale Price or Total Sold in order to meet a given target for the amount of profit. If we decide on a projected profit of £1500 for 2001 we proceed as follows:

Select cell G3

Select Tools/Goal Seek - the dialogue box has three input boxes

The Set Cell box is already set to the target cell

Click in the To value box and type '1500'

Click in the By changing cell box and click on cell A3

Press Enter and observe the new value in A3 - it should be £0.33

Type £0.33 directly into cell C7 - this makes a copy for future reference

Type the original value of 0.45 back into cell A3

Excel varies the value in cell A3 and feeds this into the formula in G3 until the desired value is reached.

You should now repeat the Goal Seek operation with different cell references for the By changing cell entry - D3 and E3 to get values for the Selling Price and Total Sold items respectively. In any business the total produced may not be the same as the total sold so there doesn't seem much point in seeking a new value for the Quantity Made.

There is a further use of Goal Seek demonstrated in the example Cubes and Spheres.

Note that Goal Seek cannot be used with a more complex problem like the Monkeys and Bananas puzzle where more than one value of a particular type (integer) is sought. In such problems you will have to use other techniques such as copying the formulae into adjacent columns or using Visual Basic.

Use the Back Button to Return to the previous page