Virginia Tech $-$ adapted from Spreadsheet Modeling and Decision Analysis by C$.$ T$.$

Ragsdale $(35$ points$)$

Virginia Tech operates its own power generating plant which bums coal to produce

steam. The plant can bum any blend of the three coals in the following table, given with

their respective Sulfur content per ton burned, Coal Dust content per ton burned, and

pounds of steam produced per ton burned. The EPA requires that each ton of coal

burned contains no more than $2,500$ parts per million $($ppm$)$ of sulfur and no more than

$2\mathrm{.}8$ kilograms $($ kg $)$ of coal dust. The manager wants to find the blend of coal that

maximizes the pounds of steam produced per ton of coal burned without violating the

EPA requirements.

Hint: See Unit $4$A Practice Problem #$1$

a$.$ State the decisions to be made, the objective and the constraints in words using

or $=$ in the constraints. $($Submit copy of Handwritten answer, $5$ pts$)$

b$.$ Define your inputs algebraically as decision variables. Then, using your objective and

constraints in part $($a$),$ formulate the problem as an algebraic linear optimization model.

$($Submit copy of Handwritten answer, $5$ pts$)$

c$.$ Set each of your decisions in $($a$)$ equal to $33\mathrm{.}33\%$ and then compute the corresponding

numerical values for your objective and constraints. Is this solution feasible and what is

the corresponding value of the objective? $($Submit copy of Handwritten answer, $5$ pts$)$

d$.$ Write your algebraic model from $($c$)$ as an EXCEL model. $($Submit answer in an Excel

file, $5$ pts$)$

e$.$ Using your Excel model with a value of $33\mathrm{.}33\%$ for each decision cell, compute the

values for the objectives and the constraints. Do you get the same values as in part $($b$)?$

$($Submit answer in an Excel file, $5$ pts$)$

f$.$ Optimize your EXCEL model using Solver to find the optimal solution and the optimal

value of the objective. $($Submit answer in an Excel file, $5$ pts$)$

g$.$ If the furnace can bum up to $30$ tons of coal per hour, what is the maximum amount of

steam that can be produced per hour? $($Submit copy of Handwritten answer, $5$ pts$)$