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:

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.