73-220 Excel Assignment 2Summer, 2016
This assignment is due on Friday, July15th 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 / Q4production 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 capacity140015001000
quarterly demand100016001100
production costs/unit$500$525$515
storage costs/unit $12 $13 $15
initial inventory 30
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.