

  Author:  Golbsco  Posted:  Sep 3, 2019 10:02  Subject:  Perpart profit tracking program?  Viewed:  119 times  Topic:  Selling  

 Recently i've had this thought on my mind that i'm unable to truly calculate
exactly how much profit is made through sales without manually tracking every
part in my inventory.
So far i've been calculating profit by simply totaling sales and subtracting
expenses. This gets the job done for tax purposes but not much else.
I would like to be able to track exactly how much profit is made on each part,
for example i can easily calculate the cost per part by simply dividing the cost
of a set by part quantity then subtracting that average part cost from the price
of the part sold. While this is absolutely possible to do manually (and i think
i have a system in mind to accomplish this) it requires immense amounts of work
and is not viable for scaling up.
This then gives data on best and worst selling parts, and could hopefully expand
to generating data on demand and trends.
Preferably i would be able to import orders directly into the system too.
Short of designing a program to achieve this and perhaps getting it created 
Does something like this already exist?
What do some of the larger sellers do to solve these issues?
I apologise if i haven't described this very well but i'm not exactly
sure what it is im after, which brings me to the forums to gather some info and
feedback.



     
    Author:  Teup  Posted:  Sep 3, 2019 10:14  Subject:  Re: Perpart profit tracking program?  Viewed:  49 times  Topic:  Selling  

 In Selling, Golbsco writes:
 Recently i've had this thought on my mind that i'm unable to truly calculate
exactly how much profit is made through sales without manually tracking every
part in my inventory.
So far i've been calculating profit by simply totaling sales and subtracting
expenses. This gets the job done for tax purposes but not much else.
I would like to be able to track exactly how much profit is made on each part,
for example i can easily calculate the cost per part by simply dividing the cost
of a set by part quantity then subtracting that average part cost from the price
of the part sold. While this is absolutely possible to do manually (and i think
i have a system in mind to accomplish this) it requires immense amounts of work
and is not viable for scaling up.
This then gives data on best and worst selling parts, and could hopefully expand
to generating data on demand and trends.
Preferably i would be able to import orders directly into the system too.
Short of designing a program to achieve this and perhaps getting it created 
Does something like this already exist?
What do some of the larger sellers do to solve these issues?
I apologise if i haven't described this very well but i'm not exactly
sure what it is im after, which brings me to the forums to gather some info and
feedback.

Hmm.. dividing the cost price equally over all parts.. That begs the question
if considering a minifig head the same cost price as a round brick is really
what you want.
I would much rather take the profit ratio (say, 2x) of the entire partout, and
then divide the selling price of the item by the profit ratio. That way, all
items have a cost that is proportional to their selling price. If you bought
a set for 10, part it out for 20, a minifig head sells for 1, the cost price
of that minfig head is 0.50. For another part in that same set that sells for
0.10, the cost price was 0.05. This way it makes more sense to me, because otherwise
it will seem as if you will make loads of profit on some orders and hardly any
profit on other orders, even though they came from exactly the same partout.



         
      Author:  runner.caller  Posted:  Sep 3, 2019 10:42  Subject:  Re: Perpart profit tracking program?  Viewed:  30 times  Topic:  Selling  

  In Selling, Teup writes:
I would much rather take the profit ratio (say, 2x) of the entire partout, and
then divide the selling price of the item by the profit ratio. That way, all
items have a cost that is proportional to their selling price. If you bought
a set for 10, part it out for 20, a minifig head sells for 1, the cost price
of that minfig head is 0.50. For another part in that same set that sells for
0.10, the cost price was 0.05. This way it makes more sense to me, because otherwise
it will seem as if you will make loads of profit on some orders and hardly any
profit on other orders, even though they came from exactly the same partout.

+1 Teup! I like for my margins to be the same for every part from the same lot.
The steps I outline below weights the costs based on the 6mo sell price.
This is what I do for used lots:
Load all the parts I want to sell into excel.
Run pricing macro to find the (6mo part price) for all items.
Use SUMPRODUCT function to get a (Grand Total) 6mo value on all items together.
Run equation; (Part Cost) = (6mo part price) * (Lot cost) / (Grand Total)
Drag equation down for every excel line.
Double check using SUMPRODUCT of (PartCost) & (QTY) to assure it matches (Lot
Cost)
Upload changes via XML.



         
      Author:  calsbricks  Posted:  Sep 3, 2019 10:55  Subject:  Re: Perpart profit tracking program?  Viewed:  32 times  Topic:  Selling  

 In Selling, Teup writes:
 In Selling, Golbsco writes:
 Recently i've had this thought on my mind that i'm unable to truly calculate
exactly how much profit is made through sales without manually tracking every
part in my inventory.
So far i've been calculating profit by simply totaling sales and subtracting
expenses. This gets the job done for tax purposes but not much else.
I would like to be able to track exactly how much profit is made on each part,
for example i can easily calculate the cost per part by simply dividing the cost
of a set by part quantity then subtracting that average part cost from the price
of the part sold. While this is absolutely possible to do manually (and i think
i have a system in mind to accomplish this) it requires immense amounts of work
and is not viable for scaling up.
This then gives data on best and worst selling parts, and could hopefully expand
to generating data on demand and trends.
Preferably i would be able to import orders directly into the system too.
Short of designing a program to achieve this and perhaps getting it created 
Does something like this already exist?
What do some of the larger sellers do to solve these issues?
I apologise if i haven't described this very well but i'm not exactly
sure what it is im after, which brings me to the forums to gather some info and
feedback.

Hmm.. dividing the cost price equally over all parts.. That begs the question
if considering a minifig head the same cost price as a round brick is really
what you want.
I would much rather take the profit ratio (say, 2x) of the entire partout, and
then divide the selling price of the item by the profit ratio. That way, all
items have a cost that is proportional to their selling price. If you bought
a set for 10, part it out for 20, a minifig head sells for 1, the cost price
of that minfig head is 0.50. For another part in that same set that sells for
0.10, the cost price was 0.05. This way it makes more sense to me, because otherwise
it will seem as if you will make loads of profit on some orders and hardly any
profit on other orders, even though they came from exactly the same partout.

Hi Teup
Forgive me for butting in, but that isn't really the best way to determine
your profit on your items. Firstly the cost price of your stock should be based
on the average cost price of those items. So lets say you add 100 at 10p each,
then you get a good deal and have another 100 at 8p  average cost price is now
9p, Bricklink doesn't cater at all for inventory management capabilities
like this and we have found the cost price field to be not worth the effort.
All of our costing/profit calculations are done in our offline system as there
we keep stock values by part.
As both our stores add lots of items (some unique and some topping up shallow
stock) you really do have to have a measure of inventory control to get an accurate
picture.
If you part out a set which is 3 x cost price and then a little later another
of the same set which is only 2 times cost  you have to manually remember all
the items which were in the first part out and adjust those manually (Not really
possible or viable) but that is how Bricklink expects you to do that.
The costing element here is not fit for purpose, to be honest and has needed
major revamping since it was developed. But like most developments needed it
isn't going to happen.
When we started all those years ago and were adding stock, we tried to use the
cost field in a similar way to what you are describing and it simply was no where
near accurate enough and actually gave a false impression of profitability per
item.
Again, sorry to but in but costing is a really important thing to me (and our
store) We take it very seriously and have put effort into getting it as right
as possible.
Much better to have a proper inventory system and let it do the work for you.



             
        Author:  Teup  Posted:  Sep 3, 2019 11:24  Subject:  Re: Perpart profit tracking program?  Viewed:  24 times  Topic:  Selling  

 In Selling, calsbricks writes:
 In Selling, Teup writes:
 In Selling, Golbsco writes:
 Recently i've had this thought on my mind that i'm unable to truly calculate
exactly how much profit is made through sales without manually tracking every
part in my inventory.
So far i've been calculating profit by simply totaling sales and subtracting
expenses. This gets the job done for tax purposes but not much else.
I would like to be able to track exactly how much profit is made on each part,
for example i can easily calculate the cost per part by simply dividing the cost
of a set by part quantity then subtracting that average part cost from the price
of the part sold. While this is absolutely possible to do manually (and i think
i have a system in mind to accomplish this) it requires immense amounts of work
and is not viable for scaling up.
This then gives data on best and worst selling parts, and could hopefully expand
to generating data on demand and trends.
Preferably i would be able to import orders directly into the system too.
Short of designing a program to achieve this and perhaps getting it created 
Does something like this already exist?
What do some of the larger sellers do to solve these issues?
I apologise if i haven't described this very well but i'm not exactly
sure what it is im after, which brings me to the forums to gather some info and
feedback.

Hmm.. dividing the cost price equally over all parts.. That begs the question
if considering a minifig head the same cost price as a round brick is really
what you want.
I would much rather take the profit ratio (say, 2x) of the entire partout, and
then divide the selling price of the item by the profit ratio. That way, all
items have a cost that is proportional to their selling price. If you bought
a set for 10, part it out for 20, a minifig head sells for 1, the cost price
of that minfig head is 0.50. For another part in that same set that sells for
0.10, the cost price was 0.05. This way it makes more sense to me, because otherwise
it will seem as if you will make loads of profit on some orders and hardly any
profit on other orders, even though they came from exactly the same partout.

Hi Teup
Forgive me for butting in, but that isn't really the best way to determine
your profit on your items. Firstly the cost price of your stock should be based
on the average cost price of those items. So lets say you add 100 at 10p each,
then you get a good deal and have another 100 at 8p  average cost price is now
9p, Bricklink doesn't cater at all for inventory management capabilities
like this and we have found the cost price field to be not worth the effort.
All of our costing/profit calculations are done in our offline system as there
we keep stock values by part.
As both our stores add lots of items (some unique and some topping up shallow
stock) you really do have to have a measure of inventory control to get an accurate
picture.
If you part out a set which is 3 x cost price and then a little later another
of the same set which is only 2 times cost  you have to manually remember all
the items which were in the first part out and adjust those manually (Not really
possible or viable) but that is how Bricklink expects you to do that.
The costing element here is not fit for purpose, to be honest and has needed
major revamping since it was developed. But like most developments needed it
isn't going to happen.
When we started all those years ago and were adding stock, we tried to use the
cost field in a similar way to what you are describing and it simply was no where
near accurate enough and actually gave a false impression of profitability per
item.
Again, sorry to but in but costing is a really important thing to me (and our
store) We take it very seriously and have put effort into getting it as right
as possible.
Much better to have a proper inventory system and let it do the work for you.

Oh, it was only meant as a theoretical remark to that particular statement, not
to the topic as a whole. I totally agree with you: Bricklink doesn't allow
us to do this at all, and I don't really have any efficient workarounds to
do it.
I just think that theoretically:
(cost price of the part) = (selling price of the part) * ((cost price of the
set) / (partout upload value of the set))
And, as you also added:
(new part cost) = ((old part cost) * (old amount) + (new part cost) * (new amount))
/ (total amount)
As far as I can see these two formulas is really all that's needed to get
some really badass detailed and correct stats.
But really how and where to put these formulas into practise, I have no clue.
I do the same as the OP: I'm tracking cost prices of my sets and partout
values of my sets, and that's all. I don't have any clever method in
place for looking at it on a part level.
As I've mentioned at other occasions, it would be cool if Bricklink had a
field at the end of the partout procedure where you enter how much you spent
on the set. Then the system understands the profit factor of that set. And then
it can auto populate the "my cost" field to the right percentage of all the selling
prices of all the parts. And upon combining with old lots, it calculates correct
new values for the my cost fields (average cost of old and new amount). I think
that'd be an awesome system, which I would definitely been advocating if
seller tools weren't cancelled



             
        Author:  runner.caller  Posted:  Sep 3, 2019 11:31  Subject:  Re: Perpart profit tracking program?  Viewed:  20 times  Topic:  Selling  

  In Selling, calsbricks writes:
So lets say you add 100 at 10p each,
then you get a good deal and have another 100 at 8p  average cost price is now
9p, Bricklink doesn't cater at all for inventory management capabilities
like this and we have found the cost price field to be not worth the effort.
All of our costing/profit calculations are done in our offline system as there
we keep stock values by part.

Can't you use that function in the Mass Upload page under "Consolidate Lots
by Using:"?
Then select the "By Qty Avg Cost"? That would average it to 9p like you say.
Maybe that doesn't work when using the "part out" function vs. a mass upload.
It'd be pretty easy to copy a set inventory page though and paste it to an
excel document that could be converted to be mass upload friendly instead of
using the part out function when loading inventory from a set.



             
        Author:  Golbsco  Posted:  Sep 3, 2019 12:43  Subject:  Re: Perpart profit tracking program?  Viewed:  28 times  Topic:  Selling  

 In Selling, calsbricks writes:
 Forgive me for butting in, but that isn't really the best way to determine
your profit on your items. Firstly the cost price of your stock should be based
on the average cost price of those items. So lets say you add 100 at 10p each,
then you get a good deal and have another 100 at 8p  average cost price is now
9p, Bricklink doesn't cater at all for inventory management capabilities
like this and we have found the cost price field to be not worth the effort.
All of our costing/profit calculations are done in our offline system as there
we keep stock values by part.

Thanks for your input Calsbricks
Could i ask you some questions about your offline system? I don't want to
pry too much, but it must be the vehicle which allows you to manage almost 1
million parts without going completely mad. I've looked at your store a number
of times over the years and wondered how it's at all possible.
Is your system scratch built and coded to tailor your specific need, or is it
an enormous advanced spreadsheet?
Does it have virtual SKUs that stock is assigned to? (i ask because my SKUs
only exist if something is in them, leaving me now knowing how organised my draws
are)
Does it calculate the exact profit per piece that is sold from your inventory?
Does it generate data on trends and demand for colours, categories, themes,
etc?
Can you directly import orders into it to save manual data input?
Answer as much or as little as you like, im not asking you to spill all the
secrets, but these are the questions im asking myself if I was to build a similar
system. Any info is greatly appreciated but I understand if you would rather
not give info to another seller in your region



                 
          Author:  calsbricks  Posted:  Sep 3, 2019 13:22  Subject:  Re: Perpart profit tracking program?  Viewed:  26 times  Topic:  Selling  

 In Selling, Golbsco writes:
 In Selling, calsbricks writes:
 Forgive me for butting in, but that isn't really the best way to determine
your profit on your items. Firstly the cost price of your stock should be based
on the average cost price of those items. So lets say you add 100 at 10p each,
then you get a good deal and have another 100 at 8p  average cost price is now
9p, Bricklink doesn't cater at all for inventory management capabilities
like this and we have found the cost price field to be not worth the effort.
All of our costing/profit calculations are done in our offline system as there
we keep stock values by part.

Thanks for your input Calsbricks
Could i ask you some questions about your offline system? I don't want to
pry too much, but it must be the vehicle which allows you to manage almost 1
million parts without going completely mad. I've looked at your store a number
of times over the years and wondered how it's at all possible.
Is your system scratch built and coded to tailor your specific need, or is it
an enormous advanced spreadsheet?
Does it have virtual SKUs that stock is assigned to? (i ask because my SKUs
only exist if something is in them, leaving me now knowing how organised my draws
are)
Does it calculate the exact profit per piece that is sold from your inventory?
Does it generate data on trends and demand for colours, categories, themes,
etc?
Can you directly import orders into it to save manual data input?
Answer as much or as little as you like, im not asking you to spill all the
secrets, but these are the questions im asking myself if I was to build a similar
system. Any info is greatly appreciated but I understand if you would rather
not give info to another seller in your region

Hi there and thank you for y our comments.
I will answer as much of the questions you raised as I can.
Scratch built  yes
Coded by us  yes (Not a spreadsheet although we have feeds to spreadsheets for
charting purposes
The inventory system is constructed on a partno/colour and condition basis (That
is what makes up the unique code that we post to. We started by downloading the
Bricklink catalogue, many years ago and then converted that to our product file
 so we kind of have a miniature catalogue which is fed by orders in and orders
out as well as inventory adjustments.
We believe it does calculate exact profit per piece but cannot be 100% certain
about that.
It will, if we ask it produce data for parts, categories, families, colours,
and condition either in summary or detail form. It also exports that data to
Excel for more agility and features.
The order side is a bit more complicated still, as the formats coming from Bricklink
are cumbersome, do not have images associated with them and we do, so order input
is a bit of a combination of manual and import  but it works and is not too
long winded. We manage with it but if I ever get my developers involved I will
get that sorted.
I used access as the tool and had a colleague tidy up the code where I came unstuck.
Hope that helps and answers your questions and good luck if you decide to go
that way.



         
      Author:  Golbsco  Posted:  Sep 3, 2019 11:23  Subject:  Re: Perpart profit tracking program?  Viewed:  23 times  Topic:  Selling  

 In Selling, Teup writes:
 Hmm.. dividing the cost price equally over all parts.. That begs the question
if considering a minifig head the same cost price as a round brick is really
what you want.
I would much rather take the profit ratio (say, 2x) of the entire partout, and
then divide the selling price of the item by the profit ratio. That way, all
items have a cost that is proportional to their selling price. If you bought
a set for 10, part it out for 20, a minifig head sells for 1, the cost price
of that minfig head is 0.50. For another part in that same set that sells for
0.10, the cost price was 0.05. This way it makes more sense to me, because otherwise
it will seem as if you will make loads of profit on some orders and hardly any
profit on other orders, even though they came from exactly the same partout.

That's a good point that I had not considered. It would be daft to assume
the same cost for clearly more expensive parts which could also result in some
parts always coming out as negative profit.
However in terms of profit percentages, i think your method would always result
in the exact same profit margin for every part giving no indication which parts
generate more profit relative to their cost. Ideally i'd like to be able
to see the markup each part is generating. Please correct me if i'm wrong.
Also, if i have understood you correctly, how would you calculate your profit
ratio before selling all of the parts in a set? I assume by using the price guide
but to me the partout 6 month average only serves as a guideline as it does
not account for statistical outliers or parts that have zero demand. Would this
not also result in showing zero costs until a part is sold? It seems paradoxical
to me.
I typically price my inventory by taking the part out value and subtracting 20%
and only parting out sets that return a partout value of 3x the set cost, which
means (if my math is correct) that i have to sell 40ish% of the expected revenue
to break even on the cost of the set. It's not ideal but I do this to try
to overcome a lot of variables that i'm currently unable to account for on
a large scale such as any given parts demand.



             
        Author:  Teup  Posted:  Sep 3, 2019 11:32  Subject:  Re: Perpart profit tracking program?  Viewed:  29 times  Topic:  Selling  

 In Selling, Golbsco writes:
 However in terms of profit percentages, i think your method would always result
in the exact same profit margin for every part giving no indication which parts
generate more profit relative to their cost. Ideally i'd like to be able
to see the markup each part is generating. Please correct me if i'm wrong.

Yeah, that's true. So if, say, profit is 60% of the whole set, then it assumes
that on any part, whether a minifig or a technic pin, 60% of that turnover is
profit and 40% is cost. But what does it really mean to assess which parts are
profitable? I mean... it's kind of a philosophical question and I wonder
if there's really any point in answering it. Well, maybe over the course
of many part outs (provided that this hypothetical system takes the correct average
cost when merging with old lots) a picture emerges of which parts have a high
profit %?
 Also, if i have understood you correctly, how would you calculate your profit
ratio before selling all of the parts in a set? I assume by using the price guide
but to me the partout 6 month average only serves as a guideline as it does
not account for statistical outliers or parts that have zero demand. Would this
not also result in showing zero costs until a part is sold? It seems paradoxical
to me.

Well not the price guide, but your actual prices that you sell the parts at.
And they can be known, by the value that appears at the bottom of the partout
verification page. The moment that it tells you you're about to upload items
for a total value of 300, and from your receipts you know that you've spent
150 on it, at that point you could say that all parts that you are about to upload
have a 50% profit rate.
Of course, that assumes that you are not going to change the price anymore afterwards.
Hmm... and that is exactly what happens if you merge with old lots and use the
new price... I haven't thought of that scenario yet, but I am sure that
theoretically you could make a formula for that too.



                 
          Author:  Golbsco  Posted:  Sep 3, 2019 12:23  Subject:  Re: Perpart profit tracking program?  Viewed:  18 times  Topic:  Selling  

 In Selling, Teup writes:
 In Selling, Golbsco writes:
 However in terms of profit percentages, i think your method would always result
in the exact same profit margin for every part giving no indication which parts
generate more profit relative to their cost. Ideally i'd like to be able
to see the markup each part is generating. Please correct me if i'm wrong.

Yeah, that's true. So if, say, profit is 60% of the whole set, then it assumes
that on any part, whether a minifig or a technic pin, 60% of that turnover is
profit and 40% is cost. But what does it really mean to assess which parts are
profitable? I mean... it's kind of a philosophical question and I wonder
if there's really any point in answering it. Well, maybe over the course
of many part outs (provided that this hypothetical system takes the correct average
cost when merging with old lots) a picture emerges of which parts have a high
profit %?

Currently it does feel philosophical because parting out sets is more of a "hit
and hope" situation where I can never be 100% I will definitely make the profit
i'm hoping for as I could be stuck with 40% of the parts in the set having
high 6 months avg but only 5 sales, which makes it look like a great opportunity
but is actually a loss once you factor in time and storage expenses.
Perhaps knowing the exact profit of each part is overkill because I can only
buy parts in sets of which I can't dictate the contents of anyway.
Preferably I would turn it into a science and program something to tell me exactly
what it is i need to part out but alas, here i am with draws full of yellow and
bright orange parts because the part out value looked "nice".



                     
            Author:  Teup  Posted:  Sep 3, 2019 13:34  Subject:  Re: Perpart profit tracking program?  Viewed:  30 times  Topic:  Selling  

 In Selling, Golbsco writes:
 In Selling, Teup writes:
 In Selling, Golbsco writes:
 However in terms of profit percentages, i think your method would always result
in the exact same profit margin for every part giving no indication which parts
generate more profit relative to their cost. Ideally i'd like to be able
to see the markup each part is generating. Please correct me if i'm wrong.

Yeah, that's true. So if, say, profit is 60% of the whole set, then it assumes
that on any part, whether a minifig or a technic pin, 60% of that turnover is
profit and 40% is cost. But what does it really mean to assess which parts are
profitable? I mean... it's kind of a philosophical question and I wonder
if there's really any point in answering it. Well, maybe over the course
of many part outs (provided that this hypothetical system takes the correct average
cost when merging with old lots) a picture emerges of which parts have a high
profit %?

Currently it does feel philosophical because parting out sets is more of a "hit
and hope" situation where I can never be 100% I will definitely make the profit
i'm hoping for as I could be stuck with 40% of the parts in the set having
high 6 months avg but only 5 sales, which makes it look like a great opportunity
but is actually a loss once you factor in time and storage expenses.
Perhaps knowing the exact profit of each part is overkill because I can only
buy parts in sets of which I can't dictate the contents of anyway.
Preferably I would turn it into a science and program something to tell me exactly
what it is i need to part out but alas, here i am with draws full of yellow and
bright orange parts because the part out value looked "nice".

Yeah, I was strictly talking about sets that you already are parting out, not
about a method for deciding what to buy and what not to buy. (either way it's
only theoretical because it's not like any of this will really be developed
by Bricklink )
But I think for deciding what sets to buy, I think the part out value is good
enough. Sooner or later everything sells, so part out value minus cost price
is pretty much the profit you will eventually get. The individual parts values
doesn't really interest me that much. The first additional info that I am
interested in, is how much of the set is stuff that I already have in my inventory.
And then some sellers have illustrated that some parts are in permanent oversupply
so they sell slow. So yeah, there definitely are factors besides a simple partout
value number, but exact part values aren't my first concern.



     
    Author:  brikomania  Posted:  Sep 3, 2019 12:26  Subject:  Re: Perpart profit tracking program?  Viewed:  24 times  Topic:  Selling  

 In Selling, Golbsco writes:
 Recently i've had this thought on my mind that i'm unable to truly calculate
exactly how much profit is made through sales without manually tracking every
part in my inventory.
So far i've been calculating profit by simply totaling sales and subtracting
expenses. This gets the job done for tax purposes but not much else.
I would like to be able to track exactly how much profit is made on each part,
for example i can easily calculate the cost per part by simply dividing the cost
of a set by part quantity then subtracting that average part cost from the price
of the part sold. While this is absolutely possible to do manually (and i think
i have a system in mind to accomplish this) it requires immense amounts of work
and is not viable for scaling up.
This then gives data on best and worst selling parts, and could hopefully expand
to generating data on demand and trends.
Preferably i would be able to import orders directly into the system too.
Short of designing a program to achieve this and perhaps getting it created 
Does something like this already exist?
What do some of the larger sellers do to solve these issues?
I apologise if i haven't described this very well but i'm not exactly
sure what it is im after, which brings me to the forums to gather some info and
feedback

If you use bricksync, it does this with the mycost command. Takes the cost and
"spreads" it through a bsx file.





