hw#1 2024 Q4

Homework Problem Set #1 OIDD 6120: Business Analytics Page 1 Spring 2024 1. Next Frontier Assistance. Next Frontier Assistance (NFA) is a charitable organization that specializes in rapid assistance to victims of natural disasters around the globe. After a recent cyclone in South-East Asia, the agency has received a budget of $10,000,000 to relief the shelter shortage arisen in one of the affected areas. To address the short-term demand for shelter, NFA will help building three types of housing units: A, B, and C. Constructing each unit type requires a certain amount of general labor, specialized labor, as well as land, as follows: Unit Type A B C General Labor (Hours) 30 20 16 Specialized Labor (Hours) 10 8 4 Land (000’s sq. ft) 2.5 1.6 1.2 Table 1. Requirements (per Housing Unit) for Each Unit Type. Housing units, once built, will provide shelter for 7, 6, and 4 people for each unit of type A, B, and C, respectively. The local government has determined that at least 3000 units of each type must be built. The HWB relies on the local government to provide building materials and is responsible only for the cost of procuring labor and allocating and clearing land. HWB estimates that it can procure local labor at the rate of $2 per hour of general labor, and $3 per hour of specialized labor. The cost of allocating land and clearing it from debris is estimated at $50 per thousand sq. feet. At these prices/rates, HWB cannot procure more than the following amounts of resources: Resource Maximum Amount General Labor (Hours) 980,000 Specialized Labor (Hours) 350,000 Land (000’s sq. ft) 100,000 Table 2. Maximum Amount of Each Resource. The organization must decide how many units of each type should be built to maximize the total number of people that are provided with shelter while keeping its total procurement cost from exceeding $10,000,000, and also without exceeding the resource availabilities and satisfying the local government’s requirements on minimum numbers of each unit to build. a) Formulate a linear algebraic model corresponding to the NFA’s decision. As a template, you can use the “Domaine Paul Autard” model on slide 12 of the handout for Class 1. In particular, define decision variables and their units, and write down algebraic expressions for the objective function and for the constraints. For each constraint, attach a descriptive label. b) Implement your model in a spreadsheet and solve the problem using Solver. Attach a one- page printout showing your model and the optimal solution.

Homework Problem Set #1 OIDD 6120: Business Analytics Page 2 Spring 2024 2. Issuing Municipal Bonds in Lower Saddle River. Town of Lower Saddle River (LSR) is planning to issue municipal bonds now (in “year 0”) to finance road improvement and school expansion projects. The bonds that LSR can issue are given in Table 3. For example, one unit of Bond 3 with the face of value of $100 will give Lower Saddle River $100 today (i.e., in year 0), but will require LSR to pay bondholders $6.50 (“coupon payment”) at the end of Year 1, $6.50 at the end of Year 2, and $106.50 (face value of $100 plus the last coupon payment of $6.50) at the end of Year 3. All eight bonds can be issued in any quantity, including fractions, at the stated price. Bond 1 Bond 2 Bond 3 Bond 4 Bond 5 Bond 6 Bond 7 Bond 8 Face Value 100 100 100 100 100 100 100 100 Annual Coupon 5 6 6.5 7 7.5 8 8.5 8 Maturity (year) 1 2 3 4 5 6 7 8 Table 3 . Description of Bonds that LSR can issue. The town plans to pay the bondholders using its future tax revenues. Table 4 shows the future tax amounts that LSR can use to pay bondholders. Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 10 12 15 25 25 20 10 6 Table 4. Tax amounts (in $mm) that LSR can use to pay bondholders. LSR would like to maximize the total amount, expressed in $mm, it raises “now,” i.e,. in year 0, while making sure that the estimated tax amounts are sufficient to pay LSR’s obligations to bondholders in each of eight years. If LSR has any excess tax revenue in a particular year, it can invest it in a money market account that earns 4% annual interest and use it to pay the bondholders in the future years. a) Formulate a linear algebraic model corresponding to LSR’s decision. In particular, define decision variables and their units, and write down algebraic expressions for the objective function and for the constraints. For each constraint, attach a descriptive label. b) Implement your model in a spreadsheet and solve the problem using Solver. Attach a one-page printout showing your model and the optimal solution.

Homework Problem Set #1 OIDD 6120: Business Analytics Page 4 Spring 2024 When making its shipping decisions, UD must satisfy the capacity constraints for each route connecting storage facilities and warehouses. In particular, the number of trucks that UD assigns to each route limits the total number of containers of both types shipped on each route as follows: From / To W1 W2 W3 S1 25 25 28 S2 25 27 32 S3 25 30 29 Table 9: Maximum Total Number of Containers on Each Route. UD needs to determine the numbers of each container type to move from each storage facility to each warehouse during the next week to minimize the overall shipping cost while satisfying the storage facilities supply constraints, warehouse requirements, as well as the limitations on the total numbers of containers that can be shipped on each route. The linear model below describes this decision problem. Decision Variables: L ij = # number of lighting containers to be shipped from storage facility i to warehouse j , i =1,2,3, j =1,2,3, F ij = # number of furniture containers to be shipped from storage facility i to warehouse j , i =1,2,3, j =1,2,3. Objective Function (to be minimized): Total transportation cost (in $) = 205 L 11 + 235 L 12 + … + 240 L 33 + 200 F 11 + 227 F 12 + … + 221 F 33 Constraints: L 11 + L 12 + L 13 = 20 (S1 Supply of Lighting Containers) L 21 + L 22 + L 23 = 17 (S2 Supply of Lighting Containers) L 31 + L 32 + L 33 = 27 (S3 Supply of Lighting Containers) F 11 + F 12 + F 13 = 35 (S1 Supply of Furniture Containers) F 21 + F 22 + F 23 = 22 (S2 Supply of Furniture Containers) F 31 + F 32 + F 33 = 32 (S3 Supply of Furniture Containers) L 11 + L 21 + L 31 ≥ 15 (W1 Demand for Lighting Containers) L 12 + L 22 + L 32 ≥ 25 (W2 Demand for Lighting Containers) L 13 + L 23 + L 33 ≥ 23 (W3 Demand for Lighting Containers) F 11 + F 21 + F 31 ≥ 13 (W1 Demand for Furniture Containers) F 12 + F 22 + F 32 ≥ 25 (W2 Demand for Furniture Containers) F 13 + F 23 + F 33 ≥ 22 (W3 Demand for Furniture Containers)

Homework Problem Set #1 OIDD 6120: Business Analytics Page 5 Spring 2024 L 11 + F 11 ≤ 25 (Capacity of S1-W1 route) L 12 + F 12 ≤ 25 (Capacity of S1-W2 route) L 13 + F 13 ≤ 28 (Capacity of S1-W3 route) L 21 + F 21 ≤ 25 (Capacity of S2-W1 route) L 22 + F 22 ≤ 27 (Capacity of S2-W2 route) L 23 + F 23 ≤ 32 (Capacity of S2-W3 route) L 31 + F 31 ≤ 25 (Capacity of S3-W1 route) L 32 + F 32 ≤ 30 (Capacity of S3-W2 route) L 33 + F 33 ≤ 29 (Capacity of S3-W3 route) L 11 , L 12 , …, F 33 ≥ 0 (non-negativity) The optimized spreadsheet for this model, the Solver settings, and the corresponding Sensitivity Report are as follows.

Homework Problem Set #1 OIDD 6120: Business Analytics Page 7 Spring 2024 Variable Cells Final Reduced Objective Allowable Allowable Cell Name Value Cost Coefficient Increase Decrease $B$15 L S1 W1 0 10 205 1E+30 10 $C$15 L S1 W2 18 0 235 5 25 $D$15 L S1 W3 2 0 220 25 5 $B$16 L S2 W1 0 10 213 1E+30 10 $C$16 L S2 W2 0 8 243 1E+30 8 $D$16 L S2 W3 17 0 210 8 1E+30 $B$17 L S3 W1 16 0 197 10 1E+30 $C$17 L S3 W2 7 0 230 25 5 $D$17 L S3 W3 4 0 240 5 25 $J$15 F S1 W1 25 0 200 8 1E+30 $K$15 F S1 W2 2 0 227 12 5 $L$15 F S1 W3 8 0 208 5 8 $J$16 F S2 W1 7 0 209 8 10 $K$16 F S2 W2 0 12 240 1E+30 12 $L$16 F S2 W3 15 0 199 10 8 $J$17 F S3 W1 9 0 190 26 10 $K$17 F S3 W2 23 0 210 5 26 $L$17 F S3 W3 0 5 221 1E+30 5 Constraints Final Shadow Constraint Allowable Allowable Cell Name Value Price R.H. Side Increase Decrease $B$18 Total L W1 16 0 15 1 1E+30 $C$18 Total L W2 25 32 25 1 2 $D$18 Total L W3 23 17 23 1 2 $B$22 Total S1W1 25 -8 25 1 18 $C$22 Total S1W2 20 0 25 1E+30 5 $D$22 Total S1W3 10 0 28 1E+30 18 $B$23 Total S2W1 7 0 25 1E+30 18 $C$23 Total S2W2 0 0 27 1E+30 27 $D$23 Total S2W3 32 -10 32 7 1 $B$24 Total S3W1 25 -26 25 1 2 $C$24 Total S3W2 30 -25 30 4 2 $D$24 Total S3W3 4 0 29 1E+30 25 $E$15 Total L S1 20 203 20 2 1 $E$16 Total L S2 17 203 17 2 1 $E$17 Total L S3 27 223 27 2 1 $J$18 Total F W1 41 0 13 28 1E+30 $K$18 Total F W2 25 19 25 1 2 $L$18 Total F W3 23 0 22 1 1E+30 $M$15 Total F S1 35 208 35 18 1 $M$16 Total F S2 22 209 22 18 7 $M$17 Total F S3 32 216 32 2 1

Homework Problem Set #1 OIDD 6120: Business Analytics Page 8 Spring 2024 Using the spreadsheet solution and sensitivity report shown above, please answer each of the following questions. Note… • The questions should be answered using only the printouts of the solution and sensitivity analysis that are included on the previous pages. You should not run various versions of the LP to determine the answers. • The questions are independent of each other. That is, unless explicitly stated, each question assumes that you are comparing the impact on the basic model and optimal solution, shown above, by question’s proposed change. a) UD has learned that, due to road improvements on the route between S2 and W2, the cost for each lighting container shipped on this route may be $5 less than the estimate in Table 7. If this happens, would the optimal shipping plan change? Would the optimal total transportation cost be different? If so, by how much? How do you know? b) The service contract between Pier Zero and UD allows the retailer, on a short notice, to increase by one the minimum number of furniture containers it must ship to exactly one of its warehouses. If Pier Zero were to exercise this option, what is the maximum increase in the total optimal transportation cost that UD will incur? How do you know? c) UD has been informed that the capacity of the route between S3 and W2 has been extended and it can now ship up to 35 containers on this route. The analyst working for UD states that as a result of this change the total optimal transportation cost that UD incurs will go down by $125. • Do you agree with the analyst’s statement? Explain. • Would the optimal shipping plan change under this capacity extension? Explain. d) UD is planning to invest in a pilot project on fuel saving measures for its trucks on route between S1 and W2. As a result, UD estimates that it will be able to decrease the shipping cost for any container shipped on that route by $20 per container. The cost of this pilot project is estimated to be $350. Should UD proceed with this pilot project? Why or why not?