20. Go to the Suppliers worksheet, where Benicio wants to determine whether subcontracting could reduce the cost of the new virtual assistant product.
Run Solver to solve this problem as follows:
- Set the total number of virtual assistants produced (cell E5) as greater than or equal to 11,000, DIG Technology's minimum production goal.
- Set the total cost (cell E10) to be less than or equal to 925,000, the maximum total cost DIG Technology wants to spend.
- Set the total number of virtual assistants produced by a single supplier (range B5:D5) to be less than or equal to 4000 to balance the production among the suppliers.
- Make sure the values in the range B5:D5 are integers since DIG Technology cannot sell a fraction of a product.
Run Solver, keep the solution, and then return to the Solver Parameters dialog box. Save the model in the range A14:A21.
Run solver again, create an Answer report, and then close the Solver Parameters dialog
davidap asked 2 years ago
Go to the Data tab and there must be Solver under Analyze group. If you don't find it, go to the search tab and type add-ins click it and click at the Solver Add-in checkbox and hit OK.
Now click at the Solver. Enter E10 at the Set Objective and make sure you select Min.
Enter B5:D5 in By Changing Variable Cells.
Click Add button of the right hand side. Enter E5 in the Cell Reference and then select >= and enter 11000 in Constraint. Then click Add.
Again at the same dialog, enter E10 in Cell Reference, <= and 925000 in Constraint. and click add.
Again, enter B5:D5 in cell reference, <= and 4000 in Constraint. and click add
After that, click on B5:D5 <= 4000 and then click on Options button which is at the bottom of right hand side. Click Ignore Integer Constraints checkbox. Hit OK.
Click at Solve botton which is at the bottom. After that, click at "Return to Solver Parameters Dialog" checkbox of Solver Results dialog box. And hit OK.
After that click on "Load/Save" button.
Enter A14:A21 range in the Load/Save Model.
Click the Solve button again. Click Answer under Reports section of the Solver Results dialog box. Hit OK and close the model.
That's all.