none
Goal seek across columns RRS feed

  • Question

  • I have developed a worksheet that does a series of financial calculations for up to 35 years – 2019 to a max of 2053.

    The worksheet allows me to run different scenarios for periods of a least 2 years and no more than 35 years. Each scenario starts with 2019 and runs sequentially by year. For example, I may want to run a scenario for the 5-year period from 2019 through 2023. I may want to run a completely different scenario for the 13-year period from 2019 through 2031.

    The final stage of the scenario analysis is to optimize a certain calculated amount for each of the years in the scenario (2019, 2020, etc) which appears on line 100 of the spreadsheet.

    I have in mind an optimal or “goal” value for the calculated values on row 100. For simplicity assume the goal value is constant and is equal to 100,000. Ideally though I would like to input a different goal value for each year of the scenario.

    I can input a goal value for each year of the scenario on row 99 and then use row 98 to calculate for each year the difference [D] between the starting value for that year (appearing on row 100) and the “goal” value for that year (appearing on row 99).

    If this difference [D] is > 0, I would like to determine the amount which will achieve the target value and have that value appear on row 15. However, if the difference [D] is < 0, I would like the amount needed to achieve the target value to appear on row 10.

    The calculations have to be done sequentially. This is so since the starting point for 2020 before running “goal seek” is not known until completing “goal seek” for 2019.

    The way the model has been developed there will always be a unique solution for each year.

    I am able to run “goal seek” for each year manually but this is not efficient and I would like to automate run the goal seek calculations across all years of a scenario.

    I found a suggested VBA solution to a somewhat similar problem which I suspect can be modified to do what I am hoping to do. The key difference between my problem and the problem for which the code below was written is that my problem may in some years require a solution on row 15 and in other solutions the solution may need to be on row 10 (see discussion a few paragraphs above). Since I am not yet conversant with VBA I am hoping to get some assistance.

    Sub Test()

        Dim i As Integer

        For i = 1 To 900

            If IsEmpty(Cells(28, i)) Then

                Cells(17, i).GoalSeek Goal:=1, ChangingCell:=Cells(28, i + 1)

            Else

                Cells(17, i).GoalSeek Goal:=1, ChangingCell:=Cells(28, i)

            End If

        Next i

    End Sub

    Wednesday, September 11, 2019 11:35 PM