Creating a commission calculation

[richie_tenenbaum]

A little background: all I know about excel is self taught so it is pretty basic. I manage a semi-small restoration business. I am looking for help at creating a formula for a new commission plan we are rolling out.

The basic:
The estimator and project manager are supposed to run jobs at 61% job cost. For this, they receive a 5% commission on the revenue generated for that job.

Those are the basics. I can do that calculation all day. However, we have added a wrinkle that complicates the math for me. If a job is ran over 61%, the following happens. Greater than 61-66 percent, the overage is split 50% company, 25% estimator, 25% PM. This amount is reduced from their 5% base. Every dollar spent over 66% is split between the PM and Estimator and subtracted from their base commission.

If the job is ran under budget between 56-61%, the underage is shared 30% to estimator, 30% project manager. This profit bonus is only shared when cost is 56% or more. If it is ran below 56%, the company keeps all of that up to 56%.

What I want do is only enter few inputs and have the formula crank out the commission for the PM and Estimator.

Input: estimator name
Input: project manager name
Input: sale revenue
Input: job cost

Any help is appreciated.

 

[Dave Patton]

We do not fully understand your challenge; we cannot see your spreadsheet.
Please post a few examples complete with expected results.