Please solve the following two problems and submit your final solutions in a single Excel

workbook with each spreadsheet model on a separate worksheet. You may answer any written

questions within the corresponding worksheet.

$1.$ Production mix problem:

Riverside Oil Company in eastern Kentucky produces regular and supreme gasoline. Each barrel

of regular sells for $$21$ and must have an octane rating of at least $90.$ Each barrel of supreme

sells for $$25$ and must have an octane rating of at least $97.$ Each of these types of gasoline are

manufactured by mixing different quantities of the following three inputs:

Input Cost per Barrel Octane Rating Barrels Available

$($in $1000$s$)$

$1$ $$17\mathrm{.}25100150$

$2$ $$15\mathrm{.}7587350$

$3$ $$17\mathrm{.}75110300$

Riverside has orders for $300,000$ barrels of regular and $450,000$ barrels of supreme. How

should the company allocate the available inputs to the production of regular and supreme

gasoline if they want to maximize profits?

a$.$ Formulate an LP model for this problem

b$.$ Create a spreadsheet model for this problem and solve it using Solver.

c$.$ What is the optimal solution?

$2.$ Agricultural production planning problem:

Margaret Black's family owns five parcels of farmland broken into a southeast sector, north

sector, northwest sector, west sector, and southwest sector. Margaret is involved primarily in

growing wheat, alfalfa, and barley crops and is currently preparing her production plan for next

year. The Pennsylvania Water Authority has just announced its yearly water allotment, with

the Black farm receiving $7,400$ acre$-$feet. Each parcel can only tolerate a specified amount of

irrigation per growing season, as specified in the following table:

Parcel Area $($acres$)$

Water irrigation limit $($acre$-$

feet$)$

Southeast $2,0003,200$

North $2,3003,400$

Northwest $600800$

West $1,100500$

Southwest $500600$

BIT $3434$ Homework $3$

Each of Margaret's crops needs a minimum amount of water per acre, and there is a projected

limit on sales of each crop. Crop data follow:

Crop Maximum sales Water needed per acre $($acre$-$feet$)$

Wheat $110,000$ bushels $1\mathrm{.}6$

Alfalfa $1,800$ tons $2\mathrm{.}9$

Barley $2,200$ tons $3\mathrm{.}5$

Margaret's best estimate is that she can sell wheat at a net profit of $$2$ per bushel, alfalfa at $$40$

per ton, and barley at $$50$ per ton. One acre of land yields an average of $1\mathrm{.}5$ tons of alfalfa and

$2\mathrm{.}2$ tons of barley. The wheat yield is approximately $50$ bushels per acre.

$($a$)$ Formulate Margaret's production plan

$($b$)$ What should the crop plan be$,$ and what profit will it yield?

$($c$)$ The Water Authority informs Margaret that for a special fee of $$6000$ this year, her farm

will qualify for an additional allotment of $600$ acre$-$feet of water. How should she

respond?