Construct a linear optimization model using excel to answer the following questions:

1. How many wrenches and pliers to produce per day to maximize the earnings?

2. What would be the total earnings?

3. Which resources would be most critical?

Screen shot the excel model to answer the above question.

Gemstone Tool Company (GTC) is a privately held firm that competes in the consumer and industrial market for construction tools. In addition to its main manufacturing facility in Seattle, Washington, GTC operates several other manufacturing plants located in the United States, Canada, and Mexico. These plants produce their entire range of products, including power drills and saws, nail guns, and hand-held tools such as hammers, screwdrivers, wrenches, and pliers. For the sake of simplicity, let us suppose that the Winnipeg, Canada plant only produces wrenches and pliers. Wrenches and pliers are made from steel, and the process involves molding the tools on a molding machine and then assembling the tools on an assembly machine. The amount of steel used in the production of wrenches and pliers and the daily availability of steel is shown in the first line of Table 1. On the next two lines are the machine utilization rates needed in the production of wrenches and pliers and the capacity of these machines as well. Finally, the last two rows of the table indicate the daily market demand for these tools and their variable (per unit) contribution to earnings. For example, according to the second column of Table 1, each wrench produced by GTC uses 1.5 lbs. of steel, and spends 1.0 hour on the molding machine and 0.3 hour on the assembly machine. The demand for wrenches is 15,000 per day. Every 1,000 wrenches produced (and then sold) contributes $130 to the earnings of GTC. According to the fourth column of Table 1, the amount of steel available per day is 27,000 lbs. The amount of molding machine capacity is 21,000 hours per day and the amount of assembly machine capacity is 9,000 hours per day. Table 1 Data for the GTC supply planning problem Wrenches Pliers Steel (lbs.) 1.5 1.0 Molding Machine (hours) 1.0 1.0 Assembly Machine (hours) 0.5 Demand 15000 16000 Unit Earnings ($/1000 units) $130 $100 Availability 27,000 lbs./ day 21,000 hours/day 9,000 hours/day 0.3