A complex little query about tracking items in stock with variable cost prices

Hi, guys!

I'm so glad I found this site! I have high hopes that someone is going to be able to help me...

I'm an IT consultant (NOT a mathematician!), and have been asked by a client ,who imports and sells items from the Far East, to create a spreadsheet to track sales, profits and stock levels. (I realise that this could all be accomplished easily by an accounting package, but best not to ask any more about that..!).

I've created a spreadsheet that does most of the job, but I've run into a problem. Firstly, here's a sample line from the sheet itself, to show the column headings I'm using:

1. Item Code / 2. Cost Price / 3. Catalogue Price / 4. Units Sold at Catalogue Price/ 5. Price B / 6. Units Sold at Price B / 7. Total Gross Profit / 8. # of new items bought / 9. cost price of new items / 10. last month's closing stock level / 11. current stock level/ 12. total current stock value

The columns for units sold and new units bought use a macro so that when a figure is added to a cell, that number is automatically added to whatever was there before. This allows the accounts team to input data on a daily basis.

As you can see, each item has a list price, and then a special price for customers wishing to negotiate. (In fact, there are multiple columns for other prices, but I've left them out here to keep things simple). Also, the cost price of new items purchased may vary from the cost price at the beginning of the month. (!)

So, two questions:

1. How can I give an accurate figure for the financial value of the current total stock level of an item? I've got all the maths working so that I know HOW MANY items are in stock, but given that some were bought at Cost Price A and some at Cost Price B, and some or all of the items were then sold, I can't see how to write the forumula. My sense is that it's something to do with an IF statement, but other than that, I'm lost!

2. Related to Question 1, how can I write a formula that knows when all the stock at the old levels has been sold, and then starts using the new stock cost price to calculate gross profits? So if 100 items were sold at Cost Price A, 200 new items were bought in, and 150 of those were also sold, how can I make the spreadsheet switch to the new Cost Price when the old stock is gone?

I'm in your hands, guys!

Best wishes,

Simon.