I just need help setting this up in excel. formulating the LP problem (defining decision variables, objective function, and constraints). You will need to start with all the calculations necessary to formulate the problem (e.g., cost calculation, availability of resources).

Smiley Citrus LLC has a packing facility in Fresno, CA and has recently acquired another packing facility in Sullivan City, TX. Smiley Citrus buys oranges and limes from outside growers. The newly acquired plant in Sullivan City only packs limes. The company grades and packs the product and sells it ready-for-retail to large distributors. The oranges and limes are graded at the packing facilities as No.1 (higher quality) and No. 2 (medium quality) based on color, firmness, maturity, texture, and shape. The Company works with seven different distributors located in Chicago, Nashville, Atlanta, Dallas, New York, Las Vegas, and Phoenix. The product is packed into 40 lb. cardboard cartons and then put onto pallets. Each pallet has 60 cartons. The company has hired you as a consultant because management would like help planning the monthly production and distribution schedule. Specifically, management would like to know the optimal level of production and supply for each of the distributors, keeping in mind that the objective is to maximize profits. In order to facilitate the analysis, we will assume supply and demand of oranges and lemons is the same throughout the year. We will also assume that no fruit is discarded during grading (0% fruit scraps). In order for you to conduct the analysis, the company has provided some information about their operations. Sales to Distributors: . Demand: The company has forecasted the maximum number of cartons of oranges and limes they can sell to the distributors each month. The information is summarized in the table below: Chicago Nashville Atlanta Dallas New York Phoenix No. 1 4.750 4,160 Oranges 3,400 6,600 1,700 No. 2 Las Vegas 4,000 11,360 1,625 4,185 8,775 1,395 4,200 9,900 2.240 3,840 9,000 960 12,540 1.750 No. 1 8,320 3,040 6,080 Limes No. 2 3,300 2,925 5.280 2,250 4.620 4,615 . Prices: The sales department has agreed on a baseline price per carton with each of the distributors as shown in the table below: Chicago Nashville Atlanta Dallas Phoenix New York $28.91 $38.37 Las Vegas $26.21 $35.53 $26.21 $27.25 $25.27 Oranges Limes $27.97 $37.98 $27.21 $37.93 $38.50 $36.04 $36.36 The baseline price is for product with grade No. 2. The distributors pay a 9% price premium for product with a grade No. 1. Availability of fruit for processing (supply from outside growers): Plant in Fresno: the company can procure a monthly supply of 1.300 tons of oranges and 700 tons of limes from outside growers. Plant in Sullivan City: the company has secured a monthly supply of 420 tons of limes from outside growers. The company estimates that 30% of the product coming into the plant from outside growers is grade No.1, while the remaining 70% is grade No. 2. Labor and machine time: Grading and packing at the company requires both machine time (e.g., grading lines, packing, labeling, etc.) and manual labor. Recently, the company conducted a time study to determine the amount of labor and machine-time required to grade, pack and store each carton of oranges and limes by quality (No.1 and No. 2). Better quality fruit can be run through the grading lines faster as it requires lower inspection time. The study was conducted at both plants. The results of this time study are provided in the Excel tabs "Machine Time" and "LaborTime". (Hint: You will need the results from this time study to build constraints in your optimization model.) Plant in Fresno: The Company has 132 employees working approximately 40 hours per week at the plant. Since you will also need to estimate the amount of machine time available, the company has provided the following information: there are 8 grading lines (machines) that can operate for 10 hours a day and 6 days per week. The lines can be used to grade and pack both oranges and limes. Plant in Sullivan City: The Company has 30 emplo working approximat 40 hours per week, and they have 3 grading lines (machines) that can operate for 9 hours a day and 6 days per week. The grading/packing lines can be used for both oranges and limes. (Hint: To facilitate analysis when estimating total labor and machine time availability, assume each month has 4 weeks). . Transportation Costs: The company has estimated a transportation cost of $1.75 per mile per truckload for the plant in Fresno and $1.70 per mile per truckload for the plant in Sullivan City. Each truck can carry 22 pallets. The transportation distance in miles from the packing facility to each of the distribution centers is summarized below: New Chicago Nashville Atlanta Dallas York Las Vegas 396 Phoenix 2.064 2,052 2.223 1.480 2.800 514 Fresno, CA Sullivan City, TX 1.453 1,147 1,161 528 2.025 1.242 1.539 Cost of Goods Sold (COGS): . . Plant in Fresno: The management in Fresno has provided the following information regarding the COGS (cost of goods sold) for the limes graded and packed at their facility. Oranges: The price paid to the growers is $8.90 per carton of regular fiuit. The growers charge a 10% premium for oranges graded No. 1. The packing and labeling cost is $1.25 per carton, and the direct labor cost allocated to each carton is estimated at $6.30. Limes: The price paid to the growers is $10.50 per carton of regular fruit. The growers charge a 10% premium for limes graded No. 1. The packing and labeling cost is $1.50 per carton, and the direct labor cost allocated to each carton is estimated at $7.50. . o Plant in Sullivan City: The management in Sullivan City, TX has provided the following information regarding the COGS (cost of goods sold) for the limes graded and packed at their facility. The price paid to the growers is $10.15 per carton of grade No. 2. The growers charge a 10% premium per carton of No.1. The packing and labeling cost is $1.60 per carton, and the direct labor cost allocated to each carton is estimated at $6.90. (Hint: To estimate the COGS per carton, you need to add the cost of the fruit, packing and labeling, and direct labor costs). Smiley Citrus LLC has a packing facility in Fresno, CA and has recently acquired another packing facility in Sullivan City, TX. Smiley Citrus buys oranges and limes from outside growers. The newly acquired plant in Sullivan City only packs limes. The company grades and packs the product and sells it ready-for-retail to large distributors. The oranges and limes are graded at the packing facilities as No.1 (higher quality) and No. 2 (medium quality) based on color, firmness, maturity, texture, and shape. The Company works with seven different distributors located in Chicago, Nashville, Atlanta, Dallas, New York, Las Vegas, and Phoenix. The product is packed into 40 lb. cardboard cartons and then put onto pallets. Each pallet has 60 cartons. The company has hired you as a consultant because management would like help planning the monthly production and distribution schedule. Specifically, management would like to know the optimal level of production and supply for each of the distributors, keeping in mind that the objective is to maximize profits. In order to facilitate the analysis, we will assume supply and demand of oranges and lemons is the same throughout the year. We will also assume that no fruit is discarded during grading (0% fruit scraps). In order for you to conduct the analysis, the company has provided some information about their operations. Sales to Distributors: . Demand: The company has forecasted the maximum number of cartons of oranges and limes they can sell to the distributors each month. The information is summarized in the table below: Chicago Nashville Atlanta Dallas New York Phoenix No. 1 4.750 4,160 Oranges 3,400 6,600 1,700 No. 2 Las Vegas 4,000 11,360 1,625 4,185 8,775 1,395 4,200 9,900 2.240 3,840 9,000 960 12,540 1.750 No. 1 8,320 3,040 6,080 Limes No. 2 3,300 2,925 5.280 2,250 4.620 4,615 . Prices: The sales department has agreed on a baseline price per carton with each of the distributors as shown in the table below: Chicago Nashville Atlanta Dallas Phoenix New York $28.91 $38.37 Las Vegas $26.21 $35.53 $26.21 $27.25 $25.27 Oranges Limes $27.97 $37.98 $27.21 $37.93 $38.50 $36.04 $36.36 The baseline price is for product with grade No. 2. The distributors pay a 9% price premium for product with a grade No. 1. Availability of fruit for processing (supply from outside growers): Plant in Fresno: the company can procure a monthly supply of 1.300 tons of oranges and 700 tons of limes from outside growers. Plant in Sullivan City: the company has secured a monthly supply of 420 tons of limes from outside growers. The company estimates that 30% of the product coming into the plant from outside growers is grade No.1, while the remaining 70% is grade No. 2. Labor and machine time: Grading and packing at the company requires both machine time (e.g., grading lines, packing, labeling, etc.) and manual labor. Recently, the company conducted a time study to determine the amount of labor and machine-time required to grade, pack and store each carton of oranges and limes by quality (No.1 and No. 2). Better quality fruit can be run through the grading lines faster as it requires lower inspection time. The study was conducted at both plants. The results of this time study are provided in the Excel tabs "Machine Time" and "LaborTime". (Hint: You will need the results from this time study to build constraints in your optimization model.) Plant in Fresno: The Company has 132 employees working approximately 40 hours per week at the plant. Since you will also need to estimate the amount of machine time available, the company has provided the following information: there are 8 grading lines (machines) that can operate for 10 hours a day and 6 days per week. The lines can be used to grade and pack both oranges and limes. Plant in Sullivan City: The Company has 30 emplo working approximat 40 hours per week, and they have 3 grading lines (machines) that can operate for 9 hours a day and 6 days per week. The grading/packing lines can be used for both oranges and limes. (Hint: To facilitate analysis when estimating total labor and machine time availability, assume each month has 4 weeks). . Transportation Costs: The company has estimated a transportation cost of $1.75 per mile per truckload for the plant in Fresno and $1.70 per mile per truckload for the plant in Sullivan City. Each truck can carry 22 pallets. The transportation distance in miles from the packing facility to each of the distribution centers is summarized below: New Chicago Nashville Atlanta Dallas York Las Vegas 396 Phoenix 2.064 2,052 2.223 1.480 2.800 514 Fresno, CA Sullivan City, TX 1.453 1,147 1,161 528 2.025 1.242 1.539 Cost of Goods Sold (COGS): . . Plant in Fresno: The management in Fresno has provided the following information regarding the COGS (cost of goods sold) for the limes graded and packed at their facility. Oranges: The price paid to the growers is $8.90 per carton of regular fiuit. The growers charge a 10% premium for oranges graded No. 1. The packing and labeling cost is $1.25 per carton, and the direct labor cost allocated to each carton is estimated at $6.30. Limes: The price paid to the growers is $10.50 per carton of regular fruit. The growers charge a 10% premium for limes graded No. 1. The packing and labeling cost is $1.50 per carton, and the direct labor cost allocated to each carton is estimated at $7.50. . o Plant in Sullivan City: The management in Sullivan City, TX has provided the following information regarding the COGS (cost of goods sold) for the limes graded and packed at their facility. The price paid to the growers is $10.15 per carton of grade No. 2. The growers charge a 10% premium per carton of No.1. The packing and labeling cost is $1.60 per carton, and the direct labor cost allocated to each carton is estimated at $6.90. (Hint: To estimate the COGS per carton, you need to add the cost of the fruit, packing and labeling, and direct labor costs)