IENG $440$

Test II

$(1)$ Construct the spreadsheet modeling for the following LP and write a Module to solve the problem.

Maximize $Z=36Y1+58Y2-40Y3+20Y4$

Subject to

$Y1,Y4$

$,Y2,Y3$

$Y1,+Y2+Y3+Y4,4$

$Y1,+Y2+Y3+Y4,2$

$,Y2,+Y4,1$

$Y1,Y_2,Y3,$ and $Y4$ are binary variables.

$(2)$ You plan to build at most two plants at SE$,$ LA$,$ and NY$.$ In addition, one warehouse should be built in either NY or DC$.$

$\backslash$table$[[,\backslash$table$[[$Plant$],[$LA$]],\backslash$table$[[$Plant$],[$SF$]],\backslash$table$[[$Plant$],[$NY$]],\backslash$table$[[$Warehouse$],[$LA$]],\backslash$table$[[$Warehouse$],[$SF$]],\backslash$table$[[$Warehouse$],[$NY$]]],[$Revenue$,$$A$,$SB$,$$C$,$SD$,$SE$,$SF$],[\backslash$table$[[$Building$],[$Expenses$]],$$G$,$$H$,$$I,$J$,$SK$,$$L$]]$

$\backslash$table$[[$Name$,$SA$,$SB$,$SC$,$SD$,$SE$,$SF$,$SG$,$SH$,$SI$,$SJ$,$SK$,$SL$,$Budget$],[$lerome$,10,9,8,7,6,5,4,6,4,2,3,2,27],[$Kenneth$,8,9,10,6,7,5,5,4,4,3,3,3,23],[$William$,9,8,10,5,5,6,5,5,5,4,2,2,24],[$Shamera$,7,9,9,6,6,5,4,5,5,3,2,2,25]]$

$(1)$ Write VBA to solve the problem for the given budget range, $20$ Budget $35$ for Module $1.$ Write your observations. Set your increment of the Budget to be $1.$

$(2)$ In Module $2,$ write VBA to search for the solution for the Budget requested by the user.

Instruction: You must submit two separate files for each problem. With VBA, you should save the file as the Macro$-$Enabled File, Otherwise, your VBA will disappear. Your file name is LastName IENG$440$ T$2.$ Submit the two files to

jedhong$87$iagmail.com and joug a sesu edu before Wednesday's class $(11/15/23).$