VIEWS: 17 PAGES: 13 CATEGORY: Business POSTED ON: 8/22/2011 Public Domain
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. 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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). Yield Term Price Purchase CDs in months: 1-mo CDs: 1.0% 1 $2,000 1, 2, 3, 4, 5 and 6 Interest 3-mo CDs: 4.0% 3 $3,000 1 and 4 Earned: 6-mo CDs: 9.0% 6 $5,000 1 Total $0.00 Month: Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 End Init Cash: $400,000 $325,000 $335,000 $355,000 $275,000 $225,000 $240,000 Matur CDs: $0 $0 $0 $0 $0 $0 Interest: $0 $0 $0 $0 $0 $0 1-mo CDs: 0.00 0.00 0.00 0.00 0.00 0.00 3-mo CDs: 0.00 0.00 6-mo CDs: 0.00 Cash Uses: $75,000 ($10,000) ($20,000) $80,000 $50,000 ($15,000) $60,000 End Cash: $325,000 $335,000 $355,000 $275,000 $225,000 $240,000 $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 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 One_month_CDs = integer Three_month_CD1 = integer Three_month_CD2 = integer 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 Page 2 871f7c01-d91f-45bd-9efa-9f655894166e.xls decision whether or not to use integer variables. Page 3 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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 5% Expected Investment Cash Flows and Net Present Value Opp. 1 Opp. 2 Opp. 3 Opp. 4 Opp. 5 Opp. 6 Year 1 ($5.00) ($9.00) ($12.00) ($7.00) ($20.00) ($18.00) Year 2 ($6.00) ($6.00) ($10.00) ($5.00) $6.00 ($15.00) Year 3 ($16.00) $6.10 ($5.00) ($20.00) $6.00 ($10.00) Year 4 $12.00 $4.00 ($5.00) ($10.00) $6.00 ($10.00) Year 5 $14.00 $5.00 $25.00 ($15.00) $6.00 $35.00 Year 6 $15.00 $5.00 $15.00 $75.00 $6.00 $35.00 NPV $8.01 $2.20 $1.85 $7.51 $5.69 $5.93 Percentage to invest 0% 0% 0% 0% 0% 0% Cash Flow Total Budget Surplus Year 1 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $45.00 $45.00 Year 2 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $30.00 $30.00 Year 3 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $20.00 $20.00 Year 4 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Year 5 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Year 6 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Revenue Total NPV $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $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: 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: 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 Page 4 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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. Page 5 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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 5% Expected Investment Cash Flows and Net Present Value Opp. 1 Opp. 2 Opp. 3 Opp. 4 Opp. 5 Opp. 6 Year 1 ($5.00) ($9.00) ($12.00) ($7.00) ($20.00) ($18.00) Year 2 ($6.00) ($6.00) ($10.00) ($5.00) $6.00 ($15.00) Year 3 ($16.00) $6.10 ($5.00) ($20.00) $6.00 ($10.00) Year 4 $12.00 $4.00 ($5.00) ($10.00) $6.00 ($10.00) Year 5 $14.00 $5.00 $25.00 ($15.00) $6.00 $35.00 Year 6 $15.00 $5.00 $15.00 $75.00 $6.00 $35.00 NPV $8.01 $2.20 $1.85 $7.51 $5.69 $5.93 Decision to invest 0.00 0.00 0.00 0.00 0.00 0.00 Cash Flow Total Budget Surplus Year 1 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $45.00 $45.00 Year 2 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $30.00 $30.00 Year 3 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $20.00 $20.00 Year 4 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Year 5 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Year 6 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 Revenue Total NPV $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $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: investments <= 1 and investments >= 0 via the Assume Non-Negative option. In place of these constraints, we can tell the Solver to use binary integer variables, with: 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 Page 6 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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. Page 7 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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). Storage Storage Ordering space Holding Space per Demand per cost per available Cost unit (m3) month order (m3) Product 1 $25 440 200 $50 50000 Product 2 $20 850 325 $50 Product 3 $30 1260 400 $50 Product 4 $15 950 150 $50 Quantity to order each month EOQ Cost Space used (m3) Product 1 10 28.28427 $1,125 2200 Product 2 10 40.31129 $1,725 4250 Product 3 10 36.51484 $2,150 6300 Product 4 10 31.62278 $825 4750 Total $5,825 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 Quantities >= 0 via the Assume Non-Negative option 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 Page 8 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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. Page 9 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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. Storage Holding Space per Demand per Ordering cost Price per Cost unit (m3) month per order unit Product 1 $25 440 200 $50 $200 Product 2 $20 850 325 $50 $300 Product 3 $30 1260 400 $50 $275 Product 4 $15 950 150 $50 $400 Storage Capacity 50000 Budget $30,000 Quantity to order each month Cost of holding Space EOQ and ordering used (m3) Product 1 10 28.28427 $1,125 2200 Product 2 10 40.31129 $1,725 4250 Product 3 10 36.51484 $2,150 6300 Product 4 10 31.62278 $825 4750 Cost of products $11,750 Total $5,825 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: Cost_of_products <= Available_money and we also have Space_used <= Available_space as before We still have Quantities >= 0 via the Assume Non-Negative option. This time, we also require integer quantities: 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. Page 10 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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 6% Areas concerned Northwest North Northeast Southwest South Southeast Daily Payments $325,000 $475,000 $300,000 $275,000 $385,000 $350,000 Cities to be considered Seattle Chicago New York L.A. Dallas Miami Operating cost $55,000 $50,000 $60,000 $53,000 $58,000 $55,000 Average number of days from mailing to clearing of payment 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) 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 $0 $0 $0 $0 $0 $0 North $0 $0 $0 $0 $0 $0 Northeast $0 $0 $0 $0 $0 $0 Southwest $0 $0 $0 $0 $0 $0 South $0 $0 $0 $0 $0 $0 Southeast $0 $0 $0 $0 $0 $0 Total of Lost Interest $0 Operating Cost $0 Total Cost $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 Page 11 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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: assignments = binary 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 Chicago_boxes <= Chicago_decision Dallas_boxes <= Dallas_decision LA_boxes <= LA_decision Miami_boxes <= Miami_decision New_York_boxes <= New_York_decision 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 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. Page 12 871f7c01-d91f-45bd-9efa-9f655894166e.xls 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. 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: 0 0 0 0 0 Demands by Whse --> 180 80 200 160 220 Plants: Supply Shipping costs from plant x to warehouse y (at intersection): S. Carolina 310 $10 $8 $6 $5 $4 Tennessee 260 $6 $5 $4 $3 $6 Arizona 280 $3 $4 $5 $5 $9 Arkansas 0 $4 $3 $6 $4 $7 Shipping: $0 $0 $0 $0 $0 $0 Extra shipping cost if opened $100 Decision to open plant 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: Shipped_from_plants <= Supply Shipped_to_warehouses >= Demand Second, since we can't ship a negative number of products, we have the logical constraint 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: 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. Page 13