+91-8826547882

Solver Add-in in Excel for Linear Programming Problems:

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

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 *

© copyright www.excelcrazy.com. All rights reserved. Design and Development by www.creativebrainweb.com

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Location (required)

Your Message for us...

captcha