The Operations Research Airplane Company builds jet airplanes to sell to corporations for the use of 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. Operations Research Airplane Company received purchase requests from three customers. However, due to the regulations, Operations Research Company can serve at most two customers at a time. Therefore, a decision needs to be made on the number of airplanes the company will agree to produce and the customers that will be served. The relevant data are given in the following table. The first column gives the star-up cost required to initiate the production of airplanes for each customer. The marginal revenue from each airplane produced is shown in the second column. The third column gives the percentage of the available production capacity that would be used for each airplane produced. The last column indicates the maximum number of airplanes requested by each customer (but less will be accepted) Start-up cost Marginal net revenue Capacity used per plane Maximum order $2.5 million $2.5 million 25% Customer $2 million $3 million 35% $1.8 million $2.2 million 20% The Operations-Research now wants to determine the customers to be served and how many airplanes to produce for each customer (if any) to maximize the company's total profit (total net revenue minus start-up costs). a. Develop a spreadsheet model and find the optimal solution using Excel Solver. b. What is the total maximum profit Operations-Research Airplane Company can make? The Operations Research Airplane Company builds jet airplanes to sell to corporations for the use of 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. Operations Research Airplane Company received purchase requests from three customers. However, due to the regulations, Operations Research Company can serve at most two customers at a time. Therefore, a decision needs to be made on the number of airplanes the company will agree to produce and the customers that will be served. The relevant data are given in the following table. The first column gives the star-up cost required to initiate the production of airplanes for each customer. The marginal revenue from each airplane produced is shown in the second column. The third column gives the percentage of the available production capacity that would be used for each airplane produced. The last column indicates the maximum number of airplanes requested by each customer (but less will be accepted) Start-up cost Marginal net revenue Capacity used per plane Maximum order $2.5 million $2.5 million 25% Customer $2 million $3 million 35% $1.8 million $2.2 million 20% The Operations-Research now wants to determine the customers to be served and how many airplanes to produce for each customer (if any) to maximize the company's total profit (total net revenue minus start-up costs). a. Develop a spreadsheet model and find the optimal solution using Excel Solver. b. What is the total maximum profit Operations-Research Airplane Company can make
