Maybe you have seen the Goal Seek option on the Tools Menu and wondered what it is. Or maybe you have been in the situation where you are trying to solve an Excel problem by populating a cell with different values to see which one works the best.
Goal Seek is a very slick and easy to use Excel feature that will basically determine a cell value for you by trial and error. Its found on the Tools menu as shown below :
I think Goal Seek is best explained using a very simple example. I have set up a simple formula below to calculate profit where margin per unit, and total overhead are known.
I want to calculate the breakeven volume based on these parameters. I could do this a number of ways, but since this is an example of Goal Seek, I will use Goal Seek to calculate my breakeven volume.
Select Goal Seek from the Tools menu, and the following screen pops up :
Select Goal Seek from the Tools menu, and the following screen pops up :
All I need to do now is set cell $c$8 (the profit) to value zero (breakeven) by changing the value in cell $c$6 (the volume). Hit OK.
In about half a second goal seek finds the solution which is that at a volume of 133,333, profit is zero, and therefore 133,333 is your breakeven volume.
Obviously this is a very simple example, but you get the picture. Its a very powerful tool that can be used in quite complex spreadsheet models.
No comments:
Post a Comment