Here is an example of a simple Profit & Loss statement for a small business selling one product:
I listed in column C listed the formulas that are in column B for easy reference.
The company is not doing badly, but you want to see how many more items you need to sell to get your Profit Percentage up to 45%. You can sit there an continuously increase the quantity in cell B2 until you get the percentage desired, or you can use Goal Seek.
Goal Seek is located under the What-If Analysis button of the Data Tools group on the Data tab:
Once selected, it presents you with a pop-up box for entry of the scenario you want to resolve. In this case you want to “Set Cell” B12 (Profit Percentage) to value .45 (45%) by changing cell B2 (Quantity):
When you hit OK it will change cell B2 to the value that will cause the Profit Percentage to be 45%:
Goal Seek has determined that you need to sell 3,061 instead of 2,450 items to achieve 45% Profit Percentage. At this point you can accept the result (OK) or cancel it and try other scenarios. For example, instead of changing the quantity, what price do we need to sell at to achieve the 45% target:
Goal Seek indicates that you need to increase sales to just over 122 items.
This tool can be used in a variety of scenarios and what-if possibilities. Remember, Goal Seek can only be used to find a single variable with data that contains formulas. Explore this tool and see how it can help you achieve your goals!