FIFO based on date only...

[Another User]

Good morning everyone,
I've spent nearly a whole day on this but can't seem to get it right...

I'm trying to make a running spreadsheet that works on the FIFO principles, but with the same unit costs. The issue is that the status of the goods changes from "Certified" no "non-certified" after 24 months. I thought it was pretty straightforward, but can't get a formula to work. I think I must be getting into VBA territory now. Please see image below.
If I put a sale in the output column for the month, it needs to come off the "Credit Balance". Data is entered into the shaded columns only...

 

[BSALV]

and a non-certified status means trash ?

 

[Another User]

Pretty much.

 

[BSALV]

AnotherUser.xlsb
	A	B	C	D	E	F	G	H
1	month	input 100%CS	sales	trash	effective sales	balance	average age (months)	paternoster
2	jan-18	100		0	0	100	1,0	/ / / / / / / / / / / / / / / / / / / / / / / / / 100
3	feb-18	100		0	0	200	1,5	/ / / / / / / / / / / / / / / / / / / / / / / / 100/ 100
4	mrt-18	100		0	0	300	2,0	/ / / / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100
5	apr-18	100		0	0	400	2,5	/ / / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100
6	mei-18	100		0	0	500	3,0	/ / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100
7	jun-18	100		0	0	600	3,5	/ / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100
8	jul-18	100		0	0	700	4,0	/ / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100
9	aug-18	100	100	0	100	700	4,0	/ / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100
10	sep-18	100		0	0	800	4,5	/ / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
11	okt-18	100		0	0	900	5,0	/ / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
12	nov-18	100		0	0	1.000	5,5	/ / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
13	dec-18	100	150	0	150	950	5,3	/ / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
14	jan-19	100		0	0	1.050	5,8	/ / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
15	feb-19	100		0	0	1.150	6,3	/ / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
16	mrt-19	100	1.000	0	1.000	250	1,8	/ / / / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100
17	apr-19	100		0	0	350	2,3	/ / / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100
18	mei-19	100		0	0	450	2,8	/ / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100
19	jun-19	100		0	0	550	3,3	/ / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100
20	jul-19	100		0	0	650	3,8	/ / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100
21	aug-19	100		0	0	750	4,3	/ / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100
22	sep-19	100		0	0	850	4,8	/ / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
23	okt-19	100		0	0	950	5,3	/ / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
24	nov-19	100	900	0	900	150	1,3	/ / / / / / / / / / / / / / / / / / / / / / / / 50/ 100
25	dec-19	100		0	0	250	1,8	/ / / / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100
26	jan-20	100		0	0	350	2,3	/ / / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100
27	feb-20	100		0	0	450	2,8	/ / / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100
28	mrt-20	100		0	0	550	3,3	/ / / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100
29	apr-20	100		0	0	650	3,8	/ / / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100
30	mei-20	100		0	0	750	4,3	/ / / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100
31	jun-20	100		0	0	850	4,8	/ / / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
32	jul-20	100		0	0	950	5,3	/ / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
33	aug-20	100		0	0	1.050	5,8	/ / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
34	sep-20	100		0	0	1.150	6,3	/ / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
35	okt-20	100		0	0	1.250	6,8	/ / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
36	nov-20	100		0	0	1.350	7,3	/ / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
37	dec-20	100	500	0	500	950	5,3	/ / / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
38	jan-21	100		0	0	1.050	5,8	/ / / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
39	feb-21	100		0	0	1.150	6,3	/ / / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
40	mrt-21	100		0	0	1.250	6,8	/ / / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
41	apr-21	100		0	0	1.350	7,3	/ / / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
42	mei-21	100		0	0	1.450	7,8	/ / / / / / / / / / / 50/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
43	jun-21	100	1.700	0	1.550	0	0,0	/ / / / / / / / / / / / / / / / / / / / / / / / /
44	jul-21	100		0	0	100	1,0	/ / / / / / / / / / / / / / / / / / / / / / / / / 100
45	aug-21	100		0	0	200	1,5	/ / / / / / / / / / / / / / / / / / / / / / / / 100/ 100
46	sep-21	100		0	0	300	2,0	/ / / / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100
47	okt-21	100		0	0	400	2,5	/ / / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100
48	nov-21	100		0	0	500	3,0	/ / / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100
49	dec-21	100		0	0	600	3,5	/ / / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100
50	jan-22	100		0	0	700	4,0	/ / / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100
51	feb-22	100		0	0	800	4,5	/ / / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
52	mrt-22	100		0	0	900	5,0	/ / / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
53	apr-22	100		0	0	1.000	5,5	/ / / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
54	mei-22	100		0	0	1.100	6,0	/ / / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
55	jun-22	100		0	0	1.200	6,5	/ / / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
56	jul-22	100		0	0	1.300	7,0	/ / / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
57	aug-22	100		0	0	1.400	7,5	/ / / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
58	sep-22	100		0	0	1.500	8,0	/ / / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100
59	okt-22	101		0	0	1.601	8,5	/ / / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101
60	nov-22	102		0	0	1.703	9,0	/ / / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102
61	dec-22	103		0	0	1.806	9,5	/ / / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103
62	jan-23	104		0	0	1.910	10,0	/ / / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104
63	feb-23	105		0	0	2.015	10,4	/ / / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105
64	mrt-23	106		0	0	2.121	10,9	/ / / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106
65	apr-23	107		0	0	2.228	11,4	/ / / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107
66	mei-23	108		0	0	2.336	11,9	/ / / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107/ 108
67	jun-23	109		0	0	2.445	12,3	/ / 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107/ 108/ 109
68	jul-23	110		0	0	2.555	12,8	/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107/ 108/ 109/ 110
69	aug-23	111		100	0	2.566	12,8	/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107/ 108/ 109/ 110/ 111
70	sep-23	112		100	0	2.578	12,7	/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 100/ 101/ 102/ 103/ 104/ 105/ 106/ 107/ 108/ 109/ 110/ 111/ 112
Blad2

Cell Formulas
Range	Formula
A2:A70	A2	=IF(ROW()=2,DATE(2018,1,1),EDATE(OFFSET(A2,-1,,,),1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C70	Expression	=-C2<>-E2	text	NO

 

[BSALV]

Public Dict, lStock, lTrash, Result(), iRij, DBR

Sub MyFifo()
     Application.ScreenUpdating = False
     Set Dict = CreateObject("scripting.dictionary")
     For ptr = 1 To 25
          Dict(ptr) = Array(ptr, 0)
     Next
     Set DBR = Sheets("blad2").ListObjects(1).DataBodyRange
     a = Sheets("blad2").ListObjects(1).DataBodyRange.Resize(, 3).Value2     'read first 3 columns
     ReDim Result(1 To UBound(a), 1 To 10)
     For iRij = 1 To UBound(a)
          DoEvents
          datum = CDbl(a(iRij, 1))
          Dict.Add datum, Array(datum, a(iRij, 2))
          MaxSizeDictionary
          Sales CInt(a(iRij, 3))

          DBR.Offset(, 3).Resize(UBound(Result), UBound(Result, 2)).Value = Result
     Next


End Sub

Sub MaxSizeDictionary()
     a = Application.Sort(Application.Index(Dict.items, 0, 0), 1)
     r = UBound(a) - 25
     lTrash = 0
     For i = 1 To r
          lTrash = lTrash + a(i, 2)
          Dict.Remove (a(i, 1))
     Next
     Result(iRij, 1) = lTrash

End Sub

Sub Sales(QTY)
     qty0 = QTY
     a = Application.Sort(Application.Index(Dict.items, 0, 0), 1)
     For i = 1 To UBound(a)
          qty1 = Application.Min(qty0, a(i, 2))
          If qty1 > 0 Then Dict(a(i, 1)) = Array(a(i, 1), a(i, 2) - qty1): qty0 = qty0 - qty1
          If qty0 <= 0 Then Exit For
     Next
     Result(iRij, 2) = QTY - qty0

     a = Application.Transpose(Application.Index(Dict.items, 0, 2))
     Result(iRij, 3) = Application.Sum(a)
     If Result(iRij, 3) = 0 Then
          Result(iRij, 4) = 0
     Else
          Weight = [transpose(26-row(1:25))]
          Result(iRij, 4) = WorksheetFunction.SumProduct(Weight, a) / Result(iRij, 3)
     End If

     For i = 1 To UBound(a): Result(iRij, 5) = Result(iRij, 5) & "/" & Right(WorksheetFunction.Rept(" ", 5) & Format(a(i), "#,###"), 5): Next
End Sub