(Linear Programming Problem: Use Excel) Problem Scenario: ORSA MAC Auto, a large automobile manufacturing company, organizes the vehicles it manufactures into three families: a family of trucks, a family of small cars, and a family of midsize and luxury cars. One plant located in Nowhere, VA assembles two models from the family of midsized and luxury cars. The first model, the Family Thrillseeker, is a four-door sedan marked as a great buy for middle-class families with tight budgets. Each Family Thrillseeker sold generates a profit of $3,600 for the company. The second model, the Classy Cruiser, is a two-door luxury car marketed as a privilege of affluence for upper-middle-class families and each Classy Cruiser generates a healthy profit of $5,400 for the company.
Iam Smart, the assembly plant manager, is working on the production schedule for next month. Specifically, he must decide how many Family Thrillseekers and Classy Cruisers to assemble in the plant to maximize profit for the company. Currently, the plant has a labor capacity of 48,000 labor-hours and it takes six labor-hours to assemble one Family Thrillseeker and ten and a half hours to assemble one Classy Cruiser.
Because the plant is simply an assembly plant, the required parts are manufactured elsewhere and shipped to the assembly plant. For the next month there are no apparent parts issues with the exception of doors. Because of a recent strike the doors supplier will not be at full production. Iam knows that he will only be able to obtain a maximum of 20,000 doors (10,000 left-hand doors and 10,000 right-hand doors) during the coming month.
A recent company forecast of monthly demands for different automobile models suggest that the demand for the Classy Cruiser is limited to 3,500 cars. There is no limit on the demand for the Family Thrillseeker within the capacity limits of the assembly plant.
A. (5 points) Formulate and solve a linear programming problem to determine the number of Family Thrillseekers and Classy Cruisers to assemble during the month. Provide a copy of your Excel model as support for your solution.
B. (10 points) The marketing department knows that it can pursue a targeted $500,000 advertising campaign that will increase demand for the Classy Cruiser next month by 20%. Additionally, Iam knows that using overtime he can increase next month’s plant labor capacity by 25%. The estimated costs for the maximum usage of overtime labor-hours is $1,600,000 beyond the regular time rates. Should Iam employ either of these options? Justify your recommendation. Once again, provide a copy of your Excel model.
C. (10 points) ORSA MAC Auto discovered that dealerships are actually heavily discounting the price of the Family Thrillseeker to move them off the lot. Because of a profit-sharing agreement with its dealers, the company is therefore only making a profit of $2,800 on the Family Thrillseeker.
Through a random testing of the Family Thrillseeker at the end of the assembly process Iam discovered that in 60% of the cases two of the four doors did not seal properly. Because the percentage of defective cars is so high, Iam decided to perform quality control tests on every Family Thrillseeker at the end of the line. Because of the added tests, the time it takes to assemble a Family Thrillseeker will increase from six hours to seven and a half hours.
What impact will these two issues have on the original production plan (see requirement A)? Would implementing the advertising campaign or using overtime lessen the impact of these problems? Justify your recommendation and provide a copy of your Excel model.