Margin Calculator

**CHAStats** · 02-21-2007, 01:29 PM

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

**Carim** · 02-21-2007, 01:50 PM

Hi,

Think the easiest solution for you is to try from main menu
Tools Goal Seek ...
It does what you are looking for : setting the end result to help you determine the input required ...

**CHAStats** · 02-21-2007, 03:02 PM

How do I use the Goal Seek in my example?

Can it be done just in the spreadsheet?

thx for your help!

**Carim** · 02-22-2007, 11:51 AM

Hi,

http://www.ozgrid.com/Excel/goal-seek.htm

**CHAStats** · 02-22-2007, 01:57 PM

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

**Carim** · 02-22-2007, 02:03 PM

Hi,

As I told earlier, Goal Seek is a very quick simple way ...
But there is always the alternative to write all mathemical formulas backwards in order to achieve the same objective ...
Would you need help for that or not ...?

**Carim** · 02-22-2007, 02:27 PM

Hi,

See attached ...

**CHAStats** · 02-22-2007, 04:27 PM

AWESOME!!! Thank-you... Thank-you! Works like a charm!

One last thing...

I have 2 rows above the headers labelled AVERAGE and TOTALS, which calculate the avg/totals in the columns. Is there a way to have the spreadsheet be dynamic in length (ie # of entries) and still calc AVG and TOT?

Right now, I have pre-set my spreadsheet to 500 rows of data. I would like it if I didn't have to pre-set everything. Keep in mind that I want to lock the spreadsheet down, so the everyday user can't mess with things.

Originally, I was thinking of using FORMS for data entry. But when it came to viewing past data and/or printing, this would be a slight problem as it would want to print the entire spreadsheet (incl. empty spaces). Hence the reason why I put the AVG and TOTALS above the column headers...

Not sure if that makes sense...

Regards,
CHAStats