Allocation based on priority tier

[cpfrwdc]

I'm working to create an allocation model to quickly determine whether or not an order can be supported when compared to the available inventory. For example, I have 3 customers, each in their own tier (1,2,3). Tier 1 customers will have priority and first dibs at the available inventory, then Tier 2 so on. With the example below, I'd like some way to highlight/signal that we will not have enough allocation to support Customer 3 and Customer 2.

Customer	Tier
Customer 1	1
Customer 2	3
Customer 3	2

<colgroup><col><col></colgroup><tbody>
</tbody>

Item	Account	Orders	Inventory
ABC	Inventory		5
ABC	Customer 1	3
ABC	Customer 2	1
ABC	Customer 3	9
		13	5

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Thank you!

 

[shg]

Sort orders by priority:

	A​	B​	C​	D​	E​	F​
1​	Item​				Invent​
2​					5​
3​	Item​	Acct​	Pri​	Order​	Alloc​	​
4​		Alan	1​	3​	3​	E4: =MIN(D4, $E$2 - SUM(E$3:E3))
5​		Barb	2​	9​	2​
6​		Cain	3​	1​	0​