How to reach the target? Excel can help you

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.

Goal Seek 1

Data of a mini store

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.

Goal Seek 2

Function Goal Seek

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.
Goal Seek 3

Fill in those boxes

What’s next? Simply click OK and see how amazing it is.

Goal Seek 4

It turns out the result

It finally returns that sales revenue of apples should be 3413.33 Euro.

Note

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s