calculating selling price

I would like to calculate selling prices in different ways, based on a choice made by the user.
Choices would be as follows:

1. Fixed Profit Amount - eg.15
2. Cost Plus eg.10%
3. To give a fixed % profit based on selling price - eg.10%

The complication is that part of the cost of the sale is variable based on the selling price - eg. a sales commission.

I've attached a worksheet.

Would really appreciate any help with this conundrum!

Hi,

Hopefully I've interpretted your three mechanisms correctly. The formulas are in the spreadsheet provided.

Here's an explanation of the derivation of each of the three, since I think that's probably what you're more interested in.

Please note for the following I shifted the cost Direct Cost to cell G8 and added a cell for Commission % in cell G9.

For the below, the legend is as follows:

Price = Selling Price
Comm = Commission
Comm% = Commission Percentage
Cost = Cost
Profit = Profit
Profit% = Profit Percentage

1. Fixed Profit

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)

2. Cost Plus %

Assuming Profit is supposed to be total cost (Direct Cost + Commission) times the Profit %.

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

Price =(G8*(1+G6))/(1-G9*(1+G6))

3. Fixed % Profit off Selling Price

Assuming Profit should equal Selling Price times the Profit Percentage.

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

Price =G8/(1-G9-G5)
______________________________________________________

All three of these are provided in the spreadsheet separately (see column J). Additionally, all three are combined into cell D4.

S