Commission Calculator

Jan 22, 2008

Attached is sample data. Target payout is the amount of the base salary a salesman can earn if all goals are reached. Commissions are payed quarterly. Listed on the left are 7 goals, each with a weight of importance toward the target payout amount. The percentage achieved can only be 100% or 0%, nowhere in the middle, although they are allowed to make up a goal later if they do not make it at first. This is where I get stumped. For example, if they don't make a goal in quarter one but make the goal in quarter two plus what they missed in one, they get two quarters worth of commission in quarter 2 for that goal. I am having trouble figuring out the easiest way to go about this.

ADVERTISEMENT

Commission Formula

Sep 15, 2009

I am trying to write a formula for my commissions spreadsheet, which calculates commission clawbacks based on a sliding scale. From my understanding I need a code that will calculate additions or deductions based on a range of probabilities.

For example, if I have a percentage figure that is below 8%, I would like to add 15% to the total commission earned.

Here are the ranges below:

8% or under+15%
8-13%+ 10%
13-17%0
17-22%-10%
22% or more-15%

If I say my % is in K5 and the monetary value is in I5, what formula would I type into L5 to calculate the amendment?

View 14 Replies View Related

Calculating Commission

Mar 24, 2007

Sub setcommission()
Sheet3.Select

Dim cellsNum As Integer
Dim commission As Single, rating As String
Dim sales As Single
Range("b2").Select

cellsNum = ActiveCell. CurrentRegion.Row.Count

For i = 1 To cellsNum
If ActiveCell.Value > 150000 Then
commission = ActiveCell.Value * 0.012
rating = "superior"
Else sales > 100000 And sales <150000 Then
commission = ActiveCell.Value * 0.08
rating = "satisfactory"
Else

I am trying to calculate the commission for the sales for sales>150000 a commision charge of 0.012 and rating of superior. for 150000> sales >100000 a commision charge of 0.08 and rating of satisfactory. for other sales a commision charge of 0.04 and rating of unsuperior.

i am finding a few problems with the code 1st a problem with 'cellsNum = ActiveCell.CurrentRegion.Row.Count' it says complie error: invalid qualifier 2nd a problem with 'Else sales > 100000 and sales <150000 then'

View 7 Replies View Related

IF / OR Formula For Calculating Commission

May 6, 2014

I am trying to create a formula that calculates multiple commissions based on profit margin. So here is what I'm looking to. If the profit margin is between 50 and 70% than there is an additional 2% commission, if it's between 70.01-100% profit margin, than it's an additional 5% here is the equation I have=IF(OR(E2>50,E2<70),D2*2%,(IF(OR(E2>70.01,E2<100),D2*5%)))but it's still calculating at the 2% even thought it's an 86% margin.

View 4 Replies View Related

Multiple Commission Calculations?

Feb 12, 2014

I want to pay different commission rates for different levels of sales...

IE nothing if sales are under 250,000 per year.
5% between 250,000 and 500,000,
8 % between 500,000 and 750,000
10% between 750,000 and 1,000,000
12.5 % between 1,000,000 and 1,250,000
and 15 % over 1,250,000

The issue that im having trouble with is that if the sales guy brings in 1,500,000 in yeary sales he would be paid some at 0%, some at 5% some at 8% some at 10% , so at 12.5 and some at 15%

How do i calculate that? I have included a excel spreadsheet.

View 6 Replies View Related

Creating Commission Calculation?

May 15, 2014

I'm having trouble creating a commission calculation.

The commission schedule is paid on a tiered rate (below), and occasionally there will be recoveries due to client non-payment, which comes out of commission. There is also a budget variance every month. I'd like to have the Monthly Budget in cell A1, the Revenue in cell A2, Recoveries in cell A3 and the Commission (formula) in cell A4. I only need the formula for cell A4, everything else will be entered manually.

Revenue Schedule
$0-$14,000 = 0%
$14,001 - $18,000 = 7.5%
$18,001 - $BUDGET = 10%
$102% BUDGET - Forever = 15%

Note that once you hit 18K, the 14-18K does not jump up to 10%, it stays at the 7.5%.

View 7 Replies View Related

Sales Commission Calculation

Jan 4, 2010

I need to know what formulas to put into the cells in excel to make the following sales compensation example compute properly:

Time
Period Draw
Paid Actual
Commissions Owed to
Company Commission
Paid Total
Earnings Month 1$3,000$4,000-$1,000$4,000Month 2 $3,000$2,000$1,000-$3,000Month 3 $3,000$5,000($1,000)$1,000$4,000TOTALS$9,000$11,000-$2,000$11,000

View 9 Replies View Related

Commission Formula Not Complete

Sep 21, 2006

Looking for a formula for a zero based commission structure. I am having trouble with the formula. I have attached a breakout of what I need and an explanation of the end goal.

View 2 Replies View Related

Computing Commission Formula

May 11, 2007

determining a formula to compute a sales commission.

Here is a sales scenario.

A $25 commission will be paid on sales between $50 to $150.
A $50 commission will be paid on sales between $151 to $300
A $75 commission will be paid on sales between $301 to $600

The sales person will enter the sale amount into column B. Column C should compute the total commission for multilple sales.

Example:

Column A Column B
Sales Commission
$50 $150 (which is the comm. for the combined sales)
$175
$360

View 6 Replies View Related

Calculate Conditional Commission

Aug 25, 2007

The following is assumed.

I have a base salary of $5k/monthMy PROFIT on hardware sales must equal or exceed my monthly salary in order for me to earn a commission on hardware sales. If it does, my commission is then Profit beyond monthly salary X 25%I also earn commission on software sales. Total Commission is the sum of Software commission plus commission on hardware sales.

Example: HW Profit is $6k. Software commission is $3k.

$6k - $5k = $1k.
$1k * 25% = $250.
$250 + $3k = $3250

However, there are times (unfortunately) when my total hardware profit does not equal or exceed my monthly salary. When that happens, I am now in a negative status regarding hardware commission. The negative status is simply salary minus hardware profit.

Total commission is then that negative number plus software commission.

Example: HW Profit is $3k. Software commission is $3k.

$5k - $3k = $-2k.
-$2k + $3k = $1k

I need a formula in one cell to account for both possibilities.

View 9 Replies View Related

Calculate Bracket Commission

Sep 5, 2007

I have been thinking about this for a few days and have no idea where to start.
The commission scheme pays like this:

upto $40,000 in sales pays 30%
$40,001 to $80,000 pays 40%
$80,001 + pays 50%

Also, the sales person will only earn commission once they have invoiced 1/3 of their basic salary. Example

$60,000 must invoice $20,000 per month, therefore commission is actually 30% of the remaining $20,000.

I want to create a spreadsheet that allows me to enter the basic salary for individual sales persons and their individual sales figures to calculate their gross commission and also their gross basic salary if I can.

View 3 Replies View Related

Formula / Function For Commission Calculation?

Jul 26, 2006

if i could get a hand creating a commission calculation.. here is what i'm looking for and my brain hurts trying to make it... I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%.

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

View 9 Replies View Related

How To Calculate Total Commission In Excel

Apr 1, 2014

How do you calculate the total commission of a particular person on a consecutive months ?

Attached is the excel file for reference : Total Commsn.xls‎

View 1 Replies View Related

Real Estate Commission Schedule

Dec 3, 2009

I receive a certain percentage of my broker's commission based on what type of house sale occurs. When one of my listings sell I receive the commission in A2:A7. When I sell a house to Company A I receive the commisions from B2:B7, company B C2:C7, and company C D2:D7.

My own personal commission percentages increase based on the income schedule E2:F7. For example, once I have earned $8137, my percentages for sales all jump to Row 3.

I have set up a chart below the commission schedule for each individual sale to calculate the commission for each type of sale. Each "x" represent a sale for each category (LISTING, COMP A, COMP B, COMP C). The broker's commission is always 3.5% of the total sales price. My commission will be a certain percentage of the broker's commission based on the scale above.

View 3 Replies View Related

Multiple Variables In A Commission Structure

Jan 20, 2006

I'm trying to create a worksheet to calculate ourcommsiion structure, but can't figure out a way to attack it. We have manyvariables (5) in our commission structure based on each order.

Here's how I set it up so far:

(In Cloumns)
A= Order Amount
B= "Y" is A-15%; "N"=A
C= "Y" is B*20%; "N" is B*10%
D= "Y" is B+2%; "N" is B
E= "Y" is B+2%; "N" is B
F="Y" is B+1%; "N" is B
G= SUM(A:F)

For example, if the order is $1000, and I answer y,y,y,y,y=$212.5

How do I create the formulas so I can just put in the order amount and the
appropriate letter to get the correct commission structure?

View 9 Replies View Related

Sales Commission Formula Required

Jan 14, 2009

I have a new sale structure to put in place the commission is paid in the following way:

below 1500 zero commission
between 1501 and 3000, commission at 16%
between 3001 and 8000, commission at 23%
above 8001, commission paid at 30%
Ergo if you generate 5000 you would be paid 700 ie nothing for the first 1500, 16% of the second 1500 and 23% of the remaining 2000. ( I hope my maths is correct! )


I have tried to manipulate other solutions using sumproduct but my knowledge is poor, the formula I have tried manipulating is =SUMPRODUCT( (A2 > {0,1500,3000,8000}) * (A2 - {0,1500,3000,8000}) * {0,0.16,0.23,0.3}). I prefer single line formula rather than lookups as staff will not be able to see commission rates easily.

View 4 Replies View Related

Variable Commission Rate Calculations?

Mar 29, 2012

I need a way to calculate commission for my sales reps. The commission is based off a calculated value in H39 of a sheet that I have developed.

They get 7.5% of the value when between $1 - $100,000, example: sales of $50,000 = $3,750 in commission.

If the value in H39 is > $100,000 but $250,000 but $500,000 they get 7.5% on the first $100,000 plus 5% on the amount between $100,001 and $250,000 plus 3% on the remainder between $250,001 and $500,000 plus 2% on any amount >$500,000 example: sales of $800,000 = $7,500 (7.5% on first $100,00) plus $7,500 (5% on the next $150,000) plus $7,500 (3% on the next $250,000) plus $6,000 (2% on the sales >$500,000 for a total of $28,500.

View 5 Replies View Related

Weekly Average Commission Calculation

May 5, 2009

I hope this question has not been addressed but the closest I can find is in this link:
[url]

I am now a commission based contractor who started earlier this year & I want to be able to calculate my current average weekly income which should fluctuate greatly. I have a spreadsheet that works out what my current to date net income is but can't figure out how to break this down to a weekly avarage. My basic guess is that I want to take the figure provided and divide it by the number of weeks from "stated start date" to the current date (but on a divided by 7 basis?) to get my average weekly net income.

I am sure this is pretty simple for you all so I hope I am not wasting time it's just that if I try figure it out I am using something like WEEKNUM & that will fail after the next new year. -Although it would be better to base it all around the financial year if that can be done? (March 1st - April 31st in NZ)

View 10 Replies View Related

Rewrite Commission Spreadsheet With A New Structure

Jan 20, 2010

I'm trying to rewrite our commission spreadsheet with a new structure and as my excel knowledge is limited, I'm not really getting anywhere.

I'm looking for a few lines of formula however I'll just post one at a time otherwise I'm in danger of scaring you all off!

Firstly, I am trying to work out the formula for the following:

If the value in C7 is up to 14,999 = 1.5% of the whole value is given, if it's over 15,000 = 3.5% of whole value is given. I would like the total amount of commission to show in F7.

View 9 Replies View Related

Formula/Function For Commission Calculation

Jul 26, 2006

I put in excel an employees gross fees for a month,, their commission calculation is based on the following scheudule, for which i'd love an easy calculation, function, code etc. for..

$0 - $10,000 - 60% commission
$10,001 - $15,000 - 65% commission
$15,001+ - 70% commission..

i'm sure this seems simple, but i just can't get it because if for instance their first gross fee is $12,000, i don't know how to have it calculate the first $10,000 at 60% and the last $2,000 at 65%. any help is greatly appreciated..

ps.. my excel sheet is set up as follows:
Rows a-g (stuff that is irrelivant)
row h, gross fees
row i, commission (in dollars)

View 9 Replies View Related

Step Based Commission Formula

Sep 7, 2006

I am having trouble with the code for this stepped scale commission structure.

Net Service
$1,1400

Step Scale Comm. %
$0-500 40%
$500-750 45%
$750-1000 47%
$1000-9999.99 50%

Final Commission Paid $ ?

View 7 Replies View Related

Setting Up A Vlookup Table For Sales Commission

Mar 12, 2014

I'm trying to set up a Vlookup table for commission rates but all my answers are 0. What I'm trying to do is set up Vlookup to return total sales less than 700,000 have 0% commission, 700,000 to 749999 10%, 750,000-799,999 11%, etc. . The commission rate increases by 1% for each additional 50,000 in sales, with the highest commission rate 20%.

View 6 Replies View Related

Nested IF Formula: Calculate The Commission For My Employees

Feb 22, 2007

I am trying to write a command to calculate the commission for my employees. There commission is based on the spread between sale price and cost. For example:

If Profit is between $1.00 and $2.00 - commission = 15%
If Profit is between $2.01 and $4.00 - commission = 20%
If Profit is between $4.01 and $6.00 - commission = 25%
If Profit is > than $6.00 then - commission = 30%

I am able to calculate the first level ex: =IF((C3-B3)<=2,"15%") It Displays the 15% in the formatted cell. (C3-B3 is the profit spread). How can I include the other 3 commission levels in the formula to display the correct commission % based on profit spread?

View 5 Replies View Related

Formula For Range To Calculate Commission Paid In Month

Feb 26, 2014

I have attached a spreadsheet which shows my desired result which often works.

Range C2:C13 is my desired result...

The problem /criteria is...

For every 140 meters sold the sales person receives £140 commission.

Meters (range B2:B13) can be carried to future months.

I am looking for a formula for range C2:C13 which calulates if commission should be paid in this month or not and if so, how much.

View 5 Replies View Related

Find The Commission Rate Per Worker Using Lookup Functions

Dec 24, 2005

I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc.

Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions??

View 12 Replies View Related

Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )

Feb 27, 2009

I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.

Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.

The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.

View 2 Replies View Related

Lookup Formula: Commission Calculation To Be Done Automatically Once Data Is Inputted In Cell

Mar 25, 2009

I am trying to come up with a formula that will allow the commission calculation to be done automatically once data is inputted in cell A2 and E2. I have tried IF statements, but can not figure out how to make it work. I am not able to figure out how to get cells F9 and F19 to work with the proper formula.

View 4 Replies View Related

Little Calculator Wanted

Jan 10, 2007

I've been trying but excel does not seem to recognise x and y

so here it is

2* X = Z

Where 2 is changable
X = Z-2

Now i just want to find what Z is.

Here's an Example
1.3x - z
1.3(x-.06) = z
1.3z - .078 = z
1.3z = z + .78
1.3z-z = .78
.3z = .78
z = .78/.3
z = 2.6

View 14 Replies View Related

Birthday Calculator

Jan 26, 2009

I am hopeless at remembering birthdays tbh - so rather than rely on family to remind me, I decided to make a spreadsheet that shows: D.O.B, current age (in years, months, days), and number of days remaining until next birthday.

Please see attached - I can't figure out why the current age calculation is a month out. e.g 'Sebastian' was born on 16 Nov 2008, which makes him 3 months and 11 days old - but '=TODAY()-C16' yields "00 Years 4 Month(s) 12 Days"

Also, 'Leah' has just her birthday - but now where it is supposed to give 'days until next birthday' it gives an error with the formula: '=DATEDIF(TODAY(),EDATE(C4,(YEAR(NOW())-YEAR(C4))*12),"d")'

View 6 Replies View Related

Freight Calculator

Nov 16, 2009

I'm building a freight calculator and am considering some professional consulting options, but before I do that I wanted to see if I could overcome this one problem. If I can, I think I might be able to complete the calculator myself.

Here's my conundrum:

A potential customer enters "80802" for zip code and "Solomon" for store.

StoreLocation_________ City_______ State_____ Zip______ Distance
Solomon__________ Arapahoe _______CO ____ 80802_____ 270
Garden City_______ Arapahoe_______ CO _____80802_____ 143

The formula (or series of formulas) I'm looking for would then refer to the following hidden sheet and return Arapahoe, CO and a distance of 270 miles from Solomon.

View 14 Replies View Related

Making A Calculator From Vb.net To Vba

Feb 11, 2008

i made a claculator in vb.net, and now i have to make one in vba, what i did was as follows, first ill post the code from last year, then my vba attempt;

View 14 Replies View Related

Progress Tax Calculator

Jun 19, 2006

I'm trying to come up with an efficient formulae or function to calculate tax

The problem I have is that the tax is progressive. As below

the first 20,000 is taxed at 5%
the next 20,000 is taxed at 6%
the next 20,000 is taxed at 7%
the next 20,000 is taxed at 8%
the next 20,000 is taxed at 9%
more than 100,000 is taxed at 10%

I'm trying to do a formula like below

Cell B3 is my taxable amount
Cell B5 =IF(B$3>20000,20000*0.05,B$3*0.05)
Cell B6 =IF(B$3>40000,40000*0.05,(B$3-20000)*0.05)

This gives me a problem in that for 35k say, I end up with a negative number for the second part in cell B6. My other issue is that each calculation will take up 6 rows on my
spreadsheet. I was hoping to set up a function that could do this in a cell, but even the simple stage defeats me at the moment.

View 12 Replies View Related

Timesheet Calculator

Dec 18, 2008

See workbook attached.

I'm looking for help to detemine rates so it automates in the sheet.

Can you give me assistance and code perhaps ? I'm pretty basic at V-Lookup and If functions. Is this the best route to take ?

All is explained within the workbook.

View 10 Replies View Related

Best Way To Implement Calculator?

Feb 20, 2014

Creating a calculator for employees and possibly customers to use to return values based on information inputted. Created a worksheet but thought there may be another way to enter and return the calculated information that may be more user friendly. New to user forms but thought that would work for the user to enter information. Can information be returned from the worksheet to the user form?

View 2 Replies View Related

Get Rid Of The Calculator Toolbar

Nov 25, 2006

I downloaded the calculator toolbar, and for some reason it doesn't look right, it doesn't resemble a calculator layout at all.

The numbers are skewed, as though the layout is in landscape view instead of portrate. I've tried repeately to delete it from the toolbars menu option, with no success. It's become annoying.

View 9 Replies View Related

12 Digit Calculator

Feb 20, 2007

Is there a way to build a 12 digit calculator in excel?

View 9 Replies View Related

Absence Calculator

Jan 30, 2008

im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07).

I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work.

Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence).

How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months).

I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.

View 9 Replies View Related

Age Based Calculator

Jun 7, 2008

I am trying to put together a spreadsheet that works out values based on a persons age or service length.

So in I have the following data in columns starting at B4 and ending at k4:

Date of birth; start date; text; text; text; text; Weekly Pay; Start age; Current Age; Years Service.

The formula I need to provide in l4, m4 and n4 comes from the following rules:

* 0.5 week's pay for each full year of service where age during year less than 22
* 1.0 week's pay for each full year of service where age during year is 22 or above, but less than 41
* 1.5 weeks' pay for each full year of service where age during year is 41+

So in l4 I have:

=IF(J4

View 9 Replies View Related

Billing Calculator

Aug 12, 2008

I'm working on a 4-week billing calculator. I rent equipment on a day,week & month rate system. For example: Equipment 1 rents at $30 Day, $90 Week, & $270 a month. If you keep Equipment 1 for 4 days the calculator computes 4 days at a total of $120. The way a 4-week cycle works is the customer will receive the cheaper rate once the daily rate meets or exceeds the weekly rate.

So instead of $120 for 4 days, the calculator tells me to bill him for 1 week at $90 instead and the customer essentially will get the following 3 days at no extra charge until the cycle starts over. As the cycle continues, the same rules apply for the monthly rate in relation to the weekly rate & daily rate combined.

Once the weekly + daily rates add up to equal or more than the monthly, then the monthly rate is used and that's what the customer pays. What I'm trying to do is make a calculator that I input the rates and the rental period and the spread sheet will tell me (based on those rules) how many days, weeks and/or months the customer needs to pay and how much his total dollar amount will be.

View 9 Replies View Related

Golf Calculator

Feb 6, 2009

Not sure if this is do-able but I figured I would try. For a golf league coming up later this year I want to figure out how many Birdies, Pars, Bogies, Dbl. Bogies and Others each (20) golfers have during the season. After each round I would input their scores and I am looking for a program that would look at the score of the hole and the par for the hole and figure out what they got 1 under par = birdie, even par = par, 1 over = bogie, 2 over = dbl bogie and 3 over = other.

I started by creating a simple if statement but it ran out too long and my other issue is adding up the number of birdies, pars..etc for each round. Meaning a golfer can have bogies on hole #1 and #2 and the if statement can take care of that but how would get a total saying the golfer had 2 bogies.

Something like this with the - meaning a column.

birdies Pars bogies dbl bogies others total holes
4 - 5 - 3 - 4 - 4 - 5 - 6 - 5 - 8 1 4 2 1 1 9

I would do this each week and total the number of each to keep a running total at the bottom of each column. We play on the same course each week so the pars for each hole can be hard coded.

View 9 Replies View Related

Date Calculator

Oct 22, 2009

I'm building a calculator of sorts for dates. I have a start date in A1. Next to the start date I have a list:

10 days from -start date- is:
15 days from -start date- is:
20 days from -start date- is:
and so on...

I need a formula to return a date that is however many days specified from the start date. If that date lands on a sat or sun I need it to return me the date for the monday after. For example, if my start date is 10/15/09 (thursday) and 10 days from that is 10/25/09 (sunday) I need it to return the date of 10/26/09 (monday). I also need it exclude a range of holidays i have listed.

View 9 Replies View Related

ADVERTISEMENT