Goal Seek to find NEAREST answer

[STUARTXL]

Hi All,

With regards to the Goal Seek function, I am attempting running some simulations that the Goal Seek cannot generate an exact answer for (i.e. a value that generates the an exact goal amount).

However, I would be satisfied with a result that generates the nearest possible result to the goal. How can I ask Goal Seek to generate a result that gives the nearest result to the goal I am seeking?

Thanks in advance

[MrShorty]

Goal Seek probably will not be able to find an "approximate" result. I would probably do this in Solver, where I have a cell to calculate the difference (or difference squared) between your current target cell and a cell with the target value. Use this cell in Solver as the "set target cell" and have Solver find a minimum value for that cell. Without knowing more details about your model, it is difficult to know for sure if it will work. If Goal seek works to find an exact value, then this should be adaptable to find the closest value.

[STUARTXL]

Thanks MrShorty.

Attached my model. I will explain the problem I'm experiencing, but first, a bit of context.

I work in a country that has it's own currency, but in practice every person and company uses USD to execute daily transactions. However, when reporting to government bodies (VAT, payroll taxes etc.), all reporting must be stated in the local currency. All laws that are passed by the state (such as minimum wage) are also expressed in local currency.

The local currency is quite volatile however and so the company I work for (which I recently joined) has always executed employment contracts on a NET USD basis i.e. the company guarantees the employee the same amount of USD at the end of each month, even if there is a tax hike (why this has been allowed is another issue but not of concern here).

A new minimum wage is being introduced in the country and as expected, the minimum wage has been expressed in local currency terms. The way minimum wage works in this country though is that there are different minimum wages for each level of employee (an employee's level is determined by their position in the company, skill, responsibilities etc.). So I have been tasked with working back from the NET USD agreements to see what the respective employee's current minimum wage is, to then evaluate whether any employees are currently being paid less than minimum wage. This is harder than it sounds due to a number of factors, not least that the exchange rate moves every month i.e. the local currency equivalent of a net USD salary differs every month as exchange rates differ and since income tax is bracketed, this means an employee can fall into different income tax brackets month to month.


Anyone, that probably confused more than it helped. What I'm trying to do is this:
- column S - U show the agreed USD salaries for each employee, the current exchange rate and the local currency equivalent of the net USD salary
- columns D - P are all fixed amounts or fixed rates, so these do not need changing.
- I am trying to get the amount in column Q to match the amount in column S (or as close to it as possible, but in this case, the amount in column Q must be greater than the amount in column S) by performing the following Goal Seek (taking row 6 as an example):
- navigate to cell Q6 and go to Data > What-If Analysis > Goal Seek >
- Set cell: Q6
- Cell value: 610,880 (the value in S6)
- By changing: E6
This has worked for rows 4 and 5 but for row 6 it doesn't work. However, I've manually calculated that if I put the value in cell E1 into cell E6, then the net wage in cell Q6 is very close to the value in cell S6, whilst remaining above it.

[MrShorty]

It looks to me like it is just that goal seek is not a very robust algorithm. If I do the same thing using Solver instead of goal seek, E6 readily converges to -139312.30... which makes Q6 the same as S6. Solver's more robust algorithm may be all that is needed.

I would also note that, looking at the formulas, algebraically "inverting" the function in Q is likely straightforward. The ugliest part is understanding what is going on in column L (I did not take the time to really dig into it). The most reliable approach might be to spend some time with the algebra going from E to Q and derive a suitable sequence of formulas that will allow you to work backwards from S to E. I will leave that decision up to you.

[STUARTXL]

Working backwards from S to E was my first approach. Having spent a lot of time attempting this, using a function such as Goal Seek seemed like the easiest option. Key to understanding why I thought this to be the easiest approach, is the understanding the tax system (formula in column L).

Column L essentially performs the same function as the table on rows 37 - 45 of the 'Tax-table' worksheet on the attached, which allows me to calculate the tax liability for any gross salary amount. By virtue of the fact I have been able to construct this tax table, one would assume I would be able to invert the formula so as to perform the calculation backwards (from net to gross). But although working forwards was easy enough, working backwards is in fact much more difficult. You see, it is easy enough to calculate what tax 1 is (3.5% x Taxable Base) and tax 2 is ((Taxable Base - Tax 1)*relevant tax bracket rate), when you have the gross/base salary to begin with. And provided there are no complications in the payroll, it should be relatively straight forward to work backwards too i.e. taking the net salary and working out what tax has been levied (on the logic that it is impossible for two different gross salaries to be taxed according to the exact same rules-based approach and give the same net salary, hence there must be a unique gross salary for each net salary) and in fact I successfully performed this backwards calculation in a similar model...

However, what I failed to realise at the time I 'successfully' performed this backwards calculation is that applicable tax rates isn't just a case of which tax bracket the salary falls into, there are additional factors impacting on the rate of tax such as number of dependants and overall tax rate. This might not make much sense without an example, so allow me to provide one.

An employee with a local salary of 615,000 (please insert 615,000 into cell D37 of the 'Tax_table' worksheet) would normally pay 21,525 (tax 1 at 3.5%) and a further 120,616.75 (tax 2 - see workings in table on rows 47 - 59 on the 'Tax_table' worksheet - hopefully this table is easy enough to understand.

However, employees also get relief on tax 2 depending on the number of 'dependants' they have (dependants classed as wife and children). For each dependant, the employee obtains a 2% reduction on tax 2, but this is limited to a maximum of 9 dependants i.e. 18% relief.

To add further complication, if the value of an employee's salary (and in this respect, the salary is calculated as taxable base - tax 1) falls in brackets 7 - 9, then the tax reduction that they obtain is restricted - it is no longer calculated as a 2% reduction in their 'tax 2' liability per dependant; instead it is calculated as a 2% reduction on 297,298 for each dependant (still limited to 9 dependants). 297,298 represents the maximum tax that an individual can pay if their (taxable base - tax 1) falls into the seventh bracket.

Once all the above has been calculated, one must then determine whether the overall tax 2 liability (after all reliefs) is greater than 30% of the salary (again, salary in this respect means 'taxable base - tax 1') and if it is greater than 30%, then the tax 2 liability is then limited to 30% of the salary (taxable base - tax 1). If this is the case, no further consideration is given to dependants relief, tax 2 is simply set at 30%.

Now that that has been explained, you may be able to see why it is difficult to work backwards. By working backwards, we must start with a net salary, which will have been subject to tax 2. But tax 2 may or may not have been subject to restriction at 30%, it may or may not have had dependants relief applied etc. I am sure there is a way to calculate, but I get the very strong feeling that doing so will essentially involve performing a Goal Seek function through formula, since we have to first make a number of assumptions about what restrictions or reliefs tax 2 has been subject to, before we can work back to calculate the base salary and only once we calculate the base salary, will we be able to make a determination about our assumptions and, if they transpire to be wrong, we will have to perform another scenario.

Would welcome your advice on whether you think it is still possible to do this via formula.

[STUARTXL]

Before you pass comment on the earlier post (if you intended to), I wonder if you could address two, more important issues regarding Solver. This is the first time I have used Solver by the way.

1) With Solver, I am not managing to get the exact result in cell Q6.
- I am navigating to cell Q6 and entering Solver.
- Set objective field = $Q$6
- To = 'Value of' = 610,880
- By Changing Variable Cells = $E$6
- I am also making sure to uncheck the box next to 'Make Unconstrained Variables Non-Negative' (on my first attempt I didn't uncheck this and no result was generated).

The result being generated after I perform the above is -139,313.24, which generates a net salary of 610,879.29 (not 610,880 exactly as you said you were seeing).

First of all I'm concerned that we are getting different results, but no doubt this is because I am doing something slightly different than you.
Secondly, whether or not the net salary is slightly off isn't much of a concern, but as I said, it is important that the net salary in Q6 is equal to or higher than S6, if it is indeed going to be different. How would I dictate this to Solver?

2) Is it possible to get Solver to generate results for all rows at once?
...or do I have to calculate each row individually?

[Alf]

You solver setup will not work for setting Q6 to 610,880 by changing E6 because the formula in L6 has no reference to E6 as there is a "Ref!" error in the formula but the solver setup works fine for Q7 and Q8 so I would assume it should work for the rest of the values.

solver_atarg.jpg

As for getting solver to generate result for all rows at once it's not possible but running solver in a loop using a macro could that solves one row at a time is possible. If the target value for the Q values are as I think the corresponding S values there is also the possibility of adding a constraint (an upper and a lover limit).

So Q should be equal to or less than S + 1,00 and equal to or greater than S - 1,00 for instance.

I could set up a macro for you if my assumption Q value should be equal to corresponding S value.

Alf

[STUARTXL]

Thanks for pointing out that error. This has now been corrected in the attached.

There's still something wrong with the logic in the 'corrected' formula as it is generating an incorrect tax liability for those who individuals who have salaries that fall within the highest (tenth) bracket. However, this is something I can look at later.

What you are proposing would be great and thank you, but as I don't know how macros work, I don't know if it would be of any use in that the sheets I've attached are very condensed versions of the primary spreadsheet I'm using. For example, I've eliminated a lot of columns that you don't see on the sheets in this post e.g. cost-to-company columns etc. I fear that I will not be able to simply copy and paste the macro into my real spreadsheet. Likewise, if you build the macro on this sheet and I were to use your sheet as my primary sheet going forward, I will need to add all this extra information back in and I don't know if your macro will function properly if I begin adding new columns and/or rows (I will need to add about another 300 rows for all employees so the loop will have to be built for this).

Kindly advise.

[Alf]

So I wrote a macro to run solver in a loop. Macro looks like this

Please Login or Register  to view this content.

Before you can run the macro you need to set a reference to solver in visual basic. Click on the "Visual Basic" icon ("Developers tab"), then click "Tools" and then click "References.

In the new window that opens find "Solver" and tick the box in front of it. Now you can run macro "SolverLoop" (Developres tab). Click "Macro" icon and then click "Run".

The macro loops through values from 7 to 94 (set by the Range("B" & Rows.Count).End(xlUp).Row) so that the range is set dynamically. Also found the solution for setting "Unconstrained variables to negative"

The uploaded file contains the macro but you probably need to set a reference in VBA to solver.

If there is an error in the formula in the L column there well normally not be a value set by solver in the corresponding row in the E column, still I found one exception in the uploaded file check E75 and L75 and Q75.

Have also uploaded the file with the new modified macro.

Alf