r Questions ar:.d Prob:e:ns 121 For instance, each of the first 10 ads the company places in newspapers will cost $1,000 and is expected to reach 900 new customers. Each of the next 10 newspaper ads will cost $900 and is expected to reach 700 new customers.Nate that the number of new customers reached by increasing amounts of advertising decreases as the advertising saturates the market. Assume the company will purchase no more than. 30 newspaper ads and no more than 15 television ads. a. Formulate an LP model for this problem to maximize the number of new cus-tomers reached by advertising. b. Implement your model in a spreadsheet and solve it. c. What is the optimal solution? d. Suppose the number of new customers reached by 11-20 newspaper ads is 400 and the number of new customers reached by 21-30 newspaper ads is 700. Make these changes in your spreadsheet and reoptimize the problem. What is the new optimal solution? What (if anything) is wrong with this solution and why? 25. The Shop at Home Network sells various household goods during live television broadcasts. The company owns several warehouses to hold many of the goods it sells but also leases extra warehouse space when needed. During the next five months, the company expects it will need to lease the following amounts of extra warehouse space: Month 2 3 4 5 Square Feet Needed 20,000 30,000 40,000 35,000 50,000 At the beginning of any month, the company can lease extra space for one or more months at the following costs: Lease term {months) 2 3 4 5 Cost per Sq. Ft. Leased $55 $95 $130 $155 $185 So, for instance, at the start of month 1 the company can lease as much space as it wants for four months at a cost of $155 per square foot. Similarly, at the start of month 3, the company can lease any amount of space for two months at a cost of $95 per square foot. The company wants to determine the least costly way of meeting its warehousing needs over the coming five months. a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. c. What is the optimal solution? d. How much would itcost the company to meet its space needs if in each month it leases for one month exactly the amount of space required for the month? 26. A manufacturer of prefabricated homes has decided to subcontract four compo-nents of the homes. Several companies are interested in receiving this business, but none can handle more than one subcontract. The bids made by the companies for the various subcontracts are summarized in the following table. · Bids by Companies {in $1,000s) for Various Subcontracts Company Component A B c D 1 185 225 193 207 2 200 190 175 225 3 330 320 315 300 4 375 389 425 445122 Chapter 3 Modeling and Soivlng LP Problems in a Spreadsheet Assuming all the companies can perform each subcontract equally well, to which company should each subcontract be assigned if the home manufacturer wants to minimize payments to the subcontractors? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using S9lver. c. What is the optimal solution? 27. Holiday Fruit Company buys oranges and processes them into gift fruit baskets and fresh juice. The company grades the fruit it buys on a scale from 1 (lowest quality) to 5 (highest quality). The following table summarizes Holiday's current inventory of fruit. Grade 1 2 3 4 5 Supply (1000s of lbs) 90 225 300 100 75 Each pound of oranges devoted to fruit baskets results in a marginal profit of $2.50, whereas each pound devoted to fresh juice results in a marginal profit of $1.75. Holi-day wants the fruit in its baskets to have an average quality grade of at least 3.75 and its fresh juice to have an average quality grade of at least 2.50. a. Formulate an optimization model for this problem. b. Implement your model in a spreadsheet and solve it. c. What is the optimal solution? 28. Riverside Oil Company in eastern Kentucky produces regular and supreme gaso-line. Each barrel of regular sells for $21 and must have an octane rating of at least 90. Each barrel of supreme sells for $25 and must have an octane rating of at least 97. Each of these types of gasoline are manufactured by mixing different quantities of the following three inputs: Barrels Available Input Cost per Barrel Octane Rating (in 1000s) 1 $17.25 100 150 2 $15.75 87 350 3 $17.75 110 300 Riverside has orders for 300,000 barrels of regular and 450,000 barrels of supreme. How should the company allocate the available inputs to the production of regular and supreme gasoline to maximize profits? a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver. c. What is the optimal solution? 29. Maintenance at a major theme park in central Florida is an ongoing process that oc-curs 24 hours a day. Because it is a long drive from most residential areas to the park, employees do not like to work shifts of fewer than eight hours. These eight-hour shifts start every four hours throughout the day. The number of maintenance work-ers needed at different times throughout the day varies. The following table sum-marizes the minimum number of employees needed in each four-hour time period. Time Period Minimum Employees Needed 12 a.m. to 4 a.m. 90 4 a.m. to 8 a.m. 215 8 a.m. to 12 p.m. 250 12 p.m. to 4 p.m. 165 4 p.m. to 8 p.m. 300 8 p.m. to 12 a.m.