Allocating Costs by Dynamic Weighted Average

Hi All,

I am working on a model that compares costs between 2 business cases. The first, labelled as Outflows (Commitments), shows how much total spend is remaining for scenario 1 if nothing is changed. The decision comes in to play when the remaining Operational Costs become higher than the commitment for the outflows, at which time the service should be shut down.

The issue I am having is re-balancing the fixed costs between just the cities where it makes sense to continue to run the service (ie the remaining operational costs are lower than the outflow commitment).

What I need is a way to do a weighted average to spread the fixed costs among only the cities that it still makes sense to have the service running where the operational costs are lower than the commitments. Every city will have a different percentage each month as the numbers of subscribers fluctuate and as cities are removed from the calculation.

Thanks!

Copy of Workbook.xlsx