Calculating Selling Price Formula in Excel

**hitari** · 03-23-2013, 05:47 PM

Hi,

I am quite new to this forum, I am working on a formula to calculate the selling price based on variables available including Item cost, commissions on sale, postage charge and fixed profit.

Example will be as follow:

Cost of the item = £10

Postage charge = £0.50

1st Commission on Sales = 9% (this include VAT of 15%)

2nd Commission on Sale = 1.9% (No VAT on this)

The Fixed Net Profit after deducting all VAT to make on this sale = £1

(Note: the selling price inc 20% VAT)

Any suggestions or help would highly be appreciated.

Thank you

**Richard Buttrey** · 03-23-2013, 06:03 PM

Hi,

Can you give a couple or three of actual worked examples. The precise relationships between the various inputs and results are not clear from your description. In your examples identify which cells are .inputs

**hitari** · 03-23-2013, 06:39 PM

Thank you for the quick reply.

Examples as follow:

Cost price: £10
Postage: £0.75
1st Commission: 9%
2nd commission: 1.9%
Profit sought: £1
The selling price should be £15.96

-----

Cost Price: £32
the rest of values are the same
the selling price should be £45.85

----

I used the formula (Fixed Profit) on this thread but the results are not accurate. Very close but not correct something seems to be missing and result do not match exactly: http://www.excelforum.com/excel-gene...ing-price.html

**Richard Buttrey** · 03-23-2013, 08:15 PM

Hi,

I'm attaching a workbook. I don;t get quite the same answers but that may be because I'm not interpreting your rules correctly. For instance whether Sales Commission is based on pre Vat or Post Vat Sales.

Also note that since this is a recursive calculation, the 'Iterative Calc' setting must be on so that you have a circular calculation.

Hopefully you can modify the rules as per your understanding, but if not post back with a detailed manual calculation of all the numbers that total the Sales figure and explain what manual calculation you are making at each stage.

**hitari** · 03-24-2013, 10:07 AM

This is the formula required but when implementing it in Excel (ignoring the 2nd commission with VAT element for now), it does not give the correct result (Note: the cost here includes the postage charge too of 75p):

Price = Cost + Comm + Profit
Price = Cost + Price*Comm% + Profit
Price - Price*Comm% = Cost + Profit
Price * (1 - Comm%) = Cost + Profit
Price = (Cost + Profit) / (1 - Comm%)

Price =(G8+G4)/(1-G9)

e.g:
The Cost = £10.75
Commission = 2%
Profit = £1

The formula needs to give us the selling price (inc vat) = £14.45