Reference no: EM131867713

Must use the excel pluggin, solver to answer this.

Puget Sound Watersports (25 points). Puget Sound Watersports (PSW) is a company that manufactures up to three different products: a slalom waterski, a wakeboard, and a kneeboard. They are trying to decide what quantity of each to produce per week.

All three products are formed out of the same fiberglass material. PSW has a long-term contract with a supplier of the fiberglass and receives a 20,000 pound shipment each week. Each slalom waterski requires 5 pounds of fiberglass;

each wakeboard requires 10 pounds of fiberglass; each kneeboard requires 12 pounds of fiberglass. The production of the slalom waterski requires the use of a ski-extruder machine. If any slalom waterskis are produced, this machine must be leased at a weekly cost of $40,000. This single ski-extruder machine can be used to produce any quantity of skis. The wakeboards and kneeboards require the use of a board-extruder machine.

If any wakeboards and/or kneeboards are produced, this machine must be leased at a weekly cost of $50,000. This single board-extruder machine can be used to produce any combination or quantity of wakeboards and/or kneeboards.

The sales forecasts indicate that at most 2000 slalom waterskis, 1500 wakeboards, and 1200 kneeboards could be sold each week.

Each slalom waterski requires 45 minutes of manufacturing labor to produce, and when sold generates a gross profit (not counting labor or machine leasing costs) of $200. Each wakeboard requires 30 minutes of manufacturing labor to produce and when sold generates a gross profit of $275. Each kneeboard requires 30 minutes of labor and when sold generates a gross profit of $300.

Each full-time employee in manufacturing at PSW is paid for a 40-hour week. By union contract, the number of fulltime employees in manufacturing can never drop below 40. Part-time workers can also be hired to work in manufacturing. Each part-time employee is paid for a 20-hour week.

The union has imposed a restriction that there must be at least three full-time employees in manufacturing on the payroll for each part-time employee in manufacturing on the payroll. Each full-time employee earns $22 per hour while each part-time employee earns $16 per hour.

These manufacturing employees are used to supply the labor necessary to produce PSW's products. Management wishes to know what quantity of each of the three products to produce per week, as well as how many full-time and part-time workers to employ in manufacturing.

They would like to maximize their net weekly profit (accounting for gross profits, manufacturing labor costs, and machine leasing costs). Develop a linear spreadsheet model for this problem and solve it using Solver's Simplex LP algorithm. Please be sure to set Integer Optimality to 0% in Solver Options.