# Solver Add-in in Excel for Linear Programming Problems:

Share Now

An Organization is preparing a trip for 400 employees.

The company who is providing the transportation has 10 buses of 50 seater each and 8 buses of 40 seater, but only has 9 drivers available.

The rental cost for a large bus is \$800 and \$600 for the small bus. Calculate how many buses of each type should be used for the trip for the least possible cost.

To solve this problem using Microsoft excel, click on the below video. Let solve this problem mathematically:
Assume
No. of busses 50 Seater  =x

No. of busses 40 Seater   =y

Cost for Busses = C

Find

Minimum Cost – C = 800x + 600y

Conditions

X <= 10      Number of 50 seater buses ….. (i)

y <= 9         Number of 40 Seater buses ….. (ii)

x+y <= 9    Number of drivers available …. (iii)

x =9-y

If y = 0, x = 9

If y = 1   ——–  (x = 9-1)  ——   x = 8

If y = 4   ——–  (x = 9-4)  ——   x = 5

If y = 7   ——–  (x = 9-7) ——-   x = 2

50x+40y >= 400       Total Number of Employees   ….. (iv)

5x + 4y = 40

5x = 40 – 4y

X = 40/5 – 4/5y

If y = 5  ——- (X = 8 – 4)  ——  X = 4

If y = 0  ——- (X = 8)

If y = 1 ——– (X = 8 – 4/5)    —– (X= 7.2)

If y = 10 —— (X = 8 – (4*10)/5)  ——  X = 0

x,y >= 0                          …. (v)

Now let’s draw the graph. After solving equations (i) to (v)

At Optimum Point

X = 4 , y = 5

Minimum Cost C = 800x + 600y

C = 800 * 4 + 600 * 5

C = 6200

No. of 50 Seater Buses  = 4

No. of 40 Seater Buses   = 5

Share Now
November 15, 2019