{"text_format":"3","workbook":"xls","n_sheets":8,"sheets":["Summary","Finance","Budget1","Budget2","Invent1","Invent2","Lockbox","Open"],"not_rendered":{"defined_names":648},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Summary","index":0,"n_rows":28,"n_cols":1,"rows_kept":28,"flags":["formulas_cached_only"]}
["Finance Examples"]
[]
["On each example worksheet, read the comments at the bottom of the sheet, then"]
["click Tools Solver... to examine the decision variables, constraints, and objective."]
["To find the optimal solution, click the Solve button."]
[]
["This workbook contains five example models from the area of corporate finance:"]
[]
["* A working capital management model"]
["* A capital budgeting model (in two versions)"]
["* An inventory policy model (in two versions)"]
["* A lockbox location model"]
["* A plant opening/closing model"]
[]
["In the working capital management model, Finance, we look at how to invest money"]
["in 1-month, 3-month and 6-month CDs, while meeting cash requirements in each month."]
[]
["In the capital budgeting models, Budget1 and Budget2, a company wants to"]
["maximize the Net Present Value of a combination of investment opportunities."]
[]
["In the inventory policy models, Invent1 and Invent2, we compare the EOQ "]
["(Economic Order Quantity) with the optimal solution determined by the Solver."]
[]
["In the lockbox location model, Lockbox, a firm needs to decide where to open "]
["lockboxes to minimize the 'float', i.e., the lost interest, due to mail delay."]
[]
["In the capacity planning model, Open, we determine whether to open or close facilities"]
["such as plants and warehouses."]

{"sheet":"Finance","index":1,"n_rows":56,"n_cols":8,"rows_kept":56,"flags":["formulas_cached_only"]}
[" Working Capital Management."]
["Determine how to invest excess cash in 1-month, 3-month and 6-month CDs so as to"]
["maximize interest income while meeting company cash requirements (plus safety margin)."]
[]
[null,"Yield","Term","Price","Purchase CDs in months:"]
["1-mo CDs:",{"v":0.01,"d":"1.0%"},1,{"v":2000,"d":"$2,000"},"1, 2, 3, 4, 5 and 6",null,null,"Interest"]
["3-mo CDs:",{"v":0.04,"d":"4.0%"},3,{"v":3000,"d":"$3,000"},"1 and 4",null,null," Earned:"]
["6-mo CDs:",{"v":0.09,"d":"9.0%"},6,{"v":5000,"d":"$5,000"},"1",null,"Total",{"v":0,"d":"$0.00"}]
[]
["Month:","Month 1","Month 2","Month 3","Month 4","Month 5","Month 6","End"]
["Init Cash:",{"v":400000,"d":"$400,000"},{"v":325000,"d":"$325,000"},{"v":335000,"d":"$335,000"},{"v":355000,"d":"$355,000"},{"v":275000,"d":"$275,000"},{"v":225000,"d":"$225,000"},{"v":240000,"d":"$240,000"}]
["Matur CDs:",null,{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Interest:",null,{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["1-mo CDs:",{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"}]
["3-mo CDs:",{"v":0,"d":"0.00"},null,null,{"v":0,"d":"0.00"}]
["6-mo CDs:",{"v":0,"d":"0.00"}]
["Cash Uses:",{"v":75000,"d":"$75,000"},{"v":-10000,"d":"($10,000)"},{"v":-20000,"d":"($20,000)"},{"v":80000,"d":"$80,000"},{"v":50000,"d":"$50,000"},{"v":-15000,"d":"($15,000)"},{"v":60000,"d":"$60,000"}]
["End Cash:",{"v":325000,"d":"$325,000"},{"v":335000,"d":"$335,000"},{"v":355000,"d":"$355,000"},{"v":275000,"d":"$275,000"},{"v":225000,"d":"$225,000"},{"v":240000,"d":"$240,000"},{"v":180000,"d":"$180,000"}]
[]
[]
["Problem"]
["A company wants to invest excess cash in 1-month, 3-month and 6-month Certificates of Deposit (CDs).  The company"]
["has expected uses of cash in the next 6 months, and it wants to make sure that the principal and interest from maturing "]
["CDs meet the requirements for cash plus a safety margin for each month. For simplicity we assume that 3-month CDs "]
["can only be bought at the start of months 1 and 4, and 6-month CDs can only be bought in month 1. Initial cash available"]
["is $400,000.  How many and what kind of CDs should the company buy in order to maximize the earned interest, and "]
["meet the safety margin of $100,000 each month?"]
[]
["Solution"]
["The characteristics of the 3 different CDs are given in cells A5 through F9."]
["1) The variables are the number of CDs to buy in each month.  The variable cells are given names One_month_CDs, "]
["Three_month_CD1 and Three_Month_CD2, and Six_month_CDs.  There are 6+2+1 = 9 variables."]
["2) The constraints are the limitations on the formulas in this model. First, there is the safety margin requirement for each "]
["month. This gives"]
[null,"Monthly_cash >= 100000"]
["Then there are the logical constraints on the number of CDs to be bought. It is not possible to buy half or other fractions, "]
["or negative amounts of CDs. We can rule out negative amounts with the Assume Non-Negative option. This gives"]
[null,"One_month_CDs = integer"]
[null,"Three_month_CD1 = integer"]
[null,"Three_month_CD2 = integer"]
[null,"Six_month_CDs = integer"]
["3) The objective is to maximize earned interest. This is calculated by multiplying the number of CDs bought of each kind "]
["by the interest earned for each CD. This is given the name Total_interest."]
[]
["Remarks"]
["This is a good example of how the solver can help you make intelligent decisions in investments. Before solving the "]
["model with the Solver try to find a solution by hand. What interest is earned? "]
[]
["The time required by the solver to solve this model can be considerable. Integer problems are very difficult to solve. In a "]
["model like this it would be possible to change the variables to the amount of money to be invested. This would give a "]
["'normal' problem. We chose this form since often it is not possible to invest an arbitrary amount of money in a CD."]
[]
["In some situations it is not desirable to use integer constraints. When an expected solution of a model yields a value"]
["that is 2034.86, for example, it is safe to assume we can round this number to 2035. If the value is 0.34 however, it is "]
["not safe to assume we can round this number. In each model, you have to tradeoff precision vs solution time to make a"]
["decision whether or not to use integer variables. "]

{"sheet":"Budget1","index":2,"n_rows":72,"n_cols":12,"rows_kept":72,"flags":["formulas_cached_only"]}
["Capital Budgeting 1"]
["A company wants to maximize the combined Net Present Value (NPV) of a maximum of 6 opportunities"]
["that require up to 6 yearly investments. In each year there is only a limited amount of money available."]
["All amounts are give in millions of dollars. Interest rate is",{"$nulls":4},{"v":0.05,"d":"5%"}]
[]
["Expected Investment Cash Flows and Net Present Value"]
[null,"Opp. 1","Opp. 2","Opp. 3","Opp. 4","Opp. 5","Opp. 6"]
["Year 1",{"v":-5,"d":"($5.00)"},{"v":-9,"d":"($9.00)"},{"v":-12,"d":"($12.00)"},{"v":-7,"d":"($7.00)"},{"v":-20,"d":"($20.00)"},{"v":-18,"d":"($18.00)"}]
["Year 2",{"v":-6,"d":"($6.00)"},{"v":-6,"d":"($6.00)"},{"v":-10,"d":"($10.00)"},{"v":-5,"d":"($5.00)"},{"v":6,"d":"$6.00"},{"v":-15,"d":"($15.00)"}]
["Year 3",{"v":-16,"d":"($16.00)"},{"v":6.1,"d":"$6.10"},{"v":-5,"d":"($5.00)"},{"v":-20,"d":"($20.00)"},{"v":6,"d":"$6.00"},{"v":-10,"d":"($10.00)"}]
["Year 4",{"v":12,"d":"$12.00"},{"v":4,"d":"$4.00"},{"v":-5,"d":"($5.00)"},{"v":-10,"d":"($10.00)"},{"v":6,"d":"$6.00"},{"v":-10,"d":"($10.00)"}]
["Year 5",{"v":14,"d":"$14.00"},{"v":5,"d":"$5.00"},{"v":25,"d":"$25.00"},{"v":-15,"d":"($15.00)"},{"v":6,"d":"$6.00"},{"v":35,"d":"$35.00"}]
["Year 6",{"v":15,"d":"$15.00"},{"v":5,"d":"$5.00"},{"v":15,"d":"$15.00"},{"v":75,"d":"$75.00"},{"v":6,"d":"$6.00"},{"v":35,"d":"$35.00"}]
["NPV",{"v":8.009543768453742,"d":"$8.01"},{"v":2.1953216235580917,"d":"$2.20"},{"v":1.8498185314921671,"d":"$1.85"},{"v":7.5076714732149155,"d":"$7.51"},{"v":5.692247641699917,"d":"$5.69"},{"v":5.927254655716555,"d":"$5.93"}]
[]
["Percentage to invest "]
[null,{"v":0,"d":"0%"},{"v":0,"d":"0%"},{"v":0,"d":"0%"},{"v":0,"d":"0%"},{"v":0,"d":"0%"},{"v":0,"d":"0%"}]
[]
["Cash Flow",{"$nulls":7},"Total",null,"Budget","Surplus"]
["Year 1",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":45,"d":"$45.00"},{"v":45,"d":"$45.00"}]
["Year 2",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":30,"d":"$30.00"},{"v":30,"d":"$30.00"}]
["Year 3",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":20,"d":"$20.00"},{"v":20,"d":"$20.00"}]
["Year 4",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
["Year 5",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
["Year 6",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
[]
["Revenue",{"$nulls":7},"Total"]
["NPV",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"}]
[]
[]
["Problem"]
["A company has six different opportunities to invest money.  Each opportunity requires a certain investment over a "]
["period of 6 years or less. The company wants to invest in those opportunities that maximize the combined Net"]
["Present Value. It also has an investment budget that needs to be met for each year. "]
["We assume that it is possible to invest partially in an opportunity. For instance, if the company decides to invest "]
["50% of the required amount in an opportunity, the return will also be 50%. How should the company invest?"]
[]
["Solution"]
["1) The variables are the cells in the worksheet that we want to change. In this model, they are the percentages that "]
["are invested in each opportunity. By changing these values, the Net Present Value of the combined investments also"]
["changes.  The variables in this model are given the name investments in the worksheet."]
["2) The constraints are the limitations we have when changing the variables.  It is not possible to invest more than "]
["100% in an opportunity. This gives:"]
[null,"investments <=1"]
["We can tell the Solver not to invest a negative amount of money, using the Assume Non-Negative option."]
["It is a common mistake to forget these kinds of logical constraints."]
["The last constraint is given by the fact that the company has a budget. The sum of the expected cash flow of the "]
["investments and the budget must be positive. This leads to:"]
[null,"Monthly_surplus >= 0"]
["3) The objective is to maximize the NPV which is given the name Total_NPV on the worksheet. This amount is "]
["calculated by adding the NPV's of each investment, multiplied by the percentages that are invested in them."]
[]
["Remarks"]
["When creating this model we start out by putting the characteristics of the 6 opportunities on the worksheet. In this "]
["worksheet we decided to lay out the opportunities (horizontally) vs. the years (vertically). It would be perfectly fine"]
["to switch this around and have different colums for different years."]
[]
["We then assign cells to the variables we are using. In this case we used 6 cells for 6 different investments and "]
["defined them as investments in the worksheet. When dealing with a linear model as this one, it does not matter "]
["what the initial values of these variables are. In non-linear models, however, it is very important to give the "]
["variables an initial value that you expect to be close to the solution. Therefor, it is good practice to give the "]
["variables reasonable starting values. In this model, 50% for instance."]
[]
["After the variables have been created, we must put the constraints on the worksheet. Normally, no extra work is "]
["necessary for logical constraints. We simply tell the solver to keep the investments between 0 and 100% when "]
["defining the model.  The other constraints do require some work. In this model we want the sum of the expected "]
["cash-flow of the investments and the yearly budget to be positive. The easiest way to do this is to create cells that"]
["calculate this sum and tell the Solver that the values of these cells must be positive. In the worksheet these cells are "]
["defined as Monthly_surplus. Finally, we create a cell that calculates the combined NPV of all investments. "]
[]
["You may  notice that we also created cells that calculate the NPV for each individual investment. This is not strictly "]
["necessary, but it makes the model easier to read and understand, and it provides an easy way of calculating NPV."]

{"sheet":"Budget2","index":3,"n_rows":58,"n_cols":12,"rows_kept":58,"flags":["formulas_cached_only"]}
["Capital Budgeting 2"]
["A company wants to maximize the combined Net Present Value (NPV) of a maximum of 6 opportunities"]
["that require up to 6 yearly investments. In each year there is only a limited amount of money available."]
["All amounts are give in millions of dollars. Interest rate is",{"$nulls":4},{"v":0.05,"d":"5%"}]
[]
["Expected Investment Cash Flows and Net Present Value"]
[null,"Opp. 1","Opp. 2","Opp. 3","Opp. 4","Opp. 5","Opp. 6"]
["Year 1",{"v":-5,"d":"($5.00)"},{"v":-9,"d":"($9.00)"},{"v":-12,"d":"($12.00)"},{"v":-7,"d":"($7.00)"},{"v":-20,"d":"($20.00)"},{"v":-18,"d":"($18.00)"}]
["Year 2",{"v":-6,"d":"($6.00)"},{"v":-6,"d":"($6.00)"},{"v":-10,"d":"($10.00)"},{"v":-5,"d":"($5.00)"},{"v":6,"d":"$6.00"},{"v":-15,"d":"($15.00)"}]
["Year 3",{"v":-16,"d":"($16.00)"},{"v":6.1,"d":"$6.10"},{"v":-5,"d":"($5.00)"},{"v":-20,"d":"($20.00)"},{"v":6,"d":"$6.00"},{"v":-10,"d":"($10.00)"}]
["Year 4",{"v":12,"d":"$12.00"},{"v":4,"d":"$4.00"},{"v":-5,"d":"($5.00)"},{"v":-10,"d":"($10.00)"},{"v":6,"d":"$6.00"},{"v":-10,"d":"($10.00)"}]
["Year 5",{"v":14,"d":"$14.00"},{"v":5,"d":"$5.00"},{"v":25,"d":"$25.00"},{"v":-15,"d":"($15.00)"},{"v":6,"d":"$6.00"},{"v":35,"d":"$35.00"}]
["Year 6",{"v":15,"d":"$15.00"},{"v":5,"d":"$5.00"},{"v":15,"d":"$15.00"},{"v":75,"d":"$75.00"},{"v":6,"d":"$6.00"},{"v":35,"d":"$35.00"}]
["NPV",{"v":8.009543768453742,"d":"$8.01"},{"v":2.1953216235580917,"d":"$2.20"},{"v":1.8498185314921671,"d":"$1.85"},{"v":7.5076714732149155,"d":"$7.51"},{"v":5.692247641699917,"d":"$5.69"},{"v":5.927254655716555,"d":"$5.93"}]
[]
["Decision to invest "]
[null,{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"},{"v":0,"d":"0.00"}]
[]
["Cash Flow",{"$nulls":7},"Total",null,"Budget","Surplus"]
["Year 1",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":45,"d":"$45.00"},{"v":45,"d":"$45.00"}]
["Year 2",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":30,"d":"$30.00"},{"v":30,"d":"$30.00"}]
["Year 3",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":20,"d":"$20.00"},{"v":20,"d":"$20.00"}]
["Year 4",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
["Year 5",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
["Year 6",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"}]
[]
["Revenue",{"$nulls":7},"Total"]
["NPV",{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},{"v":0,"d":"$0.00"},null,{"v":0,"d":"$0.00"}]
[]
[]
["Problem"]
["In this model we extend the problem we solved in Budget1. Once again, a company needs to make a decision "]
["how to invest in 6 different opportunities. This time however, the company can only go with an investment 100%"]
["or ignore the opportunity and thus invest 0%."]
[]
["Solution"]
["The solution is almost identical to the one in Budget1. The variables and objective have remained the same. "]
["The only difference is in the logical constraints. In Budget1 the investments needed to be between 0 and 100%."]
["Now they are required to be 0 or 100% (or 0 or 1). "]
["These kinds of (binary) decision variables often occur in models. They come up when decisions have to be made, "]
["such as: open or closed, yes or no, buy or not buy, etc. The Solver allows you to use these kind of variables by"]
["entering a constraint that says the variables must be binary integer. In Budget1 we used:"]
[null,"investments <= 1 and"]
[null,"investments >= 0 via the Assume Non-Negative option."]
["In place of these constraints, we can tell the Solver to use binary integer variables, with:"]
[null,"Investment_decisions = binary"]
["This will force the variables to be either 0 or 1."]
[]
["Remarks"]
["By making the variables 0 or 1, there is less flexibility in the investments. In mathematical terms, we have "]
["tightened the constraints. Because of this we can expect our goal, the total NPV, to be less than in Budget1. "]
["Compare the 2 models and make sure this is indeed the case."]
[]
["You might be surprised by the investment decisions of this model compared to the solution of Budget1. In the"]
["previous model we were told to invest 100% in opportunity 2. In the second model we are advised not to invest in "]
["opportunity 2 at all! The explanation is that we have a limited budget. Because the Solver can only choose between "]
["0 or 1 in the variables, this can lead to surprising results. It is important to realize that simply 'rounding' the results "]
["of the first model clearly does not guarantee an optimal (or even feasible!) solution."]

{"sheet":"Invent1","index":4,"n_rows":55,"n_cols":7,"rows_kept":55,"flags":["formulas_cached_only"]}
["Inventory Policy 1"]
["What is the best ordering policy for a warehouse to minimize cost, while meeting demands?"]
["The warehouse has a limited storage capacity of 50000 cubic meters (m3)."]
[]
[null,"Holding Cost","Storage Space per unit (m3)","Demand per month","Ordering cost per order",null,"Storage space available (m3)"]
["Product 1",{"v":25,"d":"$25"},440,200,{"v":50,"d":"$50"},null,50000]
["Product 2",{"v":20,"d":"$20"},850,325,{"v":50,"d":"$50"}]
["Product 3",{"v":30,"d":"$30"},1260,400,{"v":50,"d":"$50"}]
["Product 4",{"v":15,"d":"$15"},950,150,{"v":50,"d":"$50"}]
[]
["Quantity to order each month"]
[null,null,"EOQ",null,"Cost",null,"Space  used (m3)"]
["Product 1",10,28.284271247461902,null,{"v":1125,"d":"$1,125"},null,2200]
["Product 2",10,40.311288741492746,null,{"v":1725,"d":"$1,725"},null,4250]
["Product 3",10,36.51483716701107,null,{"v":2150,"d":"$2,150"},null,6300]
["Product 4",10,31.622776601683793,null,{"v":825,"d":"$825"},null,4750]
[{"$nulls":3},"Total",{"v":5825,"d":"$5,825"},null,17500]
[]
[]
["Problem"]
["A warehouse sells 4 products with a different demand for each product. Each product has a different holding cost "]
["and requires a certain amount of space. What should the ordering policy for the warehouse be, given its limited "]
["storage capacity?"]
[]
["Solution"]
["There is an analytical solution for this problem, which is known as the Economic Order Quantity (EOQ) and is "]
["given by the following formula: q = SQRT(2 k d/h), where q is the quantity to order, k is the cost to place an order, "]
["d is the demand and h is the holding cost of the product. Unfortunately, this formula doesn't always work in the real "]
["world.  Demand usually fluctuates, ordering time is variable, and other factors arise to further complicate the "]
["problem. In this model we have one such factor, a limited storage space."]
[]
["1) The variables are the amounts to order each month for each product. These are defined as Quantities in this "]
["worksheet. By changing these variables we change the total cost."]
["2) The constraints are very simple. We have a logical constraint and the storage capacity constraint. This gives"]
[null,"Quantities >= 0 via the Assume Non-Negative option"]
[null,"Space_used <= Available_space"]
["If the latter constraint wasn’t present, the solution to the problem could be calculated by the formula given above."]
["3) The objective is to minimize the total cost, which is defined as Total_cost. It is calculated by adding the "]
["individual costs for each product. Those costs are calculated by using the formula:"]
["Cost = h q /2 + k d /q, where h, q, k and d are as above."]
["This formula is easy to understand if we realize that the average inventory level is q/2 and the average number of "]
["orders is d/q."]
[]
["Remarks"]
["In this worksheet we have also calculated the EOQ with the formula given above. Check to see that when you"]
["increase the storage capacity and thus relax that constraint, the answers found by the Solver will approach the"]
["analytic solution."]
[]
["This model is an example of a non-linear problem, as can easily be seen by looking at the cost formula. Whereas in "]
["linear problems it does not matter what are starting values for the variables are, it can be very important to have "]
["reasonable starting values in non-linear problems. In this model it is not possible to start with a quantity of 0, since "]
["this would cause an error in the calculation of the cost."]
[]
["Please see for yourself that the Solver will still find the correct answer, even when the starting values are close "]
["(but not equal to) zero. "]

{"sheet":"Invent2","index":5,"n_rows":43,"n_cols":7,"rows_kept":43,"flags":["formulas_cached_only"]}
["Inventory Policy 2"]
["What is the best ordering policy for a warehouse to minimize cost, while meeting demands?"]
["The warehouse has a limited storage capacity of 50000 cubic meters (m3) and a budget of $30,000."]
[]
[null,"Holding Cost","Storage Space per unit (m3)","Demand per month","Ordering cost per order",null,"Price per unit"]
["Product 1",{"v":25,"d":"$25"},440,200,{"v":50,"d":"$50"},null,{"v":200,"d":"$200"}]
["Product 2",{"v":20,"d":"$20"},850,325,{"v":50,"d":"$50"},null,{"v":300,"d":"$300"}]
["Product 3",{"v":30,"d":"$30"},1260,400,{"v":50,"d":"$50"},null,{"v":275,"d":"$275"}]
["Product 4",{"v":15,"d":"$15"},950,150,{"v":50,"d":"$50"},null,{"v":400,"d":"$400"}]
["Storage Capacity",50000,"Budget",{"v":30000,"d":"$30,000"}]
[]
["Quantity to order each month",{"$nulls":3},"Cost of holding",null,"Space"]
[null,null,"EOQ",null,"and ordering",null,"used (m3)"]
["Product 1",10,28.284271247461902,null,{"v":1125,"d":"$1,125"},null,2200]
["Product 2",10,40.311288741492746,null,{"v":1725,"d":"$1,725"},null,4250]
["Product 3",10,36.51483716701107,null,{"v":2150,"d":"$2,150"},null,6300]
["Product 4",10,31.622776601683793,null,{"v":825,"d":"$825"},null,4750]
["Cost of products",{"v":11750,"d":"$11,750"},null,"Total",{"v":5825,"d":"$5,825"},null,17500]
[]
[]
["Problem"]
["This model continues to build on the first inventory policy model. We expand the model by giving the warehouse a "]
["budget for buying new products. In other words:  A warehouse sells 4 products with a different demand for each "]
["product. Each product has a different holding cost and requires a certain amount of space. What should the ordering "]
["policy for the warehouse be, given its limited storage capacity and limited budget?"]
[]
["Solution"]
["The variables are exactly the same as in the first model. So is the objective, and the way it is calculated. The "]
["difference is that we have an extra constraint which keeps us within the budget. This new constraint is expressed as:"]
[null,"Cost_of_products <= Available_money   and we also have"]
[null,"Space_used <= Available_space   as before"]
["We still have Quantities >= 0 via the Assume Non-Negative option.  This time, we also require integer quantities:"]
[null,"Quantities = integer"]
[]
["Remarks"]
["Once again, we have calculated the EOQs as discussed in the first inventory policy model. If we would give a "]
["unlimited budget and unlimited storage space, the Solver would find exactly those values. "]
[]
["There is one more change we made in this model compared to the one on worksheet Invent1. This time we "]
["required the variables to be integers. Whether this is a valid assumption would depend completely on the type of "]
["product that is dealt with. If a model is trying to determine how many cars, airplanes or other such articles to buy, it "]
["could be very important to use integer variables. If the model, on the other hand, is giving an indication how much "]
["sugar to buy, for example, it would not be appropriate to use integer variables."]

{"sheet":"Lockbox","index":6,"n_rows":98,"n_cols":8,"rows_kept":98,"flags":["formulas_cached_only"]}
["Lockbox Location"]
["A company is considering opening lockboxes in several cities to reduce the 'float' (lost interest) waiting for"]
["mailed payments. In what cities should lockboxes be opened to minimize lost interest and operating cost?"]
["Each area can send payments to only one city.The interest rate is",{"$nulls":3},{"v":0.06,"d":"6%"}]
[]
["Areas concerned"]
[null,"Northwest","North","Northeast","Southwest","South","Southeast"]
["Daily  Payments",{"v":325000,"d":"$325,000"},{"v":475000,"d":"$475,000"},{"v":300000,"d":"$300,000"},{"v":275000,"d":"$275,000"},{"v":385000,"d":"$385,000"},{"v":350000,"d":"$350,000"}]
[]
["Cities to be considered"]
[null,"Seattle","Chicago","New York","L.A.","Dallas","Miami"]
["Operating cost",{"v":55000,"d":"$55,000"},{"v":50000,"d":"$50,000"},{"v":60000,"d":"$60,000"},{"v":53000,"d":"$53,000"},{"v":58000,"d":"$58,000"},{"v":55000,"d":"$55,000"}]
[]
["Average number of days from mailing to clearing of payment"]
[null,"Seattle","Chicago","New York","L.A.","Dallas","Miami"]
["Northwest",2,5,5,4,6,8]
["North",4,2,4,6,6,6]
["Northeast",5,5,2,8,7,5]
["Southwest",4,6,8,2,4,5]
["South",6,6,6,4,2,5]
["Southeast",8,8,5,5,5,2]
[]
["Assignments of areas to cities (1=yes, 0=no)"]
[null,"Seattle","Chicago","New York","L.A.","Dallas","Miami","Total"]
["Northwest",0,0,0,0,0,0,0]
["North",0,0,0,0,0,0,0]
["Northeast",0,0,0,0,0,0,0]
["Southwest",0,0,0,0,0,0,0]
["South",0,0,0,0,0,0,0]
["Southeast",0,0,0,0,0,0,0]
[]
["Lockbox Decision",0,0,0,0,0,0]
[]
["Lost interest","Seattle","Chicago","New York","L.A.","Dallas","Miami"]
["Northwest",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["North",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Northeast",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Southwest",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["South",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Southeast",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
[]
[{"$nulls":4},"Total of Lost Interest",null,{"v":0,"d":"$0"}]
[{"$nulls":4},"Operating Cost",null,{"v":0,"d":"$0"}]
[{"$nulls":4},"Total Cost",null,{"v":0,"d":"$0"}]
[]
[]
["Problem"]
["A company wants to reduce lost interest ('float') due to mail delay, for the payments it receives every day. It is "]
["considering opening lockboxes in 6 different cities. Each lockbox would require a certain amount of money each "]
["year to operate. The company receives payments from the Northwest, the North, the Northeast, the Southwest, the "]
["South and the Southeast. The amounts involved per day are known.  Where should the company open lockboxes?"]
[]
["Solution"]
["This model differs from others in the fact that the variables do not represent amounts of money, a number of "]
["products or other such values. This time the variables are decisions. Do we open a lockbox in this city? To what city "]
["should an area send its payments?"]
["It turns out that there is an easy and elegant way to describe such variables in models. We do this by using variables "]
["that can be either 0 or 1. Decision variables like this are often called binary variables. We assign a variable to each "]
["decision and if the decision is yes we give the variable a value 1 and otherwise the value 0. "]
["On this worksheet, we have assigned such variables for the decisions to open lockboxes in the different cities. These "]
["are defined as Lockbox_decisions found in cells B32 through G32."]
["By laying out the different areas versus the cities, we can also assign 0-1 variables to the decisions whether an area "]
["should send payments to a certain city. On the worksheet these are defined as Assignments, found in cells B25 "]
["through G30. By using the properties of the numbers 0 and 1, we can now easily formulate the model. "]
[]
["1) The variables are the decisions where to open lockboxes and the decisions where to send the mail for each area. "]
["These variables are defined in the worksheet as lockbox_decisions and assignments. All these variables are either"]
["0 or 1. (They are binary variables.)"]
[]
["2) We must tell the Solver that the variables can be only 0 or 1. This gives us:"]
[null,"assignments = binary"]
[null,"lockbox_decisions = binary"]
[]
["If we do not open a lockbox in a city, we can not have any mail sent to it. These constraints are expressed as follows"]
[null,"Chicago_boxes <= Chicago_decision"]
[null,"Dallas_boxes <= Dallas_decision"]
[null,"LA_boxes <= LA_decision"]
[null,"Miami_boxes <= Miami_decision"]
[null,"New_York_boxes <= New_York_decision"]
[null,"Seattle_boxes <= Seattle_decision"]
[]
["Notice how the usage of  0s and 1s gives us the opportunity to write these constraints this way. "]
["Finally, we assume an area only sends mail to one city. Again, because of the properties of 0 and 1, we can achieve "]
["this by requiring that the sum over the cities of the variables for an area (one row) equals 1. This gives"]
[null,"assignments_total = 1"]
[]
["3) The objective is to minimize lost interest and operating cost. This is defined in the worksheet as Total_Cost."]
["This is calculated by adding the operating cost and the lost interest. The operating cost is calculated by multiplying "]
["the decisions to open lockboxes by the cost to operate them. This is again possible because the variables are exactly "]
["0 or 1. The lost interest is similarly calculated."]
[]
["Remarks"]
["The techniques used in this model are simple but very powerful. Questions that are answered by yes or no, open or "]
["closed, etc. can often be solved by using binary variables."]
[]
["Notice that the interest in this model is very important for the solution. If interest goes up, it becomes more "]
["profitable to open more lockboxes. If interest is low, it could be more profitable to use fewer lockboxes and accept a "]
["higher float. You can see how the Solver finds different answers by changing the interest rate in cell E4."]

{"sheet":"Open","index":7,"n_rows":56,"n_cols":7,"rows_kept":56,"flags":["formulas_cached_only"]}
["Plant Opening/Closing"]
["A company wants to minimize the costs of shipping goods from production plants to warehouses near "]
["metropolitan demand centers, while not exceeding the supply available from each plant and meeting"]
["the demand from each metropolitan area. The company has plants in S. Carolina, Tennessee and "]
["Arizona. It is thinking about opening a plant in Arkansas. "]
[]
[null,null,"Number to ship from plant x to warehouse y (at intersection):"]
["Plants:","Total","San Fran","Denver","Chicago","Dallas","New York"]
["S. Carolina",0,0,0,0,0,0]
["Tennessee",0,0,0,0,0,0]
["Arizona",0,0,0,0,0,0]
["Arkansas",0,0,0,0,0,0]
[]
["Totals:",null,0,0,0,0,0]
[]
[null,"Demands by Whse -->",180,80,200,160,220]
["Plants:","Supply","Shipping costs from plant x to warehouse y (at intersection):"]
["S. Carolina",310,{"v":10,"d":"$10"},{"v":8,"d":"$8"},{"v":6,"d":"$6"},{"v":5,"d":"$5"},{"v":4,"d":"$4"}]
["Tennessee",260,{"v":6,"d":"$6"},{"v":5,"d":"$5"},{"v":4,"d":"$4"},{"v":3,"d":"$3"},{"v":6,"d":"$6"}]
["Arizona",280,{"v":3,"d":"$3"},{"v":4,"d":"$4"},{"v":5,"d":"$5"},{"v":5,"d":"$5"},{"v":9,"d":"$9"}]
["Arkansas",0,{"v":4,"d":"$4"},{"v":3,"d":"$3"},{"v":6,"d":"$6"},{"v":4,"d":"$4"},{"v":7,"d":"$7"}]
[]
["Shipping:",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
[null,null,"Extra shipping cost if opened",{"v":100,"d":"$100"},"Decision to open plant",null,0]
[]
[]
["Problem"]
["A company currently distributes products from three plants to five warehouses in different cities. Management "]
["is now thinking about opening a new plant to bring down distribution cost. Should the company decide to "]
["open the new plant or not?"]
[]
["Solution"]
["This models uses 2 kinds of variables. First, there are the variables that indicate how many products to ship "]
["from each plant to each warehouse. Second, we have a decision variable to decide whether we should "]
["open the new plant. For more information on the decision variables, see the worksheet."]
[]
["1) The variables are the number of products to ship from each plant to each warehouse and the decision to"]
["open or close the new plant. These are defined on this worksheet as Shipments and plant_decision."]
["2) First, there are the 'normal' distribution constraints. These are the constraints that we cannot ship more "]
["products from the plants than the supply at these plants. Also, we don't ship more to the cities than the demand"]
["from those cities. This leads to:"]
[null,"Shipped_from_plants <= Supply"]
[null,"Shipped_to_warehouses >= Demand"]
["Second, since we can't ship a negative number of products, we have the logical constraint"]
[null,"products_shipped  >= 0  via the Assume Non-Negative option"]
["And third, we must tell the Solver to strictly use 0 or 1 for the 'decision' variable. This gives:"]
[null,"plant_decision = binary"]
["3) The objective is to minimize cost, defined as Total_cost. This is calculated by muliplying the distribution "]
["cost times the number of products shipped, plus the extra cost to open the new plant."]
[]
["Remarks"]
["You might have noticed that this model resembles a pure transportation model. This is an example of a mix"]
["between a transportation model and a pure decision model, like Lockbox. In real life situations, it is very"]
["common to combine different kind of models to get a better representation of the problem. "]
["Notice how the decision variable is used to control the supply at the potentially new plant. If the decision to "]
["open is no, the supply is zero.  "]