You'd like to use Excel to solve a resource allocation problem.
You can use Excel and Solver to tackle more complicated linear optimization problems than what you saw in the previous recipe. Resource allocation problems, like finding the optimum product mix to yield maximum profit, are commonly formulated as linear optimization problems, which are easily solved in Excel. The following discussion shows you an example. The techniques discussed here are not limited to resource allocation problems ; on the contrary, they apply to many difficult problems so long as you can formulate them in the form of an objective function plus constraints.
Let's assume you're running a laboratory that produces a certain chemical product. You currently employ 10 regular employees, technicians, who make the product. Given input from your sales and marketing group, you've put together a demand schedule for your product over the next six months. Operating budgets are tight, however, and you need to develop an optimum labor schedule for meeting demand for your product at minimum labor cost. I should point out that you know that the demand for your product over the next six months will fluctuate, so at some times you may need to hire new employees and at others you may have to let folks go.
You have the options of having your employees work overtime or hiring contract labor. Although they have a higher hourly rate than regular ...