0

Hello and thank you in advance for your help! This question is for excel.

I have created a dynamic model which outputs estimated annual interest revenue, and it depends on 5 inputs (average term length, average interest rate, etc.).

Each month, there's a new cohort of loans that originate with a slightly different set of values for my 5 inputs (month 1 of originations might have average term length of 1.5 years, but month 2 of originations might have average term length of 2.5 years).

I have forecasted what my 5 input variables will look like each month for the next 24 months (so I have 24 sets of scenarios). Is there a way my model can dynamically take in each of my 24 scenarios and output the estimated annual interest revenue to each one?

Thank you!

Stormbert Frogmichievoukhov
  • 1

1 Answers1

0

Well, here’s a pointer to get you going in the direction I have used. Once you have the model working, then doing this for 24 sheets should take 10 minutes or so:

Build the Model Sheet

Create your model in a sheet. Let’s say your model takes, as you said, 5 variable inputs, and say, provides some results, like 3 for example. Build the sheets in such a way that the top looks like this in A1 through I1:

S1

That's a very short scenario name, followed by 5 cells where inputs go, followed by my hypothetical 3 cells for results. Just type your values into these cells manually, and as you build your model, have your formulas look to B1 through F1 for their inputs. When the model is done, have formulas in G1 through I1 that gather the results in one easy to access place.

Get the model working exactly the way you want it. NAME THIS SHEET S1.

Build the Hub

In another sheet (Make it the first sheet in the book) create a table:

Scenario I1 I2 I3 I4 I5 R1 R2 R3
S1
S2
S3

You don't have yo use I1, etc, you can name the columns what they are, like sales volume, growth rates, etc.

Link the Model Master to the Hub Inputs

Back in the MODEL sheet, you'll use the INDIRECT()function to get your 5 input values into the sheet. The formula for B1 is =VLOOKUP($A1,Hub!$A2:$I25,2,0). The formula for C1 is =VLOOKUP($A1,Hub!$A2:$I25,3,0). Follow this for hub columns 4,5,6.

Link the Hub to the Model Master for Retrieving Outputs

Fill in your input values at least for scenario one and make sure they Model sheet works right and are taking their input from the hub.

To get the results in columns G, H, I: The formula in G2 will use INDIRECT() to fetch the values from your model sheets. =INDIRECT($A$1&"!G1") In this first sheet this resolves to =S1!G1 which is the first result from S1. H2 will be =INDIRECT($A$1&"!H1"), and do the same for I2. Fill those formulas down all 24 rows. The S1 results should populate, the S2-S24 shouldn't yet, they don't exist yet.

Now copy the 23 additional model sheets from S1, and name them S2 through S24. Go to cell A1 in each sheet and give it the sheet name, S1 through S24. This is easier than using a formula to "learn" the sheet name of the local worksheet.

Now Test It Out

Start populating inputs into S1 through S24 and see if you get expected results.

Max R
  • 427