Optimization: The Texas Kettle Company –>please show the excel step as much as possable The Texas Kettle Company (TKC) is experiencing tremendous growth in the demand for its barbecue grills. They produce four kinds of grills: discount and premium charcoal grills and discount and premium gas grills. In the coming year, the potential fulfillment requirement from its customers (retailers like Home Depot, Lowe’s, and Wal-mart) sums to a total of 300,000 grills of the type specified in Exhibit 4.1. Exhibit 4.1: TKC’s Fulfillment requirement Type Fulfillment Requirement Discount Charcoal 120,000 Premium Charcoal 80,000 Discount Gas 70,000 Premium Gas 30,000 Total 300,000 TKC’s production is limited by the availability of labor hours in three departments: Production, Assembly, and Packaging. Exhibit 4.2 below provides the hours available in each department and processing time required in each department by one grill of each type. The unit costs for each grill (mainly material and overheads, but not including any labor charges) are also shown. Exhibit 4.2: TKC’s Capacity and Production Costs Production Assembly Packaging Unit Costs/grill Discount Charcoal .08 .12. 05 25 Premium Charcoal. 10 .15 .05 40 Discount Gas .20 .25 . 05 75 Premium Gas .25 .30 .05 110 Available hours 25,000 40,000 20,000 Labor costs are $35 per hour, including benefits. Fearing the reaction of its labor force and local community, TKC will not hire, fire, or reassign labor from one department to another. That is, TKC pays for all of the available hours in each department whether or not they are used. Answer the following questions on the Answer Sheet for this problem (provided separately) and attach the appropriate documentation to the answer sheet, if necessary. a) Is it possible for TKC to meet the entire fulfillment requirement using existing manufacturing capacity in their current assignments? If so, how? If not, why not? b) Using its current manufacturing capacity, at most how many types of grills can TKC commit in its contract with its customers? What are those types of grills and how much is the corresponding total cost? For each type of grills, if not committed, TKC does not produce that type; once committed, TKC needs to fulfill its entire requirement, i.e., no partial fulfillment is allowed. Formulate an integer program to help TKC make the commitment decision. c) TKC wants to develop a production plan to meet all the fulfillment requirements at the lowest possible cost. So TKC has been negotiating with a manufacturer based in Mexico that has available capacity. This manufacturer has agreed to supply TKC with any combination of grills at costs described below. The grills would then be produced, assembled, packed, and shipped to TKC by the Mexican manufacturer with no labor required by TKC. Exhibit 4.3: Unit Procurement Costs from Mexico Unit Costs($ per grill) Discount Charcoal 33 Premium Charcoal 50 Discount Gas 92 Premium Gas 130 Given the availability of the Mexican manufacturer, what is the optimal production mix? That is, how many grills of each type should they make and how many should they buy from the manufacturer in Mexico? What’s the corresponding optimal cost? d) Suppose the sales force could sell more Premium Charcoal grills to TKC’s customers. At least how much do they need to charge for additional units of this kind of grill? How many more units can they offer at this price? e) If TKC is able to reassign 3000-hour labor from one department to another department, should TKC make this reassignment? If so, how? If not, why?

Optimization: The Texas Kettle Company –>please show the excel step as much as possable

The Texas Kettle Company (TKC) is experiencing tremendous growth in the demand for its barbecue grills. They produce four kinds of grills: discount and premium charcoal grills and discount and premium gas grills. In the coming year, the potential fulfillment requirement from its customers (retailers like Home Depot, Lowe’s, and Wal-mart) sums to a total of 300,000 grills of the type specified in Exhibit 4.1. Exhibit 4.1: