O'Reilly logo

Excel Scientific and Engineering Cookbook by David M Bourg

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

13.2. Exploring Resource Allocation Optimization Problems

Problem

You'd like to use Excel to solve a resource allocation problem.

Solution

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.

Discussion

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required