Use the Excel Solver to find the minimum cost for both 1) and 2).
1)
Morgan Inc. is planning to purchase one of the component parts it needs for its finished product. The anticipated demands for the component for the next 12
periods are shown in the enclosed Excel table. The cost to order the component (labor, shipping, and paperwork) is $150. The cost to hold these components
in inventory is $1 per component per period. The price of the component is expected to remain stable at $12 per unit for the next 12 periods, and no quantity
discounts are available. The maximum order size is 1,000 units.
A. Formulate a model to minimize the total cost of satisfying Morgan Inc.’s demand for this component.
B. Solve the model formulated in part A. What is the optimal cost? How many orders are placed?
2)
Brooks Development Corporation (BDC) faces the following capital budgeting decision. Six real estate projects are available for investment. The net present
value and expenditures required for each project (in millions of dollars) are given in the attached Excel file.
There are conditions that limit the investment alternatives:
At least two of projects 1, 3, 5, and 6 must be undertaken.
If either project 3 or 5 is undertaken, they must both be undertaken.
Project 4 cannot be undertaken unless both projects 1 and 3 also are undertaken.
The budget for this investment period is $220 million.
A. Formulate a binary integer program that will enable BDC to find the projects to invest in to maximize net present value, while satisfying all project
restrictions and not exceeding the budget.
B. Solve the model formulated in part A. What is the optimal net present value? Which projects will be undertaken? How much of the budget is unused?
