You have a list of objectives ranging from study results, personal finance to sales revenue; and you try your best monthly, even daily to accomplish them. Sometimes you wonder which number I need to reach the goal. Don’t worry, you are not alone; and fortunately there is an amazing Excel function supporting you on the way to touch the target. Please note that here I am using Microsoft Office Excel 2016 for Windows.
Imagine that you are an owner of a mini store selling cookies, chocolate and oranges. Based on customer feedback, you will sell a new product which is apple in the next month. Your target is to get 5,000 Euro of profit in the next month. Have a look, there is a table like this.
History data is useful to estimate sales revenue of cookies, chocolate and oranges; however how big sales revenue of apples should be? That is your concern.
Just relax and let Excel help you. The function I would like to show here named Goal Seek. It is in tab Data, area Forecast and under option What-If Analysis.
What you need to do is simply selecting Goal Seek. Then just fill in the box.
- Set cell: is the cell contains the target. Here is cell D6.
- To value: is the value of the target cell. Here we need to fill in 5000.
- By changing cell: is the cell whose value we need to figure out. The “By changing cell” here is B4 that is sales revenue of apples.
What’s next? Simply click OK and see how amazing it is.
It finally returns that sales revenue of apples should be 3413.33 Euro.
There must be a relationship between the Set cell and By changing cell, otherwise the function cannot work well. In this example, they are linked by these two formulas:
Profit = Revenue * Profit Margin
Target = sum of Profit
Hopefully, you can apply this function to both your life and work. Enjoy it, Goal Seek!