Base Problem $$Consider the production planning problem where $4500$ units of a certain product are manufactured on eight machines.$$ The setup costs, the production costs per unit and the maximum production capacity for each machine are shown in the table to the right.

The following condition also applies.

$.$ For an employee to operate machine $6$ or $8,$ the employee needs special training.$$ There is only one employee who has received that training. $$

$$

$$Create an Integer Programming model and solve it using Excel to answer the following questions: $(38$ points$)$

$1.$ How many units should be made on each machine?$$

$2.$ Which machines need to be setup?

$3.$ What is the cost $($Production and setup$)$ of your solution?$$

$$

Modifications: $$The production manager has received authorization to spend $$1500$ to improve the performance of the production system.$$ She has identified two possible ways to spend the money.

$$

Modification A Spend $$1500$ to train another worker to operate machines $6$ and $8.(3$ points$)$

$1.$ How many units should be made on each machine?$$

$2.$ Which machines need to be setup?

$3.$ What is the cost $($Production and setup$)$ of your solution?$$

What are the total costs $($Production$,$ setup and training$)?$

$$ Modification B: $$Spend $$1500$ to update machine $1$ to reduce its production cost.$$ She estimates that the production cost per unit will reduce to $$4$ per unit, but the setup cost will increase to $$2000.$ Capacity will remain the same. $(3$ points$)$

$1.$ How many units should be made on each machine?$$

$2.$ Which machines need to be setup?

$3.$ What is the cost $($Production and setup$)$ of your solution?$$

$4.$ What are the total costs $($Production$,$ setup and updating$)?$

$$

$4.$

$$

Modification C Spend $1500$ to purchase a new robotic machine.$$ Due to the inherent flexibility of robots, the setup cost is minimal $-$ $$100.$ But the production cost is higher $-$ $$9$ per unit.$$ Capacity of the robot will be $600$ units$(3$ points$)$

$1.$ How many units should be made on each machine?$$

$2.$ Which machines need to be setup?

$3.$ What is the cost $($Production and setup$)$ of your solution?$$

$4.$ What are the total costs $($Production$,$ setup and purchase$)?$

$$

$$

Final Decision:$$ Should she spend the $$1500$ on Modification A or B $$or should she leave things the way they are in the Base Problem and keep the $$1500$ to spend on a future project? $(3$ points$)$

$$

$$

What to turn in$?$

Your answer submission should take the following form:

An Excel file named $$Your Last Name $-$ QMB$4700$ Excel $1$ Integer Programming$$

$$

There should be $5$ worksheets:

$1)$ Name one worksheet $$Base$-$ this will be your main worksheet for the Basic problem $$ On this sheet, you should:

a$)$ Display your IP model.$$ Do not forget to define your decision variables. $($Create the model as a Word document, then copy and paste it into a text box on this sheet$)$

b$)$ Create your input table from the model $-$ this is the table that you will reference in the Excel solver

c$)$ Answer the questions listed for the Base Model

$$

$2)$ Name one worksheet $$Modification A$$

a$)$ Display your IP model.$$ Do not forget to define your decision variables. $($Create the model as a Word document, then copy and paste it into a text box on this sheet$)$

b$)$ Create your input table from the model $-$ this is the table that you will reference in the Excel solver

c$)$ Answer the questions listed for Modification A

$$

$3)$ Name one worksheet $$Modification B$$

a$)$ Display your IP model.$$ Do not forget to define your decision variables. $($Create the model as a Word document, then copy and paste it into a text box on this sheet$)$

b$)$ Create your input table from the model $-$ this is the table that you will reference in the Excel solver

c$)$ Answer the questions listed for Modification B

d$)$

$4)$ Name one worksheet $$Modification C$$

a$)$ Display your IP model.$$ Do not forget to define your decision variables. $($Create the model as a Word document, then copy and paste it into a text box on this sheet$)$

b$)$ Create your input table from the model $-$ this is the table that you will reference in the Excel solver

c$)$ Answer the questions listed for Modification C

$5)$ Name one worksheet $$Final Decision$$

a$)$ Create a text box and type in your answer to the question posed for the Final Decision.