PC Review


Reply
Thread Tools Rate Thread

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.

 
Reply With Quote
 
 
 
 
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) +
(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" 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.
>

 
Reply With Quote
 
 
 
 
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) +
> (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.

 
Reply With Quote
 
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) +
> > (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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find last date of sold stock and sold price. bob Microsoft Excel Worksheet Functions 2 19th Feb 2012 02:29 PM
Calculating the Median Price Sold AMB Microsoft Excel Worksheet Functions 3 22nd Aug 2008 09:37 PM
help me create a inventory input and output when items sold =?Utf-8?B?cmhvbmRh?= Microsoft Access Getting Started 1 27th Jun 2007 08:12 PM
I made a price list and now I want to type the word sold in red? =?Utf-8?B?S2F1YWkgQXJ0aXN0?= Microsoft Word New Users 2 29th Nov 2006 12:31 AM
Formula for average units sold based on # days in month sold John Sebastian Microsoft Excel Worksheet Functions 1 29th Dec 2003 08:46 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:50 AM.