• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create sensitivity data table based off input created from goal seek value

MR06

New Member
Hi all,

I am trying to create a sensitivity table that tells me what amount of equity I need to include in a deal in order to get a fixed IRR as the year 3 sales price changes. I have created a simplified version of my problem in the attached spreadsheet.

Assuming I want the IRR to be 25%, I can easily figure out what equity I need by using goal seek for the existing inputs (currently ~$437k). What I want to do is see how that changes as the year 3 price changes, but when I use the what-if data table, it spits out the same $437k number for each value. Can someone provide a solution as to how I can create a data table with varying year 3 prices, and the corresponding equity required to achieve 25% IRR?

Thanks!!

MR06

As a side note, I've also noticed that if I manually adjust one of the inputs slightly, the IRR dynamically updates (as it should). However, if I then re-run goal seek, if the dynamically updated IRR is close enough to what I put in goal seek, goal seek doesn't run. I left my example showing this (IRR is at 24.977, if you run goal seek at 25% nothing happens). Anyone have a solution to this?
 

Attachments

  • MR06 Example.xlsx
    10.3 KB · Views: 16
XIRR can be finicky. You may want to check all your cells to be certain they are formatted correctly.
 
Hi Eloise,

The issue isn't calculating the IRR, that's working fine.

The problem is currently I need to manually adjust the unit price in order to goal seek the equity that yields a 25% IRR, and repeat that for each different unit price. I am looking for a way to automate that.

Thanks!
 
Hi Hui,

Thank you for this.

I think I must be misleading in my explanation. What I am looking to do is set up a table that says "if year 3 price is X, what amount of equity do I need to input in order to yield a 25% IRR." The manual process is 1) hard code the price in year 3, which then dynamically updates the rest of the numbers in the table 2) run goal seek changing the equity input cell to achieve a 25% IRR, which then hard codes the equity and gives me the answer I need for that specific price 3) hard code a new year 3 price and repeat.

What I would like is to create a table that shows varying year 3 prices as rows, and the necessary equity input to achieve 25% IRR as the column.

Thanks!
 
The great thing about Data Tables is that they add flexibility to models
To fix this revised problem I
1. Changed the values in the data table to 50,000 to 500,000
2. Relink the data table to B10 instead of G6
3. Voila
upload_2017-7-3_21-34-3.png

see attached file
 

Attachments

  • MR06 Example.xlsx
    16.8 KB · Views: 5
Ok I misread the revised question
here is the Two Way lookup solution
upload_2017-7-3_21-42-6.png
 

Attachments

  • MR06 Example.xlsx
    17.8 KB · Views: 16
Hi Hui,

Thanks for your continued help.

I need the 25% IRR to be fixed, so I'm not looking for a sensitivity analysis on how slightly differing equity levels impact IRR. What I'm looking for is how pricing levels impact the equity required for a 25% IRR. In other words, your data table is solving for 1 specific price (or equity) input. What I need is for the data table to run a loop that solves the equity input at 25% IRR for differing pricing inputs.

I just did this manually, and here is the output that I got. The issue is it takes like 4 minutes and a ton of clicking, and I have to run this analysis on hundreds of different data sets.

Equity @ 25% IRR
9 $450,015
10 $447,223
11 $444,439
12 $441,662
13 $438,893
14 $436,132
15 $433,379


Thanks,

Matty
 
Or with a Table of Solutions
upload_2017-7-3_21-46-32.png

also Change Cell D31 to 25%
 

Attachments

  • MR06 Example.xlsx
    18.1 KB · Views: 18
Hi Hui,

Thank you for continuing to help.

This last little bit, the table of solutions, is exactly what I need. Can you let me know the steps to recreate this?

Thanks so much!
 
Examine the formulas in the Table
It is simply doing the same calculations for each Column with a different price

If You are literally doing hundreds of these I would approach the whole thing differently

I would establish a list of parameters eg: 1 to 300 with columns for each field
Then use a Data Table to set the inputs and retrieve the outputs and tabulate them

Can you post a small list of say 10 inputs and I will setup a dummy file for you
 
Hi Hui,

The example I provided is very simplified from the real thing. My actual models have much more complicated models that don't all operate the same way, although they do all have a price input, an equity input, and an IRR output. What I'm really looking for is a way to cut and paste the table of solutions in each different spreadsheet such that I can update the links within the formulas and quickly have this table as an output.

Thanks!
 
Try the following
1. Copy the Blue Bordered area to another file
upload_2017-7-3_22-24-39.png

2. In the new file, Select the red area
3. Goto the Data, What If Analysis, Data Table command
4. Link the Data Table to the new locations in the new model
5. Link the Upper Left cell in the Red Area Cell C16 to the new IRR Calculation in the new Model
upload_2017-7-3_22-26-5.png

6. If the Upper Left cell of the Blue area is not B15 you may have to adjust the formulas in Row 31, so study them in this model first
 

Attachments

  • MR06 Example.xlsx
    13 KB · Views: 20
Hi Hui,

I have followed your steps and am experiencing 2 issues:

1) this is not a linear function but I'm 100% positive that the higher the equity, the higher the resulting IRR. However, as you'll see in the attached image, for whatever reason the data table is incorrectly spitting out results where sometimes more equity = lower IRR

upload_2017-7-12_8-47-26.png

2) if I change the equity input cell (B10 in the example) the forecast results change drastically, however in reality there is only 1 answer. How am I supposed to know if I'm getting the correct answer?

Thanks!

MR06
 
Hi Hui,

One other thing: the data table doesn't match my model's output for IRR. In the image I posted, for example, the data table shows $3M equity and price of $179 yield a 10.58% IRR, but my model shows an 11.8% (hence the upper left cell shows 11.8%). Any thoughts on this?

Thanks!

MR06
 
I realise that this isn't the model I posted above
Can you send me the actual model via email or a Conversation
 
Back
Top