I'm usually quite good at excel until this: Commission calculation nightmare

**alhollin44** · 12-30-2013, 09:19 PM

I have recently started working as an independent contractor doing sales and I thought I had a really nice Excel formula going for me until I realized it had a fatal flaw. Essentially, I am able to give price breaks but if I do I get a different commission than if I sold it at full price. Also, once I hit 30k in total sales for the month the commission changes. Here is my commission scale break down:

Under 30k in total sales:
0% discount: .05
5% discount: .05
10% discount: .045
15% discount: .04

Over 30k in total sales:
0% discount: .08
5% discount: .075
10% discount: .07
15% discount: .06

I had this amazing table (or so I thought) set up but then I realized that I had created the entire table on adjusting to the 30k+ commission scale once each column hit 30k instead of when total sales hit 30k! Major flaw! I somehow need the Excel sheet to understand to use the correct commission formulas based on total sales while still taking into account the different discounts. I am going to attach what I have right now.
Amanda Sales winter.xlsx

**6StringJazzer** · 12-30-2013, 11:10 PM

I am looking at dec13, which is what is open when I open the file.

I had to make one very fundamental assumption. From your calculations, it appears that your commission scale is marginal. That is, if you sell over $30K, your commission on the first $30K is still the lower rates, and the higher commissions only apply to the amount that exceeds $30K. Am I reading that right? If that is the case, I have determined which sales fall into which bucket by sales increasing over time. If a particular sale puts you over the $30K mark that month, I have included that entire sale in the lower $30K commission level, rather than splitting the sale amount to apply two different commission rates. This is more favorable for your employer than you. If it needs to be different than that, please describe how commissions are calculated.

I have made a lot of changes in your calculations and how they are organized:

I have added rows with the commission percentages (J6:M6, J9:M9), because it makes them more visible rather than embedding them in the formulas.
I have a cell with the 30K figure as a number (I5), rather than a text description.

That gives you much more flexibility if any of these numbers ever change.

I also added a "helper cell" (Q5) that identifies the row where your total sales cross the 30K amount. This is used in several formulas (J5:M5, J8:M8) so is cleaner if we do it in one cell, rather than repeating that function in all the formulas.

As a bit of an Excel lesson, please note that your formula (originally found in cells H4:K4 but no longer used)

Formula:

Please Login or Register  to view this content.

is arithmetically equivalent to

Formula:

Please Login or Register  to view this content.

which is logically equivalent to the much simpler

Formula:

Please Login or Register  to view this content.

My version is no longer using that formula, but I thought it might be helpful to point it out.

**millz** · 12-30-2013, 11:39 PM

Another method, but involves VBA. This is assuming that your inputs (down the rows) are not in chronological order.
Calculates at which point a new entry would cause total sales exceed 30K, and splits that exceeded amount into another column. Also did not extensively test it, may have flaws.

Edit: Playing with it a little more and found a flaw, would not work properly if there's an incredibly large sale (>30k). It was checking cell L4 for the sum but L4 has a cap. Added another formula in L3 for non-capped total sales.

**alhollin44** · 12-30-2013, 11:49 PM

You are correct in your assumption and this new table/formulas is/are beautiful. I really appreciate your response time and assistance. You are a gentleman and a
scholar.

**alhollin44** · 12-30-2013, 11:52 PM

Clearly my Excel skills are surface at best! This is great. Again, thank you.

**6StringJazzer** · 12-31-2013, 09:27 AM

Only too glad to help!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved: Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

**alhollin44** · 01-28-2014, 09:12 PM

Hi again,

I am not sure if I screwed something up somehow but the table doesn't seem to be calculating correctly once the commission hits 30k. Can you take a look (Amanda Sales Winter 1)

**6StringJazzer** · 01-28-2014, 09:55 PM

The data in your first attachment "Amanda Sales winter (1).xlsx‎" only has sales up to 27,061.15. Do you have a sample file with sales over 30K where calculations are incorrect? And if so, please be very specific about what is incorrect and what you think the correct answer should be.

**alhollin44** · 01-29-2014, 05:59 PM

Hi,

Sorry, its in that document but on the Jan 14 tab. Thank you so much again

**6StringJazzer** · 01-29-2014, 09:13 PM

I'm sorry, I originally overlooked that you had multiple sheets. I used a named range to find the row where revenues go over 30K, and another one to show the value of 30K as the breaking point, but of course that's different for every sheet. I have modified your workbook to fix this, using a range reference instead of a named range. I have fixed this in all sheets.

**alhollin44** · 01-29-2014, 10:04 PM

You are amazing. Thank you so much again.

**alhollin44** · 03-03-2014, 03:38 PM

Hi again :/ I don't know whats going on but from March going forward it is giving me N/A in all the columns. I compared it to the other previous months and everything looks the same??

**6StringJazzer** · 03-03-2014, 04:16 PM

Ah, but they're not the same.

If you look in tab "feb 14" you will see that in column F there is a column of formulas labeled "Total to Date". This column has to be replicated in every sheet, but is missing in March and going forward.

I have used a little trick to copy and paste it into all sheets in one action, so this version is fixed.

**alhollin44** · 03-03-2014, 04:20 PM

wonderful as always. Hopefully this will be my last time bothering you!

**6StringJazzer** · 03-03-2014, 07:34 PM

No bother!

I'm usually quite good at excel until this: Commission calculation nightmare

LinkBack

Thread Tools

Rate This Thread

Display

I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Re: I'm usually quite good at excel until this: Commission calculation nightmare

Thread Information

Users Browsing this Thread

Similar Threads

Commission Calculation

Commission calculation

Commission Calculation Help

Commission Calculation

[SOLVED] Commission Calculation

Bookmarks

Bookmarks

Posting Permissions