Excel to Calculate Selling Price based on First in First Out

i have multiple MaterialNo’s and i am giving example of only one Material No now. There is Type Column that tells whether we have Bought or Sold the Material on a particular date. i have already Sorted data based on Material No and Date. Now i want to implement FIFO logic to calculate Total Price and Unit Price for Sold Materials. I have Manually Calculated as of now in Excel .

 

MaterialNo  Type    Date    Qty   Price   Balance-Quantity **TotalPrice**   **UnitPrice**
XXXXXX     Buy      03-2017 1250    79.99998042 1250    99999.97552     79.99998042
XXXXXX     Sell     03-2017   20                1230    -1599.999608    -79.99998042
XXXXXX     Buy      04-2017 2200    79.99999667 3430    175999.9927      79.99999667
XXXXXX     Sell     04-2017 2375                1055    -189999.9721    -79.99998825
XXXXXX     Buy      05-2017  997    79.9999625  2052    79759.96261      79.9999625
XXXXXX     Sell     05-2017 2055                  -3    -164399.9229    -79.9999625
XXXXXX     Buy      06-2017 4749    79.99996153 4746    379919.8173      79.99996153
XXXXXX     Sell     06-2017 3550                1196    -283999.8669    -79.9999625
XXXXXX     Buy      07-2017  894    80.00001111 2090    71520.00993      80.00001111
XXXXXX     Sell     07-2017 2100                 -10    -167999.9213    -79.9999625
XXXXXX     Buy      08-2017 2495    137.210024  2485    342339.0098      137.210024
XXXXXX     Sell     08-2017 1900                 585    -260699.0455    -137.210024
XXXXXX     Buy      09-2017 2000    156.28      2585    312560           156.28
XXXXXX     Sell     09-2017 2652                 -67    -414454.56      -156.28
XXXXXX     Buy      10-2017  596    156.2805875  529    93143.23015      156.2805875
XXXXXX     Sell     10-2017  476                  53    -74389.55965    -156.2805875
XXXXXX     Buy      11-2017 5600    156.2800066 5653    875168.0371      156.2800066
XXXXXX     Sell     11-2017 1111                4542    -173627.0874    -156.2800066
XXXXXX     Buy      12-2017 1118    156.2800863 5660    174721.1365      156.2800863
XXXXXX     Sell     12-2017 1222                4438    -190974.2654    -156.2800863
XXXXXX     Buy      01-2018 2162    156.28      6600    337877.36        156.28
XXXXXX     Sell     01-2018 4188                2412    -654500.64      -156.28
XXXXXX     Buy      02-2018  792    156.2796819 3204    123773.5081      156.2796819
XXXXXX     Sell     02-2018 2602                 602    -406639.7323    -156.2796819
XXXXXX     Buy      03-2018  400    156.28      1002    62512            156.28
XXXXXX     Sell     03-2018  560                 442    -87516.8        -156.28

One thing to note - When ever Balance Quantity is negative, it should calculate Total Price for Sold material based on last bought price. Let me know for any questions.

By: Rahul

Leave a Reply

Your email address will not be published. Required fields are marked *