Instructions: Review the data file and answer the questions. Your answers should be submitted in the form of a Word document. The Word document should include ALL your answers including any charts, tables, or graphs required. Answer questions with complete sentences and using appropriate units and notation. You need to submit the Excel files that supports your work
PROBLEM:
INTERNATIONAL INDUSTRIES, INC.
International Industries, Inc. is a $53 billion conglomerate that owns and runs a wide variety of businesses worldwide. The scope of these businesses (which are called “divisions” at International Industries) ranges from retail packaged foods to industrial machine tools to financial services. George Ye has been CEO of International Industries for the past six years. At a recent meeting of the Board of Directors, Mr. Ye had come under criticism for International Industries poor performance in the last two years. In particular, the Board felt that Mr. Ye was not decisive enough in selling divisions that had been underperforming, and that he was also not aggressively investing in new businesses (particularly in the computer and bio-technology sectors). To his credit, Mr. Ye had closed four divisions during his tenure as CEO (which were smart moves, even in hindsight), and he had invested more than $2 billion in a new worldwide-web-based internet division, and more than $1 billion in a new division involved in the development of industry-oriented financial software.
After the Board of Directors meeting, George Ye arranged a meeting with the Chief Financial Officer (CFO) of International Industries, Mr. Paul Glasser. George Ye asked Mr. Glasser to undertake a comprehensive re-evaluation of all the divisions of International Industries. More specifically, he asked Mr. Glasser to recommend to him which divisions to sell, which divisions merited further investment of capital, and which should be maintained at status quo. Furthermore, he wanted this analysis completed within the next two months, in time for the next quarterly meeting of the Board of Directors.
Mr. Glasser retained the services of the consulting firm GCG, Inc. for assistance in this decidedly large and important undertaking. Mr. Glasser asked the directors of each of the thirty divisions of International Industries to submit a request for capital expenditures (with justification and back-up). For each division, Mr. Glasser wanted to decide whether International Industries should further invest in the division, maintain the division with no significant additional investment, or sell the division. Of course, it would also be nice to consider other investment options for each division, such as investing more or less than the requested amount, but with only two months to complete his analysis, Mr. Glasser honestly thought he would have to keep his decision framework purposely limited in scope.
For each of the thirty divisions and for each of the three decision choices (invest, maintain, or sell), Mr. Glasser asked the division vice president to report the investment amount required, the expected financial implications of the choice (measured in net present value (NPV)), and the cash flow implications of the choice for the coming year. With the help of the GCG consultants, each of the thirty divisions submitted the requested information. For example, consider the tool and die manufacturing division, which is division number 2. The summary information for division 2 is shown below:
| Choice | Investment Requirement (in $ million) | Net Present Value (in $ million) | Cash Flow for Next Year (in $ million) |
| Invest | 300 | -257 | -200 |
| Maintain | 40 | -16 | 35 |
| Sell | 400 | 1,100 | 1,600 |
The plan to invest in this division consists of building a new factory in Mexico, buying new machinery, buying new computer systems, and expanding warehousing capacity. The plan to maintain this division merely requires upgrading obsolete equipment. The plan to sell this division involved funds expended for canceling leases and contracts and for severance packages for higher-level managers. The spreadsheet (link) contains all the information collected by GEG for all 30 divisions.
Mr. Glasser needed to determine which decision (invest, maintain, or sell) to recommend for each division. His decision criterion was to maximize the net present value to International Industries, subject to cash flow considerations. The cash-flow considerations were that the total investment amount for next year could not exceed the total cash flow for next year. Mr. Glasser also identified some choices that were interrelated, which he listed as follows in his discussions with GCG:
(a) For strategic reasons, if International Industries sells division 1, then they should invest in division 2 and vice versa. Similarly, if International Industries sells division 1, then they should sell division 3 and vice versa.
(b) If International Industries invests in division 6, then they should also invest in division 7, but not necessarily, vice versa.
(c) For diversification purposes, Mr. Glasser felt that at most one of the following choices could be made: sell division 3, sell division 4, sell division 5, maintain division 6, sell division 7, and sell division 9.
(d) Lastly, given the similarities of divisions 24 and 28, Mr. Glasser felt that if International Industries invests in division 24, then they should also invest in division 28.
Given that there are three choices for each of the thirty divisions, Paul Glasser realized that there were possible investment strategies to consider, which of course is an astronomical number. When he discussed this with the consultants at GCG, they pointed out to him that they thought they might be able to compute the optimal investment strategy by formulating and solving the decision problem as a discrete optimization problem. Mr. Glasser knew that even if he found the “optimal decision strategy” George Ye would most certainly ask him about the sensitivity of his decision to key financial assumptions. From previous experience, Paul Glasser also knew that George Ye would ask him not only for his “optimal” recommendation, but he would also ask Paul for his second-best and third-best alternative recommendations.
Questions
Suppose that you are an associate of the firm GCG. Using the data provided in the spreadsheet, construct an optimization model of the problem faced by International Industries.
- A) Provide the linear programming formulation. Define decision variables, objective function and constraints. Feel free to use short notation and abbreviate parts of the formulation.
- B) Create the spreadsheet model and solve it using Solver. Use “FORMULATEXT” to show constraint calculations and objective function calculation. <Insert screenshot of model, and answer report>
- C) What is the optimal recommendation? Use a visual to communicate the recommendation.
(b) What is the penalty for imposing restrictions (a)-(d) outlined above? That is, how much does the optimal NPV change if these restrictions are removed?
(c) Your boss knows that Mr. Glasser likes to have options and asked you to also include in your report the second best and third best solution to the optimization model.
- 1) Devise, explain and implement a methodology to generate the second best and the third best solutions.
- 2) How do the alternative solutions differ?
- 3) How much does the optimal NPV change compare to the best?