Based on the information below, please help me fill out this excel document for question 1 and 2.

June CASE STUDY Smartphone Production at QuickTronics Rudy Hartono, general manager at QuickTronics, a con- tract manufacturer for consumer electronics, was headed TABLE 8-13 Demand Forecast for Smartphones (in '000s) to the annual planning meeting. He had the demand fore- cast for the next 12 months and the goal of the meeting Month was to develop an aggregate plan. Historically, Rudy had Demand maintained a steady workforce of 667 teams in the plants January 8,000 and built inventory in the first half of the year for use in February 10,000 the second half. While this approach made workforce March 11,000 management easy, it led to a large buildup of inventory. As Rudy walked into the meeting, he wondered whether April 11,000 it was better to hire and fire workers as needed in order May 11,000 to reduce the amount of inventory held. 12,000 QuickTronics Production Planning July 13,000 August 14,000 QuickTronics set up a large assembly factory in Batam, Indonesia, that focused on the assembly of smartphones. September 15,000 The Indonesian government had offered incentives lead- October 17,000 ing many manufacturers to locate their factories in November 19,000 Batam. Many component suppliers were located close to December 19,000 the QuickTronics plant and sent small batches to the fac- tory on a regular basis. Assembled phones were stored in demand in this market, each unit delayed in this manner a warehouse from where they were shipped in response cost 100,000 Rupiah in discounts offered to customers to to customer orders from Asia, Europe, and America. The keep them happy. The company had a policy of ensuring supply chain team at QuickTronics had worked with its that there were no stockouts in December so the new customers to develop a monthly forecast of demand, as shown in Table 8-13. Demand for smartphones peaked cost for each phone was 500,000 Rupiah. year started out without any unfilled orders. The material in the fourth quarter of the year. The factory ended December with 667 assembly Smartphone assembly was handled by teams of 10 teams and a million phones in inventory. The produc- workers each. Each team had the capacity to assemble tion plan at the factory attempted to meet demand in 125 phones per hour. The capacity of each factory was Table 8-13 at the lowest possible cost while ensuring determined by the number of assembly teams deployed. that the factory ended December of the coming year Each factory operated for 20 days a month, 8 hours a with the same labor and inventory as the previous day. Assembly workers were paid 4,000 Rupiah/hour December during regular time. They could be asked to work up to an additional 10 hours per month as overtime. Overtime Questions was paid at the rate of 6,000 Rupiah/hour. If QuickTron- 1. What is the annual cost of the current plan where Rudy ics chose to layoff workers, each layoff cost the company 800,000 Rupiah and each hiring cost 400,000 Rupiah. It maintains a workforce of 667 throughout the year? How much should the factory produce each month? What is the cost 50,000 Rupiah to carry a phone in inventory from maximum inventory under this plan? one month to the next. Quicktronics could also choose to 2. How much can Rudy reduce cost by if he gives himself the delay a customer order by stocking out in a given month flexibility of hiring and firing teams as desired? How and filling the stockout from next month's production. much should the factory produce each month? What is the Given the importance of keeping up with customer maximum inventory under this plan? Input Data (Costs etc.) Item Material cost/unit Inventory holding cost/unit/month Marginal cost of stockout/unit/month Hiring and training cost/worker Layoff cost/worker Team labor hours required/ thousand units Regular time cost/hour Over time cost/hour Maximum overtime per worker per month Team size Cost ('000) 500 50 100 400 800 8 4 6 10 6670 Aggregate Plan Decision Variables (demand, inventory, production in '000s) Constraints Inventory Overtime Production Workforce 0 0 0 667 0 -7,000 -10,000 -11,000 -11,000 -11,000 -12,000 -13,000 - 14,000 -15,000 -17,000 - 19,000 -19.000 0 0 0 0 O 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 t Lt Wt Ot It St Pt Period # Teams Hired # Teams Laid off # Teams Overtime Inventory Stockout Production Demand 0 0 01 667 0 1,000 0 1 0 01 0 0.0 0.0 0.0 0 8,000 2 2 0 0 0 0 0.0 0.0 0.0 0 10,000 3 0 0 0 0 0.0 0.0 0.0 0 0 11,000 4 0 0 0 0.0 0.0 0.0 0 0 11,000 5 0 0 0 0 0.0 0.0 0.0 0 11,000 6 0 U 0 0 U 0.0 0.0 0.0 0 0 12,000 7 0 0 0 0.0 0.0 0.0 0 0 13,000 81 0 0 0 0.0 0.0 0.0 0 0 14,000 91 0 0 0 0.0 0.0 0.0 0 0 15,000 10 0 0 01 0 0.0 0.0 0.0 0 17,000 11 0 0 0 0 0.0 0.0 0.01 0 19,000 12 0 0 0 0.01 0.0 0.0 0 19,000 160,000 Costs Period Hiring Lay off Regular Time Overtime Inventory Stockout Material 1 0 0 0 0 0 0 #VALUE! 2 2 0 0 0 0 0 #VALUE! 3 0 0 0 0 0 #VALUE! 4 4 U 0 0 0 0 0 0 0 #VALUE! 5 0 0 0 0 0 0 o #VALUE! 0 6 0 0 01 0 0 0 0 0 o #VALUE! 7 0 0 0 0 0 0 #VALUE! 8 01 0 0 0 0 0 #VALUE! 9 0 0 0 0 0 0 0 #VALUE! 10 0 0 0 0 0 0 #VALUE! 11 0 0 0 0 0 0 0 0 #VALUE! 12 0 0 0 0 0 0 0 #VALUE! Total 0 0 0 0 0 0 0 #VALUE! Total Cost = #VALUE! 20,000 18,000 16,000 14,000 12,000 10,000 Inventory -Production 8,000 Demand 6,000 4,000 2,000 0 HA 1 2 3 4 5 6 7 2 4 8 9 10 11 12 13 The cost data is provided on worksheet Data. Use Data | Solver to obtain the optimal production plan. This is the optimal production plan when hring/firing is not allowed