Put all of your work in a single Excel file. Use a separate worksheet for each problem. Label the worksheet tabs. (Sensitivity reports will automatically be generated and labelled on their own worksheets.) 1. Peoria Packing & Butcher shop produces a sausage mixture in 200 pound batches. The mixture contains three ingredients - chicken, veal and pork. The cost per pound of each of these ingredients is as follows: chicken $3 per pound, veal $8 per pound and pork $4 per pound. Each batch of sausage mixture has the following recipe requirements: at least 75 pounds of chicken, at least 25 pounds of veal. Also, the ratio of chicken to pork must be at least 3 to 1. The company wants to determine the optimal mixture of ingredients that will minimize cost. Write the LP formulation 2. During each 4-hour period, the Smalltown police force requires the following number of on-duty police officers: Period midnight to 4am 4am to 8am 8am to noon noon to 4pm 4pm to 8pm 8pm to midnight Number required 8 7 6 6 5 4 Each police officer works 2 consecutive 4-hour shifts. Write the LP formulation to determine the schedule that will minimize the number of officers needed to meet Smalltown's daily requirements. 3. Maggie Stewart loves desserts but due to weight and cholesterol concerns, she has decided that she must plan her desserts carefully. There are two possible desserts she is considering: snack bars and ice cream. After reading the nutrition labels on the snack bar and ice cream packages, she learns that each \"serving\" of snack bar weighs 37 grams and contains 120 calories and 5 grams of fat. Each serving of ice cream weighs 65 grams and contains 160 calories and 10 grams of fat. Maggie will allow herself no more than 450 calories and 25 grams of fat in her daily desserts, but because she loves desserts so much she requires at least 120 grams of dessert per day. Also, she assigns a \"taste index\" to each gram of each dessert, where 0 is the lowest and 100 is the highest. She assigns a taste index of 95 to ice cream and 85 to snack bars (since she prefers ice cream to snack bars. Write the LP formulation to determine Maggie's daily dessert plan that would to maximize the total taste index? 4. Bloomington Breweries produces beer and ale. Beer sells for $5 per barrel and ale sells for $3 per barrel. Producing a barrel of beer requires 5 lb. of corn and 2 lb. of hops. Producing a barrel of ale requires 2 lb. of corn and 2 lb. of hops. Sixty pounds of corn and forty pounds of hops are available Write the LP formulation, set the problem up in Excel and use Solver to find the optimal solution Use your Solver output to answer the questions below. a. What is the optimal solution and the total profit? b. If additional amounts of corn were available what would its value to the firm be? Within what limits could the firm expect that benefit? c. If additional amounts of hops were available at a cost of $1.00 per pound should the firm buy it? d. Within what limits could the profit contribution of beer change without changing the optimal solution? e. If the brewery could sell beer for $5.50 per barrel and ale for $3.75 would the optimal solution change? f. The amount of yeast available is being limited by global supply problems. Regular beer will require 3 ounces of yeast and ale will require 4 ounces of yeast. The total amount of yeast available is 55 ounces. Will the current solution still satisfy the constraints or must we resolve the problem with the new constraint set? g. The brewery is considering adding a third product - dry beer. The dry beer will require 3 lbs of corn and 2 lbs of corn. It will sell for $4.50 per barrel. Should the company consider adding the dry beer to its product line, given the above resource requirements? (Calculate the opportunity cost) 5. A marketing manager is trying to determine the advertising mix for a new product. Three types of ads are being considered: radio, TV and newspaper. The firm's ad agency has provided the following data: Type of ad Cost per ad Number of customers exposed per ad Number of units sold per ad Radio $1900 35,000 3,000 TV $3000 70,000 8,000 Newspaper $1400 50,000 5,000 Several other criteria must be met: the advertising budget is $100,000, total exposure must be 2,500,000 customers, and no more than 30 ads may appear in any medium. The firm wants to maximize total number of units sold. Write the LP formulation, set the problem up in Excel and use Solver to find the optimal solution Use your Solver output to answer the questions below. A. What is the optimal solution and the total number of units sold? B. Within what range can the objective function coefficient for newspapers vary without changing the optimal solution? C. What would the number of units sold per radio ad have to change to in order for radio ads to be used in the advertising mix? D. What would marginal benefit of adding to the advertising budget be? How high could we increase the budget and still expect this benefit? E. If we didn't restrict the number of newspaper ads, how many could we allow before the optimal solution would change? F. Would the solution change if the number of units sold per ad changed to the values in the following table? Radio TV Newspaper No. of units sold per ad 3500 10000 4500