MMIS/MCIS 671 Assignment 2 | Complete Solution

MMIS/MCIS 671: Fundamentals of Analytics and Business Intelligence

Assignment 2: Due July 19, 2015 (Total 40 points)

Linear Programming (LP) and Sensitivity Analysis
Cycle City Inc., a large bicycle store is placing an order for bicycles with its supplier. Four models can be ordered: the adult racer, the adult Mountain bike, 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 32, 28, 25, and 20. The LP model should maximize profit. There are several conditions that the store 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 need 4 hours of assembly time; the racer needs 5 hours and the mountain bike needs 6 hours. The store would like to place an order for at least 275 bikes.

1. Formulate a model for this problem. Specifically, do the following: Identify the decision variables (make sure that you specify the units for these variables). Specify the objective function and the relevant constraints……………………………………………..(10 pts)

2. The production manager uses a DSS to make decisions about what to do. The maximum attainable profit is required. Develop a simple DSS prototype to solve this problem.
a. How many of each kind of bike should be ordered?
b. What is the total profit realized?
c. What is the profit contribution for each bike?
……………………………………………………………………………(12 pts)

3. Perform sensitivity analysis to determine: (Remember to change back to original value after each change below).
a. What would the profit be if the store had 100 hours of assembly time?
b. If the profit on the Mountain bike increases to $35, how many of those bikes should be ordered?
c. If we require 5 more bikes in inventory, what will happen to the value of the optimal solution?
………………………………………………………………………..….(12 pts)

4. Write a short memo to the management of Cycle City Inc., explaining
a. Over what range of assembly hours is the dual price applicable?
b. Which resource should the company work to increase, inventory space or assembly time? Explain.

…………………………………………………………………………….(6 pts)
Please note: You are required to submit the Microsoft Excel file and Microsoft Word file for this
Assignment. All all workings should be shown and explained.