Question

Par Inc. is a small manufacturer of golf equipment. It produces two types of golf bags: Standard and Deluxe. Each bag type requires the following operations (and production times) to produce one unit:

	Production Time (hours)		Time Available
Operation	Standard	Deluxe	(hours/month)
Cutting and Dyeing	0.7	1	630
Sewing	0.5	5/6	600
Finishing	1	2/3	708
Inspection	0.1	0.25	135

Time available refers to the production capacity for each of the above operations. For example, 630 total hours a month are available for cutting and dyeing, which will be distributed for the production of the two types of bags.

Every standard bag makes a profit of $10, and every deluxe bag makes a profit of $9.

The problem is to determine the optimal number of standard bags and deluxe bags to produce every month to maximize the profit contribution.

Define the variables, formulate the problem, and SOLVE it in Excel (generate the Answer and Sensitivity reports)

Please use excel and post pictures so I can see how to do this on Excel

Answer 1

Let X,Y be the quantities of Standard and Deluxe golf bags to be produced. We want to find these quantities and hence these are the decision variables.

The total profit from selling X,Y be the quantities of Standard and Deluxe golf bags is

We want to maximize the profit and hence this is the objective function.

The constraints are

The time available for cutting and dyeing is 630 hours/month. The time needed to produce X,Y quantities needs to be within that.

The time available for Sewing is 600 hours/month. The time needed to produce X,Y quantities needs to be within that.

The time available for Finishing is 708 hours/month. The time needed to produce X,Y quantities needs to be within that.

The time available for Inspection is 135 hours/month. The time needed to produce X,Y quantities needs to be within that.

The LP model can be stated as

Maximize

s.t

Prepare the following sheet

get this

set up the solver using data--->solver

get this solution

ans: the optimal number of standard bags and deluxe bags to produce every month to maximize the profit contribution are

Standard (X)	Deluxe (Y)
540	252

the answer and sensitivities reports can be generated by selecting those options before the run