Linear Programming : Basic Concepts. Solution to Solve Problem
Chapter 2
Linear Programming: Basic Concepts
Solution to Solved Problems
2.S1 Back Savers Production Problem
Back Savers is a company that produces backpacks primarily for students. They are considering offering some combination of two different models—the Collegiate and the Mini. Both are made out of the same rip-resistant nylon fabric. Back Savers has a long-term contract with a supplier of the nylon and receives a 5000 square-foot shipment of the material each week. Each Collegiate requires 3 square feet while each Mini requires 2 square feet. The sales forecasts indicate that at most 1000 Collegiates and 1200 Minis can be sold per week. Each Collegiate requires 45 minutes of labor to produce and generates a unit profit of $32. Each Mini requires 40 minutes of labor and generates a unit profit of $24. Back Savers has 35 laborers that each provides 40 hours of labor per week. Management wishes to know what quantity of each type of backpack to produce per week.
a. Formulate and solve a linear programming model for this problem on a spreadsheet.
To build a spreadsheet model for this problem, start by entering the data. The data for this problem are the unit profit of each type of backpack, the resource requirements (square feet of nylon and labor hours required), the availability of each resource, 5400 square feet of nylon and (35 laborers)(40 hours/laborer) = 1400 labor hours, and the sales forecast for each type of backpack (1000 Collegiates and 1200 Minis). In order to keep the units consistent in row 8 (hours), the labor required for each backpack (in cells C8 and D8) are converted from minutes to hours (0.75 hours = 45 minutes, 0.667 hours = 40 minutes). The range names UnitProfit (C4:D4), Available (G7:G8), and SalesForecast (C13:D13) are added for these data.
[pic]
The decision to be made in this problem is how many of each type of backpack to make. Therefore, we add two changing cells with range name UnitsProduced (C11:D11). The
Linear Programming: Basic Concepts
Solution to Solved Problems
2.S1 Back Savers Production Problem
Back Savers is a company that produces backpacks primarily for students. They are considering offering some combination of two different models—the Collegiate and the Mini. Both are made out of the same rip-resistant nylon fabric. Back Savers has a long-term contract with a supplier of the nylon and receives a 5000 square-foot shipment of the material each week. Each Collegiate requires 3 square feet while each Mini requires 2 square feet. The sales forecasts indicate that at most 1000 Collegiates and 1200 Minis can be sold per week. Each Collegiate requires 45 minutes of labor to produce and generates a unit profit of $32. Each Mini requires 40 minutes of labor and generates a unit profit of $24. Back Savers has 35 laborers that each provides 40 hours of labor per week. Management wishes to know what quantity of each type of backpack to produce per week.
a. Formulate and solve a linear programming model for this problem on a spreadsheet.
To build a spreadsheet model for this problem, start by entering the data. The data for this problem are the unit profit of each type of backpack, the resource requirements (square feet of nylon and labor hours required), the availability of each resource, 5400 square feet of nylon and (35 laborers)(40 hours/laborer) = 1400 labor hours, and the sales forecast for each type of backpack (1000 Collegiates and 1200 Minis). In order to keep the units consistent in row 8 (hours), the labor required for each backpack (in cells C8 and D8) are converted from minutes to hours (0.75 hours = 45 minutes, 0.667 hours = 40 minutes). The range names UnitProfit (C4:D4), Available (G7:G8), and SalesForecast (C13:D13) are added for these data.
[pic]
The decision to be made in this problem is how many of each type of backpack to make. Therefore, we add two changing cells with range name UnitsProduced (C11:D11). The