Imagine the following scenario:
Margin from unit sales: X in year 1, Y in year 2, Z in year 3 [imagine this is linked to table of sales units by year and changing margins over time]
Overheads: A in year 1, B in year 2, C in year 3
Therefore profit of D in year 1, E in year 2, F in year 3
I want to add a row summarising how many units I would need to sell in each year in order to break even (i.e. how many sales units at given assumptions to make profit = 0). This would look like: "Break even sales units: G in year 1, H in year 2, I in year 3"
I can use Goal Seek to figure this number out for a single year by setting D to 0 and then solving for the sales units number in the subtable.
BUT I want the sales units numbers in the model to be my planned sales, not the break even sales. I don't want to interrupt the main calculations, I just want a row that reports how many sales units I would need under those assumptions to break even. So that it dynamically updates when I change assumptions, which the above Goal Seek approach doesn't do.
Any ideas? A single variable data table? What if analysis? Something completely different?
Bookmarks