How to use the Excel Goal Seek function to find what input value will give you a desired output e.g. what price do I sell a product at to achieve a gross profit of 1 million.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Excel Goal Seek 2013
In Excel it is sometimes necessary to try and determine how we can achieve a certain number by making changes to some underlying inputs. This is where the Excel goal seek function comes into play.
So for example if we run this business and we need to achieve a gross profit of 250 – what would the sales price have to go to to achieve that? So you could do it manually – if I click in there and go R11.25 – OK it has moved up but not enough. So we could do it manually or we could do it automatically. To achieve that we’ll click in this gross profit cell – under data – you’ll see you have got an option called “What If Analysis” – there is something called “Goalseek”.
When I click on it – what it is asking for is “where is the cell we are going to set?” – so I am just going to click over here – and say that’s the cell. Excel now wants to know what value do you want that cell to become – so I am going to put 250 and then it needs to know what is it allowed to change in this spreadsheet – either on this sheet or on another sheet to achieve that. So I am going to say you can change the selling price. When I say OK – you’ll see it runs through and says I think I found a solution – so I am going to keep it and say OK.
When it has determined that this number here will give me the answer I want. Let’s say that we take this to our Manager and they say “No ways – we can never sell this product for that much” – the most we can do – I don’t know – say R11.50 for example. We haven’t achieved our budget but we still have to reach 250. So we can run another goal seek – data – whatif analysis – goalseek –change that to 250 and this time I am going to play with the Cost of Sales – when I say OK- it runs through, finds an answer and in order to get to 250 we will have to drop the Cost of Sales to 17.99%.
If it cannot find an answer – it will then tell you it cannot find the answer.