Linear programming spreadsheet model

Formulate a linear programming spreadsheet model and solve it using Solver.

E*9.15.
The Fly-Right Airplane Company builds small jet airplanes to sell to corporations for use by their executives. To meet the needs of these executives, the company's customers sometimes order a custom design of the airplanes being purchased. When this occurs, a substantial start-up cost is incurred to initiate the production of these airplanes. Fly-Right has recently received purchase requests from three customers with short dead-lines. However, because the company's production facilities already are almost completely tied up filling previous orders, it will not be able to accept all three orders. Therefore, a decision now needs to be made on the number of airplanes the company will agree to produce (if any) for each of the three customers.

The relevant data are given in the table below The first row gives the start-up cost required to initiate the production of the airplanes for each customer. Once production is under way, the marginal net revenue (which is the purchase price minus the marginal production cost) from each airplane produced is shown in the second row. The third row gives the percentage of the avail-able production capacity that would be used for each airplane produced. The last row indicates the maximum number of airplanes requested by each customer (but less will be accepted).

(see the table attached)

Fly-Right now wants to determine how many airplanes to produce for each customer (if any) to maximize the company's total profit (total net revenue minus start-up costs). Formulate and solve a spreadsheet model with both integer variables and binary variables for this problem.

E*9.21. An increasing number of Americans are moving to a warmer climate when they retire. To take ad-vantage of this trend, Sunny Skies Unlimited is undertaking a major real-estate development project. The project is to develop a completely new retirement community (to be called Pilgrim Haven) that will cover several square miles. One of the decisions to be made is where to locate the two fire stations that have been allocated to the community. For planning purposes, Pilgrim Haven has been divided into five tracts, with no more than one fire station to be located in any given tract. Each station is to respond to all the fires that occur in the tract in which it is located as well as in the other tracts that are assigned to this station. Thus, the decisions to be made consist of (1) the tracts to receive a fire station and (2) the assignment of each of the other tracts to one of the fire stations. The objective is to minimize the overall average of the response times to fires. The following table gives the average response time to a fire in each tract (the columns) if that tract is served by a station in a given tract (the rows). The bottom row gives the forecasted average number of fires that will occur in each of the tracts per day.

(see the table attached)

Formulate and solve a BIP model on a spreadsheet for this problem. Identify any constraints that correspond to mutually exclusive alternatives or contingent decisions.

E9.22. Reconsider Problem 9.21. The management of Sunny Skies Unlimited now has decided that the decision regarding the locations of the fire stations should be based mainly on costs. The cost of locating a fire station in a tract is $200,000 for tract 1, $250,000 for tract 2, $400,000 for tract 3, $300,000 for tract 4, and $500,000 for tract 5. Management's objective now is the following: Determine which tracts should receive a station to minimize the total cost of stations while ensuring that each tract has at least one station close enough to respond to a fire in no more than 15 minutes (on the average). In contrast to the original problem, note that the total number of fire stations is no longer fixed. Furthermore, if a tract without a station has more than one station within 15 minutes, it is no longer necessary to assign this tract to just one of these stations.

b. Display and solve this model art a spreadsheet.