Problem solving a linear program using Excel - Operations Research Stack Exchange most recent 30 from or.stackexchange.com 2022-01-20T20:45:07Z https://or.stackexchange.com/feeds/question/3876 https://creativecommons.org/licenses/by-sa/4.0/rdf https://or.stackexchange.com/q/3876 3 Problem solving a linear program using Excel jeremy909 https://or.stackexchange.com/users/3333 2020-04-14T00:40:24Z 2020-04-14T20:28:49Z <p>The exercise is as follows:</p> <blockquote> <p>ACI has decided to put an order for golf shoes twice every year and expects to receive one shipment of <span class="math-container">$960$</span> pallets of shoes by the beginning of January and another shipment of <span class="math-container">$1250$</span> pallets of shoes by July. Each pallet contains <span class="math-container">$20$</span> 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 <span class="math-container">$19$</span> percent duty cost and the company’s goal to return <span class="math-container">$13$</span> 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. </p> <p>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 <span class="math-container">$\$0.36$</span> 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.</p> <p>(a) Write the model in Excel using color-coded and clearly-defined cells, then solve the model using Excel solver. </p> <p>(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. </p> <p>(c) Now, assume that CBSA storage facilities will not store more than <span class="math-container">$4000$</span> 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 <span class="math-container">$\$0.1$</span> 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. </p> <p>(d) Write the model described in (c) in Excel and use Excel Solver to solve it. </p> <p>(e) Describe your observations from the solutions.</p> <p><a href="https://i.stack.imgur.com/4oo4Z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4oo4Z.png" alt="demand + duty costs by month"></a></p> </blockquote> <p>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:</p> <p>Let <span class="math-container">$x_i =$</span> the number of cartons of shoes released in month <span class="math-container">$i$</span> (month <span class="math-container">$1$</span> is January, month <span class="math-container">$2$</span> is February, etc.). Then the constraints on my objective functions are as follows:</p> <p><span class="math-container">$\cdot \ x_1 + x_2 + x_3 + x_4 + x_5 + x_6 \leq 19200$</span></p> <p><span class="math-container">$\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$</span></p> <p><span class="math-container">$\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$</span></p> <p><span class="math-container">$\cdot \ x_i \in \mathbb{N_0}$</span></p> <p>Letting <span class="math-container">$Z =$</span> total cost:</p> <p><span class="math-container">$$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}))$$</span></p> <p>My objective is to minimize <span class="math-container">$Z$</span>. I will do this using a linear program solver in Microsoft Excel.</p> <p><a href="https://i.stack.imgur.com/ynthw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ynthw.png" alt="part a w/ formulas"></a></p> <p><a href="https://i.stack.imgur.com/jV4st.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jV4st.png" alt="part a solver"></a></p> <p><a href="https://i.stack.imgur.com/egb2v.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/egb2v.png" alt="part a solved"></a></p> <p>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).</p> <p>The new constraints on our model are as follows:</p> <p><span class="math-container">$\cdot \ 19200 - x_1 \leq 4000$</span></p> <p><span class="math-container">$\cdot \ 44200 - \sum_{i=1}^7x_i \leq 4000$</span>.</p> <p>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.</p> <p>Let <span class="math-container">$d_i =$</span> demand in month <span class="math-container">$i$</span>. Let <span class="math-container">$Y =$</span> the new cost. Then</p> <p><span class="math-container">$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)$</span></p> <p><span class="math-container">$= 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))$</span></p> <p><a href="https://i.stack.imgur.com/ZQzsO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZQzsO.png" alt="enter image description here"></a> <a href="https://i.stack.imgur.com/X1XVm.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/X1XVm.png" alt="![enter image description here"></a></p> <p>And finally, here is where I have an error:</p> <p><a href="https://i.stack.imgur.com/7m4qf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/7m4qf.png" alt="enter image description here"></a></p> <p>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.</p> https://or.stackexchange.com/questions/3876/-/3884#3884 4 Answer by prubin for Problem solving a linear program using Excel prubin https://or.stackexchange.com/users/67 2020-04-14T20:28:49Z 2020-04-14T20:28:49Z <p>Try to come up with a feasible (not necessarily optimal) solution, plug it into cells F2 to F13, and see if any of your constraints are violated. If so, and assuming your solution is really feasible, those are the constraints to fix. If not, meaning all constraints are satisfied, then possibly there is an issue with how the model was plugged into Solver.</p>