LINEAR PROGRAMMING

BA2014 Data Analysis for Business Decision Making Semester 1 (2011/12) Coursework

BA2014 Data Analysis for Business Decision Making Semester 1 (2011/12) Coursework

Task 1

(A)

 

Labour cost per hour

Labour Cost per minute

Internal Modem

External Modem

Circuit Board

CD Drive

Hard Disc Drive

Memory Board

Test device1

25

0.42

0.83

1.25

2.08

2.50

1.67

3.33

Test device2

15

0.25

0.25

1.25

0.75

0.50

1.25

1.25

Test device3

18

0.30

1.50

0.60

0.90

0.90

0.90

0.60

Test device4

12

0.20

0.60

0.80

0.40

0.20

0.40

0.60

Total Variable Labour Cost Per Unit

3.18

3.90

4.13

4.10

4.22

5.78

Devices

Selling Price per unit (£)

Material Cost Per Unit (£)

Variable Cost Per Unit (£)

Contribution Margin Per Unit (£)

Internal modem

200

40

3.18

156.82

External modem

185

25

3.90

156.10

Circuit board

290

35

4.13

250.87

CD drive

212

40

4.10

167.90

Hard disk drive

380

85

4.22

290.78

Memory board

330

50

5.78

274.22

(B)

Linear Programming Model

For decision variables, Let:

a = total number of units of internal modem produced every week

b = total number of units of external modem produced every week

c = total number of units of circuit board produced every week

d = total number of units of CD drive produced every week

e = total number of units of hard disk drive produced every week

f = total number of units of memory board produced every week

Objective Function:

Profit max = 156.82 a + 156.10 b + 250.87 c + 167.90 d + 290.78 e + 274.22 f

Subjective Constraints:

For test device 1 (Total time in minutes)

2a + 3b + 5c + 6d + 4e + 8f <= 9000

For test device 2 (Total time in minutes)

1 a + 5b + 3c + 2d + 5e + 5f<= 7800

For test device 3 (Total time in minutes)

5a + 2b + 3c + 3d + 3e + 2f<= 6600

For test device 4 (Total time in minutes)

3a + 4b + 2c + 1d + 2e +3f<= 6120

a, b, c, d, e, f = 0

(C)

Using Excel's Solver for the above illustrated problem of how many peripheral devices should be manufactured by the Northern Hi-Tec E. Ltd. To maximize profits, following results have been achieved.

 Product

Optimal Units to Produce

Internal Modem

168.00

External Modem

0.00

Circuit Drive

984.00

CD Drive

0.00

Hard Disk Drive

936.00

Memory Board

0.00

Company should product 168 units of internal mode, 984 units of circuit drives, and 936 units of hard disk drives as an optimal solution. Producing above calculated units of devices, maximum profit that can be achieved is 545371.2 pounds. Value of objective function is illustrated below.

Name

Original Value

Final Value

Objective

0

545371.2002

(D)

First three constraints are binding since they are completely utilized at optimal production level. However, constraint for Test Device 4 is non-binding since considerable slack time is available for the test device 4. Slack amount that non-binding constraint 4 has is 1776. In the context of problem, this shows that company has 1776 minutes available for testing for Test Device 4 every week.

(E)

If a reduction of £10 was made to the profit margin of 'Internal Modem', no impact will occur on the optimal solution, since profit is maximizing at the same combination of number of units produced. However, value of objective function will change from 545371.2 pounds to 543691.2 pounds. This shows that if profit margin of internal modem is reduced by £10, company is not required to make changes in the optimal production quantity level. Optimal output in such condition will remain as follows:

 Product

Optimal Units to Produce

Internal Modem

168.00

External Modem

0.00

Circuit Drive

984.00

CD Drive

0.00

Hard Disk Drive

936.00

Memory Board

0.00

(F)

The value of additional minute of time per week on test device 1 is ...