A small mine works two coal seams and produces three grades of coal. It costs £1

ID: 3041286 • Letter: A

Question

A small mine works two coal seams and produces three grades of coal. It costs £10 per hour to work to upper seam, obtaining in that time 1 tonne of anthracite, 5 tonne of best quality and 2 tonne of ordinary coal. The lower seam is more expensive to work at a cost of £15 per hour. However, it yields per hour 4 tonne of anthracite, 6 tonne of best and 1 tonne of ordinary coal.
Faced with just one order for 8 tonne of anthracite, 25 tonne of best and 10 tonne of ordinary coal each day, how many hours should each seam be worked daily so as to fulfil the order as cheaply as possible? What is the minimum cost of meeting this order?

How do I make this work with excel? Please explain solution fully

Explanation / Answer

Let us write the given data on coal excavation in following table:

Seam

Anthracite

Best Quality

Ordinary Quality

Cost Per Hour

Upper

Lower

Requirement

Assume that coal excavation will take place for X and Y hours on Upper and Lower seam. Then this problem on minimization can be expressed as follows:

Minimize the total cost Z = 10 X + 15 Y

Subject to the conditions,

X + 4 Y 8 ……. (1)

5 X + 6 Y 25 ……(2)

2 X + Y 10 …….(3)

With additional constraint of non-negativity as X 0 and Y 0

If we convert these three inequality to equality we will find that these three lines will pass through the following pair of points:

After drawing the graph and showing that requisite area as expected in sign we find that minimum will occur at the point of intersection of (1) and (3).

Solving these two equations simultaneously we find that X = 292 Minutes and Y = 52 Minutes.

Therefore it suggests that continue coal excavation on Lower seam only for 52 minutes and on Upper seam up to 292 minutes resulting into the total minimum cost

Z= 10*292/60+15*52/60 = 62 Pounds.

Dear Student it is not possible to get the exact solution through Excel. This is the problem on Optimization Theory. Excel will provide approximate answer which will depend upon what kind of combination one should take. So my suggestion is apply Optimization Technique.