24 month account

[ExcelSwede]

Hi
Three years later I'm back with a new tricky question.
FSC standard says that FSC certified wood cannot be stored for more than 24 month if you want to sell the wood with FSC claim. If it is stored for 25 month, the wood must be sold without FSC claim.
I hav not beeing able to crate a perfect running excel sheet to manage this FSC account.
I'll try to illustrate below.

Simple example:
With 10 units bought in Januari 2022, I have 10 units of FSC claimed material to sell every month until December 2023. In January 2024 the FSC claim expire, and the balance goes to zero. Just as it should.

If I sell my 10 units of FSC claimed material i Januari 2023, then the balance goes to zero.
But, the balance should still be zero in Januari 2024, not minus 10 units.

If I buy 10 units in February 2023, there should be 10 units available all the way down to january 2025, but in my simple sheet, it's not the case. During January - December 2024 it shows zero balance. And then, in January 2025 the 10 units show up (as they should, but it fails the whole 2024)

Someone out there ho can create the right formula for column D?
I'll be greatful for your help.
Best regards
ExcelSwede

 

[ExcelSwede]

Sorry about my english. This sentence from above needs to be explained.
With 10 units bought in Januari 2022, I have 10 units of FSC claimed material to sell every month until December 2023. In January 2024 the FSC claim expire, and the balance goes to zero. Just as it should.
Of course I can not sell 10 units every month. I can sell my 10 units during the 24 month, when ever I want. But maximum unit to sell is limited to my bought 10 units. Sure you already have figured it out, but I have to tell anyway.

 

[rlv01]

You did not post your formulas, so I'm just making guesses here.

Book1
	A	B	C	D	E	F	G
1	Year	Period	Raw Material Input (wood added)	Remaining Credits for Sale	Sold Material	FSC Cert Expires
2	2022	Jan	10	10		31-Dec-2023
3		Feb		10
4		Mar		10
5		Apr		10
6		May		10
7		Jun		10
8		Jul		10
9		Aug		8	2
10		Sep		6	2
11		Oct		6
12		Nov		6
13		Dec		6
14	2023	Jan		6
15		Feb		6
16		Mar		6
17		Apr		4	2
18		May		4
19		Jun		4
20		Jul		4
21		Aug		4
22		Sep		4
23		Oct		4
24		Nov		3	1
25		Dec		3
26	2024	Jan		0			FSC Expired!
27		Feb		0			FSC Expired!
28		Mar		0			FSC Expired!
29		Apr		0			FSC Expired!
Sheet1

Cell Formulas
Range	Formula
F2	F2	=IF(C2<>"",EOMONTH(B2,23),"")
A14,A26	A14	=A2+1
B2,B26,B14	B2	=EOMONTH(DATE(A2,1,1),0)
B27:B29,B15:B25,B3:B13	B3	=EOMONTH(B2,1)
D2	D2	=IF(B2<=$F$2,C2-E2,0)
D3:D29	D3	=IF(B3<=$F$2,D2-E3,)
G2:G29	G2	=IF(B2<=$F$2,"","FSC Expired!")

 

[ExcelSwede]

Thanks for your help, rlv01. And sorry for not havinge exposed my formula. That was a very simpel formula. Remaining credits (column D) =sum(C2:C25)-sum(E2:E24). That formula works every 24 month period, but it doesn't for consecutive 24 month periods.

Unfortunately, your first attempt does not work either. Sorry for deficient description.
Below screenshot shows the result when I added som new input during the period. Column H show the proper result.
Hope this will make the purpose clear

 

[rlv01]

You have purchased 10 units at ~60 day intervals for a total of 30 units. Each group of 10 making up the the 30 total has a different FSC expiration date. In order to track expiration dates you have to be able to differentiate by group when you sell wood. How are you doing that? Your spreadsheet shows no evidence of any kind of group tracking.

 

[ExcelSwede]

Problem solved (I think).
Purchasing and selling is of course an ongoing process every day and month. But it was not a easy thing to create a formula that works from first month.
Between brackets in formula there is 24 month period. For the first 23 month in a new sheet you must modify formula, but then running 24-month periods will be correct from row 25 and futher on.
Below formula taken from first row where it works, range D25
=IF(D24-E24+C25<=SUM(C2:C25),D24-E24+C25,SUM(C2:C25))

Sorry beeing bothering you. Thank you anyway.