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.


 

 
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.

Linear Programming

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
June 4, 2014

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

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

    Drop us a Query

    Call Us: +91 8826547882

    Drop us a Query