First of all, if you think this problem belongs on a different stack exchange, I am happy to move it. The exercise is as follows:
ACI has decided to put an order for golf shoes twice every year and expects to receive one shipment of $960$ pallets of shoes by the beginning of January and another shipment of $1250$ pallets of shoes by July. Each pallet contains $20$ cartons of shoes. Upon arrival to Canada, CBSA takes custody of the shoes. The company should decide how many cartons of shoes to release each month from Customs to supply all their demand while maintaining the lowest cost. For the amount of shoes that are released from Customs, the company needs to pay their duty cost. Because of the time value of money, it costs the company more to clear the shoes sooner than it does to release them later. So, the company might decide to delay releasing some of the shoes by a few months. All the shoes however should be released by the end of December. If we consider the value of shoes, the $19$ percent duty cost and the company’s goal to return $13$ percent on their investment, the cost of releasing one carton in each month along with the expected amount of demand for each month is listed in the table. Note that the duty cost and the return rate are already considered and you do not need to account for them.
The company can choose to delay releasing some of the shoes and keep them in the CBSA storage facilities. In this case, the company will not have to pay duty until the time the shoes are released but will have to pay $\$0.36$ per month for each carton that is being held in the storage facilities. Formulate a linear programming model for the problem that minimizes the company’s overall cost. Define the three linear programming elements of the model and write down the formulation.
(a) Write the model in Excel using color-coded and clearly-defined cells, then solve the model using Excel solver.
(b) Observe the solutions, and very briefly describe your observations. This can include a brief description of what the company should do in plain words.
(c) Now, assume that CBSA storage facilities will not store more than $4000$ cartons at a time. The company however, has an internal storage capacity that can store the rest of the shoes that are not sold and are already released from Customs. There is a $\$0.1$ cost for storing one carton of shoes for one month internally. Note that in order for the company to store the shoes in the internal storage facilities, the shoes must have been released (i.e. the duty cost must have been paid). Write a linear programming model for this problem.
(d) Write the model described in (c) in Excel and use Excel Solver to solve it.
(e) Describe your observations from the solutions.
I am having explicit trouble with my solution for (d) as well as suspicions about my solution for part (a). Here's what I did:
Let $x_i = $ the number of cartons of shoes released in month $i$ (month $1$ is January, month $2$ is February, etc.). Then the constraints on my objective functions are as follows:
$\cdot \ x_1 + x_2 + x_3 + x_4 + x_5 + x_6 \leq 19200$
$\cdot \ x_1 + x_2 + x_3 + x_4 + x_5 + x_6 + x_7 + x_8 + x_9 + x_{10} + x_{11} + x_{12} = 19200 + 25000 = 44200 $
$\cdot \ x_1 \geq 7000, \ x_2 \geq 6600, \ x_3 \geq 2800, \ x_4 \geq 1200, \ x_5 \geq 0, \ x_6 \geq 1600, \ x_7 \geq 2800, \ x_8 \geq 4000, \ x_9 \geq 4400, \ x_{10} \geq 4400, \ x_{11} \geq 4600, \ x_{12} \geq 4800$
$\cdot \ x_i \in \mathbb{N_0}$
Letting $Z =$ total cost:
$$Z = 1824x_1 + 1787.52x_2 + 1751.77x_3 + 1716.73x_4 + 1682.4x_5 + 1648.75x_6 + 1824x_7 + 1787.52x_8 + 1751.7696x_9 + 1716.73421x_{10} + 1682.39952x_{11} + 1648.75153x_{12} + (19200-x_1)(.36) + (19200 - (x_1 + x_2))(.36) + \cdot \cdot \cdot + (19200 - (x_1 + x_2 + x_3 + x_4 + x_5 + x_6))(.36) + (44200 - (x_1 + x_2 + x_3 + x_4 + x_5 + x_6 + x_7))(.36) + \cdot \cdot \cdot + (44200 - \left(\sum_{i=1}^{12}x_i\right))(.36) \\ = 1824x_1 + 1787.52x_2 + 1751.77x_3 + 1716.73x_4 + 1682.4x_5 + 1648.75x_6 + 1824x_7 + 1787.52x_8 + 1751.7696x_9 + 1716.73421x_{10} + 1682.39952x_{11} + 1648.75153x_{12} + .36((12*19200 + 6*25000) - (12x_1 + 11x_2 + 10x_3 + 9x_4 + 8x_5 + 7x_6 + 6x_7 + 5x_8 + 4x_9 + 3x_{10} + 2x_{11} + x_{12}))$$
My objective is to minimize $Z$. I will do this using a linear program solver in Microsoft Excel.
The reason I am suspicious of this solution is that it seems trivial. It's not unbelievable, but it does seem slightly odd that this would be the answer. But okay, on to part (c).
The new constraints on our model are as follows:
$\cdot \ 19200 - x_1 \leq 4000$
$\cdot \ 44200 - \sum_{i=1}^7x_i \leq 4000$.
I believe these are sufficient to represent the new scenario, because if we don't have overflow in the CBSA storage in January and July, then we won't have overflow at any other time because those are the only times we have new supply.
Let $d_i =$ demand in month $i$. Let $Y =$ the new cost. Then
$Y = Z + .1(x_1 - d_1) + .1(x_1 + x_2 - (d_1 + d_2)) + ... + .1(\sum_{i=1}^{12}x_i - \sum_{i=1}^{12}d_i)$
$= 1824x_1 + 1787.52x_2 + 1751.77x_3 + 1716.73x_4 + 1682.4x_5 + 1648.75x_6 + 1824x_7 + 1787.52x_8 + 1751.7696x_9 + 1716.73421x_{10} + 1682.39952x_{11} + 1648.75153x_{12} + .36((12*19200 + 6*25000) - (12x_1 + 11x_2 + 10x_3 + 9x_4 + 8x_5 + 7x_6 + 6x_7 + 5x_8 + 4x_9 + 3x_{10} + 2x_{11} + x_{12})) + .1(12(x_1 - 7000) + 11(x_2 - 6600) + 10(x_3 - 2800) + 9(x_4 - 1200) +8x_5 + 7(x_6 - 1600) + 6(x_7 - 2800) + 5(x_8 - 4000) + 4(x_9 - 4400) + 3(x_{10} - 4400) + 2(x_{11} - 4600) + (x_{12} - 4800))$
And finally, here is where I have an error:
So I suppose I must have made an error somewhere. I cannot find it. I know the problem is extremely long - I guess that's the nature of these linear programming problems - so I thank you infinitely for the help.
The problem is the constraint F2:F13 >= C2:C13. You do not need to release the supply each month because you can also use the internal storage facility. After a few months the CBSA is empty, that is why your problem is infeasible.
This formulation becomes easier if you add an extra nonnegative decision variable $y_i$, being the inventory level at the end of month $i$, along with an equality constraint that relates $y_i$ to $d_i$, $x_i$ and $y_{i-1}$.