inventory Add, Cost, Sold, Sold price - formula

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

  1. Summer

    Summer Guest

    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, Jul 19, 2008
    #1
    1. Advertisements

  2. Summer

    Joel Guest

    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$2:D2)

    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.
    >
     
    Joel, Jul 20, 2008
    #2
    1. Advertisements

  3. Summer

    Summer Guest

    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$2:D2)
    >
    > Notice where the dollar sign is located. When yhou copy the formula down
    > the row more rows are counted.
     
    Summer, Jul 20, 2008
    #3
  4. Summer

    Joel Guest

    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$2:D2)
    > >
    > > Notice where the dollar sign is located. When yhou copy the formula down
    > > the row more rows are counted.
     
    Joel, Jul 20, 2008
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    0
    Views:
    626
    Guest
    Oct 5, 2005
  2. Guest
    Replies:
    6
    Views:
    423
    Guest
    Oct 12, 2005
  3. Guest

    Price List overall price increase

    Guest, May 22, 2006, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    193
    Guest
    May 22, 2006
  4. Ian_W-at-GMail
    Replies:
    6
    Views:
    660
    Sandy Mann
    Mar 21, 2007
  5. Renae

    Formula using cost and margin % to get retail price?

    Renae, Feb 4, 2009, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    964
    Renae
    Feb 4, 2009
Loading...

Share This Page