Costing individual items in a set

[Neville Bailey]

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.

[daveob]

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.

[vieome]

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.

[vieome]

Edited

[Xplosiv]

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.