My wife and I recently bought a lot of peice parts for a show and
sale we are going to shortly. We have been discussing how to pro rate
the freight and our labour costs to arrive at a fair selling price
for each peice. We want to try to capture the true costs for freight,
labor and customs duties plus our time to make each peice. We are
also making some pendants, earring and broaches from PMC I bought
locally in addition to the inported parts.
We are concerned that simply dividing freight cost by the number of
parts would not work as some are valuable natural stones and or
silver parts and others are less valuable parts like rubber cord
etc.
If anyone has a good formula or ideas on how to valuate the
different parts as to their finished sale costs or perceived value
for resale we would be appreciative.
Offline replies are also kindly accepted at the below email.
I did this on an Excel spreadsheet years ago and its easy.
In excel in column “A” we went down the line and placed the dollar
amount of each part, line by line, then at the bottom it added a
total.
In the next column, column “B” we had what PERCENTAGE each line was
of the total.
At the top we entered the total shipping charge.
Then in column “C” we had a formula that MULTIPLIED the shipping
charge by the percentage to figure what percentage of the shipping
wwent to that part.
Column “D” totaled the part and shipping.
Similar to below:
TOTAL SHIPPING = $20.00 (Assume this $20 sits in cell “G-1”)
“A” “B” “C” "D"
PART PERCENTAGE SHIPPING PART TOTAL FOR PART
$15.00 23% $4.60 $19.60
$20.00 31% $6.20 $26.20
$30.00 46% $13.80 $43.80
TOTAL OF PARTS ($15 + $20 +$30) = $65.00 (Assume this is cell “B-10”
The formulas in Excel
The 1st $15 part is in cell “A-5” for example. The "23% cell would
be “B-5”. In B-5 the formula would be =sum(A5/$b$10)
Format this cell to be a percentage)
Then copy the formula to the 31% cell and 46% cell. The dollar signs
"$b$’ tells excel to keep that cell constant as you copy.
The “C” column formula would be =sum(c5*$g$1)
The Total of Parts in B-10 would be =sum(a5:a7) This adds up the lines
from $15 down to $30.