SQL Server code to duplicate Excel calculation that includes circular reference -

is there way duplicate formula circular reference excel file sql server? client uses excel file calculate selling price. selling price field (costs/1-projected margin)) = 6.5224 (1-.6) = 16.3060. 1 of numbers goes costs commission defined sellingprice times commission rate.

costs = 6.5224 projected margin = 60% commissions = 16.3060(selling price) * .10(commission rate) = 1.6306 (which part of 6.5224)

they around circular reference issue in excel because excel allows them check enable iterative calculation option , stops iterations after 100 times.

is possible using sql server 2005?

thanks

don

this business problem, not one, follows need business solution, not one. doesn't sound you're working particularly astute customer. essentially, you're feeding commission costs , recalculating commission 100 times. salesman earning commission based on commission?!? seriously? :-)

i try persuading them calculate costs , commissions separately. in professional organisations accounting practices i've worked before these costs broken down operating , non-operating or raw materials costs, should improve understanding of business. report total costs later on, add commission , raw materials costs. no circular loops , accounting reports.

at banks i've worked these costs called things cost (no commissions or fees), net cost (cost + commission) , bizzarely net net cost (cost + commission + fees). depending on business model, cost breakdowns can quite interesting.

here 2 sensible options might suggest them calculate selling price.

option 1: if you're going calculate margin exclude commission

price before commission = cost + (cost * (1 - projected margin))

selling price = price before commission + (price before commision * commission)

option 2: if client insists on calculating margin include commission (which sounds might want do) then

cost price = cost + (cost * commission)

profit per unit or contribution per unit = cost price * (1-projected margin)

selling price = cost price + profit per unit

this sensible in accounting terms , doddle implement sql or other software tool. means customer has way of analysing sales highlight per unit costs , per unit profits when projected margin different per product. invariably happens business grows.

don't blindly accept calculations spreadsheets. think them through , don't afraid ask customer they're trying achieve. broken business processes make far department before being called question. don't afraid of doing job , means challenging customer requests when don't make sense.

good luck!