Linear Programming need detailed solution on Excel. Please help !!! I do not have any handouts, this is a Linear Programming Question !

Case: Electric Corporation Elizabeth Smithson is the Vice President of Purchasing for the customer electronics division of the Chief Electric Corporation (CEC). The company recently introduced a new type of video camcorder that has taken the market by storm. While Henry is pleased with the strong demand for this product in the marketplace, it has been a challenge to keep up with CEC's distributors' order of this camcorder. His current challenge is how to meet requests from CEC's major distributors in Pittsburgh, Denver, Baltimore and Houston who have placed orders of 10,000, 20,000, 30,000 and 25,000 units, respectively, for delivery in two months. (There is a one-month manufacturing and a one-month shipping lead time for this product.) CEC has contract with companies in Hong Kong, Korea and Singapore who manufacture camcorders for the company under the CEC label. These contracts require CEC to order a specified minimum number of units each month at a guaranteed per unit cost. The contracts also specify the maximum number of units that may be ordered at this price. The following table summarizes these contracts: Supplier Hong Kong Korea Singapore Table 1: Monthly Purchasing Contract Provisions Unit Cost Minimum Required Maximum Required $375 20,000 30,000 $390 25,000 40,000 $365 15,000 30,000 CEC also has a standing contract with a shipping company to transport product from each of these suppliers to ports in San Francisco and San Diego. The cost of shipping from each supplier to each port is given in the following table along with the minimum required and maximum allowable number of shipping containers each month: Supplier Cost per Cost per Table 2: Monthly Shipping Contract Provision San Francisco Requirements San Diego Requirements Minimum Maximum Minimum Maximum container containers container container containers container $2,000 5 20 $2,300 5 20 $1,800 10 30 $2,100 10 30 $2,400 5 25 $2,200 5 15 Hong Kong Korea Singapore Under the terms of this contract, CEC guarantees it will send at least 20 but no more than 65 shipping containers to San Francisco each month and at least 30 but not more than 70 shipping container to San Diego each month. Each shipping container can hold 1,000 video cameras and will ultimately be tucked from the seaports to the distributors. Again, CEC has a standing contract with a trucking company to provide trucking services each month. The cost of trucking a shipping container from each port to each distributor is summarized below: 1 Questions: 1. Formulate a linear programming (LP) model to determine the least costly purchasing and distribution plan to fill the distributors' requests. 2. Use a software/programming language of your choice to obtain the optimal solution for your formulated LP model. 3. How much can the minimum and maximum amounts in table 2 and 4 be changed while we have no change in optimal basic variables? Use sensitivity analysis reports in Excel Solver to answer this question. You can also draw appropriate graphs to illustrate your analysis. 4. How much can the unit cost (of product or container) in table 1, 2 and 3 be changed while we have no change in optimal solution? Use sensitivity analysis reports in Excel Solver to answer this question. You can also draw appropriate graphs to illustrate your analysis. Case: Electric Corporation Elizabeth Smithson is the Vice President of Purchasing for the customer electronics division of the Chief Electric Corporation (CEC). The company recently introduced a new type of video camcorder that has taken the market by storm. While Henry is pleased with the strong demand for this product in the marketplace, it has been a challenge to keep up with CEC's distributors' order of this camcorder. His current challenge is how to meet requests from CEC's major distributors in Pittsburgh, Denver, Baltimore and Houston who have placed orders of 10,000, 20,000, 30,000 and 25,000 units, respectively, for delivery in two months. (There is a one-month manufacturing and a one-month shipping lead time for this product.) CEC has contract with companies in Hong Kong, Korea and Singapore who manufacture camcorders for the company under the CEC label. These contracts require CEC to order a specified minimum number of units each month at a guaranteed per unit cost. The contracts also specify the maximum number of units that may be ordered at this price. The following table summarizes these contracts: Supplier Hong Kong Korea Singapore Table 1: Monthly Purchasing Contract Provisions Unit Cost Minimum Required Maximum Required $375 20,000 30,000 $390 25,000 40,000 $365 15,000 30,000 CEC also has a standing contract with a shipping company to transport product from each of these suppliers to ports in San Francisco and San Diego. The cost of shipping from each supplier to each port is given in the following table along with the minimum required and maximum allowable number of shipping containers each month: Supplier Cost per Cost per Table 2: Monthly Shipping Contract Provision San Francisco Requirements San Diego Requirements Minimum Maximum Minimum Maximum container containers container container containers container $2,000 5 20 $2,300 5 20 $1,800 10 30 $2,100 10 30 $2,400 5 25 $2,200 5 15 Hong Kong Korea Singapore Under the terms of this contract, CEC guarantees it will send at least 20 but no more than 65 shipping containers to San Francisco each month and at least 30 but not more than 70 shipping container to San Diego each month. Each shipping container can hold 1,000 video cameras and will ultimately be tucked from the seaports to the distributors. Again, CEC has a standing contract with a trucking company to provide trucking services each month. The cost of trucking a shipping container from each port to each distributor is summarized below: 1 Questions: 1. Formulate a linear programming (LP) model to determine the least costly purchasing and distribution plan to fill the distributors' requests. 2. Use a software/programming language of your choice to obtain the optimal solution for your formulated LP model. 3. How much can the minimum and maximum amounts in table 2 and 4 be changed while we have no change in optimal basic variables? Use sensitivity analysis reports in Excel Solver to answer this question. You can also draw appropriate graphs to illustrate your analysis. 4. How much can the unit cost (of product or container) in table 1, 2 and 3 be changed while we have no change in optimal solution? Use sensitivity analysis reports in Excel Solver to answer this question. You can also draw appropriate graphs to illustrate your analysis