Solved

Lookup Calculations

Posted on 2016-09-02
4
39 Views
Last Modified: 2016-09-03
Hi, I am hoping somewhere can help with lookup calculations I am trying to put into my spreadsheet as it is beyond my Excel skill set.

The workbook is for the calculation of potential sales rep commissions. There is a base rate and 4 bonus rates. The base rate is only paid if the ASP (average sales price) is above the minimum $ value (column B). Then bonuses are paid on a scaled basis again relating back to the ASP.  It will be clear once the workbook is viewed but to clarify I will detail how the scaled bonuses should work.

Base commission, if minimum ASP is reached, is 2% and then bonus rates are:
Bonus 1: 12.5%
Bonus 2: 25.0%
Bonus 3: 37.5%
Bonus 4: 50.0%

If we take batteries as the example:
Min: $2.00
Rate 1: $2.20
Rate 2: $2.40
Rate 3: $2.60
Rate 4: $2.80

If the ASP is $2.71/kg then the bonus amount should be $0.15625/kg based on the below calculations:
Min: $2.00 * 2%: $0.04000
Rate 1: $0.20 * 12.5% = $0.02500
Rate 2: $0.20 * 25.0% = $0.05000
Rate 3: $0.11 * 37.5% = $0.04125

There are 2 highlighted areas I need help with in the attached workbook.
1. The calculator which is highlight in Cell B36
2. The income table which is highlighted in Cells J5:N12 (table is based on product selected in Cell I5)

Thanks to anyone who can help.

Troy
Sales-Commissions.xlsx
0
Comment
Question by:recycleaus
  • 2
  • 2
4 Comments
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41783224
Check if attached does what you expect.
The formula in B36 is awful long, so I have added a table below, to show how the values are calculated.
Perhaps you should use the result from that instead, because it will be much easier to maintain.
Sales-Commissions.xlsx
0
 

Author Comment

by:recycleaus
ID: 41783229
Ejgil. Thanks for that, looks like it working to me and yeh that formula in B36 looks huge as it is so it must have been massive.

Just one other thing, I need to move cell I5, which was the product selector, due to the text not fitting in but in the process I have killed the calculations. Would you please be able to correct this for me?

Thanks again
Sales-Commissions.xlsx
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 41783238
0
 

Author Closing Comment

by:recycleaus
ID: 41783242
Thank you
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now