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.

NPV-analysis-shellVBA2.xlsm

**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

**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)

Capture.JPG

**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

Capture.JPG

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

Thread: Goal Seek without Cell Reference

Thread Tools

Display

Goal Seek without Cell Reference

Tags for this Thread

Posting Permissions