# inventory Add, Cost, Sold, Sold price - formula

Discussion in 'Microsoft Excel Misc' started by Summer, Jul 19, 2008.

1. ### SummerGuest

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

very much.

Summer, Jul 19, 2008

2. ### JoelGuest

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.

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

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" wrote:

> 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
>
>
> very much.
>

Joel, Jul 20, 2008

3. ### SummerGuest

Can i put the sample sheet on this Discussion group?

"Joel" wrote:

> 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.
>
>
> (total sold price) - (total purchase price) - (previous profits) +
>
> 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, Jul 20, 2008
4. ### JoelGuest

Email me at

joel dot warburg at itt dot com

"Summer" wrote:

> Can i put the sample sheet on this Discussion group?
>
> "Joel" wrote:
>
> > 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) +
> >
> > 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.

Joel, Jul 20, 2008