Question

2. Devos Inc. makes desks that are made up of 1 top, 2 sides, and 4 legs. We need to make at least 15,000 desks to fulfill the demand of our customers.

The costs are as follow:

Make the top - $7

Buy the top - $10

Make the side - $3

Buy the side - $5

Make a leg - $2

Buy a leg - $3

1) One top requires 4 feet of wood, one side requires 2 feet of wood, and one leg requires 1 foot of wood and we have 180,000 feet of wood available.

2) Each top takes 8 minutes to make a top, each side takes 3 minutes to make, and each leg takes 2 minutes to make. We have 300,000 minutes available.

3) Each top takes up 2 units of space, each side takes up 2 units of space, and each leg takes up .1 units of space before it is assembled in our inventory. We have 80,000 units of space available.

Answer the following using solver:

1. How many tops should we make? How many tops should we buy? How many sides should we make? How many sides should we buy? How many legs should we make? How many legs should we buy?
2. Without re-running Solver, what happens to our cost if we require that we buy at least one leg?
3. Without re-running solver, what happens to our cost if we need to make 16,000 tops (instead of 15,000)?

Answer 1

	Top	Side	Leg	Demand
Desks	1	2	4	15000
Requirement to meet demand	15000	30000	60000

Cost	Top	Side	Leg	Limits
Make	7	3	2
Buy	10	5	3
Wood	4	2	1	180000
Time	8	3	2	300000
Space	2	2	0.1	80000

Formulating LP in excel solver:

Below are the formulae used in objective and constraint cells

Open solver excel plug in and add the parameters as shown in the screenshot.

Decision Variables table has the blank cells which are entered in the by changing variable cells area.

Objective function reference is given in the Set Objective section.

Then add the constraints. You can add the constraints one by one or at once for >= or <= values(I have done in this way)

Select solving method as simplex LPand click solve.

Solution

All constraints are based on the make variables as making requires wood, time and storage but buying doesn't require these activities.

a. The number of top, side, and legs required to make and buy are, as mentioned in the tables.

Decision Variables	Top	Side	Leg
Make	13800	23200	60000
Buy	1200	6800	0
Total	15000	30000	60000

b. If require to buy at least 1 leg, the cost will increase with every additional leg bought.

c. In the sensitivity report, the shadow price is 10. This means with each additional increase in the RH side (15000) the cost will increase by 10 times the difference. So if it is changed to 16000, the cost will increase by 10000.