ASSIGNMENT 1 LINEAR PROGRAMMING EXERCISE Sun Ltd, manufactures a variety of chemical products used by lawn care companies. Sun Ltd. was recently bought out by a conglomerate, and managers of the two organisations have been working together to improve the efciency of Son's operations. Managers have been asked to adhere to weekly operating budgets and to develop operating plans using quantitative methods whenever possible. The manager of one department has been given a weekly operating budget of $1 1,980 for production of three chemical products, which for convenience shall be referred to as Q, R and W. The budget is intended to pay for direct labour and materials. Processing requirements for the three products, on a perunit basis are shown in the table. Labour (hours) Material A (pounds) Material B (pounds) Q 5 2 1 R 4 2 - W 2 1/2 2 The company has a contractual obligation for 85 units of product R per week. Material A costs $4 per pound, as does material B. Labour costs $8.00 an hour. Product Q sells for $122 a unit, product R sells for $1 15 a unit, and product W sells for $76 a unit. The manager is considering several different proposals regarding the quantity of each product to produce. The manager is primarily interested in maximising contribution. Moreover, the manager wants to know how much labour will be needed, as well as the amount of each material to purchase. g Questions Prepare a report that addresses the following issues: a. The optimal quantities of products and the necessary quantities of labour and materials and maximum profit. b. One proposal is to make equal amounts of the products. What amount of each will maximise prot, and what quantities of labour and materials will be needed. What is the difference in profit compared to the first proposal if the second proposal is adopted? STEPS TO FOLLOW: 1. In an Excel spreadsheet, state the objective function and the constraint equations in this linear programming exercise. 2. Formulate this problem in a format that is conducive to the use of the Excel Solver. 3. Use Excel Solver to produce a linear programming report of the solution that should maximise the Sun Ltd Company's prot for the period. 4. Interpret this report by clearly stating below the report, the maximum prot the company would make and optimal quantities of products and the necessary quantities of labour and materials. 5. Explain in the context of this problem, what is the range of optimality for the prot coefcient of each variable 6. Explain in the context of this scenario the shadow price constraints. 7. In the second scenario, the proposal is to make equal amounts of the products. What amount of each will maximise prot, and what quantities of labour and materials will be needed. 8. In the second scenario identify difference in prot compared to the rst proposal if the second proposal is adopted? 9. Both scenarios should be explained in the context of the problem by using the appropriate unit of measurement. 10. Submit to the Dropbox in Excel format