Dynamic Model using if function to fulfil criteria

Hi All

I am in need of some help!

I am building a model which i need help with that requires a complex if function.

Basically i have an asset register in units from year 0 to year 10 (units) which will grow continuously. Therefore the asset register continues to grow based upon inputs (top table in the attached)

However i also have losses in every year which will need to come off the asset register, however these losses will be completed at FIFO ( First in First out)

So i have displayed a table that has a. (Assets on Register (Purchases - Top up) & b. Write off Units (Writing off assets First In First Out )


The easy part is clearly the regular addition of assets as the assets keep compounding, however the tricky part (where i cannot get a formula working) is factoring a table below it which populated a write off quantity that fulfils

1) The assets written off must match the assets written off in the given year.

2) the assets in the horizontal cells do not exceed the quantities to be written off first in first out. (using the units that were first acquired)


I have attempted numerous times to get an If function that will fulfil the criteria and for it to be dynamic (where if update the assumptions of purchased units or or written off units (light yellow cells in rows 7&8) the written down values drop out as at the moment i am having to manually adjust.

I have attached the working file and a sample of some values i am working with.


Unfortunately the format must be the same or similar as i will be working financials off these (written down value) using the units and reforecasting depreciation based upon the updated quantities.

I appreciate any help.

Thanks

Also - at the moment i have hard coded (Bright Yellow) cells which match what i want the formula to do...

Can Anyone help?