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


ADVERTISEMENT

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

Rewrite Sheet With Some Saved Values

Aug 11, 2006

I previously had a query resolved in this forum when I was trying to write data to multiple sheets but the requirements have now changed and I need to update the script.

I have a multi-sheet workbook that calls a routine to rewrite sheet4 based on values in sheet1 each time sheet4 becomes the active sheet. This script makes single line entries or double line entries in sheet4 depending on whether data appears in one or both of a two specific data cells (col:J,K) in sheet1. However I now need to manually input extra data items into blank cells (col:B,C,J) in sheet4 and retain those data items after input, but each time I make sheet4 the active one it clears the contents of all the cells including the data that I have entered into cols B,C,J. Is there a way of fixing cols B,C,J on sheet4 so that they are not cleared when sheet4 is activated. Or is there a way of writing just the the additional lines added to sheet1 since the sheet4 was previously activated rather than clearing all the data lines written previously into sheet4 and rewriting them all from scratch each time the sheet4 becomes the active one.

I think you will need to see the workbook that I am working on to see how the sheets link and a zipped blank version is attached.

View 8 Replies View Related

Rewrite Protect Code So That It Will Work With A Hidden Sheet.

Aug 25, 2009

I need this code below to work in a macro on a hidden sheet.. FYI the sheet name is "Closed".

View 2 Replies View Related

Rewrite Line Of Code To Prevent Changing Number Format

Aug 5, 2007

The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:

Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.

Original Code: Unedited.

For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If

Next Item
Next i

View 9 Replies View Related

Browse A Text File With 10 Textual Lines And Rewrite The Contents In The Same File

Feb 5, 2010

i want to browse a text file with 10 textual lines and rewrite the contents in the same file (starting from line number 11) from bottom to top.

View 9 Replies View Related

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

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.

View 2 Replies View Related

Dictionary Object Structure

Oct 31, 2009

I know the Dictionary Object is confined to the Microsoft Word Object structure though can it be used in Excel. If not, is there a suitable replacement.

View 3 Replies View Related

Folder Structure By Shell

Nov 18, 2009

I want to find the folder structure, but walking down. I have try this code

View 2 Replies View Related

Tiered Bonus Structure

Jun 18, 2009

I need to present dollar values to my boss for a proposed tiered bonus structure and am trying to figure out how to create the necessary formula.

The bonus structure is as follows:

Level 1$8,500 = 3% of fees up to $8,500
Level 2$10,500 = 5% of fees between $8,501 and $10,500
Level 3$15,000 = 7% of fees between $10,500 and $15,000

Tricky part is bonus amounts do not go back to dollar zero. The percentages are only for the stated range.

EX: Level 1 = $255, Level 2 = 99.95, Level 3 = 314.93
Therefore collecting $15,000 would net $669.88

View 3 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

Set A Password Under The Permissions Tab But All It Does Is Protect The Structure?

Dec 3, 2012

I have set a password under the permissions tab but all it does is protect the structure and not the content. I want all users to be able to view the file, but have to have the password to edit any of the sheets or data on the sheets. I can still protect the individual sheets, but that doesnt quite do what I want.

View 1 Replies View Related

Group / Ungroup - Tree Structure

Mar 6, 2014

I am trying to create a sheet to resemble tree structure of rows. That is by default all the parent rows must be visible and click of + sign, the row should expand to show its child rows which in turn can have child rows but appear collapsed. When I choose two rows and click on Group and select rows, I see a grouping but when I try to collapse using - sign, both 2 rows gets disabled. I want to make the first row as parent and second row as child. How do I do it?

View 1 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

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

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







Copyrights 2005-15 www.BigResource.com, All rights reserved