Custom goal seek methodology

[MS_Xsmell]

The standard goal seek function runs slowly in a financial model I am building when solving for an acquisition price that meets a user defined investment hurdle target.

Solver seems to provide more efficient options to ‘goal seek’ (newtons method, etc), but some users will not have the addin or ability to install the add in.

Any guidance on code that will use alternative approaches to the standard goal seek application?

It’s a linear function: acquisition price goes up= investment metric goes down… acq price goes down, metric goes up.

Thoughts? Thanks in advance.

 

[BSALV]

if it's lineair and you have 2 known points, you can calculate a 3rd.

Map2
	A	B	C
3		x	y
4	dataset1	5	10
5	dataset2	6	12
6
7	known Y, wanted X	7,5	15
8	known X, wanted Y	3	6
Blad1

Cell Formulas
Range	Formula
B7	B7	=FORECAST(C7,B4:B5,C4:C5)
C8	C8	=FORECAST(B8,C4:C5,B4:B5)

 

[MS_Xsmell]

if it's lineair and you have 2 known points, you can calculate a 3rd.

Map2
	A	B	C
3		x	y
4	dataset1	5	10
5	dataset2	6	12
6
7	known Y, wanted X	7,5	15
8	known X, wanted Y	3	6
Blad1

Cell Formulas
Range	Formula
B7	B7	=FORECAST(C7,B4:B5,C4:C5)
C8	C8	=FORECAST(B8,C4:C5,B4:B5)

Thanks BSALV.

In my case, the workbook is only showing [dataset1], I'm trying to understand the VBA necessary to perform the following:

1. save the current X & Y values active in the workbook [dataset1]
2. change the value of X to a different value and observe the resulting Y value; save the new X&Y values as [dataset2]
3. use the X & Y values from [dataset1] and [dataset2] to solve for a user defined Y value

Any suggestions? Thanks in advance.

 

[BSALV]

#2 was the manual version, you check with 2 X-values and receive the Y-value, write the 4 values in B4:C5.
Then depending on your situation with a known X or Y what is the corresponding Y or X is line 7 or 8

This is a VBA-version, with a lineair function between X and Y in A15:B15

xxxxxxx.xlsx
	A	B
14	x	y
15	200	46,6666
Blad1

Cell Formulas
Range	Formula
B15	B15	=100-0.266667*A15

Sub forecasting()
     Dim myX(1), myY(1)
     
     'part1 : with 2 X-values, what is the Y-value ??
     For i = 0 To 1
          If i = 0 Then Range("A15") = 100 Else Range("A15") = 200
          myX(i) = Range("A15").Value
          myY(i) = Range("B15").Value
     Next
     
     'part2 : what do you want, known Y -> corresponding X ?
     
     MsgBox "intersect with X-axis (Y=0) is at " & WorksheetFunction.Forecast(0, myX, myY)
    MsgBox " with Y=60, the X-value is " & WorksheetFunction.Forecast(60, myX, myY)
    
      'part3 : what do you want, known X -> corresponding Y ?
     MsgBox "intersect with Y-axis (X=0) is at " & WorksheetFunction.Forecast(0, myY, myX)
       MsgBox "with X=150, the Y-value is " & WorksheetFunction.Forecast(150, myY, myX)
End Sub

 

[MS_Xsmell]

#2 was the manual version, you check with 2 X-values and receive the Y-value, write the 4 values in B4:C5.
Then depending on your situation with a known X or Y what is the corresponding Y or X is line 7 or 8

This is a VBA-version, with a lineair function between X and Y in A15:B15

xxxxxxx.xlsx
	A	B
14	x	y
15	200	46,6666
Blad1

Cell Formulas
Range	Formula
B15	B15	=100-0.266667*A15

Sub forecasting()
     Dim myX(1), myY(1)
    
     'part1 : with 2 X-values, what is the Y-value ??
     For i = 0 To 1
          If i = 0 Then Range("A15") = 100 Else Range("A15") = 200
          myX(i) = Range("A15").Value
          myY(i) = Range("B15").Value
     Next
    
     'part2 : what do you want, known Y -> corresponding X ?
    
     MsgBox "intersect with X-axis (Y=0) is at " & WorksheetFunction.Forecast(0, myX, myY)
    MsgBox " with Y=60, the X-value is " & WorksheetFunction.Forecast(60, myX, myY)
   
      'part3 : what do you want, known X -> corresponding Y ?
     MsgBox "intersect with Y-axis (X=0) is at " & WorksheetFunction.Forecast(0, myY, myX)
       MsgBox "with X=150, the Y-value is " & WorksheetFunction.Forecast(150, myY, myX)
End Sub

Much appreciated.

It it amazing how much faster this works than goal seek. 5 seconds of excel grinding vs a near instant result. Thanks!