How to avoid circular reference

Happy new year! Guys, maybe you could help me with the following.

For school I'm looking for a formula to be able to avoid a circular reference. Let me explain further:
I want to determine a sales price in a formula, with a variable which is called 'profit' in %. I have a certain amount of costs and over that there should be a profit % and that will result in a sales price. All cool so far, but in the total amount of the costs is a commission which is determined as a % of the salesprice, that's where the circular reference comes from. I heard some things about a goal seek but I'm not sure if this works for me, does anyone have any ideas?

Thanks in advance!

Best regards,
Christian Hoedeman

Hi Chris,

Thanks for your fast response, I've uploaded a file with this message, I hope it works
As you can see there are all kinds of costs calculated to result in a total which name is 'Direct Order cost'. Included in 'Direct Order cost' is also a 'Commission' for an agent or RE = Regional Entity, which is calculated as a % from the 'Net Sales whole'. 'Net sales whole' = 'Net Sales NL' + 'RE Discount' and 'Net Sales NL' = the 'Direct Order cost' / 100% minus 'Gross Profit Margin'. I hope I didn't make this too complicated now .
The general question is, is there any formula that can hold in the cell at 'Net Sales NL' (AE) which calculates the sales taking consideration of the margin over the direct order costs and the commission which is calculated over the sales whole?

Thanks in advance!

Christian
PS: the columns concerning are marked orange

Guys,

I can do this, when I make some kind of mark up over the 'Production Cost' and then do the function goalseek to see what % mark up I need to realize a certain percentage of Gross Profit. But ofcourse I wan't to make this worksheet simple to use as it's not only used by me, is there any way to get a marco or formula running that makes the goal seek automatic?

This would really help alot!

Thanks already!

Best regards,
Christian

Good morning guys,

I think I have found a way to bypass the circular reference by putting a mark up on the part of the costs which doesnt include the commission. Then this way I can change the mark up with goal-seek to set it to a certain value to create a certain ammount of Gross Profit. I want to have this as a 'button' or macro, I have now made this:

Sub calculeer2()
'
' calculeer2 Macro
' Macro recorded 1/3/2013 by hoede00c
'

'
Range("C7").Select
ActiveCell.FormulaR1C1 = "35%"
Range("D10").Select
Range("D10").GoalSeek Goal:=0.35, ChangingCell:=Range("C10")
End Sub

As you can see it uses 35%, but I want to make this 35% into a cell, is this possible? Maybe with some extra way by copying the content of a certain cell and pasting it into the goalseek formula... :-)

Thanks in advance!

Best regards,
Christian

MrShorty, Thanks for your response, an algebraic solution might be possible but will be very difficult. Let me explain further:
Let's say we have:
A total cost (without commissions)
B commissions (% over Net Sales Company (whole))
C total cost (with commission %)
D grossprofit (%)
E Net sales NL
F RE Discount
G Net sales Company (whole)
H GFA Discount
I Gross Sales

Schematic:
total cost (with commission %)
+ grossprofit % over total cost (with commission %) (="total cost (with commission %)"/(1 - grossprofit %)
--------------------
= Net sales NL
+ RE Discount % over Gross Sales
------------------
= Net Sales Company (whole)
+ GFA Discount % over Gross Sales (this is a discount for a particular client)
------------------
Gross Sales

The value's which are known are the total cost (without commissions) and all the percentages mentioned. How would I make a formula going from these "total cost (without commissions)" towards the Gross Sales? Looks like an algebraic challenge

SOLVED! Thanks for all the help guys!