can you explain 2 question with excel and prper formula used in this

CASE STUDY Smartphone Production at QuickTronics 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 had maintained a steady workforce of 667 teams in the plants and built inventory in the first half of the year for use in the second half. 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. QuickTronics Production Planning QuickTronics 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 QuickTronics plant and sent 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 QuickTronics had worked with its customers to develop a monthly forecast of demand, as shown in Table 8-13. Demand for smartphones peaked in the fourth quarter of the year. Smartphone assembly was handled by teams of 10 workers each. Each team had the capacity to 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 during regular time. They could be asked to work up to an additional 10 hours per month as overtime. Overtime was paid at the rate of 6,000 Rupiah/hour. If QuickTronies chose to layoff workers, each layoff cost the cornpany 800,000 Rupiah and each hiring cost 400,000 Rupiah. It cost 50,000 Rupiah to carry a phone in inventory from one month to the next. Quicktronics could also choose to delay a customer order by stocking out in a given month and filling the stockout from next month's production. Given the importance of keeping up with customer demand in this market, each unit delayed in this manoer cost 100,000 Rupiah in discounts offered to customers to keep them happy. The company had a policy of ensuring that there were no stockouts in December so the ncw year started out without any unfilled orders. The material cost for each phone was 500,000 Rupiah. The factory ended December with 667 assembly teams and a miltion phones in inventory. The production plan at the factory attempted to meet demand in Table 813 at the lowest possible cost while ensuring that the factory ended December of the coming year with the same labor and inventory as the previous December. Questions 1. What is the aninual cost of the current plan where Rudy maintains a workforce of 667 throughout the year? How much should the factory produce each month? What is the maximum inventory under this plan? 2. How much can Rudy reduce cost by if he gives himself the flexibility of hiring and firing teams as desired? How much should the factory produce each month? What is the maximum inventory under this plan? CASE STUDY Smartphone Production at QuickTronics 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 had maintained a steady workforce of 667 teams in the plants and built inventory in the first half of the year for use in the second half. 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. QuickTronics Production Planning QuickTronics 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 QuickTronics plant and sent 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 QuickTronics had worked with its customers to develop a monthly forecast of demand, as shown in Table 8-13. Demand for smartphones peaked in the fourth quarter of the year. Smartphone assembly was handled by teams of 10 workers each. Each team had the capacity to 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 during regular time. They could be asked to work up to an additional 10 hours per month as overtime. Overtime was paid at the rate of 6,000 Rupiah/hour. If QuickTronies chose to layoff workers, each layoff cost the cornpany 800,000 Rupiah and each hiring cost 400,000 Rupiah. It cost 50,000 Rupiah to carry a phone in inventory from one month to the next. Quicktronics could also choose to delay a customer order by stocking out in a given month and filling the stockout from next month's production. Given the importance of keeping up with customer demand in this market, each unit delayed in this manoer cost 100,000 Rupiah in discounts offered to customers to keep them happy. The company had a policy of ensuring that there were no stockouts in December so the ncw year started out without any unfilled orders. The material cost for each phone was 500,000 Rupiah. The factory ended December with 667 assembly teams and a miltion phones in inventory. The production plan at the factory attempted to meet demand in Table 813 at the lowest possible cost while ensuring that the factory ended December of the coming year with the same labor and inventory as the previous December. Questions 1. What is the aninual cost of the current plan where Rudy maintains a workforce of 667 throughout the year? How much should the factory produce each month? What is the maximum inventory under this plan? 2. How much can Rudy reduce cost by if he gives himself the flexibility of hiring and firing teams as desired? How much should the factory produce each month? What is the maximum inventory under this plan