Linear Programming Case Study

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.
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.
• 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

Please submit your assignment in a Word file attached with your Excel results. Please make sure to type your answers in your Word file instead of copy pasting them from the Excel worksheet.

Bike Athletic Company is placing an order for bicycles with its supplier. Four models can be ordered: the adult Open Trail, the adult Cityscape, the girl's Sea Sprite, and the boy's Trail Blazer. It is assumed that every bike ordered will be sold, and their profits, respectively, are 30, 25, 22, and 20. There are several conditions that the company needs to worry about. One of these is space to hold the inventory. An adult's bike needs two feet, but a child's bike needs only one foot. The store has 500 feet of space. There are 1200 hours of assembly time available. The child's bike needs 4 hours of assembly time; the Open Trail needs 5 hours and the Cityscape needs 6 hours. The company would like to place an order for at least 275 bikes.

a. Formulate a model for this problem in a Word document. Identify what type of problem this is.

b. Solve your model with either Excel or QM for windows.

c. How many of each kind of bike should be ordered and what will the profit be?Report in the Word document.

d. In the Word document, report the sensitivity analysis and the shadow price results and interpret them.

e. What would the profit and the change in slack variables be if the company had 100 more feet of storage space? Report in a Word document.

f. Starting from the original problem, if the profit on the Cityscape increases to $45, will any of the Cityscape bikes be ordered?Report the new answers and the profit in the Word document.

g. Over what range of assembly hours is the dual price applicable? Report in the Word document.

h. If we require 5 more bikes in inventory, what will happen to the value of the optimal solutioncompare to the original case (both profit and the decision variables)? Report in the Word document.
Submissions should include both completed Excel file results and the completed Word document. Incomplete submissions are not accepted.