Solver Simplex LP. Negative Profit Tax Problem

Dear Community,

I've already examined many topics on this forum and still unable to figure out a solution, although it is probably very simple.



Brief intro
There are several Locations: Location_1..Location_n.

Each Location has a certain amount of Revenue. Also, each Location has its local Profit Tax Rate (given in percent). So, in Location_1 there is ProfitTax%_1...Location_n has ProfitTax%_n.

Locations can trade each other, so that one Location is a seller and the other one is a buyer. There are many rules and constraints here, so I omit this part as is does not appear important.

What we need is that after Locations distribute their revenues between each other, the remaining amount in each location will be called EarningsBeforeTax: EBT_1..EBT_n.

Solver Goal
My goal is to distribute revenues between locations is a way to maximize NetPnL=SUM(EBT_n - EBT_n*ProfitTax%_n).

I use SimplexLP method to distribute revenues and get EBTs.

I have built the Solver problem, and it works fine, except for one issue.

Issue
It can happen that EBT_n is negative. However, ProfitTax must be applied only to the positive part.
So I need the objective to maximize NetPnL=SUM(EBT_n - MAX(0,EBT_n*ProfitTax%_n)).

Obviously, this breaks linearity conditions.

And I cannot figure out a solution to overcome this.

To the best of my understanding, Binary variables are not applicable here, as Profit Tax is not something that Solver manages here, not a decision variable. I do not need to enforce positive EBT, I need to charge tax only from profits.

Also I tried to set ProfitTax% to a very small percent in case when EBT<0, but this also breaks linearity conditions.

I'm using Excel for Microsoft 365 with Solver Add-In.

Sample file attached
solver_profitTax_demo.xlsx

Just as a logical statement, I think that "I do not need to enforce positive EBT" is incorrect. A location with negative EBT has excess 'tax avoidance capacity' for lack of a better term: transferring positive EBT to that location would reduce the tax load on another location without increasing the tax load at that location. So you should constrain your solutions to 0 or positive EBTs for all locations.

Further, "There are many rules and constraints here, so I omit this part as is does not appear important." also seems incorrect. Mathematically, to maximize profits, only locations at the lowest rate should have ANY profit at all, and all locations not having the lowest rate should have exactly 0 profit. Apparently, you can't do that, so the rules would seem to impact the optimal solution.

Attaching an example: solver_profitTax_demo.xlsx
The challenge is to take profit tax % only from positive EBTs.

Admin note: Moved from Excel Programming / VBA / Macros