Need the most help with developing it in an excel spreadshee

Need the most help with developing it in an excel spreadsheet and solving with the solver

Question 2: Modern Textile Mills produces two types of cotton cloth, denim and corduroy. Corduroy is a heavier grade of cotton cloth and requires 5 pounds of raw cotton per yard, whereas denim requires 3 pounds of raw cotton per yard. A yard of corduroy requires 3.6 hours of processing time; a yard of denim requires 3.0 hours. Although the demand for denim is practically unlimited, the maximum demand for corduroy is 20 yards per month. The manufacturer has 150 pounds of cotton and 90 hours of processing time available each month The manufacturer wants to make sure that it produces denim at least as twice the size of the corduroy produced. The manufacturer makes a profit of $5.00 per yard of corduroy and $2.50 per yard of denim. The manufacturer wants to know how many yards of each type of cloth to produce to maximize profit. a. Formulate an LP model to find the number of units of each product to be produced to maximize the profit. b. Find the optimal solution using graphical approach. c. Develop an excel spreadsheet model d. Find the optimal solution by solving the spreadsheet model.

Solution

Modern Textiles Mills Cotton required in pounds per yard Processing time in hours Profit in USD per yard Max Demand in yards Denim 3 3 2.5 130 Corduroy 5 3.6 5 20 Manufacturing capacity Raw material cotton in pounds 150 Processing time in hours 90 Variables: x1 Denim x2 corduroy x1 x2 Constraints Raw material available 3x1+5x2<=150 0 <= 150 Time available 3x1+3.6x2<=90 0 <= 90 Maximize 0 x1 x2 Objective function 2.5 5 Maximize Profit 0 Constraints Resouces Used Resources available Rawmaterial available 3 5 0 <= 150 158 <= 150 processing time 3 3.6 0 <= 90 96.6 <= 90 order size 120 30 Total Profit Name the range 1125 Unit profit 2.5 order size 120 processing time 6.6 resources available 240