inventory Add, Cost, Sold, Sold price - formula

[Summer]

Hello every one i need your help with a formula.

Example

Date Delivered Cost Sold Retail
Price Profit
Pepsi Coke Pepsi Coke Pepsi Coke Pepsi
Coke Pepsi Coke
7/1/08 100 50 1.00 1.15 50 25 1.29
1.39 ???? ????
7/2/08 0 40 15
1.19 1.29 ???? ????
7/3/08 250 70 0.79 .85 60 45 0.99
1.09 ???? ????
7/4/08 0 55 35
1.15 1.25 ???? ????

Every day delivery does not come. Each time cost is different.
Every day i put sold and every day sold price is different.

Now i need a formula that will find profit but it must use the old inventory
first. until old inventory is all gone then start next one. The current
formula i have right now is =(K5-E5)*H5


Please help me out i been working on this for about a month. Thank you very
very much.

 

[Joel]

I'm haveing problems figuring out which data is in which columns so I wil
just describe the answer. The solution is not perfect because you could have
stolen or damaged bottles that are losses that eventually have to be deducted
from the profits.

Your reall profit is

(total sold price) - (total purchase price) - (previous profits) +
(adjustment)

The adjustment is your initial investment or inventory. If you initially
stock 10-0 bottles of coke the 1st day but only sold 5 bottles you make a
profit on the 5 bottles.

You can use sumproduct. If your data starts in row 2.

for pepsi with the total purchase is for Row 1

=sumproduct(B$2:B2,D$22)

Notice where the dollar sign is located. When yhou copy the formula down
the row more rows are counted.

 

[Summer]

Can i put the sample sheet on this Discussion group?

 

[Joel]

Email me at

joel dot warburg at itt dot com

Can i put the sample sheet on this Discussion group?