Dear excel community,

I just finished building a financial model and I am trying to do some sensitivity analysis on gross margins. Basically I would like to see the effect of various levels of pricing on the annual gross margin. As I see it, I need a one dimensional data table where my input is price and output in gross margin. My issue is my pricing input is on a separate assumption sheet and then the gross margin is calculated based on cost data that is flowing in from several sheets to the P&L, which is again a separate sheet from the sensitivity analysis sheet where I want to place the data table. Can anyone suggest a way to accomplish this?

I searched this forum and google as I assumed this would be a fairly common issue but surprisingly I could not find a solution. Help!!!!

Regards,
Aman