Microsoft's Goal Seek helps with the finer points of maths in Excel

We are casting ourselves in the role of an imaginary club treasurer who is asking the question: “In order to meet our revenue requirements, should we put up prices or try to attract more members?” In Excel you can answer that two-sided poser using the Goal Seek command. First, though, we will need to set up a suitable spreadsheet. It’s not necessary to follow our exact wording for the headings, nor the formatting (which we have applied purely for clarity). However, to better understand how Goal Seek works we would advise you to use our values and formulae in the first instance. To begin, create a new, blank spreadsheet and type the headings shown in our screenshot into cells A1, A2 and A3.

Now click in cell C2, type the formula =sum(a2*b2) and press Enter. This will multiply the number entered into cell A2 (the membership roll) by the membership fee, which will be typed into cell B2. Obviously, both those cells are currently empty, so the result shown in C2 will be zero. If you want to format this cell so that the figure is displayed as currency, first right-click the cell and choose Format Cells from the pop-up menu. Now click the Number tab, click to select Currency from the Category list, check that Decimal places is set to ‘2’ and that the selected Symbol is ‘£ English (U.K.)’, or just ‘£’ in Excel 2003, then click OK to close the Format Cells dialogue box.

Now type some figures into cells A2 and B2, to represent the number of members on the club’s roll and the current membership fee. To format B2 as currency, just follow the guidance given in Step 2. With these figures in place, cell C2 should now show the total income derived from the current membership roll and the fee that they pay. We’ve gone for 987 members, each paying £5.50, giving a total membership revenue figure of £5,428.50. Now, we can decide what increased revenue level we would like to reach and see how Goal Seek can help find ways to reach the desired target.

Goal Seek works the same way in all versions of Excel, but it is accessed in different ways. First, in all versions, click to select cell C2. Excel 2003 users should now choose Goal Seek from the Tools menu. In the 2007 and 2010 versions, first select the Data tab from the ribbon then open the What-If Analysis dropdown menu in the Data Tools group and choose Goal Seek. This displays a small Goal Seek dialogue box, with C2 as the Set cell field.

Our club treasurer wants to reach a revenue level of £6,500. We are going to use Goal Seek to consider two scenarios: putting up the membership fee or attracting new members. To determine the membership fee increase, based on the existing membership numbers, type 6500 into the To value field and B2 into the By changing cell field (or click the small grid icon to the right of the By changing cell field and click B2). This tells Goal Seek to vary the value in cell B2 (the membership fee) to meet a required revenue of £6,500 in cell C2. Click OK.

Goal Seek takes a few seconds to work its magic. When it’s finished, a Goal Seek Status dialogue box will appear, confirming that a solution has been found. Click OK to clear this. In our example, the first solution is to increase the membership fee to £6.59 - because with 987 members each paying that sum we would reach our target of £6,500. In fact, if you click, you will see that the precise figure in cell B2 is 6.58561296859169 but as we formatted the cell to display as currency, this has been rounded up. Now let’s imagine that our treasurer isn’t happy with the idea of putting up the membership fee to £6.59. Type the original membership (£5.50) back into cell B2 and execute Goal Seek once more on cell C2. Again, set the To value field to 6500 but this time the By changing cell field should be set to work on cell A2 – the membership roll. Click OK and Excel shows that, in order to bring in a revenue of £6,500 without increasing the £5.50 membership fee, the club’s paying fellowship would need to increase to 1,181.