Costs Comparison and Generate Rate

[sorpasso]

Hi,

I am trying to find the way to compare products costs between 3 vendors against mine and generate a sell rate with the best profit possible. So, for example the best competitor cost is $1 and mine is $ 0.50, I want to generate a rate to be close and below to $1. Do you know any macro or formula for it? The trick is that my costs may be higher than any of these vendors' cost in certain products.


Thanks,
Sorpasso

 

[sorpasso]

I see it, but I am afraid I don't really understand what it is I am looking at or how to determine what you are after.

Hi Joe4, Yes, I am with you and let me explain this.

Each carrier has a cost based on Per Piece and Kilo units or Per Kilo unit only. In my Costs Comparison chart I calculated the cost for trucking one package of 40 grams. So, for example Carrier one cost is ($ 10.50 * $ 0.04) + $ 0.50= $ 0.92. This is the cost for trucking a 40 grams packet. Same calculations were done for the rest of the carriers and my cost. Then, as explained before, I have placed a conditional formatting for highlighting the best cost out of these 4 carriers (3 competitors and ours). Please, let me know if this is more clear now and do not hesitate to keep asking me. Have a great weekend!

Thanks,
Sorpasso

 

[Joe4]

OK, I don't understand why there are three different lines for each vendor. What is the difference?
I am also not sure I understand where exactly we are going, based on this chart.
What is the automatic goal to show, in connection with all the data you have presented?
Which rates are variable and which are not?

 

[sorpasso]

OK, I don't understand why there are three different lines for each vendor. What is the difference?
I am also not sure I understand where exactly we are going, based on this chart.
What is the automatic goal to show, in connection with all the data you have presented?
Which rates are variable and which are not?

Hi Joe4,

There are three lines in this example because there are 3 different cities (city 1,2,3) and thus, their different costs. The target is to be the best against our competitors by 1 cent as you said, for example. The goal is to have excel to calculate a sell rate that is less expensive than the costs of those 3 carriers with a maximum profit per city for us. No rate is variable, those are fixed costs, but we need to come up with sell rates based on my costs against our competitors costs (carrier 1,2,3).


Thanks,
Marcelo

 

[Joe4]

You have listed the costs that each vendor incurs, but don't you also need to know what price they are selling their products at?

 

[sorpasso]

You have listed the costs that each vendor incurs, but don't you also need to know what price they are selling their products at?

Their costs are their rates, my costs are my purely costs without any markup. This is why I can take advantage of it, I know what they sell their service for but they don't know my costs.


Thanks,
Sorpasso

 

[Joe4]

OK, I think I got it now.

The first thing you want to do is find the minimum Cost of your competitors. So you can use the MIN function and take the minimum value from the C1, C2, and C3 columns.
Then, subtract $0.01 from that, and divide by 0.04 (since you only have the KG cost and not the PC cost).

So, for the first row, you would have:
=MIN(0.92,0.48,0.62) = 0.48
and then:
=(0.48-0.01)/0.04 = 11.75

So that is the maximum KG cost you can charge and still come in lower than your competition.

 

[sorpasso]

OK, I think I got it now.

The first thing you want to do is find the minimum Cost of your competitors. So you can use the MIN function and take the minimum value from the C1, C2, and C3 columns.
Then, subtract $0.01 from that, and divide by 0.04 (since you only have the KG cost and not the PC cost).

So, for the first row, you would have:
=MIN(0.92,0.48,0.62) = 0.48
and then:
=(0.48-0.01)/0.04 = 11.75

So that is the maximum KG cost you can charge and still come in lower than your competition.

Hi Joe4,

Thanks. Yes, I was thinking of doing something similar but keep present that our costs could be more expensive than $ 11.75/kg. I guess that the next step is to do a cost calculation between this $ 11.75/kg. rate against our cost $ 9.16/kg. Yes, in this case it will be easy to see that our cost is lower but I am pretty sure we will be more expensive than our competitors in other cities. How would you check and make sure we are not losing our shirts here? You can see this problem in City # 2 costs.

How would you calculate a Per Piece and Per Kilo?


Thanks,
Sorpasso

 

[Joe4]

If you perform the same calculation on the other two lines, it returns $12.75 and $13.75, respectively.
So, if you are looking for one set price across ALL cities, then you would just take the smallest of those three values ($11.75).

How would you calculate a Per Piece and Per Kilo?

You didn't seem to have a Per Piece component in the table showing your costs (just the per kilo component).
If you have both, and they both can be variable, well now you are getting much more complex. Probably some sort of linear relationship that you would want to graph out.

I think you might be wading out of the area of Excel help, and really getting into Cost Analysis, which is more math/business related than Excel.
I have no background in that, so probably cannot offer much more than I have already done.

 

[sorpasso]

If you perform the same calculation on the other two lines, it returns $12.75 and $13.75, respectively.
So, if you are looking for one set price across ALL cities, then you would just take the smallest of those three values ($11.75).


You didn't seem to have a Per Piece component in the table showing your costs (just the per kilo component).
If you have both, and they both can be variable, well now you are getting much more complex. Probably some sort of linear relationship that you would want to graph out.

I think you might be wading out of the area of Excel help, and really getting into Cost Analysis, which is more math/business related than Excel.
I have no background in that, so probably cannot offer much more than I have already done.

Hi Joe4,

Thanks. I think I can handle it from now forward. I came up with a complex rate Per Piece and Per Kilo where present by coming up with the net profit between our rate against our cost and applying it on our Per Piece and Per Kilo cost. Works well. Then, as you said, I would need to pay attention where the conditioning formatting says that we are not competitive and keep the same costs plus a bit of profit for those destinations. Thanks a lot Joe4, I really appreciate your help here.


Thanks,
Sorpasso

 

[Joe4]

I am not sure how much I was able to help, but you are welcome!