supply chain management problems in Excel

For this HW, feel free to use Excel to calculate your answers. Although, Excel is allowed, it is expected that you write down the formulations of your models when asked to do so. Also, if you do use Excel, you must submit your Excel template with your homework. Please answer the following questions for the following problems: Problem 1. (5 points) The Seahawk Manufacturing Company has discontinued the production of a certain unprofitable product line. This act created considerable excess production capacity. Management is considering devoting this excess capacity to one or more of three products, Products 1, 2, and 3. The available capacity of the machines that might limit output is summarized in the following table. Machine Type Avaliable Time in machine-hours per week) Milling Machine 500 Lathe 350 Grinder 150 The number of machine-hours required for each unit of the respective products are shown in the next table. Product 3 Machine Type Milling Machine Lathe Grinder Productivity Coefficient (in Machine Hours per Unit Product 1 Product 2 9 5 4 3 0 0 2 SCM 478 | This material is for your use during the class only and your future individual reference. This material may not be transferred, uploaded, or sold elsewhere outside this course. SCM 478 - Supply Chain Modeling & Analysis The Sales Department indicates that the sales potential for Products 1 and 2 exceeds the maximum production rate and that the sales potential for product 3 is 20 units per week. The unit profit would be SSO, 820, and S25, respectively, for Products 1, 2 and 3. The objective is to determine how much of each product Seahawk should produce to maximize profit. Indicate why this is a resource-allocation problem by identifying both the activities and the limited resources to be allocated to these activities. b. Summarize the model in algebraic form. c. Formulate a spreadsheet model for this problem. Then use Solver lo solve the model