USE EXCEL

3 Tina Distribution Co.(25 points) Tina Fey has decided to quit show business and start a distribution company. This new company distributes organic vegetables to different grocery stores. The vegetables are shipped in standard cardboard boxes that occupy 1.25 cubic feet in the warehouse. The company purchases it vegetables on a weekly basis and rarely has any inventory remaining at the end of the week. An entire week's supply of vegetables arrives each Monday morning at the warehouse, which can hold up to 18,000 cubic feet of product. Tina buys all her vegetables from a supplier who extends a credit of up to $30,000 per week (i.e. the maximum value of vegetables they can purchase every week). She can predict sales for each of fifteen products. This forecast is expressed as minimum and maximum level of sales. The minimum quantity is based on a contractual agreement she have made with some grocery stores and she is bound to supply the minimum quantities every week. The maximum quantity represents an estimate of the sales potential in the coming week. Table 3 provides costs, selling prices, and demands for all the 15 vegetables. a. Propose a product mix that maximizes Tina's profit for the coming week. (14 points) Table 3: Data for 15 Vegetables Product Cost ($) Price ($) per Carton per Carton Minimum Maximum Demand Demand 2.27 2.48 2.70 5.20 2.92 Potatoes Corn Chick Peas Artichokes Carrots Succotash Okra Cauliflower Green Peas Spinach Lima Beans Brussel Sprout Green Beans Squash Broccoli 2.15 2.20 2.40 4.80 2.60 2.30 2.35 2.85 2.25 2.10 2.80 3.00 2.60 2.50 2.90 2.48 2.20 3.13 2.48 2.27 3.13 3.18 2.92 2.70 3.13 300 400 250 0 300 200 150 100 750 400 500 100 500 100 400 1,500 2,000 900 150 1,200 800 600 300 3,500 2,000 3300 500 3,200 500 2,500 b. The supplier is willing to extend additional weekly credit of $500 but he will charge $10 as cost of financing. Should Tina take this additional credit? (3 points) c. An old friend of Tina has a building that can be used for additional warehousing. A total of 5,000 cubic feet can be rented for as low as $1 per cubic feet per week. Should Tina rent this additional space? Why or Why not? (3 points) 3 Tina Distribution Co.(25 points) Tina Fey has decided to quit show business and start a distribution company. This new company distributes organic vegetables to different grocery stores. The vegetables are shipped in standard cardboard boxes that occupy 1.25 cubic feet in the warehouse. The company purchases it vegetables on a weekly basis and rarely has any inventory remaining at the end of the week. An entire week's supply of vegetables arrives each Monday morning at the warehouse, which can hold up to 18,000 cubic feet of product. Tina buys all her vegetables from a supplier who extends a credit of up to $30,000 per week (i.e. the maximum value of vegetables they can purchase every week). She can predict sales for each of fifteen products. This forecast is expressed as minimum and maximum level of sales. The minimum quantity is based on a contractual agreement she have made with some grocery stores and she is bound to supply the minimum quantities every week. The maximum quantity represents an estimate of the sales potential in the coming week. Table 3 provides costs, selling prices, and demands for all the 15 vegetables. a. Propose a product mix that maximizes Tina's profit for the coming week. (14 points) Table 3: Data for 15 Vegetables Product Cost ($) Price ($) per Carton per Carton Minimum Maximum Demand Demand 2.27 2.48 2.70 5.20 2.92 Potatoes Corn Chick Peas Artichokes Carrots Succotash Okra Cauliflower Green Peas Spinach Lima Beans Brussel Sprout Green Beans Squash Broccoli 2.15 2.20 2.40 4.80 2.60 2.30 2.35 2.85 2.25 2.10 2.80 3.00 2.60 2.50 2.90 2.48 2.20 3.13 2.48 2.27 3.13 3.18 2.92 2.70 3.13 300 400 250 0 300 200 150 100 750 400 500 100 500 100 400 1,500 2,000 900 150 1,200 800 600 300 3,500 2,000 3300 500 3,200 500 2,500 b. The supplier is willing to extend additional weekly credit of $500 but he will charge $10 as cost of financing. Should Tina take this additional credit? (3 points) c. An old friend of Tina has a building that can be used for additional warehousing. A total of 5,000 cubic feet can be rented for as low as $1 per cubic feet per week. Should Tina rent this additional space? Why or Why not? (3 points)