Refer to the table below where the numbers inside the cells represent profit.
$$\begin{array}{c|c|c|} & \text{Area A} & \text{Area B} & \text{Area C} & \text{Area D} \\ \hline \text{Person 1} & 3 & 2 & 2 & 2 \\ \hline \text{Person 2} & 2 & 3 & 2 & 1 \\ \hline \text{Person 3} & 4 & 3 & 1 & 1 \\ \hline \text{Person 4} & 2 & 3 & 2 & 2 \\ \hline \text{Person 5} & 1 & 2 & 2 & 1 \\ \hline \end{array}$$
Given the constraint that a person can only be in one area at a time, I have a few questions:
How to assign people to areas to maximise profit across all areas (Area A + Area B + Area C + Area D)?
How to assign people to areas to maximise profit given a minimum profit constraint in each area (i.e. profit for Area Xi > 1)?
I am trying to model this in Excel, but happy to hear the theory behind it or be redirected to a more suitable exchange.
You want to think of having 20 variables, $$x_{A1}, \ldots, x_{A5}, \ldots, x_{D1}, \ldots, x_{D5}.$$ We have $x_{cd}$ taking value $1$ if person $d$ is at location $c$ and $0$ otherwise.
Now we can only have one person at each location so $\sum_{d} x_{cd} = 1$ for all $c \in \{A,B,C,D\}$, and each person can only be in one location so $\sum_{c} x_{cd} = 1$ for all $d \in \{1,2,3,4,5\}$. These are the constraints of the model. Now we want to maximize the function $\sum_{c,d} x_{cd} w_{cd}$ where $w_{cd}$ is the profit for having person $d$ in location $c$.
If you want to put a restriction of a minimum profit at each location, these go in as new constraints (one for each area).
You actually do not seem to have a requirement that only one person can be in each area, so the first type of constraint I mentioned above would be absent. To maximize total profit is then easy: put each person where they make the most profit. Techniques to solve in general are computational, I use Gurobi to solve these types of problems.