Hi !
I'd be super glad if someone could assist me with the following Solver task. In the table attached in the 1st column there is contract value, in the 2nd - present commission, and in the 3rd we have an equasion that expresses new commission that will soon replace the present one. Next to the data there is a table with coefficients for the equasion.
What's wring with the present commissions? They're threshold commissions which means that they are, let's say, unjust, eg. the commission is 10 if contract value=200 but if the value increases by 1, to 201, the commission jumps to 20 (sic).
Now, the task is to find optimal coefficients. Objectives? The most important one is this:
- the total of new commissions must not be less than that total of present commissions (the earnings must remain the same).
I tried to do it like that:
- goal/aim cell is the total of new commissions
- the target should equal the abovementioned total (i typed it in)
- the changed cells are my 3 coefficients
- I was adding constraints but it didn't change a thing
When I tried to solve it, the Solver said "no no, I cannot do it for you, pal". But I have a feeling that essentially, I'm not looking at it from the right angle.
I'm attaching a clean file with pure data in it.
Could you give me a hint?
Bookmarks