Excel has some great functions for “What if…” scenarios. One tool that can be used is the Excel Goal Seek. This allows you to choose any current formula and set the answer value to your own goal.
e.g. If you have a formula that works out how much profit you make from selling items, such as £20, but you would like it to be £30 profit, Goal Seek can change various cells to achieve that £30 goal.
Below is an example similar to ones we use on our Excel Training courses based in London. We have a simple loan calculator that uses the PMT function to work out monthly loan repayments.
Open the Goal Seek from the Data Ribbon, Data Tools Section, What If Analysis.
The Goal Seek dialog appears. Fill out as follows: Enter the cell you wish to set your goal to in Set Cell Field. Enter the figure you want the Goal to achieve in the To Value Field. Enter the variable (cell) you would like Excel to change to reach your set Goal in the By Changing Cell Field.
Click OK
The Sheet content will change to reflect the Goals set. You will be presented with another dialog box, Click OK to keep the changes, or Click Cancel to revert the cells back to original values.