Hi,
I am trying to set up solver to determine the best possible tax distribution of income (variables) across multiple entities, based on their initial existing income, in order to minimize their gross tax payable (objective).
My main constraint is a cell which must equal zero. When the distribution has been allocated fully then this cell equals zero.
I have 4 separate types of tax which are calculated using UDFs: Medicare Levy, Low Income Tax Offset, HELP Debt Repayment, and Income Tax Payable. These are all summed to be Gross Tax Payable.
There are 2 IF functions in the data table. The first is the HELP debt repayment which will only calculate if the user indicates via drop down that the entity has a HELP debt. The other If function is to select if the entity is an individual or a company. If a company, all sub categories of tax are changed to nil except for income tax where the UDF is replaced by (*0.30) to get the company tax rate.
I have tried GRG Nonlinear which works reasonably well (if I run it exactly twice) but only if the vertical order of entities is a certain layout, being smallest initial income to largest (but even then I can;t be sure it is working 100% without further testing).
Evolutionary works very haphazardly when I set the variable constraints to be <= total distribution. It comes to a reasonable solution (sometimes, other times it is very wrong) but even the reasonable solution I can manually beat with very little thought. For instance after at least a minute of calculating it gets stuck on giving a company the entire distribution, despite there being a better solution (see image). I am racking my brain to think of other variable constraints to set bounds.
I would like to know if what I am attempting to achieve is possible with Excel solver, especially given the if functions and UDFs contained in the data table. If not I'll stop trying. If it is possible, what options, methods, constraints can I adjust in order to make the objective the absolute minimum with consistency and less mucking around.
Any assistance appreciated. I have attached some images to assist. Happy to provide more information if required.
I am trying to set up solver to determine the best possible tax distribution of income (variables) across multiple entities, based on their initial existing income, in order to minimize their gross tax payable (objective).
My main constraint is a cell which must equal zero. When the distribution has been allocated fully then this cell equals zero.
I have 4 separate types of tax which are calculated using UDFs: Medicare Levy, Low Income Tax Offset, HELP Debt Repayment, and Income Tax Payable. These are all summed to be Gross Tax Payable.
There are 2 IF functions in the data table. The first is the HELP debt repayment which will only calculate if the user indicates via drop down that the entity has a HELP debt. The other If function is to select if the entity is an individual or a company. If a company, all sub categories of tax are changed to nil except for income tax where the UDF is replaced by (*0.30) to get the company tax rate.
I have tried GRG Nonlinear which works reasonably well (if I run it exactly twice) but only if the vertical order of entities is a certain layout, being smallest initial income to largest (but even then I can;t be sure it is working 100% without further testing).
Evolutionary works very haphazardly when I set the variable constraints to be <= total distribution. It comes to a reasonable solution (sometimes, other times it is very wrong) but even the reasonable solution I can manually beat with very little thought. For instance after at least a minute of calculating it gets stuck on giving a company the entire distribution, despite there being a better solution (see image). I am racking my brain to think of other variable constraints to set bounds.
I would like to know if what I am attempting to achieve is possible with Excel solver, especially given the if functions and UDFs contained in the data table. If not I'll stop trying. If it is possible, what options, methods, constraints can I adjust in order to make the objective the absolute minimum with consistency and less mucking around.
Any assistance appreciated. I have attached some images to assist. Happy to provide more information if required.
