FIFO (First in, First out) with Sell price and Profit/Lose

Question

Hey everyone am trying to adapt this FIFO (First in, First out) Inventory I need to realize FIFO algorithm by adding a row for price of the Sold Item, The total Value of the Sell and if a Profit/Lose was made.

For the demostration purposes i used the same Data.

items = data.frame(
  Items = c('Item A', 'Item B','Item A','Item A','Item A','Item B','Item B','Item A','Item C'),
  Quantity = c(5,100,4,7,10,50,30,1,1000),
  BuySell = c('B','B','B','S','B','S','S','S','B'),
  Price = c(100,50,110,130,90,45,60,120,5)
)

The Code that was presented by kath

library(dplyr)
library(tidyr)

items <- items %>% 
  group_by(Items) %>% 
  mutate(index = 1:n()) %>% 
  spread(BuySell, Quantity, fill = 0) %>% 
  arrange(Items, index) %>% 
  mutate(TotalStock = cumsum(B) - cumsum(S), 
         Sold = case_when(B == 0 ~ 0, # Nothing bought - cannot be sold
                          cumsum(B) < sum(S) ~ B, # Total items bought is smaller than total item sold - everything is sold
                          sum(S) < (cumsum(B) - B) ~ 0, # Total sum is smaller than total amount bought excluding the current buy - nothing sold
                          TRUE ~ B - (cumsum(B) - sum(S))), 
         InStock = B - Sold) 

Desired Output:

items
# A tibble: 9 x 9
# Groups:   Items [3]
#   Items  Price Value index     B     S TotalStock  Sold InStock SellPrice SellValue GL
#   <fct>  <dbl> <dbl> <int> <dbl> <dbl>      <dbl> <dbl>   <dbl>
# 1 Item A   100   500     1     5     0          5     5       0   130         650   150
# 2 Item A   110   440     2     4     0          9     3       1   130         390   -50
# 3 Item A   130   910     3     0     7          2     0       0   0             0     0
# 4 Item A    90   900     4    10     0         12     0      10   0             0     0
# 5 Item A   120   120     5     0     1         11     0       0   0             0     0
# 6 Item B    50  5000     1   100     0        100    80      20   50.625     4050    50
# 7 Item B    45  2250     2     0    50         50     0       0   0             0     0
# 8 Item B    60  1800     3     0    30         20     0       0   0             0     0
# 9 Item C     5  5000     1  1000     0       1000     0    1000   0             0     0

The SellPrice for Item B should be the averageprice of the Sell #7 and #8 [(45x50+60x30)/80 = 50.625)]. The SellValue is the Quanity of the SoldItems x Averageprice The GL (Gain/Lose) is the diff between SellValue-Value

I also checked R calculate aggregate gains or loss using FIFO method this method works very well, yet it lacks the crucial information of the the InStock Items. I tried to implement this code into the code by kath, but faild doing so. Which is probably due to the fact that one methode uses separete df for sell and buys while the other spread the Buys/Sells into two diffrent rows in the same df.

Help would be much appreciated.