Reference no: EM131130413

This assignment is due on Friday, July15^th at 11:59pm.  The file (S16220ExcelAssign2) containing the data for this assignment can be found on Blackboard and your solution (by completing the worksheet containing the data and using Solver) is to be submitted through Blackboard.

The problem for this assignment is a production scheduling problem and is to be solved using the creative approach as illustrated in my creative Excel solution to problem 14 in chapter 4.  That is, the solution should look similar to the following solution:

	Q1	Q2	Q3	Q4
production cost/unit	$10,000	$11,000	$12,100	$13,310
demand	2000	4000	3000	1500
storage cost/unit	$250	$250	$300	$300
capacity	4000	3000	2000	4000
beginning inventory	100
beginning inventory	100	2100	1100	100
production	4000	3000	2000	1900
demand	2000	4000	3000	1500
ending inventory	2100	1100	100	500
total cost	$123,469,000

The scenario for this problem is as follows:

A manufacturer of ebikes wants to schedule its production over the next 3 quarters of the year so that it can minimize total production and storage costs over this period while meeting quarterly demands.  The following table includes quarterly production capacities, quarterly demands, production costs per unit in each quarter, storage costs per unit in each quarter (based on ending inventory levels for that quarter) and the beginning inventory in quarter 1.

                                                          Quarter 1             Quarter 2              Quarter 3

production capacity                             1400                       1500                       1000

quarterly demand                               1000                       1600                       1100

production costs/unit                         $500                       $525                       $515

storage costs/unit                               $12                          $13                         $15                                                      

initial inventory

Based on this information and the need for at least 60 ebikes in inventory at the end of quarter 3, find the optimal solution that will allow the manufacturer to minimize total costs while satisfying the demands each quarter.  If you solved this correctly, your total cost should be $1,921,390. You do not need to produce an answer or sensitivity report.