Tax Lot Allocations

[hanalei]

I'm trying to figure out a way to allocate a sell transaction to tax lots where the order might change over time.

For example:
Say you have a table with four columns and the number of units of stock stored in a row across the four columns.
So
A1 has the number 10
A2 has the number 4
A3 has the number 20
A4 has the number 2

The number in each cell represents shares of stock you own but there is a different purchase date and cost basis for each.

Now you are going to do a sale 5 units.
Your optimization analysis says the units should be sold from A2 first, then A1, then A3, then A4. So you would sell 4 from A2 and 1 from A1 and update the values post sale.

New balances are:
A1 has the number 9
A2 has the number 0
A3 has the number 20
A4 has the number 2

Now you need to allocate the sale of 25 units and your optimization says they should be allocated in this DIFFERENT order: sell in the order of A4, A3, A1. Since A4 has only 2 units, you sell those. Then you sell the 20 units in A3 and then you sell 3 units from A1.

New Balances are:
A1 has 6
A2 has 0
A3 has 0
A4 has 0

Any sell transaction is contingent on the size of the sale v. the inventory you have in a given bucket but the order of operations from sales change. What is the best way to model this in Excel??

 

[Andrew Fergus]

Hello and welcome to MrExcel

I'm sure this can be done but right now there is not enough information to answer the question. How do you know it is advantageous to sell the quantity in cell A2 first? Does it have a higher purchase price? If so, where are those values held? It's probably better to see the true layout of what you are doing.

 

[hanalei]

Thanks Andrew,
I just posted a response but I don't see it.

This might be a repeat but I figured out a better way to lay out the problem.
You have 4 distribution center in various parts of the world.
You have inventory in each distribution center.
You get customer orders that need to be fulfilled from some combination of 1 to all 4 distribution centers based on the size of the order, the stocks on hand in each distribution center and the relative distance of each distribution center from the customer destination.

So the order of the distribution center you fulfill an order from varies based on the customer location, and the amount you fulfill from each distribution center depends on the rank order of distance, the amount of inventory on hand and the size of the distribution center.

Here is a toy model with the allocation done by hand.

Section A says the order size to allocate
Section B gives the relative ranking of the distribution center based on distance from the ordering customer
Section C gives the inventory balance in each distribution center
How would you automate the unit allocation in Section D based on the info in Sectin A,B,C

https://docs.google.com/spreadsheets/d/1lnhnCmeksDz6SMDrt6XcAkJ8WYdfG8NEoMHf4_0V25U/edit?usp=sharing

 

[hanalei]

A typo in the above...the last two words should be replaced with "customer order"

"So the order of the distribution center you fulfill an order from varies based on the customer location, and the amount you fulfill from each distribution center depends on the rank order of distance, the amount of inventory on hand and the size of the customer order.

 

[Andrew Fergus]

Hello

I'm not sure how you work out the values in section B but let's run with what you have for the moment.

Section C formula starting in row 6 will be:
formula in K6: =K5-P5
and copy down and across in section C.

Section D starting in row 5 will be:
U5 formula: =MAX(MIN(K5,$D5-SUMIF($F5:$I5,"<"&F5,$K5:$N5)),0)
and copy down and across in section D.
This allocates the sale to the distribution centre according to the rankings in section B, but does not allocate more stock than what is available in section C.

I think you had an error on row 7 given there was not enough stock to allocate to the order on that row. Something you might want to think about is the ability to add new stock.

I trust this helps.

 

[hanalei]

Thanks Andrew. Very helpful.
Is there a way to PM you? Tried via platform but your message box is full.