PDA

View Full Version : [SOLVED] Goal Seek without Cell Reference



tucjake
05-29-2016, 12:02 PM
Hello!

I'm working on a financial modeling sub. I'd like the model to be completely self-contained without the user manipulating the actual worksheet.

There are two scenarios; a baseline and an alternative. Basically, the user enters cash flows at time zero for various projects, a discount rate, and a baseline growth rate through input boxes.

The sub calculates the two different NPVs and finds the difference. I would like to use Goal Seek/Solver (or anything) to determine what alternate growth rate would be required for the difference between the baseline and alternate NPVs to be 0 (without referencing any cells, just declared variables).

Thanks in advance.

SamT
05-30-2016, 07:56 AM
I'd like the model to be completely self-contained without the user manipulating the actual worksheet.That just requires a VBA UserForm.

In order to improve the chances of getting help on this project, pretend you are speaking to idiot savants who have no clue about accounting and finance.

For example: What are Cash flows, Baselines, Growth Rates, Discount Rates, and NPVs, and how do they compute and what are their relationships?

If you can tell me how you do it manually, I can do it programmatically.

tucjake
05-30-2016, 08:29 AM
Thank you for your reply!

Basically, what I want to do is set variable x (not cell) to value 0 by changing variable y (not cell).

Please don't hesitate to ask for more clarification. I am very new to VBA/coding.

16291

Paul_Hossler
05-30-2016, 09:50 AM
1. Also probably not a good idea to name your variables the same name as your sub



Sub AltGrowth()
Dim Tax As Double
Dim Discount As Double
Dim Periods As Double
Dim BaseGrowth As Double
Dim AltGrowth As Double




2. A For / Next might be easier



Dim i As Integer
For i = 1 To Periods
BaseComArea(i) = ((1 - BaseComP1) / Periods) * BaseComSQFT
BaseComAV(i) = BaseComAV(i - 1) * (1 + BaseGrowth)

.............

Next I




3. As a long range plan, you can use a UserForm for getting user inputs all at once



Tax = InputBox("Enter the Tax Rate (as decimal)")
Discount = InputBox("Enter the Discount Rate (as decimal)")
Periods = InputBox("Enter the number of periods")
BaseGrowth = InputBox("Enter the Baseline Growth Rate (as decimal)")

...


but there are no error checks, so if you or someone entered the 6% tax rate as '6' instead on '.06' there'd be problems

tucjake
05-30-2016, 10:02 AM
I'm going to incorporate all your suggestions! Thank you!

Any thoughts on the Goal Seek?

mdmackillop
05-30-2016, 10:41 AM
I can't see how goal seek can be used directly. An old iteration solution is posted here (http://www.excelforum.com/showthread.php?t=376747&p=1032643&viewfull=1#post1032643)

Paul_Hossler
05-30-2016, 01:11 PM
I did a little restructuring mostly because I didn't want to type all those InputBox's

I get the impression, that NPVTotalBase is fixed, and NPVTotalAlt is to be GoadSeeked to come as close as possible to NPVTotalBase??????


This is the formula, but I can't see what parameter in the 4 terms is to vary so come up with a 'almost 0' differencez?



NPVTotalAlt = NPV(Discount, AltComRev()) + NPV(Discount, AltFlexRev()) + NPV(Discount, SFRRev()) + NPV(Discount, MFRRev())





Option Explicit
Sub MyAltGrowth()
Dim Tax As Double
Dim Discount As Double
Dim Periods As Double
Dim BaseGrowth As Double
Dim AltGrowth As Double
Dim BaseComP1 As Double
Dim BaseFlexP1 As Double
Dim AltComP1 As Double
Dim AltFlexP1 As Double
Dim SFRP1 As Double
Dim MFRP1 As Double
Dim BaseComSQFT As Double
Dim BaseFlexSQFT As Double
Dim AltComSQFT As Double
Dim AltFlexSQFT As Double
Dim SFRGrossSQFT As Double
Dim SFRBuild As Double
Dim SFRNetSQFT As Double
Dim MFRSQFT As Double
Dim ComAV As Double
Dim FlexAV As Double
Dim SFRAV As Double
Dim MFRAV As Double
Dim ComFAR As Double
Dim FlexFAR As Double
Dim SFRFAR As Double
Dim MFRFAR As Double
Dim NPVTotalBase As Double
Dim NPVTotalAlt As Double
Dim NPVDiff As Double
With Worksheets("Input-Output")
Tax = .Range("B5")
Discount = .Range("B6")
Periods = .Range("B7")
ComFAR = .Range("B8")
FlexFAR = .Range("B9")
ComAV = .Range("B10")
FlexAV = .Range("B11")

BaseGrowth = .Range("B15")
BaseComP1 = .Range("B16")
BaseFlexP1 = .Range("B17")
BaseComSQFT = .Range("B18")
BaseFlexSQFT = .Range("B19")

AltComP1 = .Range("B23")
AltFlexP1 = .Range("B24")
SFRP1 = .Range("B25")
MFRP1 = .Range("B26")

AltComSQFT = .Range("B27")
AltFlexSQFT = .Range("B28")
SFRGrossSQFT = .Range("B29")
SFRBuild = .Range("B30")
MFRSQFT = .Range("B31")
SFRFAR = .Range("B32")
MFRFAR = .Range("B33")
SFRAV = .Range("B34")
MFRAV = .Range("B35")

End With

SFRNetSQFT = SFRGrossSQFT * SFRBuild
Dim BaseComArea(1000) As Double
Dim BaseComAV(1000) As Double
Dim BaseComValue(1000) As Double
Dim BaseComRev(1000) As Double
Dim BaseFlexArea(1000) As Double
Dim BaseFlexAV(1000) As Double
Dim BaseFlexValue(1000) As Double
Dim BaseFlexRev(1000) As Double
Dim AltComArea(1000) As Double
Dim AltComAV(1000) As Double
Dim AltComValue(1000) As Double
Dim AltComRev(1000) As Double
Dim AltFlexArea(1000) As Double
Dim AltFlexAV(1000) As Double
Dim AltFlexValue(1000) As Double
Dim AltFlexRev(1000) As Double
Dim SFRArea(1000) As Double
Dim SFRAV2(1000) As Double
Dim SFRValue(1000) As Double
Dim SFRRev(1000) As Double
Dim MFRArea(1000) As Double
Dim MFRAV2(1000) As Double
Dim MFRValue(1000) As Double
Dim MFRRev(1000) As Double
BaseComArea(0) = BaseComP1 * BaseComSQFT
BaseComAV(0) = ComAV
BaseComValue(0) = BaseComArea(0) * BaseComAV(0) * ComFAR
BaseComRev(0) = BaseComValue(0) * Tax
BaseFlexArea(0) = BaseFlexP1 * BaseFlexSQFT
BaseFlexAV(0) = FlexAV
BaseFlexValue(0) = BaseFlexArea(0) * BaseFlexAV(0) * FlexFAR
BaseFlexRev(0) = BaseFlexValue(0) * Tax
AltComArea(0) = AltComP1 * AltComSQFT
AltComAV(0) = ComAV
AltComValue(0) = AltComArea(0) * AltComAV(0) * ComFAR
AltComRev(0) = AltComValue(0) * Tax
AltFlexArea(0) = AltFlexP1 * AltFlexSQFT
AltFlexAV(0) = FlexAV
AltFlexValue(0) = AltFlexArea(0) * AltFlexAV(0) * FlexFAR
AltFlexRev(0) = AltFlexValue(0) * Tax
SFRArea(0) = SFRP1 * SFRNetSQFT
SFRAV2(0) = SFRAV
SFRValue(0) = SFRArea(0) * SFRAV2(0) * SFRFAR
SFRRev(0) = SFRValue(0) * Tax
MFRArea(0) = MFRP1 * MFRSQFT
MFRAV2(0) = MFRAV
MFRValue(0) = MFRArea(0) * MFRAV2(0) * MFRFAR
MFRRev(0) = MFRValue(0) * Tax

Dim i As Long
For i = 1 To Periods

BaseComArea(i) = ((1 - BaseComP1) / Periods) * BaseComSQFT
BaseComAV(i) = BaseComAV(i - 1) * (1 + BaseGrowth)
BaseComValue(i) = BaseComArea(i) * BaseComAV(i) * ComFAR
BaseComRev(i) = BaseComValue(i) * Tax

BaseFlexArea(i) = ((1 - BaseFlexP1) / Periods) * BaseFlexSQFT
BaseFlexAV(i) = BaseFlexAV(i - 1) * (1 + BaseGrowth)
BaseFlexValue(i) = BaseFlexArea(i) * BaseFlexAV(i) * FlexFAR
BaseFlexRev(i) = BaseFlexValue(i) * Tax

AltComArea(i) = ((1 - AltComP1) / Periods) * AltComSQFT
AltComAV(i) = AltComAV(i - 1) * (1 + BaseGrowth)
AltComValue(i) = AltComArea(i) * AltComAV(i) * ComFAR
AltComRev(i) = AltComValue(i) * Tax

AltFlexArea(i) = ((1 - AltFlexP1) / Periods) * AltFlexSQFT
AltFlexAV(i) = AltFlexAV(i - 1) * (1 + BaseGrowth)
AltFlexValue(i) = AltFlexArea(i) * AltFlexAV(i) * FlexFAR
AltFlexRev(i) = AltFlexValue(i) * Tax

SFRArea(i) = ((1 - SFRP1) / Periods) * SFRNetSQFT
SFRAV2(i) = SFRAV2(i - 1) * (1 + BaseGrowth)
SFRValue(i) = SFRArea(i) * SFRAV2(i) * SFRFAR
SFRRev(i) = SFRValue(i) * Tax

MFRArea(i) = ((1 - MFRP1) / Periods) * MFRSQFT
MFRAV2(i) = MFRAV2(i - 1) * (1 + BaseGrowth)
MFRValue(i) = MFRArea(i) * MFRAV2(i) * MFRFAR
MFRRev(i) = MFRValue(i) * Tax

Next I

With Worksheets("Input-Output")
.Range("E5").Value = NPV(Discount, BaseComRev())
.Range("E6").Value = NPV(Discount, BaseFlexRev())

.Range("E11").Value = NPV(Discount, AltComRev())
.Range("E12").Value = NPV(Discount, AltFlexRev())
.Range("E13").Value = NPV(Discount, SFRRev())
.Range("E14").Value = NPV(Discount, MFRRev())
End With

NPVTotalBase = NPV(Discount, BaseComRev()) + NPV(Discount, BaseFlexRev())

NPVTotalAlt = NPV(Discount, AltComRev()) + NPV(Discount, AltFlexRev()) + NPV(Discount, SFRRev()) + NPV(Discount, MFRRev())
NPVDiff = NPVTotalBase - NPVTotalAlt
'NPVDiff.GoalSeek Goal:=0, ChangingCell:=AltGrowth

MsgBox "Alt growth is " & AltGrowth

End Sub

tucjake
05-30-2016, 04:48 PM
Thank you both!

I'd really like this thing to be as flexible and self-contained as possible. On project may have 5 periods and another may have 1,000.

NPVTotalBase will not change. NPVTotalAlt will change because Goal Seek will change AltGrowth which will change S/MFRValue, which will change NPVTOTALALT.

Could a workaround be exporting AltGrowth to a cell and using Goal Seek to optimize?

Paul_Hossler
05-30-2016, 05:34 PM
1. In your VBA, the variable AltGrowth is not set or used (except in the .GoalSeek which doesn't work) so that doesn't really help

2. SFRFAR and MFRFAR are set to B32 and B33 initially, and then used in the loop, so I assume you mean the arrary MFRValue(I) entries



SFRArea(i) = ((1 - SFRP1) / Periods) * SFRNetSQFT
SFRAV2(i) = SFRAV2(i - 1) * (1 + BaseGrowth)
SFRValue(i) = SFRArea(i) * SFRAV2(i) * SFRFAR '<<<<<<<<<<<<<<<<<<<<<<
SFRRev(i) = SFRValue(i) * Tax

MFRArea(i) = ((1 - MFRP1) / Periods) * MFRSQFT
MFRAV2(i) = MFRAV2(i - 1) * (1 + BaseGrowth)
MFRValue(i) = MFRArea(i) * MFRAV2(i) * MFRFAR '<<<<<<<<<<<<<<<<<<<<<<<<<
MFRRev(i) = MFRValue(i) * Tax


So where does AltGrowth come into play to change S/MFRValue, which will change NPVTOTALALT?

tucjake
05-30-2016, 09:43 PM
Yes, Paul! You are correct!

I made the corrections so the S/MFRValue arrays now grow by AltGrowth.

So in the worksheet model, it doesn't really matter what AltGrowth is set to (in the VBA model, I set it equal to BaseGrowth before the loop.

Any thoughts? I really appreciate everyone's help.

Paul_Hossler
05-31-2016, 07:28 AM
Here's my attempt to do sort of a custom goal seek

Very much not sophisticated, AND I don't have a feel for the numbers so you should look closely.

The results do appear to be reasonable (to me at least)

I broke the long macro up into modules so I could see it better

If this were to be a long running project, I think there's other things to restructure that would improve maintainability and user friendly-ness

tucjake
05-31-2016, 11:34 AM
Thank you, Paul! Three cheers for Paul!

Paul_Hossler
05-31-2016, 11:48 AM
1. Check it out
2. Check it out again
3. Check it out some more

tucjake
06-13-2016, 04:24 PM
Paul, would you mind taking one more look?

I had to shelve the project as soon as you posted your solution. I've finally gotten back to it. I'm trying to incorporate a userform, have the input-output sheet as a check to see if the code works, and have the output on the calculator page. I'm still quite new to VBA. I really appreciate your help.

Paul_Hossler
06-14-2016, 01:38 PM
I can see a lot of effort went into the user forms, but, I think you'd be better off continuing with the WS approach. Just seems simpler

You'd need to store the previous UF inputs some place, and repopulate the UF each time the workbook is opened. Can be done, but a lot of effort (I'd store them in a hidden sheet like Input-Output if you do it that way)

Just as something to consider, I reformatted the original non-UF workbook, and used the worksheet change event to update the outputs when ever the inputs changed.

The yellow cells are unprotected for user input, but the rest are protected so the user can't change them

WS needs to be protected (no password on the example)

16398

tucjake
06-14-2016, 03:09 PM
Wow. This looks great, Paul. The goal seek function doesn't seem to be working though (even in your screenshot). The difference in total NPVs should be set to 0 by changing Alt Growth Rate (in this setup, Target Growth Rate is no longer necessary). I had no idea this'd be such a beast. I really appreciate your help.

Paul_Hossler
06-15-2016, 07:22 AM
I was playing with it for a while. I did have a calculation error in the Base and Alt sums on he worksheet

TGR is used in your ProForma sheet

My 'Goal Seek' is still pretty crude, but a little more accurate (I think)

It basically steps up or down and when the difference changes signs, it pro rates the difference

16401

Check it out

tucjake
06-15-2016, 09:34 AM
You're a genius! Thank you!

Paul_Hossler
06-15-2016, 01:19 PM
Like I said, make sure to check it out very thoroughly

No guarantees, and you get what you pay for :)