0

I have never used stack-overflow so I apologize if my question is formatted incorrectly. Currently I am working on an excel workbook that is basically every single financial sheet in a company (operations staffing, IS costs, warehousing, marketing expenses, CAPM calculations, income statement, you name it) and we are trying to use Goal Seek in order to do a sensitivity analysis on a few variables. Basically we are trying to see how much does x need to change % wise in order to have NPV = 0 (breakeven) so we can analyze risk. The issue comes when goal seek can't find a solution and tells us there is a circular reference. We have spent nearly 6 hours confirming there are no circular references. Additionally, if I change the sensitivity manually I can get NPV to breakeven, but goal seek can't. Has anyone encountered this before? There are no macro codes being used in this excel file and we can't for the life of us figure out what is happening.

Please help!

Olivia White
  • 1
  • Could you share some example - after removing sensitive data. Unfortunately without that it's really impossible to solve your problem. – Máté Juhász Apr 24 '21 at 04:29
  • Just checking but aren't you switching around the input and outputs sections of the goal seek dialog box? It can be confusing and has happened to me before. So there is no circular reference except when you induce it with goal seek itself. – ExcelEverything Apr 24 '21 at 06:19

1 Answers1

0

Hi thank you for all responses! We were able to fix the circular reference by moving calculation iterations to 1 and tracing NPV precedents all the way back to the start where we found one inconsistency in an IF statement causing a (very) hidden circular reference. I'm not sure if this is the best way to go about it, but it worked eventually.

Olivia White
  • 1