Problem 1 [30 pts] "A company produces skateboards in the US. The sales forecast in thousand of units for the coming year are given in the following table. The company can produce up 30,000 skateboards per month. As necessary, the company may augment its production by up to 50% through overtime working, but this increases the production cost for a skateboard from the usual $32 to $40. Currently there are 2,000 skateboards in stock. The inventory holding cost is estimated at $5 per unit held at the end of a month." (1) Build under Excel a production planning model for the following problem. Adapt the template provided in the lecture (under Week 4) for the production of video cams. Hint: In your model, create three rows for variables: Regular Units (monthly), Overtime Units (monthly) and End-of-month Inventory. Regular units have a monthly capacity of 30,000 and overtime units have a capacity of 15,000. (2) Solve the model and report your solution and cost. Jan Feb March April May June July Aug Sep Oct Nov Dec 30 15 15 25 33 40 45 45 26 14 25 30 Below is a preview of a suboptimal solution: 1 2 3 4 5 6 7 8 Input Data Demand Regular unit cost 9 10 14,000 11 25,000 12 30,000 30,000 15,000 40,000 45,000 45,000 26,000 32 15,000 25,000 33,000 capacity RG 30,000 capacity OT 15,000 40 OT unit cost unit holding costs Decisions production regular production OT 15000 0 28000 30000 0 0 30000 30000 30000 26000 10000 15000 15000 14000 25000 30000 0 0 0 0 Dashboard 3 4 5 7 9 11 12 6 2,000 8 2,000 10 2,000 2,000 2,000 5,000 2,000 2,000 2,000 2,000 5 30000 0 0 1 2,000 2,000 9696000 1600000 135000 11431000 inventory cost regular production OT production inventory total cost 15000 0 2 2,000 Problem 1 [30 pts] "A company produces skateboards in the US. The sales forecast in thousand of units for the coming year are given in the following table. The company can produce up 30,000 skateboards per month. As necessary, the company may augment its production by up to 50% through overtime working, but this increases the production cost for a skateboard from the usual $32 to $40. Currently there are 2,000 skateboards in stock. The inventory holding cost is estimated at $5 per unit held at the end of a month." (1) Build under Excel a production planning model for the following problem. Adapt the template provided in the lecture (under Week 4) for the production of video cams. Hint: In your model, create three rows for variables: Regular Units (monthly), Overtime Units (monthly) and End-of-month Inventory. Regular units have a monthly capacity of 30,000 and overtime units have a capacity of 15,000. (2) Solve the model and report your solution and cost. Jan Feb March April May June July Aug Sep Oct Nov Dec 30 15 15 25 33 40 45 45 26 14 25 30 Below is a preview of a suboptimal solution: 1 2 3 4 5 6 7 8 Input Data Demand Regular unit cost 9 10 14,000 11 25,000 12 30,000 30,000 15,000 40,000 45,000 45,000 26,000 32 15,000 25,000 33,000 capacity RG 30,000 capacity OT 15,000 40 OT unit cost unit holding costs Decisions production regular production OT 15000 0 28000 30000 0 0 30000 30000 30000 26000 10000 15000 15000 14000 25000 30000 0 0 0 0 Dashboard 3 4 5 7 9 11 12 6 2,000 8 2,000 10 2,000 2,000 2,000 5,000 2,000 2,000 2,000 2,000 5 30000 0 0 1 2,000 2,000 9696000 1600000 135000 11431000 inventory cost regular production OT production inventory total cost 15000 0 2 2,000
