I have a project that I am trying to work on for the owner of a contracting business and have some confusion on how to work this out. I work with electrical contractors to buy wire... The price of these products are directly related to the market. However, the only thing I have to start with was a price sheet from a distributor.
So we had two constants
• Price of copper (for the month average for this particular months price sheet)
• Sell price of a given SKU
What was suggested was to find the margin of the price compared to the price of copper. The owners thought was to take the price of Copper (C1) and divide by the sell price (Varied Cells)… Keeping in mind that this sheet is only accounting for the given month of Copper (May 2017)
These “believed margins” is something we hope that stays constant during price fluctuations, however, not likely… but we want to get a current sheet price sheet from the same distributor and see the adjusted sell prices with the market price that matches to proof out the work or at least determine the elasticity of the margin.
Why the need for a quadratic equation? Because if we know the “margin” and the price of copper… we can adjust the market price dynamically and then solve for “X” which would be the “Price Adjusted for Market Rate”
We worked this out by doing the following:
Existing Known
This is for the 14SOL item on the first line in the excel sheet - 2.6085 / 48.39 = 0.0539 x 100 = 5.39%
Assuming Copper is at 3.60 – how do we price this same commodity with a similar margin?
3.60 / X = 0.0539 x 100 = 5.39%
7217 x 1 = 0.0539
2000 X
7217 = 0.0539
2000x
7217 = 107.8x
107.8 107.8
X = 5155 X = 66 73/77
77
X = 66.9481
So at 3.60 per pound the same item at 5.39% margin should be sold close to 66.95 – obviously this is just a gauge, but at the end of the day trying to create a worksheet that helps calculate these market changes on the fly by knowing certain constants… Do you see flaws in this? What are your thoughts?
I am not an excel master, nor do I even know if I am looking at this correctly. My sheet was merely created as a start to determine the margins a supplier had so that I can work from these values to create another sheet that prices the assumed sell prices automatically.
File with Assumed Margins from existing price sheet: https://www.excelforum.com/attachments/excel-formulas-and-functions/538789d1505830227-calculating-commodity-sell-prices-based-on-market-changes-copper-sheet.xlsx
So we had two constants
• Price of copper (for the month average for this particular months price sheet)
• Sell price of a given SKU
What was suggested was to find the margin of the price compared to the price of copper. The owners thought was to take the price of Copper (C1) and divide by the sell price (Varied Cells)… Keeping in mind that this sheet is only accounting for the given month of Copper (May 2017)
These “believed margins” is something we hope that stays constant during price fluctuations, however, not likely… but we want to get a current sheet price sheet from the same distributor and see the adjusted sell prices with the market price that matches to proof out the work or at least determine the elasticity of the margin.
Why the need for a quadratic equation? Because if we know the “margin” and the price of copper… we can adjust the market price dynamically and then solve for “X” which would be the “Price Adjusted for Market Rate”
We worked this out by doing the following:
Existing Known
This is for the 14SOL item on the first line in the excel sheet - 2.6085 / 48.39 = 0.0539 x 100 = 5.39%
Assuming Copper is at 3.60 – how do we price this same commodity with a similar margin?
3.60 / X = 0.0539 x 100 = 5.39%
7217 x 1 = 0.0539
2000 X
7217 = 0.0539
2000x
7217 = 107.8x
107.8 107.8
X = 5155 X = 66 73/77
77
X = 66.9481
So at 3.60 per pound the same item at 5.39% margin should be sold close to 66.95 – obviously this is just a gauge, but at the end of the day trying to create a worksheet that helps calculate these market changes on the fly by knowing certain constants… Do you see flaws in this? What are your thoughts?
I am not an excel master, nor do I even know if I am looking at this correctly. My sheet was merely created as a start to determine the margins a supplier had so that I can work from these values to create another sheet that prices the assumed sell prices automatically.
File with Assumed Margins from existing price sheet: https://www.excelforum.com/attachments/excel-formulas-and-functions/538789d1505830227-calculating-commodity-sell-prices-based-on-market-changes-copper-sheet.xlsx
