Please show Excel formulas!

Case IV Aggregate Planning: Smartphone Production at Quick Tronics Rudy Hartono, general manager at QuickTronics, a contract manufacturer for consumer electronics, was headed to the annual planning meeting. He had the demand forecast for the next 12 months and the goal of the meeting was to develop an aggregate plan. Historically, Rudy has maintained a steady workforce of 667 teams in the plants. While this approach made workforce management easy, it led to a large buildup of inventory. As Rudy walked into the meeting, he wondered whether it was better to hire and fire workers as needed in order to reduce the amount of inventory held. Quick Tronics Production Planning Quick Tronics set up a large assembly factory in Batam, Indonesia, that focused on the assembly of smartphones. The Indonesian government had offered incentives leading many manufacturers to locate their factories in Batam. Many component suppliers were located close to the Quick Tronics plant and send small batches to the factory on a regular basis. Assembled phones were stored in a warehouse from where they were shipped in response to customer orders from Asia, Europe, and America. The supply chain team at Quick Tronics had worked with its customers to develop a monthly forecast of demand, as shown in Table 8-13 below. Demand for smartphones peaked in the last three months of the year. TABLE 8-13 Demand Forecast for Smartphones (in '000s Month Demand January 8,000 February 10,000 March 11,000 Apri 1 1,000 May 1 1,000 June 2,000 July 13,000 August 14,000 September 15,000 Octobe 17,000 November 19,000 December 19,000Smartphone assembly was handled by teams of 10 workers each. Each team had the capacity 1'0 assemble 125 phones per hour. The capacity of each factory was determined by the number of assembly teams deployed. Each factory operated for 20 days a month, 8 hours a day. Assembly workers were paid 4,000 Rupiah/hour (Indonesian currency) during regular time. A worker could be asked to work up to additional 10 hours per month as overtime. Overtime was paid at the rate of 6,000 Rupiah/hour. If QuickTronics chose to lay off workers, each layoff cost the company 800,000 Rupiah/worker and each hiring cost 400,000 Rupiah/worker. It cost 50 Rupiah to carry a phone in inventory from one month to the next. QuickTronics does not allow stock-outs so monthly forecasted demand must be met. The material cost for each phone was 500 Rupiah. The factory just ended December of the current year with 667 assembly teams and a million phones in inventory. The production plan at the factory attempted to meet demand in Table 8-13 at the lowest possible cost while ensuring that the factory will end December of the coming year with the same labor and inventory as the previous December. Study Question 1. What is the lowest annual cost plan under the current strategy (i.e., exibility capacity strategy) where Rudy maintains a workforce of 667 teams through the year? (Hint: Under exibility strategy, no hiring or ring workers but can use overtime from workers.) 2. What is the lowest annual cost plan under a hybrid strategy (i.e., a combination of chase and exibility strategy) if Rudy gives himself the exibility of hiring and ring teams as desired? How much cost-saving can he achieve compared to the plan in question 1? (Hint: Under chase and exibility strategy, he can both hire/re workers and/or use overtime from workers.) Tips: 1. To ensure the unit consistency, present demand quantities in their original units, e. g., the demand forecast for January is 8,000,000, etc. 2. You need to calculate the labor hour used per phone. Worker plan should be planned for teams, not individual workers. 4. A team consists of 10 workers; thus, the resulting labor hours (regular and maximum overtime) should be multiplied by 10. You need to mind the labor-related costs as well. 9