Recall Silver Star Bicycle Company (page 51 of your course packet) that will be manufacturing both mens and womens models for its Easy-Pedal 10-speed bicycles during the next 2 months. The company wants to develop a production schedule indicating how many bicycles of each model should be produced in each month. Current demand forecasts call for 150 mens and 125 womens models to be shipped during the first month and 200 mens and 150 womens models to be shipped during the second month. Additional data are shown.

Model	Production Cost	Manufacturing Labor Reqt (hrs)	Assembly Labor Reqt (hrs)	Current Inventory
Mens	$120	2.0	1.5	20
Womens	$90	1.6	1.0	30

Last month the company used a total of 1000 hours of labor. The companys labor relations policy will not allow the combined total hours of labor (manufacturing plus assembly) to increase or decrease by more than 100 hours from month to month. In addition, the company charges monthly inventory at the rate of 2% of the production cost based on the inventory level at the end of the month. The company would like to have at least 25 units of each model in inventory at the end of the 2 months. Establish a production schedule that minimizes production and inventory costs and satisfies the labor-smoothing, demand, and inventory requirements. Objective function value is $67,156.03.

• The Model sheet must clearly show ALL input data as well as:

• for each month and product type, the production and inventory schedules,
• for each month and product type, the number of bicycles on hand after production, and
• for each month, the minimum and maximum labor hour limits, and the actual labor hours used.

• There must be a cost summary that clearly shows:

• the production costs (by product type) for each month and total cost for the entire period,
• the inventory costs (by product type) for each month and the total cost for the entire period,
• total monthly cost by product type as well as the overall total cost for month, and
• total production cost for each month, total inventory cost for each month, as well as the overall total cost for the entire period.

• All calculations must be done in your spreadsheet model. Penalty for calculations outside Excel.
• All data must be to 2 decimal places and formatted correctly.
• Plan the arrangement of your model so that all information is clearly labelled, visible and requires no/minimal scrolling.
• Generate only the Answer Report.

Silver Star Bicycle LP model

MPi = number of mens bicycles produced in month i WPi = number of womens bicycles produced in month i MIi = number of mens bicycles in inventory at end of month i WIi = number of mens bicycles in inventory at end of month i where i = 1, 2

MIN 120(MP1 + MP2) + 90(WP1 + WP2) + (0.02)(120)(MI1 + MI2) + (0.02)(90)(WI1 + WI2) Subject to Conservation constraints for each product for each period:

20 + MP1 = 150 + MI1 ) Month 1 30 + WP1 = 125 + WI1 )

MI1 + MP2 = 200 + MI2 ) Month 2 WI1 + WP2 = 150 + WI2 )

MI2 25 WI2 25

Labor policy constraints for each month:

Labor hrs. used this month Labor hrs. used last month - 100 Labor hrs. used this month Labor hrs. used last month + 100

3.5 MP1 + 2.6 WP1 1000 100 Month 1 3.5 MP1 + 2.6 WP1 1000 + 100

3.5 MP2 + 2.6 WP2 3.5 MP1 + 2.6 WP1 100 Month 2 3.5 MP2 + 2.6 WP2 3.5 MP1 + 2.6 WP1 + 100

All MPi, WPi, MIi WIi 0