Margin Calculator

I have a rental business. From time to time, a customer damages my asset beyond repair. I want to develop a spreadsheet that helps me open negotiations based on a pre-determined margin% that I enter.

I am attaching a mock-up of what I currently am using. Right now, I am using a hit-and-miss tactic to come up with a potential sell price to achieve the margin that I require. Ideally, I would like to type in the margin% and the spreadsheet calculate the MINIMUM Margin Sell Price to achieve that margin%.

These are the formulas that I am using currently:
Total Revenue = Rental Revenue + (Potential) Margin Sell Price (ie negotiated)
Total Costs = Unit Standard Cost + Commission + Freight + Repair Charges
Margin = Total Revenue - Total Costs
Margin% = Margin/Total Revenue

Can anyone help? I'm not sure if there are internal Excel functions that deal with margin...

Thanks,
CHAStats

How do I use the Goal Seek in my example?

Can it be done just in the spreadsheet?

thx for your help!

Thanks alot...

I managed to get the Goal Seeker to work how it should. Just had to alter some formulas...

I suppose with the iterations, that there would be no easy way for Excel to do it using formulas... unless you've found a way? Is there a way to write some script to pre-populate the values into Goal Seeker ? (so my other team members can do this without "thinking")

Thanks again... muchly appreciated !

CHAStats