Goal Seek Analysis in Excel (Simple Explanation with Example)
Understand the basic concept of Goal Seek analysis in Excel with a simple real life example. We walk-through the implementation of goal seek analysis and also the main limitation of Goal Seek analysis.
★★★★★
Video Companion spreadsheet that was referred in the video is available at: https://drive.google.com/open?id=1rfO...
Index for this video is shown below. Feel free to move to the respective sections by clicking on the timestamp!
00:45 - What is Goal Seek in Excel? (with Example)
02:25 - Accessing the Goal Seek via Shortcut ( ALT + AWG)
02:49 - Accessing the Goal Seek via Excel Ribbon (Ribbon Navigation :- Data - What-If Analysis - Goal Seek)
03:00 - Parameters of Goal Seek and Implementing Goal Seek Analysis
03:50 - Limitation of a simple Goal Seek Analysis (Only one at a time implementation possible unless we go the VBA route... which will be a separate video)
04:55 - Using % values in Goal Seek Analysis (Goal should be given as 0.6 if your goal is 60%)
In this video, I define what Goal-Seek means in excel and how it can be accessed via Ribbon or Shortcut. Then I take a real life example of Goal Seek Analysis and walk you through how its done. I also go through the limitation of Goal seek Analysis which can be resolved by using more complex VBA scripting. (This will be explained in a separate video - • Goal Seek Analysis for Multiple Cells (Exc... )
★★★★★
Summary for the video is available below for your convenience.
Goal Seeking - Ability to calculate backward to obtain an input that would result in a given output.
A real life example of Goal Seeking - Consider a sale you have done to a customer. The invoice calculation for the same is as below...
Invoice Amount - 10K
Tax1 @ 4.5% - 450
Tax2 @ 1% of Tax1 - 4.5
Total - 10454.5
Being a loyal customer, you decide to round up the total value to 10K. However, in the invoice, you need to make the change to the Invoice amount so that the total comes as 10K.
You can try to do it manually, however... it will be a painfully slow manual process based on trial and error. This is where Goal Seek Analysis can do wonders...
Within a few clicks, you Goal Seek functionality of excel will give you the exact invoice amount that will correspond to the total of 10K.
★★★★★
Implementing Goal Seek in Excel
Step 1: Select the cell that you need to change to the "Goal" value. In this case, its the Total.
Step 2: Lets look at the shortcut to implement Goal Seek. Key Press ALT and press A W G. Release the ALT key. This will bring the Goal Seek setting window.
You can also navigate through the ribbon by going to DATA - What If Analysis - Goal Seek.
Step 3: Lets fill in the parameters of Goal Seek Analysis.
Set Cell - Will now auto populate the address of the "Total value" cell. If not auto-populated, select the cell corresponding to "Total Value".
To Value - Populate this with the "Goal". In this case its 10000. (Note: In case of percentage... say if you need to give a target of 60%, you need to give the value as 0.6 and not 60)
By Changing value - This is the cell that needs to be modified in order to reach our Goal. In this case, it's the invoice amount.
★★★★★
Limitation of Goal Seek Analysis
If you have a table where you need to implement Goal Seek on an entire column, you will have to do it one cell at a time. There is no drag option/ format paint or any such easy way to replicate Goal Seek in the neighboring cells.
However, you can definitely implement Goal Seek to a column/table via VBA. This will be a separate video - • Goal Seek Analysis for Multiple Cells (Exc...
#goalseek #goalseekexample #learn365club
Music: https://www.bensound.com