Skycell, a major European cell phone manufacturer, is making production and marketing plans for the coming year. Manufacturing is primarily an assembly operation and capacity is governed by the number of people on the production line. The plant operated 20 days a month, 8 hours each day. Production of a cell phone takes 10 minutes. Each worker is paid $20 per hour, with a 50% premium for overtime. The plant currently has 50 employees. Overtime is limited to 20 hours per employee per month. The plant currently has 500 cell phones in inventory. The cost of holding a cell phone in inventory is $13 per month. The cost of hiring a new employee is $800 and the cost of a layoff is $1200. No subcontracting is allowed. The cell phones are sold at $150 per item.

The company is considering a price promotion of 5% in either July or November. The result of a price promotion in a given month will increase sales in that month by 50% and bring forward 30% demand from the following month. Thus, July sales with a promotion will equal current July sales * 1.5 + current August sales * 0.3. The July promotion will also reduce sales in August by 30%.

Demand and costs are shown below in an embedded Excel spreadsheet.

Determine if the promotion should be done in July, November, or not done.

a

For this problem set up your entire spreadsheet and Solver program in the No Promo worksheet. Then, when you move to the July and November worksheets copy the spreadsheet (include the Load/Save option for the Solver window) to the July and November worksheets. After that, all you need to do is change demand as needed and your total revenue equation.

F G Forecasted Demand 1000 1100 1000 1200 1500 1600 1600 900 1100 800 1400 1700 14900 Month January February March April May June July August September October November December Total $ $ D E I Given Parameters Initial Inventory Material Cost Desired ending inventory Beginning Backlog Stockout (Backorder) cost Inventory holding cost Beginning work force Hiring and Training cost Layoff cost Subcontract cost Regular wage rate Overtime wage rate Working days Regular labor hours Maximum overtime labor hours Labor hours required $ 500.00 units 20.00 per unit 500.00 units units 35.00 per unit per month 13.00 per unit per month 50.00 workers 800.00 per worker 1,200.00 per worker 30.00 per unit 20.00 per hour 30.00 per hour 20 per month 8 per worker/day 20 per worker/month 3.00 hours/unit $ $ Price per unit $ 150.00 $ Calculated Parameters Regular time labor cost Regular time production rate Question 7 July Promotions Question 7 November ... 3,200.00 per worker/month 53.33 units/worker/month ... Question 5 & 6 Question 7 No Promotions F G Forecasted Demand 1000 1100 1000 1200 1500 1600 1600 900 1100 800 1400 1700 14900 Month January February March April May June July August September October November December Total $ $ D E I Given Parameters Initial Inventory Material Cost Desired ending inventory Beginning Backlog Stockout (Backorder) cost Inventory holding cost Beginning work force Hiring and Training cost Layoff cost Subcontract cost Regular wage rate Overtime wage rate Working days Regular labor hours Maximum overtime labor hours Labor hours required $ 500.00 units 20.00 per unit 500.00 units units 35.00 per unit per month 13.00 per unit per month 50.00 workers 800.00 per worker 1,200.00 per worker 30.00 per unit 20.00 per hour 30.00 per hour 20 per month 8 per worker/day 20 per worker/month 3.00 hours/unit $ $ Price per unit $ 150.00 $ Calculated Parameters Regular time labor cost Regular time production rate Question 7 July Promotions Question 7 November ... 3,200.00 per worker/month 53.33 units/worker/month ... Question 5 & 6 Question 7 No Promotions