Case Study Mars
| | | | | | | Re: | Mars Case Model Summary |
-------------------------------------------------
-------------------------------------------------
Base Case
We assume that all items can be over-supplied and no item will be under-supplied.
Then the decision of whether to accept a bid is a series of binary values (1/0). We use Solver to change these binary values to get the lowest cost $4,169. -------------------------------------------------
Adding Constraints of Number of Suppliers (a)
In addition to the constraint in the base case (supply > demand), we add a row of binary numbers (Row 20) to calculate the number of winning suppliers. We set constraints in Solver so that the new binary data will be a) less than or equal to the winning bids of each supplier (row 34) and b) larger than or equal to the winning bids divided by 7 (row 35). The constraining range enables us to count “1” if a supplier is a winning supplier (regardless of the number of successful bids the supplier has). And count “0” if a supplier is not a winning supplier (because the binary value needs to fall into [0, 0]).
We then set the maximum and minimum number of suppliers in the model and then use Solver again to change (1) whether a bid is accepted (1/0) and (2) whether a supplier is a winning supplier (1/0), with all the constraints mentioned above, to get the lowest cost.
If we set the maximum at 5 and minimum at 3, the answer will not change.
If we set the maximum at 3 and minimum at 1, the new lowest cost will be $4,639, $470 more than the base case. -------------------------------------------------
Adding Constraints of Maximum Amount Allocated to Any Supplier (b)
In addition to the constraint in the base case (supply > demand), we add a table to calculate the amount allocated to each supplier. In solver, we want to constrain the amount to be less or equal to $2200. Then the new lowest cost is $4,470, $301 more than the base
-------------------------------------------------
-------------------------------------------------
Base Case
We assume that all items can be over-supplied and no item will be under-supplied.
Then the decision of whether to accept a bid is a series of binary values (1/0). We use Solver to change these binary values to get the lowest cost $4,169. -------------------------------------------------
Adding Constraints of Number of Suppliers (a)
In addition to the constraint in the base case (supply > demand), we add a row of binary numbers (Row 20) to calculate the number of winning suppliers. We set constraints in Solver so that the new binary data will be a) less than or equal to the winning bids of each supplier (row 34) and b) larger than or equal to the winning bids divided by 7 (row 35). The constraining range enables us to count “1” if a supplier is a winning supplier (regardless of the number of successful bids the supplier has). And count “0” if a supplier is not a winning supplier (because the binary value needs to fall into [0, 0]).
We then set the maximum and minimum number of suppliers in the model and then use Solver again to change (1) whether a bid is accepted (1/0) and (2) whether a supplier is a winning supplier (1/0), with all the constraints mentioned above, to get the lowest cost.
If we set the maximum at 5 and minimum at 3, the answer will not change.
If we set the maximum at 3 and minimum at 1, the new lowest cost will be $4,639, $470 more than the base case. -------------------------------------------------
Adding Constraints of Maximum Amount Allocated to Any Supplier (b)
In addition to the constraint in the base case (supply > demand), we add a table to calculate the amount allocated to each supplier. In solver, we want to constrain the amount to be less or equal to $2200. Then the new lowest cost is $4,470, $301 more than the base