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.

To download the template click Here

**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

## 0 responses on "Solver Add-in in Excel for Linear Programming Problems:"