Solve and show all steps in Excel

PROBLEM 1: (30 Marks) Bangs Leisure Chairs produces three types of hand-crafted outdoor chairs that are popular for beach, pool, and patios: sling chairs, Adirondack chairs, and hammocks. The unit profit for these products is $40, $100, and $90, respectively. Each type of chair requires cutting, assembling, and finishing. The owner is retired and is willing to work six hours/ day for five days/week, so has 120 hours available each month. He does not want to spend more than 50 hours each month on any one activity (that is, cutting, assembling, and finishing). The retailer he works with is certain that all products he makes can easily be sold. Sling chairs are made up of ten wood pieces for the frame and one piece of cloth. The actual cutting of the wood takes 30 minutes. Assembling includes sewing of the fabric and the attachment of rivets, screws, fabric, and dowel rods, and takes 45 minutes. The finishing stage involves sanding, staining, and varnishing of the various parts and takes one hour. Adirondack chairs take two hours for both the cutting and assembling phases and finishing takes one hour. For hammocks, cutting takes 0.4 hour; assembly takes three hours; and finishing also takes one hour. How many of each type of chair should he produce each month to maximize profit? a) Develop a linear optimization model for the problem on a spreadsheet in Excel. (10 marks) b) What is the optimal solution for the problem? (4 marks) c) Create a Sensitivity Report and answer the following questions: (i) Suppose we forced Sliding Chairs to be in the solution, what would happen to the objective function's value? (4 marks) (ii) What are the maximum and minimum values of the objective function coefficients for sling chairs, Adirondack chairs, and hammocks for which the current optimal solution is valid? (4 marks) (iii) What do the shadow prices for Assembling Hours Used and Total Hours Used constraints mean? (4 marks) (iv) What are the maximum and minimum values for the assembly and total hours used constraints for which the current constraint value does not change? (4 marks)