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

Summer
Guest
Posts: n/a

 19th Jul 2008
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
Guest
Posts: n/a

 20th Jul 2008
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.

"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
>
>
> Please help me out i been working on this for about a month. Thank you very
> very much.
>

Summer
Guest
Posts: n/a

 20th Jul 2008
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
Guest
Posts: n/a

 20th Jul 2008
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.

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post bob Microsoft Excel Worksheet Functions 2 19th Feb 2012 02:29 PM AMB Microsoft Excel Worksheet Functions 3 22nd Aug 2008 09:37 PM =?Utf-8?B?cmhvbmRh?= Microsoft Access Getting Started 1 27th Jun 2007 08:12 PM =?Utf-8?B?S2F1YWkgQXJ0aXN0?= Microsoft Word New Users 2 29th Nov 2006 12:31 AM John Sebastian Microsoft Excel Worksheet Functions 1 29th Dec 2003 08:46 PM

Features