The Omega Manufacturing Company has discontinued the production of a certain unprofitable product line. This act created considerable excess production capacity. Management is considering devoting this excess capacity to one or more of three products, Products 1, 2, and 3. The available capacity of the machines that might limit output is summarized in the following table.

_________________________________ Available Time

Machine Type ________________ (in Machine-Hours per Week)

Milling machine ........................................ 500

Lathe ..................................................... 350

Grinder .................................................. 150

The number of machine-hours required for each unit of the respective products are shown in the next table.

The Sales Department indicates that the sales potential for Products 1 and 2 exceeds the maximum production rate and that the sales potential for product 3 is 20 units per week. The unit profit would be $50, $20, and $25, respectively, for Products 1, 2, and 3. The objective is to determine how much of each product Omega should produce to maximize profit.
a. Indicate why this is a resource-allocation problem by identifying both the activities and the limited resources to be allocated to these activities.
b. Identify verbally the decisions to be made, the constraints on these decisions, and the overall measure of performance for the decisions.
c. Convert these verbal descriptions of the constraints and the measure of performance into quantitative expressions in terms of the data and decisions.
d. Formulate a spreadsheet model for this problem. Identify the data cells, the changing cells, the objective cell, and the other output cells. Also show the Excel equation for each output cell expressed as a SUMPRODUCT function. Then use Solver to solve the model.
e. Summarize the model in algebraic form?

Transcribed Image Text:

Productivity Coefficient (in Machine-Hours per Unit) Machine Type Product 1 Product 2 Product 3 Milling machine Lathe 4 Grinder