Background: Linear Programming is used to inform business decisions. It involves constructing a Linear Programming model then solving the model to find the optimum choices for the decision variables. Mathematically the optimal choice for the set of decision variables is the choice that masimizes or minimizes $($depending on the type of problem addressed$)$ while satisfying all of the constraints that apply.

EXCEL offers a practical way of finding the optimal solution even if the number of decision variables and$/$or constraints is larger than can be handled by graphical methods. The EXCEL tool Solver is used for this.

About the lab: In this Lab, Linear Programming $($LP$)$ is used to optimize a supply strategy for a manufacturer who needs to order $10,000$ units of a component part. There are three possible suppliers of the component. Each supplier has a set price per unit for the component and two of the suppliers have limits on the number of units they can supply in the required time, as shown in the table below

$\backslash$table$[[$Supplier$,$Price per unit $($$$),$Maximum number of units.$],[$Supplier $1,40,$Unlimited $($greater than $10,000)],[$Supplier $2,33,4000],[$Supplier $3,37,3000]]$

Also, the manufacturer wants to maintain each of the suppliers as a possible supplier for future orders; so$,$ the manufacturer decides to order at least $25\%$ of the total number of units from each supplier.

In the LP model, the decision variables are the number of units ordered from each of the manufacturers. The manufacturer wants to minimize the cost of the total order of $10,000$ units subject to the constraints described above.Directions

Let $x,y$ and $z$ represent the order $($number of units$)$ to be placed with Suppliers $1,2$ and $3$ respectively. Before programming in the EXCEL worksheet, write out the formula for the total cost of the order in mathematical $($equation$)$ form and write each constraint as an equality or inequality. Write the constraints in such a way that only a number appears on the right$-$handside. In each case write the number on the right$-$hand$-$side of the constraint as a number of units $($e$.$g$.25\%$ of the total order is $2500).$

In Cells B$10$ to B$12$ place the initial guesses for the order from each supplier, i$.$e$.4,000$ units from Supplier $1$ and $3,000$ units from each of Supplier $2$ and Supplier $3.$

In Cell B$15$ construct a formula to calculate the total cost of the order. $($Be sure to use cell references for the numbers of units and the cost per unit$).$

In Cells B$18$ to B$23,$ type a formula to calculate the quantity that is being limited by the constraint.

Open the Solver dialogue box by clicking on the Solver icon in the Data ribbon. Complete the required information including the constraints. $($Don$\text{'}$t forget any implicit constraints$).$ Run Solver and accept the solution it finds.

Save your completed workbook and submit it via the Moodle page.