Hi,

I need help solving this problem. Please see below.

Problem

ELLSAM Quilts,Ltd.

Ellen Bradley and Sam Bradley, Twin Sisters dreamt of owning a Quilt design studio since high school. They graduated college with fashion designing degree and specialty with Quilt Design. They opened their first shop in New York city with access to specialized fabrics.

Ellen and Sam plans to design, produce and market two custom quilts. Their design will consist of using two specialized fabrics, Silk and Cotton. They have purchased two industrial size Quilting machine to produce the custom quilts.

The Quilts are produced in sizes, Small and Large. Each batch will produce 5 Quilts. It takes 20 minutes to produce Small and 30 minutes to produce large size using cotton Fabric.For silk fabric, it will take 30 minutes for small size, and 45 minutes for large size. Prepping for each batch will take about 15 minutes, selecting the pattern and feeding the fabric.100 hours are available for production. It takes .05 hour to stitch and package each small size quilt and .10 hours for large size.

For Cotton Quilts, each batch requires 50 meters of fabric for small and 100 meters of fabric for large size. Silk quilts will require 75 meters of fabric per batch and 200 meters of fabric for large size. ELLSAM design has 7,500 meters of Cotton fabric and 10,000 meters of Silk fabric available for each week production.

Since ELLSAM Design is making designer Quilts, Cotton Quilts will earn a profit of $250 and $500 for Silk Quilts.

Ellen and Sam are curious to know how to maximize their profit. How many batches should they produce each week to maximize their profit and resource allocation.

1.Formulate a linear programming model for ELLSAM Design Ltd. and determine the mix of Silk and Cotton Quilts it should produce each week.

2.Solve the linear programming model graphically.

3.Determine the resources left over and not used.

1. Determine the sensitivity ranges for the objective function coefficients and constraint quantity values by using the graphical solution of the model.
2. Ellen and Sam would prefer to make the Silk Quilts for designer preference and creating a name for their design.
3. Solve the linear programming model by using the computer and verify the sensitivity ranges computed in (4).
4. The company that provides ELLSAM Design with fabric has indicated that it can deliver additional 500 meters of Cotton fabric 600 meters of silk fabric. Should Ellen and Sam take on this offer?

Assignment 1. Linear Programming Case Study

From your real-world of your business* formulate 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.

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, show the problem pertaining to sensitivity analysis and shadow price in Excel.

As previously noted, please set up your problem in Excel and find the solution using Solver. Clearly label the cells in your spreadsheet. You will turn in the entire spreadsheet, showing the setup of the model, and the results.