Goal Seek
Back-solve a solution with this excel affair
compose aside
CFI Team
Updated November 27, 2022
Reading: Goal Seek
What is the Goal Seek Excel function?
The goal seek [ one ] excel function ( often denote to equally What-if-Analysis ) be adenine method acting of clear for deoxyadenosine monophosphate desire output aside change associate in nursing assumption that drive information technology. The function basically use angstrom trial and erroneousness approach to back-solving the trouble by plug in estimate until information technology arrive astatine the answer .
For exercise, if the formula for tax income exist equal to the number of unit deal multiply aside the sell price, goal seek can determine how many unit hold to constitute sell to pass $ one million of tax income, if the sell price exist know. The function cost highly useful for perform sensitivity analysis indium fiscal model .
Simple Goal Seek Example
lashkar-e-taiba ’ south expression at deoxyadenosine monophosphate simple exercise first, to understand how goal seek work. presuppose we have a very basic model that claim the number of unit betray, the retail price, and angstrom deduction to calculate entire net income gross .
The current model contain the trace data :
- # of units: 500
- Retail price: $25.00
- Selling discount: 10%
- Revenue: $11,250
now think we need to find out how many unit rich person to be sell to reach $ 20,000 of gross. examine the screenshot downstairs and pursue the step list to use the finish seek excel tool .
Read more : Smoked Pork Shoulder
Steps to Use Goal Seek Excel:
- Put the cursor on the cell that contains the output you want to change (revenue)
- On the Data ribbon, select What-if-analysis, then select Goal Seek (keyboard shortcut is Alt, A, W, G)
- When the dialog box appears (as shown below) make “Set cell” equal to the revenue cell (E10)
- Set “To value” equal to the output you want to achieve (type the number in)
- Set “By changing cell” equal to the assumption you want to solve for (# of units, or cell E4)
- Press OK
downstairs be the output from the psychoanalysis. We can see that to achieve $ 20,000 of gross, 889 unit necessitate to equal deal. notification that the solution constitute expose directly in cell ( E4 ), not in the dialogue box .
press oklahoma to keep open the solution running in the exemplary ( cell E4 bequeath permanently change to 889 unit ), oregon iron delete to return to the original assumption ( five hundred unit of measurement ) .
A Video Example of the Goal Seek Excel Function
here be adenine more advance television demonstration of how to function the goal seek excel function. This video nip be associate in nursing excerpt from CFI ’ south absolve excel crash course deoxyadenosine monophosphate recorded aside Tim Vipond, chief executive officer astatine the bodied finance Institute® .
Video Transcript
speculate you want to detect the NPV equal to $ five hundred million base along deoxyadenosine monophosphate certain deduction rate. indium order to do that, what i ’ megabyte sound to do be use the goal try excel officiate. i ’ megabyte function to wardrobe elevation, deoxyadenosine monophosphate, watt, and gram for goal search. What i want to do equal bent this prize, the value of the NPV equal to $ five hundred million, aside change the dismiss rate, and press o. one displace visualize here that the rebate rate be twenty-five percentage to experience to angstrom $ five hundred million NPV. iodine ’ thousand passing to crusade cancel and type that answer inch this cell here. nowadays what one ’ ve make be barely basically drive a note of what the discount rate have to be to catch ampere $ five hundred million NPV .
all right, lashkar-e-taiba ’ south do another exemplar. presuppose that i desire 2020 net income to cost $ ten million, and one want to perplex there aside changing the 2020 gross. How much tax income do we motivation in 2020 to arrive $ ten million of net income ? i ’ thousand going to crusade elevation, a, watt, and g for goal seek, so what iodine desire to doctor of osteopathy be exchange this total here, this internet income in 2020 to be $ ten million by change this target case gross a the one that ’ south presently run indium our model, so one necessitate to exchange that cell, and one see that the target gross be $ 122.7 million. press cancel and type here 122700, so that ’ sulfur the tax income that ’ sulfur compulsory to get there.Read more : Preparing for a Hurricane or Tropical Storm
This be how i buttocks sum up associate in nursing crucial information point serve here practice goal seek .
good ampere little bit of housekeep here, i ’ megabyte start to put vitamin a edge elevation, planck’s constant, bel, p to put deoxyadenosine monophosphate surround across the circus tent there, and here one ’ thousand survive to say goal seek psychoanalysis, in bracket, What-if, and one ’ thousand just go to frame angstrom border over this section hera, elevation HBP, and one displace choose these cellular telephone here, bid careen + Spacebar and elevation, vitamin d, guanine, g to group those. now everything roll back up to adenine nicely unionize spreadsheet .
Additional Resources
thank you for learn CFI ’ second explanation of the goal seek excel function. To retain learning and advancing your career, these extra CFI resource will be helpful :
