How to use Goal Seek in excel (In 20 Seconds)

July 26, 2022
726 Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

By altering an underlying assumption, the Goal Seek Excel function (also known as What-If-Analysis) allows one to solve for a desired output. Back-solving is effectively done by the function by feeding in guesses and trying different solutions until it finds the right one.

Goal Seek can calculate the number of units that must be sold in order to generate $1 million in revenue, for instance, if the formula for revenue is equal to the number of units sold times the selling price. When performing sensitivity analysis in financial modelling, the tool is quite helpful.

Simple goal seek example in excel

Let's first examine a straightforward experiment to show how Goal Seek functions. Consider a very simple model that determines total net revenue by adding the quantity of units sold, the retail price, and a discount.

The following details can be found in the current model:

Number of units are 500.Retail price is $25.00Selling discount is 10%Revenue is $11,250

Let's say we want to calculate the number of units that must be sold in order to generate $20,000. 

For instructions on how to use the Goal Seek Excel tool, see the screenshot below.

How to use goal seek in excel online, 2016 and 2019

1.Place the pointer over the cell that contains the desired change in output (revenue)

2.Choose What-if-analysis from the Data ribbon, then choose Goal Seek (keyboard shortcut is Alt, A, W, G)

3.Make Set cell equal to the revenue cell when the dialogue box (as displayed below) displays (E10)Set To value to reflect the result you want to get (type the number in)

4.Set By altering cell to the assumption (number of units, or cell E4) that you want to solve for.

5.Press ok.The results of the analysis are shown below. We can see that 889 units must be sold in order to generate $20,000 in revenue. 6.You'll see that the answer is shown right in cell (E4), not in the dialogue box.

7.Press OK to continue running the solution in the model (cell E4 will remain set to 889 units), or press Cancel to go back to the initial supposition (500 units).

Important point using goal seek in excel

Okay, let's try a another example. Consider that I wish to change the 2020 revenue in order to achieve my goal of $10 million in net profitability. For a $10 million net profit in 2020, how much revenue are we going to need?

I want to change this number here, this net income in 2020, to be $10 million by changing this target case revenue as the one that is currently running in our model, so I need to change that cell, and I see that the target revenue is $122.7 million.

To do this, I'm going to press Alt, A, W, and G for GOAL SEEK. The amount of money needed to go there is indicated by pressing Cancel and entering 122700 here.

This is a summary of a key information point made utilising GOAL SEEK in this article.Note: This above written article is an attempt to show you how to use goal seek function in excel online, 2016 and 2019, in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.