Solved

Lookup Calculations

Posted on 2016-09-02
4
43 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

867 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

24 Experts available now in Live!

Get 1:1 Help Now