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.


[TABLE="width: 192"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 483"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Account[/TD]
[TD]Orders[/TD]
[TD]Inventory[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Inventory[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Customer 1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Customer 2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD]Customer 3[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]




Thank you!

 

[shg]

Sort orders by priority:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td][/td][td][/td][td][/td][td="bgcolor:#F3F3F3"]

Invent​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td]

5​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td="bgcolor:#F3F3F3"]

Item​

[/td][td="bgcolor:#F3F3F3"]

Acct​

[/td][td="bgcolor:#F3F3F3"]

Pri​

[/td][td="bgcolor:#F3F3F3"]

Order​

[/td][td="bgcolor:#F3F3F3"]

Alloc​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td]Alan[/td][td]

1​

[/td][td]

3​

[/td][td="bgcolor:#CCFFCC"]

3​

[/td][td="bgcolor:#CCFFCC"]E4: =MIN(D4, $E$2 - SUM(E$3:E3))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td]Barb[/td][td]

2​

[/td][td]

9​

[/td][td="bgcolor:#CCFFCC"]

2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td][/td][td]Cain[/td][td]

3​

[/td][td]

1​

[/td][td="bgcolor:#CCFFCC"]

0​

[/td][td][/td][/tr]
[/table]