Discussions

Calculate average per unit cost

  • 1.  Calculate average per unit cost

    Posted 08-03-2020 22:49
    Hi folks,

    I want to calculate the true average per unit cost for future pricing. 

    We have an Inventory table, we buy "item A" at different price at different time

    item A : 1 @$10 = 10 total 

    item A : 1 @$20 = 20 total 

    item A : 2 @$40 = 80 total 


    The "average per unit cost" is different depending on number of item sold. The minimum price we can sale (with no profit no loss)

    if nothing sold, average per unit cost = (1x$10 + 1x$20 + 2x$40) / 4 = $110/4 = $27.5 

    if sold 1 unit, (1x$20 + 2x$40) / 3 = $90 / 3 = $30 average per unit cost

    if sold 2 unit, (2x$40) / 2  = $80 / 2 = $40 average per unit cost

    if sold 3 unit, (1x$40) / 1 = $40 average per unit cost

    How to I calculate average unit cost in quickbase? Any way to do this with build-in quickbase function alone?

    Much thanks!



    ------------------------------
    George Fong
    ------------------------------