Can you please provide some examples to illustrate the calculation of retail price for a give cost price?
On the face of it, it should be possible to convert cost price --> retail price using a formula approach, i.e. no need for GoalSeek, or creating a table with all results from £0.01 through £100.00.
You also quote one possible combination of Channel, Vatrate and Shipping. How does the calculation vary for other combinations, and does this mean you are calculating 10,000 prices for each possible combination of Channel, Vatrate and Shipping?
I'll try but although the maths is simple enough, the multitude of combinations can make it quite difficult to decipher! Excel Colummns then;
A - Drop-down selection containing 2 options 'eBay' or 'Amazon'
B - Drop-down selection containing 2 options for VAT rate '1.20' or '0.00'
C - Discount Percentage on Cost Price in Column D. Example 0.00%
D - ExVat Cost Price - example 5.00
E - IncVat Cost Price =IF(B2=0,D2*(1-C2),(D2*(1-C2)*1.2))
F - Markup Calculation - example 1.46
G - Retail Price =SWITCH(B492,1.2,AR492,0,AT492)
H - Drop-down selection containing 2 options for shipping 'Packet' or 'Large Letter'
I - Total Sale Value (TSV) This cell looks up another worksheet TAB and is returning a match for the Shipping Choice which is equivalent to the corresponding shipping charge being added to the RP =G2+VLOOKUP(H2,'Key Facts'!A$2:C$3,3,FALSE)
J - COGS (cost of goods/services) - This cell is looking up the choices made in A and H and entering the corresponding value from another column in that worksheet which in turn is referencing another worksheet for the resulting number. So the calculation in column J is =IF(AND(A2="eBay",H2="Large Letter"),AV2,IF(AND(A2="eBay",H2="Packet"),AX2,IF(AND(A2="Amazon",H2="Large Letter"),AZ2,IF(AND(A2="Amazon",H2="Packet"),BB2,"FALSE")))) and that is referencing cells that contain similar calculations to this =((I2*'Key Facts'!G$2)*1.2)+'Key Facts'!M$2+E2+((I2*'Key Facts'!J$2)*1.2) Their job is to return a summary of other related costs which are sale price dependent.
K - Gross Profit Margin =I2-J2
L - VAT DUE. This cell has to perform a calculation to work out the EXACT amount of VAT due (if you are not familiar with UK VAT this might be a challenge for you - essentially the VAT due is the difference between the VAT paid out and the VAT received). The calculation is this =IF(B2=0,IF(AND(A2="eBay",H2="Large Letter"),AF2,IF(AND(A2="eBay",H2="Packet"),AL2,IF(AND(A2="Amazon",H2="Large Letter"),AI2,IF(AND(A2="Amazon",H2="Packet"),AO2,"FALSE")))),IF(AND(A2="eBay",H2="Large Letter"),AE2,IF(AND(A2="eBay",H2="Packet"),AK2,IF(AND(A2="Amazon",H2="Large Letter"),AH2,IF(AND(A2="Amazon",H2="Packet"),AN2,"FALSE"))))) Again this referencing a cell which in turn uses a calculation like this =(I3/120*20)-(E3-D3)-('Key Facts'!L$3-'Key Facts'!K$3)-('Key Facts'!F$3-('Key Facts'!B$3+'Key Facts'!E$3))-((I3*'Key Facts'!J$2*1.2)-I3*'Key Facts'!J$2)-AP3 which is referencing another worksheet Tab for the resulting number.
M - Net Profit Margin =K2-L2
N - Net Margin as a percentage =M2/D2
As you can see. It's not a straightforward calculation as there are too many IFs involved to make it easy.
The goal of this spreadsheet is to ask a series of questions and give accurate answers based on user selection. Essentially you are asking for an answer to this;
"If I sell a widget on eBay or Amazon that is Vatable or Non-Vatable and is a Large Letter or a Packet, how much do I need to mark it up for to get X profit margin."
The holy grail is the answer! Unfortunately it becomes very complicated by things such as;
- Sales Channel Fees (plus vat)
- Shipping Fees (plus vat) + Variable Fuel Surcharges (Plus Vat)
- Packaging Costs (plus Vat)
- Third Party Fees (plus Vat)
- Banking fees (such as PayPal)
- VAT due on Sales price
Although I could use an approximate percentage which is equal to all those things and an approximate monetary value of costs applicable per sale, because of the margins involved in retailing in an online environment it needs to be 100% accurate to the point of a penny otherwise you simply lose money or don't make a sale.
The reason then for such a complicated way of working out a simple answer is because of the many variables involved and the need to be able to ultimately carry out some API/Auto VLookups against live cost price data every 5 minutes to return the correct corresponding retail prices as markets change with price matching.
And you thought selling on eBay was easy huh?!