Calculating Min Selling Price

So are you looking to make 5% of the VAT as well? eg if costs and fees took it to £100 then add 20% VAT to give you total selling price of £120, would you expect to make £5 or £6 profit?

One way:

Row\Col	B	C	D	G
2	Cost
3	Direct Cost	$ 30.00
4	Subtotal, Cost	$ 30.00
5
6	Cost-Based Percentages
7		0.00%
8	Subtotal, CB%	0.00%
9
10	Fixed Amounts
11	Shipping	$ 2.00
12	Subtotal, FA	$ 2.00
13
14	Sell-Based Percentages
15	Fees	7.00%
16	VAT	16.67%
17	Gross Margin	5.00%
18	Subtotal, SB%	28.67%
19
20	Sell	$ 44.86		C20: =((1+C8) * C4 + C12) / (1 - C18)

Be aware however, you charging a profit on VAT is highly highly wrong and will cause you problems with the Taxman. VAT should be charged on the selling price ie your costs + your markup, not the other way round.

Why would that be? The taxman gets 16.7% of the sell price.

Row\Col	B	C	D	E	F	G
2	Cost			Check
3	Direct Cost	$ 30.00		$ 30.00
4	Subtotal, Cost	$ 30.00
5
6	Cost-Based Percentages
7		0.00%		$ -		E7: =C7 * C3
8	Subtotal, CB%	0.00%
9
10	Fixed Amounts
11	Shipping	$ 2.00		$ 2.00		E11: =C11
12	Subtotal, FA	$ 2.00
13
14	Sell-Based Percentages
15	Fees	7.00%		$ 3.14		E15: =C15 * $C$20
16	VAT	16.67%		$ 7.48		E16: =C16 * $C$20
17	Gross Margin	5.00%		$ 2.24		E17: =C17 * $C$20
18	Subtotal, SB%	28.67%
19
20	Sell	$ 44.86		$ 44.86		C20: =(C4 * (1 + C8) + C12) / (1 - C18)

[ERRATA] Original response calculated profit as %cost instead of %price. Red edits show corrections.

Originally Posted by r3ssp

I need to create a formula that will provide me with a minimum price to sell items to ensure I cover purchase cost & selling fees & generate a set profit margin.
[....]
Information:
Cost price - fixed cost e.g. 30
VAT - 20% of selling price
Fees - 7% of selling price
Shipping - fixed cost e.g. 2
Profit - 5% after cost & fees are considered

Originally Posted by pjwhitfield

if costs and fees took it to 100 then add 20% VAT to give you total selling price of 120, would you expect to make 5 or 6 profit?

Originally Posted by r3ssp

Would be looking to make 6 profit in that case.

I see nothing wrong with seeking profit on VAT as well as other costs as long as the tax agency gets its 20% of the sales price.

But I believe it is unusual since VAT is not considered to be a cost of doing business. So your accountant might get his panties in a bunch. And you did say just "cost & fees", not "cost, fees & taxes".

Be that as it may, if C1 is the fixed cost (30), S1 is the fixed shipping cost (2), F1 is the fees rate (7%), M1 is the profit margin rate (5%), and V1 is the VAT rate (20%), the price in P1 can be calculated by:

=ROUNDUP((C1+S1)/(1-M1-V1-F1),2)

Note that because of the denominator, the profit margin rate in M1 must be less than (rounded to 2 percentage decimal places, an arbitrary choice):

=ROUNDDOWN(1-V1-F1,4)

To demonstrate, consider the following (formulas in column A are shown on the right):

	A	B
1	47.06	price	=P1
2	3.29	fees	=ROUND(P1*F1,2)
3	9.41	VAT	=ROUND(P1*V1,2)
4	2.00	shipping	=S1
5	61.76	TOTAL	=SUM(A1:A4)
6
7	47.06	price	=A1
8	-44.70	cost	=-SUM(C1,A2:A4)
9	2.36	PROFIT	=A7+A8
10	5.01%	%profit	=A9/A7

Alternatively, if you realize that VAT should not be included in the profit margin, the price in P2 can calculated by:

=ROUNDUP((C1+S1)/(1-M1-F1),2)

In that case, the profit margin rate (M1) must be less than:

=ROUNDDOWN(1-F1,4)

To demonstrate:

	A	B
12	36.37	price	=P2
13	2.55	fees	=ROUND(A12*F1,2)
14	7.27	VAT	=ROUND(A12*V1,2)
15	2.00	shipping	=S1
16	48.19	TOTAL	=SUM(A12:A15)
17
18	36.37	price	=A12
19	-34.55	cost	=-(C1+A13+A15)
20	1.82	PROFIT	=A18+A19
21	5.00%	%profit	=A20/A18

[EDIT] Note: I assume that VAT is not charged against fees. But that depends on local tax laws, which I am not aware of.

as long the tax agency gets its 20% of the sales price.

16 2/3% of the sell price.

I can't relate that worksheet to your original question.

For any combination of fixed and variable costs like those that you described, the problem reduces to calculating a multiplier and offset. For your first example, multiplying direct cost by 1.4 and adding $2.80 covers shipping, fees, VAT, and 5% gross margin on the sell price. I don't follow the workbook you posted, but there is one pair of such numbers for each venue in which you sell.