Gorilla Glass - Production plan with fixed costs

You produce gorilla glass used in cell phones. The iPad touch is one client of yours. You want to know how much to produce each month for the next 4 months. Your costs come from the following areas:

Holding cost, applied to inventory at the end of each month. This is $50/unit

Variable cost of production. This decreases over time as your technology improves:

Month 1: $70

Month 2: $60

Month 3: $50

Month 4: $40

Fixed cost to set up the machines for the iPad touch (this is what makes the problem different than Pigskin/Tesla). This is $9000 per setup and is incurred in each month in which production takes place.

You are required to meet demand, and your demand over the next four months is 150 in month 1, 140 in month 2, 130 in month 3, and 160 in month 4. You cannot produce more than 250 units in a given month. Your warehouse is not capacity constrained.

Write an optimization model in Excel that minimizes your total cost. You start with nothing in inventory. Remember, if you dont choose to produce in a given month, you dont have to pay the $9,000, but you also cant produce anything. Hint: because there is a fixed cost and a variable cost, you may want two sets of variables: one set stating how many to produce each month, and one set stating whether or not you should produce anything or not. This is a combination of pigskin/Tesla and binary optimization.

To validate your model:

Your total optimal cost should be $67,800. (although you may get this even if your model is wrong, so see next validation)

As an additional validation, if the setup cost changes to $10,000, the optimal cost should be $70,800

This latter validation is more robust and better: the former validation of $67,800 may work even if your model is wrong.

If you have $71,800 your model is wrong. You are not as close as you think. Go back to your model and think carefully about combining Telsa/pigskin with binary optimization.

Use the $10,000 value for the fixed cost as you go into CANVAS.

Note: For mac users, there could be a bug in both solver and open solver (we have not seen this bug in PCs). Recall that with constraints, we have a Left Hand Side (LHS) and a Right Hand Side (RHS). The LHS has the decision variables, whereas the RHS has the number. For instance, 3X + 4Y <= 6. Technically, Excel solver and open solver should work even if you put the decision variables on the RHS. For instance, optimization should still work if you formulate into Excel 3X <= 6 4Y. However, we have found that in at least one instance with a Mac, if a decision variable is on the RHS the optimization does not find a feasible solution, and it may not even tell the user that it couldnt find a feasible solution. To get around this (if youre working on a Mac) you can do one of a few things:

Ensure that the solutions Solver and Open Solver provide are feasible, by checking by eye. (Are any constraints violated?)

Reformulate the Excel cells so that on the LHS cells you have all the decision variables, and the RHS has no decision variables and only a number. You may need to insert columns and write a new formula to get all the decision variable on one side.

For this problem, all objective values (except the LP relaxation) are bigger than 50000, so if you get an objective value below that its a good sign your model is incorrect.