I provided all the answers just need the excel sheet
Flair Furniture Example 7-14 The Electrocomp Corporation (Example 7-14) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Example 7-15(a) The Electrocomp Corporation (Example 7-15a) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Min. Air Conditioners Max. Fans Example 7-15(b) The Electrocomp Corporation (Example 7-15b) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Min. Air Conditioners Max. Fans Example 7-17 The Outdoor Furniture Corporation (Example 7-17) Decision Variables: BenchesPicnic Tables BP Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Max. Labor Hours Max. Redwood Materials Example 7-24 (Investments) Blank, Leibowitz, and Weinberger Investments Decision Variables: Louisiana GasTrimex Insulation and Power (L)Co. (T) Amount Invested = Objective Function: Minimize Cost = Constraints: LHSRHS Min. Short-Term Appreciation Min. Intermediate Growth Min. Dividend Income Ex. 7-25 (Woofer Pet Foods) Woofer Pet Foods Decision Variables: BeefGrain BG Number of pounds = Objective Function: Minimize Cost = Constraints: LHSRHS Min. Vitamin 1 Min. Vitamin 2 1 Pound Dog Food Example 7-40 The Weinberger Electronics Corporation (Example 7-40) Decision Variables: XJ201XM897TR29BR788 Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Max. Wiring Hours Max. Drilling Hours Max. Assembly Hours Max. Inspection Hours Min. Production XJ201 Min. Production XM897 Min. Production TR29 Min. Production BR788 Sheet1 Here are the questions and the solutions. I just need you to do the excel file. I have included some pictures Problem 7-14 7-14 The Electrocomp Corporation manufactures two electrical products: air conditioners and large fans. The assembly process for each is similar in that both require a certain amount of wiring and drilling. Each air conditioner takes 3 hours of wiring and 2 hours of drilling. Each fan must go through 2 hours of wiring and 1 hour of drilling. During the next production period, 240 hours of wiring time are available and up to 140 hours of drilling time may be used. Each air conditioner sold yields a profit of $25. Each fan assembled may be sold for a $15 profit. Formulate and solve this LP production mix situation to find the best combination of air conditioners and fans that yields the highest profit. Use the corner point graphical appro Answers: 40 air conditioners; 60 fans; Maximum Profit = $1,900 Problem 7-15 (a,b) 7-15 Electrocomp�s management realizes that it forgot to include two critical constraints (see Problem 7-14). In particular, management decides that there should be a minimum number of air conditioners produced in order to fulfill a contract. Also, due to an oversupply of fans in the preceding period, a limit should be placed on the total number of fans produced. (a) If Electrocomp decides that at least 20 air conditioners should be produced but no more than 80 fans should be produced, what would be the optimal solution? How much slack is there for each of the four constraints? (b) If Electrocomp decides that at least 30 air conditioners should be produced but no more than 50 fans should be produced, what would be the optimal solution? How much slack is there for each of the four constraints at the optimal solution? Answers: (a) 40 air conditioners; 60 fans; Maximum Profit = $1,900 (b) 45 air conditioners; 50 fans; Maximum Profit = $1,875 Problem 7-17 · Similar to Flair Furniture · Answers: 262.5 benches; 25 tables; Maximum Profit = $2,862.50 Problem 7-24 · Answers: Louisiana Gas and Power = $1,358.70; Trimex Insulation Company = $1,820.65; Short-term growth = $926.09; Intermediate-term growth = $5,000; Dividends = $200; Minimum Costs = $3,179 · Given: · X = $ invested in Louisiana Gas and power · Y = $ invested in Trimax insulation Co · (a) Minimize total investment · Objective function: · Minimize Z = X+Y · Subject to: · Short term growth potential: 0.36X + 0.24Y ≥ 720 · Intermediate Growth potential: 1.67X + 1.50Y ≥ 5000 · Dividend rate Potential: 0.04X + 0.08Y ≥ 200 · X, Y ≥ 0 · (b) Excel Solver and Sensitivity report: · Build the model as shown below. Problem 7-25 ISM 6407 Fall 2009 7-25 Woofer Pet Foods produces a low-calorie dog food for overweight dogs. This product is made from beef products and grain. Each pound of beef costs $0.90, and each pound of grain costs $0.60. A pound of the dog food must contain at least 9 units of Vitamin 1 and 10 units of Vitamin 2. A pound of beef contains 10 units of Vitamin 1 and 12 units of Vitamin 2. A pound of grain contains 6 units of Vitamin 1 and 9 units of Vitamin 2. Formulate this as an LP problem to minimize the cost of the dog food. How many pounds of beef and grain should be included in each pound of dog food? What is the cost and vitamin content of the final product? Let X 1 = the number of pounds of beef in each pound of dog food X 2 = the number of pounds of grain in each pound of dog food Minimize .90X 1 + .60X 2 (minimize cost per pound of dog food) Subject to: X 1 + X 2 = 1 (total weight should be one pound) 10X 1 + 6X 2 ≥ 9 (at least 9 units of vitamin 1 in a pound) 12X 1 + 9X 2 ≥ 10 (at least 10 units of vitamin 2 in a pound) X 1 , X 2 ≥ 0 (non-negativity constraints) Optimal Solution: X 1 = .75 X 2 = .25 Cost = $.825 7-25 Woofer Pet Foods produces a low-calorie dog food for overweight dogs. This product is made from beef products and grain. Each pound of beef costs $0.90, and each pound of grain costs $0.60. A pound of the dog food must contain at least 9 units of Vitamin 1 and 10 units of Vitamin 2. A pound of beef contains 10 units of Vitamin 1 and 12 units of Vitamin 2. A pound of grain contains 6 units of Vitamin 1 and 9 units of Vitamin 2. Formulate this as an LP problem to minimize the cost of the dog food. How many pounds of beef and grain should be included in each pound of dog food? What is the cost and vitamin content of the final product? Let X 1 = the number of pounds of beef in each pound of dog food X 2 = the number of pounds of grain in each pound of dog food Minimize .90X 1 + .60X 2 (minimize cost per pound of dog food) Subject to: X 1 + X 2 = 1 (total weight should be one pound) 10X 1 + 6X 2 ≥ 9 (at least 9 units of vitamin 1 in a pound) 12X 1 + 9X 2 ≥ 10 (at least 10 units of vitamin 2 in a pound) X 1 , X 2 ≥ 0 (non-negativity constraints) Optimal Solution: X 1 = .75 X 2 = .25 Cost = $.825 7-25 Woofer Pet Foods produces a low-calorie dog food for overweight dogs. This product is made from beef products and grain. Each pound of beef costs $0.90, and each pound of grain costs $0.60. A pound of the dog food must contain at least 9 units of Vitamin 1 and 10 units of Vitamin 2. A pound of beef contains 10 units of Vitamin 1 and 12 units of Vitamin 2. A pound ofgrain contains 6 units of Vitamin 1 and 9 units of Vitamin 2. Formulate this as an LP problem to minimize the cost of the dog food. How many pounds of beef and grain should be included in each pound of dog food? What is the cost and vitamin content of the final product? · Similar to Holiday Meal Turkey Ranch · NOTE: The constraint that calls for a total of 1 pound of dog food suggests that you should get "1 pound" when you add up the total number of pounds of beef and grain (i.e., 1*B + 1*G = 1). · Answers: Beef = 0.75 pounds; Grain = 0.25 pounds; Vitamin 1 = 9 units; Vitamin 2 = 11.25 units; Minimum Cost = $0.825 Let X1 = the number of pounds of beef in each pound of dog foodX2 = the number of pounds of grain in each pound of dog food Minimize .90X1+ .60X2(minimize cost per pound of dog food)Subject to: X1+ X2= 1 (total weight should be one pound)10X1+ 6X2≥ 9 (at least 9 units of vitamin 1 in a pound)12X1+ 9X2≥ 10 (at least 10 units of vitamin 2 in a pound)X1, X2≥ 0 (non-negativity constraints)Optimal Solution: X1 = .75 X2 = .25 Cost = $.825 Problem 7-40 1. The Weinberger Electronics Corporation manufactures four highly technical products that it supplies to aerospace firms that hold NASA contracts. Each of the products must pass through the following departments before they are shipped: wiring, drilling, assembly and inspection. The time requirement in hours for each unit produced and its corresponding profit value are summarized in the following table: 2. Department Product Wiring Drilling Assembly Inspection Unit profit XJ201 0.5 0.3 0.2 0.5 9 XM897 1.5 1 4 1 12 TR29 1.5 2 1 0.5 15 BR788 1 3 2 0.5 11 The production available in each department each month, and the minimum monthly production requirement to fulfill contracts, are as follows: Department Capacity (hours) Product Minimum Prod. Level Wiring 15,000 XJ201 150 Drilling 17,000 XM897 100 Assembly 26,000 TR29 300 Inspection 12,000 BR788 400 The production manager has the responsibility of specifying production levels for each product for the coming month. Help him by calculating( that is, setting up the cnstraints and objective funtion) Weinberger's problem using LP. PLease show work Answers: XJ201 = 20,650 units; XM897 = 100 units; TR29 = 2,750 units; BR788 = 400 units; Maximum Profit = $232,700 PLEASE SHOW ALL WORK. I HAVE CONSTRUCTED An excel TEMPLATE FOR ALL PROBLEMS. Flair Furniture Example 7-14 The Electrocomp Corporation (Example 7-14) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Example 7-15(a) The Electrocomp Corporation (Example 7-15a) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Min. Air Conditioners Max. Fans Example 7-15(b) The Electrocomp Corporation (Example 7-15b) Decision Variables: A/CFans AF Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Wiring hours Drilling hours Min. Air Conditioners Max. Fans Example 7-17 The Outdoor Furniture Corporation (Example 7-17) Decision Variables: BenchesPicnic Tables BP Number of units = Objective Function: Maximize Profit = Constraints: LHSRHS Max. Labor Hours Max. Redwood Materials