Results 1 to 5 of 5

Thread: Costing individual items in a set

  1. #1
    Diamond Member Neville Bailey's Avatar
    Join Date
    Nov 2010
    Location
    Westville, Durban
    Posts
    2,760
    Thanks
    44
    Thanked 451 Times in 396 Posts

    Costing individual items in a set

    I have a bit of a brain-teasing exercise on my hands which I am battling to solve, and I was wondering if there are any mathematical / Excel fundis that can assist me?

    One of my clients purchases a set of gift boxes at a particular cost and needs to work out a formula to cost each of the individual boxes. There are 15 boxes in the set but, here's the problem, the boxes are not the same size.

    The 15 boxes range from the smallest box, which then fits into the next size box and so on. I would say that the percentage increase in size from box 1 to box 2 is the same as from box 2 to box 3, etc.

    Ideally I would like a formula or a model that can be set up on Excel, so that the client can simply insert the cost of the entire set and then let the model calculate the cost per individual box.
    Neville Bailey - Sage Pastel Accounting Consultant
    www.accountingsoftwaresupport.co.za
    neville@accountingsoftwaresupport.co.za
    IronTree Online Solutions

    "Give every person more in use value than you take from them in cash value."
    WALLACE WATTLES (1860-1911)

  2. #2
    Email problem daveob's Avatar
    Join Date
    Feb 2008
    Location
    Amanzimtoti
    Posts
    655
    Thanks
    107
    Thanked 118 Times in 103 Posts
    just a thought.

    total set costs R 200.

    Markup retail price = R 300

    Start with a basic base price per box, say R 10 per box. That accounts for R150 total ( R 10 x 15 boxes ). R 150 still to be added to boxes.

    next work out the total cubic cm of all the boxes ( L x W x H ) of each box, then add all together.

    So lets say total c.cm of all boxes = 300.

    R150 / 300 c.cm = R0.50 / c.cm

    For each box, take box c.cm and multiply by 0.50, and add to the R10 base price per box.

    You should be able to measure the outermost box, the innermost box, and calc the approx. cubic centimeter size of each box if you know the number of boxes in the set.
    Watching the ships passing by.

  3. #3
    Email problem vieome's Avatar
    Join Date
    Apr 2012
    Location
    joburg
    Posts
    540
    Thanks
    58
    Thanked 159 Times in 118 Posts
    Blog Entries
    37
    Hi Neville

    Think your best bet is give each box a weight of 1-15 and then use a ratios to divide the total cost.
    Attached Files Attached Files

  4. #4
    Email problem vieome's Avatar
    Join Date
    Apr 2012
    Location
    joburg
    Posts
    540
    Thanks
    58
    Thanked 159 Times in 118 Posts
    Blog Entries
    37
    Edited
    Attached Files Attached Files

  5. #5
    Bronze Member Xplosiv's Avatar
    Join Date
    Dec 2013
    Location
    Oudtshoorn
    Posts
    102
    Thanks
    5
    Thanked 26 Times in 22 Posts

    Formula

    Hi Neville
    The formula you're looking for is (n*(n+1))/2 where n is the number of boxes.
    Then give the boxes "weights" with 1 being the smallest box.
    Take your cost price and divide it by the result of above formula.
    Multiply by box weight and you have the cost of the individual box.
    Add your markup and sell.
    Fill in the first 3 columns, last column kicks out cost price.

    russian_dolls.xls

    Bad example, I set the number of boxes to 25 and the weight at 50 (which it can't be), but the formula works.

Similar Threads

  1. Costing for Profit
    By Abdi in forum General Business Forum
    Replies: 12
    Last Post: 05-Jul-12, 05:56 PM
  2. Individual The individual SARS tax filing season commenced on 01 July 2010
    By Pastel Payroll Consultant in forum Tax Forum
    Replies: 4
    Last Post: 25-Nov-10, 02:00 PM
  3. [Opinion] Costing per Network point
    By Cpt Chaos in forum Technology Forum
    Replies: 2
    Last Post: 15-Jul-10, 10:19 AM
  4. How much is your BBBEE and EE costing?
    By sterne.law@gmail.com in forum BEE and Employment Equity Forum
    Replies: 1
    Last Post: 26-Mar-10, 04:33 PM
  5. Tax returns mailed to individual taxpayers
    By duncan drennan in forum News Archive
    Replies: 2
    Last Post: 07-Aug-07, 10:49 AM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •