What does one conflict of interest cost you?
Five people, four accounts, and a conflict of interest that rules one pairing out. The optimizer finds the cheapest legal staffing and prices what the conflict costs you.
Work Intermediate Optimization free
After you install, this is the model to open.
Who Works on Which Client?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- The conflict costs
- $1,100 a quarter: the same solve without the ban lands at $94,400
- Cheapest legal staffing
- $95,500 of delivery cost, and it benches Cara entirely
- Margin left
- $62,500 39.6% of the $158,000 of quarterly fees
The top grid is what a quarter of delivery costs for every consultant and account pair: the hours that account needs, at that person's cost rate, so it varies by both. The bottom grid is the decision, at most one account per consultant and exactly one consultant per account, which is what makes it a staffing plan rather than a wish list. Cara advises a competitor of the food producer, so the one cell that would put her on that account is held at zero by a constraint.
That single line is the point of the template, and it is written as a constraint rather than deleted from the grid so that its cost stays visible. The sheet opens on the cheapest plan the partner group would draw by hand: Ben on the bank, Cara on the food producer, Dev on the software firm and Ama on the housing trust, costing $94,400 against $158,000 of fees, a 40.3 percent margin.
It is also not allowed, because it puts Cara exactly where she cannot go. Click Run and the optimizer returns $95,500. It does not simply swap Cara out: it moves Dev from the software firm across to the food producer and brings Eli in to lead the software firm, leaving Cara free. Two of the four pairings change, because a blocked cell in an assignment does not push one person sideways, it re-sorts the plan around the hole.
The conflict costs $1,100 a quarter, which is worth knowing before anyone argues about it. Delete that constraint and run it again and the cost goes back to $94,400, which is how you price any rule you are thinking of relaxing, whether it is a conflict, a client preference or a travel limit. Eli leads no account in the opening plan and one account in the optimized one, which is the other thing five people and four accounts is quietly asking about.
The model does not know who is any good with a difficult client, and it assumes the hours an account needs do not depend on who does the work, which is not quite true and is more untrue the more junior the person. To make it yours, retype the names and accounts, put your own hours-times-rate figures in the top grid, and add a constraint fixing any other pairing at zero that you are not allowed to make.
The model
It arrives on a tab called Template: Who Works on Which Client, carrying these columns:
- Regional bank (delivery cost $)
- Food producer (delivery cost $)
- Software firm (delivery cost $)
- Housing trust (delivery cost $)
with the model computed beside the data:
| Leads on this account | 1 |
| Total delivery cost ($) | 94,400 |
| Total fee ($) | 158,000 |
| Margin ($) | 63,600 |
| Margin (%) | 0.4025 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- Which client is quietly about to leave?Which Engagements Renew?
- Which date can you defend when the client pushes?Can I Promise That Date to the Client?
- What is this comp package really worth?
- What is the engagement worth once scope grows?Take the Engagement or Pass?
- Which day can you promise and still be right nine times in ten?What deadline gives 90% odds of finishing?
- Which bid is low enough to win and high enough to pay for the job?What bid wins with 70% odds and still makes money?
Every model like this one, and the method behind them: Optimization in Google Sheets.