Files that you will download:
Questions Covered in the Solution
- Alpine Sports. The Alpine Sports Company makes a line of winter sports equipment, including skis, snowboards, and sleds. Each product uses time on an extruder machine and requires time in final assembly. In addition, each product is largely made out of fiberglass. They have formulated a linear programming spreadsheet model to determine the production levels that would maximize profit. The spreadsheet model and sensitivity report are shown below. Answer the following questions as completely as is possible without re-solving the problem with Solver. Justify your answers using the results from the sensitivity report. All problems are independent (i.e., any change made in one part does not affect the other parts). Type your answers into a single worksheet tab on your spreadsheet, clearly labeling each answer (a, b, c, etc.).
- Suppose the profit per pair of skis increases from $80 to $100. Will this change the optimal production quantities? What can be said about the change in total profit?
- How much would the selling price of sleds need to be increased before it might become profitable to produce this product?
- Suppose the profit per pair of skis decreases by $30 and the profit per snowboard increases by $60. Will this change the optimal production quantities? What can be said about the change in total profit?
- Suppose they discover that 400 pounds of fiberglass is defective and therefore unusable. How much would this affect total profit? Will this cause a change in the optimal production quantities?
- Suppose the extruder breaks down temporarily, thus reducing the time available in by 5 hours and they obtain 200 more pounds of fiberglass. How much will this affect total profit? Will this cause a change in the optimal production quantities?
- Locating Fire Stations at Pilgrim Haven. Do problem 7.12b in the textbook (also available on Canvas under Assignments in the Problem Set 2 link.) Build a linear integer programming spreadsheet model, and solve it using Solver. (Be sure to set Integer Optimality in Solver Options to 0%.)
- An Electrical Utility Startup Problem. A problem faced by an electrical utility company each day is that of deciding which generators to start up. The utility in question has five generators with the characteristics shown in the following table. If any Megawatt-hours (MW-hr) are produced by a generator, the generator must be started up and the fixed startup cost is incurred. At least 8000 MW-hrs must be generated today. Set up and solve a linear spreadsheet model (with some binary variables) to determine the operating plan that will minimize their overall costs. For full credit, the model must be linear (no multiplication of changing cells, no IF statements, no MAX statements, etc.)
Sample of Solution
|Fixed Startup Cost||$ 3,050||$ 2,200||$ 1,300||$ 2,500||$ 3,700|
|Cost per MW-Hr Generated||$ 8||$ 9||$ 10||$ 6||$ 7|
|Maximum Capacity (MW-Hr)||2800||1800||3400||2100||2400|
|Generator Decision (1 = Must Start 0 = Not Startup)||1||0||1||1||1|