LIFO Balances and P&L

Basically, I'm designing a spreadsheet to keep track of one single product that we both buy and sell on the open market at spot (live) rates. Obviously this means we end up having inventory that cost many different rates. We use the LIFO method to calculate COGS.

What I want to do is create a spreadsheet with two sections:
1) a list detailing each purchase and sale of inventory. This will be manually entered whenever it takes place.
2) a balance of inventory at each purchase price. I would like this to be automatically updated whenever a purchase or sale of inventory is manually entered in the first section.

Obviously, the second section is going to be the hard part.
-Every time inventory is purchased at a previously unseen price, I would like a column to be automatically added in the balances with the balance of units purchased at this price.
-As we use LIFO, every time we make a sale I would like the most recent purchases to be deducted before the older purchases.

Finally, I would like to show cumulative profit somewhere in the sheet. This would be updated every time a sale of inventory is made. Obviously the profit would be LIFO based, so for example:
-Say our inventory balance is 5 units purchased at $10 and 10 units purchased at $8. The $10 units were purchased most recently. If I make a sale of 10 units for $20, the profit will be (20-10)*5 + (20-8)*5 = $110. This will leave a balance of 5 units purchased at $8.

I've tried to create a spreadsheet like this without using VBA, which is the spreadsheet I've attached. It just has the first three transactions for this month. The first two are purchases (positive quantities) while the last is a sale (negative quantity). Note that they don't actually balance and leave a deficit of units - ignore this (we can short sell units, I've been told it's not important because we don't do it often for this product). Hence I've just assumed the balance is 0, rather than -41.

I ran into a couple of problems when trying to do it without VBA: first of all, I don't know how to make it automatically add columns in the balances section when new units are added. Secondly, when a sale was made I didn't know how to make it automatically deduct from the most recent balances, as per LIFO rules. I've just included the spreadsheet as a rough guide of what I'm trying to do: I'm perfectly happy to start again.

Would really appreciate it if anyone can give any help or suggestions! Thanks

Carbon Trades Record Karl.xlsx

Close. I think you're just misunderstanding what this represents. Basically, the first two transactions are purchases of inventory. Thus that increases the NZD value of the inventory. However the third transaction is a sale (remember to ignore negatives if it complicates things, they're not too important. So therefore after the third transaction the NZD value of the balance should be 0. That should also be the first instance of profit (which would be the difference between the sale price and the purchase prices of the appropriate number of units).

So basically, I just need a code so that when people enter the raw data for a single transaction, it automatically adjusts the balances, profits, etc. The fact this will involve automatically adding columns, and subtracting from balances on a LIFO basis, made me think VBA was necessary to create this.

Cheers