Using What-If Scenario Analysis When Some Cells Contain Formulas

[ericrichard25]

Hello. I wanted to use the What-If Scenario Analysis feature (or some other method) to calculate the output (on the "Annual" tab) for the cells in the "2019 Adjusted EBITDA growth rate" table N13 through P13. The output is coming from cells B26 through D26. These cells are calculated using the cells above it in the same column. Some of the rows (i.e. rows 9, 10, and 11) are fixed for all 3 scenarios, but some rows (i.e. rows 12 and 15) contain formulas and so have different values for each sceanrio. I'm unsure how to deal with this without having to have the 3 separate columns of B, C, and D as I do now. Ideally I would just want to have one column (column B) and be able to compute different sceanarios using just the one column.

*Note: I am trying to atttach the spreadsheet but when I click on the paper clip icon an thin white bar appears but there is nothing for me to select. It seems the attachment feature isn't loading properly. I have attached an image of the spreadsheet instead.

SUP screenshot.png

[dflak]

Here is how to attach a workbook.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ericrichard25]

Thank you for the help on attaching a workbooks. It's now attached.

[FlameRetired]

Please try this in N13 filled down and across P14

Please Login or Register  to view this content.

[ericrichard25]

Thanks for the reply Dave. I want to delete columns C and D (Scenario 2 and 3) and just have Column A to use for the calculations for each of the 3 scenarios. Pasting the Index formula you gave me works if I keep columns C and D there, but not if I delete them.

[FlameRetired]

Sorry. I had misread that. Had a bad week.

With the understanding that column B has its precedents in column J these formulas reference column J directly. Please let me know if I have that wrong.

There are some formula segments in N17:P21. They have no function in the solutions. They are BandAids for my head to help me keep track of the dependency trails. I left them in place in case they help with trouble shooting in the future.

Also find a copy of 'Annual'. There I deleted columns C:D to see if the formulas hold. They did.

In N13:P13

Please Login or Register  to view this content.

In N14:P14

Please Login or Register  to view this content.

These might be shortened / simplified,( but I'm too "dizzy" right now to tell. LOL )

Edit: Please see next post.

[FlameRetired]

Those can be simplified. I over complicated this.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ericrichard25]

Hi FlameRetired. Thanks for the reply. What I was trying to do is avoid having to type in the long formula going through the calculation in column B into each of the output cells. I was thinking there is a feature in Excel (What-If Scenario) that allows you to go through different scenarios but at the same time referencing cells that contain formulas and not just hard-coded numbers. Your solution does work, but I was wondering if this feature exists somewhere.

[FlameRetired]

I was thinking there is a feature in Excel (What-If Scenario) that allows you to go through different scenarios but at the same time referencing cells that contain formulas and not just hard-coded numbers.

If I understand correctly ... and I may not ... I don't believe so.

As an after thought are you aware that you have the ISFORMULA function available to you?

If that is of help perhaps there is a way to "retro fit" this into a formula that does what you want. Right now I don't know enough about the boundaries of 'different scenarios' you have in mind.

[ericrichard25]

I have used ISFORMULA before to check whether a cell is a formula or not. I guess I'll just have to play around with it a bit. For the different scenarios, it's just the annual rate of adjusted EBITDA growth that is variable (my 3 cases were 3%, 7%, or 10%). For each case, it just projects the next year's adjusted EBITDA based on the growth rate, then subtracts fixed amounts for depreciation/amortization and interest to get pretax income, then subtracts a fixed tax rate to get net income. But I do thank you very much for your time and feedback. I'll go ahead and mark this thread as solved.

[FlameRetired]

You are welcome. Glad to help.

Thank you for the feedback and marking your thread Solved.