I have a simple optimization problem and would like to solve it using Microsoft Solver. However, the solver does not do what it is supposed to.
Briefly to the background: In the problem the gross salary for the year 2021, 2022 and 2023 is given. Income-related expenses in the amount of 17,000.00 € are to be distributed in such a way that as much of the tax as possible is reimbursed. That is, the payments must be distributed over the year so that the amount that can be reimbursed via the tax is as high as possible.
For this I have now assembled several lines.
- salary before: Is the sum of the gross salary in the respective year.
- taxes before: The income tax to be paid on the salary according to the formula given (in the cell).
- payments: The sum of payments to be paid per year (total 17000 after 3 years).
- salary after: is the amount of the row salary before minus the amount of payment.
- taxes after: Are the income taxes that are due on the salary from line 4 (again, according to the formula of the cell).
- taxes saved: The difference between what I paid in taxes before (line 2) and what I would have to pay after deducting the income-related expenses (line 5). The sum of this is on the right.
The sum from the line Taxes saved should now be maximized with the solver. This is my target variable (F11). My variables are the cells B6:D6. The constraint is that the sum of the payments (F6) must always be 17.000,00 €.
The solver also calculates, but the result is wrong. It simply divides the payments evenly over all 3 years, one third each.
My question is, why does it do that and how can I get the solver to maximize the sum of the saved taxes?
Here is the excel: https://drive.google.com/file/d/188mPFDB1f3OiQTlTBvbIsAUDTRgiflma/view?usp=sharing.
Many greetings and thanks