Author Topic: EXCEL help - I know we have smart gurus here  (Read 3006 times)

FiguringItOut

  • Pencil Stache
  • ****
  • Posts: 812
  • Location: NYC
EXCEL help - I know we have smart gurus here
« on: March 25, 2016, 07:12:07 AM »
I don't know where else to ask.  I googled, but the explanation I found did not make sense.

I have a spreadsheet I received from a client.  They are using formula
{=TABLE (CellA,CellB)} to calculate sensitivity analysis (including squiggly brackets).  This formula is used 5 separate times, all times pointing to the same two cells and getting 5 different results. 

What exactly is it supposed to do?

It looks like this:

CellA = 20.7% - WACC Assumption
CellB = 1.4% - Probability

CellC = $1,112.3 - NPV value - calculated using the WACC % of total future cash flows provided separately.
CellD = $15.57 - Risk Adjusted NPV - calculated as NPV times Probaility = CellB*CellC

Then there is a small table:

16%      18%      20.7%      22%      24%    
28.2621.6815.5713.4010.73

All purple numbers are hardcoded.  Red numbers all have that {=TABLE(CellA,CellB)} formula in them. 

Can someone please explain what this formula does and how can I recalculate these results without using this formula. 

Thank you very much in advance.
« Last Edit: March 25, 2016, 07:13:55 AM by FiguringItOut »

Paul | pdgessler

  • 5 O'Clock Shadow
  • *
  • Posts: 93
  • Age: 33
  • Location: Appleton, WI
Re: EXCEL help - I know we have smart gurus here
« Reply #1 on: March 25, 2016, 08:31:52 AM »
This thread should probably be moved to "Off-Topic".

This is difficult to explain in words, but this is a "What-If Analysis" in Excel lingo. It allows you to examine the effects of changing an input (or two inputs) to a formula without having to re-key the formula with different cell references or copy the formula around the sheet.

Excel's reference page may be helpful, though you said you already did a search: Calculating multiple results by using a data table

The arrangement of cells is significant, but from the context of the calculation results, it looks like they are recalculating CellD for each of the WACC Assumption values (the top row of the "small table"). This might explain it better than I can, or things might "click" if you see this applied to a different situation.

The gist of it is you enter the formula to calculate the result for the base case (a formula with references to CellA and Cell B). Then, to the right of that, you enter the "What-If" cases for WACC Assumption. (Here, 16%, 18%, 20.7%, ...) Below the "base case" formula, you enter the "What-If" cases for Probability. You haven't shown this part, but since your TABLE function call uses two arguments, I assume it is there. See the reference link for cell arrangement for one-dimensional What-If analyses—things are slightly different then.

Once the formula and "What-If" cases are entered, select the full range of cells starting from the "base case" formula all the way out so that both dimensions of "What-If" inputs are selected. Then go to the Data tab on the ribbon and choose "What-If Analysis" from the "Data Tools" group. Choose "Data Table..." from the dropdown. Then you get a dialog from Excel asking for the Row and Column input cells. This tells Excel which cell reference should get replaced in the "base case" formula. So choose "CellA" for the "Row input cell" (What-If cases in the upper row of the table will replace CellA in the base formula) and "CellB" for the "Column input cell" (What-If cases in the left column of the table will replace CellB in the base formula). Clicking OK will fill in the table for each of the What-If scenarios.

The curly braces around the formula means that this is an array formula (it operates on ranges of cells, not individual cells) and in this case, you cannot enter this formula by hand. It has to be created using the "What-If Analysis" wizard.

Tyler

  • Handlebar Stache
  • *****
  • Posts: 1198
Re: EXCEL help - I know we have smart gurus here
« Reply #2 on: March 25, 2016, 08:37:12 AM »
What Paul said.

Long story short, a data table allows you to take a formula in a single cell and find the different outputs from multiple inputs.  In this case, your red numbers are simply the calculated Risk Adjusted NPVs given the above WACC Assumption.  The way around it is to just make the calculations in individual cells in the table.

Row A: WACC Assumption
Row B: Probability
Row C: NPV Calculation (formula in every cell in the row)
Row D: Risk Adjusted NPV (formula in every cell in the row)

Make sense?
« Last Edit: March 25, 2016, 08:39:13 AM by Tyler »

sparky28

  • 5 O'Clock Shadow
  • *
  • Posts: 28
  • Location: Chicago,IL
Re: EXCEL help - I know we have smart gurus here
« Reply #3 on: March 25, 2016, 08:55:50 AM »
This is a bit off-topic from the original question, but what's the benefit of the What-If Data Table? Using the example Paul linked to, the answer can be provided with =PMT($C2/12,D$1,-$B$4) carried down and over for the range. Same calculation process, easier to visualize and its not volatile (doesn't refresh whenever anything else changes)

FiguringItOut

  • Pencil Stache
  • ****
  • Posts: 812
  • Location: NYC
Re: EXCEL help - I know we have smart gurus here
« Reply #4 on: March 25, 2016, 08:57:13 AM »
What Paul said.

Long story short, a data table allows you to take a formula in a single cell and find the different outputs from multiple inputs.  In this case, your red numbers are simply the calculated Risk Adjusted NPVs given the above WACC Assumption.  The way around it is to just make the calculations in individual cells in the table.

Row A: WACC Assumption
Row B: Probability
Row C: NPV Calculation (formula in every cell in the row)
Row D: Risk Adjusted NPV (formula in every cell in the row)

Make sense?

THANK YOU!!!!

This actually makes sense. 

Paul | pdgessler

  • 5 O'Clock Shadow
  • *
  • Posts: 93
  • Age: 33
  • Location: Appleton, WI
Re: EXCEL help - I know we have smart gurus here
« Reply #5 on: March 25, 2016, 09:07:21 AM »
This is a bit off-topic from the original question, but what's the benefit of the What-If Data Table? Using the example Paul linked to, the answer can be provided with =PMT($C2/12,D$1,-$B$4) carried down and over for the range. Same calculation process, easier to visualize and its not volatile (doesn't refresh whenever anything else changes)

Generally I agree: I tend to just use dollar signs judiciously in these cases. I think it really boils down to how particular you want to get about "Don't repeat yourself". With a What-If table, you can edit the base formula in one place and not have to remember to drag it around to update the whole range. It separates the scenarios from the calculation that is used.

Tyler

  • Handlebar Stache
  • *****
  • Posts: 1198
Re: EXCEL help - I know we have smart gurus here
« Reply #6 on: March 25, 2016, 09:17:02 AM »
This is a bit off-topic from the original question, but what's the benefit of the What-If Data Table? Using the example Paul linked to, the answer can be provided with =PMT($C2/12,D$1,-$B$4) carried down and over for the range. Same calculation process, easier to visualize and its not volatile (doesn't refresh whenever anything else changes)

Generally I agree: I tend to just use dollar signs judiciously in these cases. I think it really boils down to how particular you want to get about "Don't repeat yourself". With a What-If table, you can edit the base formula in one place and not have to remember to drag it around to update the whole range. It separates the scenarios from the calculation that is used.

It's the Excel equivalent of calling a formula as a subroutine rather than repeating it over and over.  In most cases it just adds unnecessary spreadsheet complexity, but I can imagine that it becomes useful in very large spreadsheets especially if it helps reduce the file size.  I may have to give that a try.