Purpose

The overall purpose of this assignment is to enhance your skills with Excel relating to a what if scenario using Solver, a linear programming solver. It will help you integrate and understand linear programming with maximizing profits or minimizing costs in a what if analysis.

Required:

In Excel, create a table, including data and constraints, needed to run solver.

a$.$ Set up the table, including data and constraints, needed to run solver. $(16$ marks$)$

Use solver to answer the following questions:

b$.$ Calculate the total shipping costs as well as the total shipping costs to each plant. $(2$ marks$)$

c$.$ Suppose PJ Company receives an order for an additional $300$ ounces of refined gold. Does PJ Company have the capacity to fill this new order? Explain. $(4$ marks$)$

$$

Data:

PJ Company specializes in mining of gold. The company mines gold from Mine A and Mine B$.$ The company refines the rough gold in plant $1$ and plant $2.$ A few weeks ago, PJ received an order for $1,600$ ounces of gold.

The following data shows the mining capacity for each mine and the grade of gold produced in refining. The grade of gold represents the volume of refined gold that can be obtained with $1$ ounce of rough gold.

Max Mining Capacity Grade of gold per ounce of rough gold

Mine A $1,2000\mathrm{.}70$

Mine B $9000\mathrm{.}80$

Plant $1$ can refine $800$ ounces and Plant $2$ can refine $1,000$ ounces.

The controller at PJ Company wants to minimize the cost of transportation of the rough gold. The controller has determined the costs of transportation $($per ounce$)$ between the two mines and two plants as follows:

Plant $1$ Plant $2$

Mine A $$6\mathrm{.}30$ $$9\mathrm{.}00$

Mine B $$8\mathrm{.}00$ $$7\mathrm{.}00$