Your instructor will assign a linear programming project for this assignment according to the following specifications.

It will be a problem with at least three (3) constraints and at least two (2) decision variables. The problem will be bounded and feasible. It will also have a single optimum solution (in other words, it won?t have alternate optimal solutions). The problem will also include a component that involves sensitivity analysis and the use of the shadow price.

I have attached the case study.

You will be turning in two (2) deliverables, a short writeup of the project and the spreadsheet showing your work.

Writeup.

Your writeup should introduce your solution to the project by describing the problem. Correctly identify what type of problem this is. For example, you should note if the problem is a maximization or minimization problem, as well as identify the resources that constrain the solution. Identify each variable and explain the criteria involved in setting up the model. This should be encapsulated in one (1) or two (2) succinct paragraphs.

After the introductory paragraph, write out the L.P. model for the problem. Include the objective function and all constraints, including any non-negativity constraints. Then, you should present the optimal solution, based on your work in Excel. Explain what the results mean.

Finally, write a paragraph addressing the part of the problem pertaining to sensitivity analysis and shadow price.

Excel.

Strayer University MAT 540 Week 8 Case Analysis - Assignment 1 Dr. Suzanne Page Case Problem Mossaic Tiles, Ltd. Taken from page 108 in the textbook Gilbert Moss and Angela Pasaic spent several summers during their college years working at archaeological sites in the Southwest. While at those digs, they learned how to make ceramic tiles from local artisans. After college they made use of their college experiences to start a tile manufacturing firm called Mossaic Tiles, Ltd. They opened their plant in New Mexico, where they would have convenient access to special clay they intend to use to make a clay derivative for their tiles. Their manufacturing operation consists of a few relatively simple but precarious steps, including molding the tiles, baking, and glazing. Gilbert and Angela plan to produce two basic types of tile for use in home bathrooms, kitchens, sunrooms, and laundry rooms. The two types of tile are a larger, single-colored tile and a smaller, patterned tile. In the manufacturing process, the color or pattern is added before a tile is glazed. Either a single color is sprayed over the top of a baked set of tiles or a stenciled pattern is sprayed on the top of a baked set of tiles. The tiles are produced in batches of 100. The first step is to pour the clay derivative into specially constructed molds. It takes 18 minutes to mold a batch of 100 larger tiles and 15 minutes to prepare a mold for a batch of 100 smaller tiles. The company has 60 hours available each week for molding. After the tiles are molded, they are baked in a kiln: 0.27 hour for a batch of 100 larger tiles and 0.58 hour for a batch of 100 smaller tiles. The company has 105 hours available each week for baking. After baking, the tiles are either colored or patterned and glazed. This process takes 0.16 hour for a batch of 100 larger tiles and 0.20 hour for a batch of 100 smaller tiles. Forty hours are available each week for the glazing process. Each batch of 100 large tiles requires 32.8 pounds of the clay derivative to produce, whereas each batch of smaller tiles requires 20 pounds. The company has 6,000 pounds of the clay derivative available each week. Mossaic Tiles earns a profit of $190 for each batch of 100 of the larger tiles and $240 for each batch of 100 smaller patterned tiles. Angela and Gilbert want to know how many batches of each type of tile to produce each week to maximize profit. In addition, they have some questions about resource usage they would like answered. A. Formulate a linear programming model for Mossaic Tiles, Ltd B. Solve the linear programming model by using the computer and determine the sensitivity ranges C. Mossaic believes it may be able to reduce the time required for molding to 16 minutes for a batch of larger tiles and 12 minutes for a batch of smaller tiles. How will this affect the solution? D. The company that provides Mossaic with clay has indicated that it can deliver an additional 100 pounds each week. Should Mossaic agree to this offer? Microsoft Excel 14.0 Answer Report Worksheet: [New Microsoft Excel Worksheet.xlsx]Sheet1 Report Created: 2/29/2016 10:46:35 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.016 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $G$31 Zmax capacity Original Value Final Value $ 47,886.60 $ 47,886.60 Variable Cells Cell Name $C$31 Optimal x $D$31 Optimal y Original Value 56.70 154.64 Constraints Cell Name $E$25 used $E$26 used $E$27 used $E$28 used Final Value Integer 56.70 Contin 154.64 Contin Cell Value Formula 55.67 $E$25= 0 Part B Zmax $ Constraints Optimal x 190.00 $ y 240.00 x 0.30 0.27 0.16 32.80 y 0.25 0.58 0.20 20.00 x 56.70 y 154.64 Part C There would be no change in optimal solution if molding time is reduced. This is because that we have idle capacity of molding hours. Part D No, Mossaic should not accept the offer as it has got idle capacity of clay. used 55.67 105.00 40.00 4952.58 = 0 When we solve the above problem with the help of simplex method, We find that optimal solution is to produce 56.70 batches of large tiles and 154.64 batches of small tiles to get the maximum profit of $ 47,886.60. A sensitivity analysis is a technique used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. In other words, Sensitivity analysis is a way to predict the outcome of a decision if a situation turns out to be different compared to the key prediction(s). On doing sensitivity analysis, we can find shadow price or the opportunity gain lost if one additional unit of limiting factor is made available. Shadow price of limiting factor: Time to glaze per batch (in hours) is $ 1170.10 Shadow price of limiting factor: Time to bake per batch (in hours) is $ 10.31 The shadow price of other two constraints are 0, as they have idle capacity. Microsoft Excel 14.0 Answer Report Worksheet: [New Microsoft Excel Worksheet.xlsx]Sheet1 Report Created: 2/29/2016 10:46:35 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. Solver Engine Engine: Simplex LP Solution Time: 0.016 Seconds. Iterations: 2 Subproblems: 0 Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Max) Cell Name $G$31 Zmax capacity Original Value Final Value $ 47,886.60 $ 47,886.60 Variable Cells Cell Name $C$31 Optimal x $D$31 Optimal y Original Value 56.70 154.64 Constraints Cell Name $E$25 used $E$26 used $E$27 used $E$28 used Final Value Integer 56.70 Contin 154.64 Contin Cell Value Formula 55.67 $E$25= 0 Part B Zmax $ Constraints Optimal x 190.00 $ y 240.00 x 0.30 0.27 0.16 32.80 y 0.25 0.58 0.20 20.00 x 56.70 y 154.64 Part C There would be no change in optimal solution if molding time is reduced. This is because that we have idle capacity of molding hours. Part D No, Mossaic should not accept the offer as it has got idle capacity of clay. used 55.67 105.00 40.00 4952.58 = 0 When we solve the above problem with the help of simplex method, We find that optimal solution is to produce 56.70 batches of large tiles and 154.64 batches of small tiles to get the maximum profit of $ 47,886.60. A sensitivity analysis is a technique used to determine how different values of an independent variable will impact a particular dependent variable under a given set of assumptions. In other words, Sensitivity analysis is a way to predict the outcome of a decision if a situation turns out to be different compared to the key prediction(s). On doing sensitivity analysis, we can find shadow price or the opportunity gain lost if one additional unit of limiting factor is made available. Shadow price of limiting factor: Time to glaze per batch (in hours) is $ 1170.10 Shadow price of limiting factor: Time to bake per batch (in hours) is $ 10.31 The shadow price of other two constraints are 0, as they have idle capacity