2. Design the aggregate production plan following a level strategy were you fluctuate stock levels, order backlogs, lost sales/delay in deliveries. How would I start this in excel?

0.75 7 7 1.4 DATA Hours to produce a pair in regular day Hours per working day (full time) Hours per working day (part time) Hours overtime per shift % max production with overtime Full-time workers (fixed number) Part-time workers N workers by shift Cost regular hour full time workers Cost regular hour part-time workers Cost overtime hour (in % over regular hour). Cost overtime hour full time workers Cost overtime hour part time workers Full-time workers in night shift Extra cost night shift (in % over regular hour) 20 25 45 9.5 9.1 10.925 10.465 $11.38 780 2 Idle time hour cost Hiring cost Layoff cost Holding cost per month Stock 31 december 2019 Safety stock units Cost of service -500 units delay Cost of service +500 units delay Delay orders 31 dec 2019 IN 4 7 Freestyle is a company specialized in the production and distribution of skates and skateboards. Right now, there are three production plants, two in Madrid and one in Guadalajara, where products that will be later sold to national and foreign clients are manufactured. Since the beginning of December 2019, Guadalajara plant is planning its coming year production. This factory is responsible of the production of three different kinds of skates commercialized by the company. As you can see in Appendix 1, there are three kinds of skates; ice, inline roller skates and 4 wheels roller skates. The percentage each represents in the total production are 30%, 60% and 10% respectively. The time required for making each pair is 45 minutes. With the aim of creating a production plan, the manufacturing department has forecasted 2020 demand divided in 12 months. The results (in pairs of skates) are shown in the following table. Jan Feb March April May June July Aug 12.950 6.405 7.8409.240 11.060 18.060 18.690 7.840 Sept Oct Nov Dec 8.750 9.380 12.540 15.580 The working journey of Freestyle is of 7 regular hours. The number days of available each month for 2020 are the following. Months Working days Months Working days January 20 July 23 February 20 August 21 March 21 September 24 April 21 October 23 May June 23 23 November December 22 24 Initialy (January) the workforce is composed by the 45 workers, 20 full time workers and the rest part time, working in two shifts. The numbers of initial workers should be maintained the first month. After January the maximum capacity of the plant is 30 workers each shift. The cost of full time working hour is 9,5 while the part time working hour is 9,1. It is not possible to hire or fire full time workers. In case of part time, the cost of firing is 1.550 per worker and hiring 780. If necessary, is allowed to work overtime at a cost 15% above the cost of normal working hours, been the daily limit for overtime hours 20% of regular ones (7 hours per-shift). The estimated cost for idle labor is 10 per hour If work force proposal (including overtime hours) is below capacity, you can establish a third shift. This third shift has an extra cost due to "overnight production". The extra cost paid for overnight is a 25% extra per common hour, and it will be totally fulfilled with temporary staff (part-time workers). in the event that a third shift is opened, an additional 8 temporary workers may be hired. These workers will only be able to work on the third shift. The third shift could not be opened in the first month. If overnight takes part, you won't be able to apply the option of overtime (restriction). Considering the inventory politicy, the number of units expected to close at the end of 2020 of safety stock is of 100 units. During 2021, a safety stock policy must be implemented in the different strategies. Stocking one pair of skates has a cost of 2 per month (holding cost). The skates are sold exclusively to a specialized retailer on sport materials. The cost of selling on delay (delay cost) the skates to this firm is of 4 per unit per month (pair of skates) until reached 500 units and of 7 per unit if the sold quantity is over 500 units. To apply this penalty you should take into account the units not delivered at the end of each month. June January 12,950 20 February 6,405 20 March 7,840 21 April 9.240 21 May 11,060 July 18,690 August 7.840 18,060 September October November December TOTAL 8,750 9.380 12.540 15,580 138,335 24 22 24 265 23 23 23 21 23 PRODUCTION PLAN: LEVEL STRATEGY Demand forecast Number of working days Regular Production hours Overtime production hours Night shift Production hours Full-time workers Part-time workers Change in part time workers Final Stock Delay units Total units to be produced Cost workers regular time Overtime hours cost Extra cost night shift Hiring and layoff cost Cost of idle workers Holding cost Delay cost TOTAL COST 0.75 7 7 1.4 DATA Hours to produce a pair in regular day Hours per working day (full time) Hours per working day (part time) Hours overtime per shift % max production with overtime Full-time workers (fixed number) Part-time workers N workers by shift Cost regular hour full time workers Cost regular hour part-time workers Cost overtime hour (in % over regular hour). Cost overtime hour full time workers Cost overtime hour part time workers Full-time workers in night shift Extra cost night shift (in % over regular hour) 20 25 45 9.5 9.1 10.925 10.465 $11.38 780 2 Idle time hour cost Hiring cost Layoff cost Holding cost per month Stock 31 december 2019 Safety stock units Cost of service -500 units delay Cost of service +500 units delay Delay orders 31 dec 2019 IN 4 7 Freestyle is a company specialized in the production and distribution of skates and skateboards. Right now, there are three production plants, two in Madrid and one in Guadalajara, where products that will be later sold to national and foreign clients are manufactured. Since the beginning of December 2019, Guadalajara plant is planning its coming year production. This factory is responsible of the production of three different kinds of skates commercialized by the company. As you can see in Appendix 1, there are three kinds of skates; ice, inline roller skates and 4 wheels roller skates. The percentage each represents in the total production are 30%, 60% and 10% respectively. The time required for making each pair is 45 minutes. With the aim of creating a production plan, the manufacturing department has forecasted 2020 demand divided in 12 months. The results (in pairs of skates) are shown in the following table. Jan Feb March April May June July Aug 12.950 6.405 7.8409.240 11.060 18.060 18.690 7.840 Sept Oct Nov Dec 8.750 9.380 12.540 15.580 The working journey of Freestyle is of 7 regular hours. The number days of available each month for 2020 are the following. Months Working days Months Working days January 20 July 23 February 20 August 21 March 21 September 24 April 21 October 23 May June 23 23 November December 22 24 Initialy (January) the workforce is composed by the 45 workers, 20 full time workers and the rest part time, working in two shifts. The numbers of initial workers should be maintained the first month. After January the maximum capacity of the plant is 30 workers each shift. The cost of full time working hour is 9,5 while the part time working hour is 9,1. It is not possible to hire or fire full time workers. In case of part time, the cost of firing is 1.550 per worker and hiring 780. If necessary, is allowed to work overtime at a cost 15% above the cost of normal working hours, been the daily limit for overtime hours 20% of regular ones (7 hours per-shift). The estimated cost for idle labor is 10 per hour If work force proposal (including overtime hours) is below capacity, you can establish a third shift. This third shift has an extra cost due to "overnight production". The extra cost paid for overnight is a 25% extra per common hour, and it will be totally fulfilled with temporary staff (part-time workers). in the event that a third shift is opened, an additional 8 temporary workers may be hired. These workers will only be able to work on the third shift. The third shift could not be opened in the first month. If overnight takes part, you won't be able to apply the option of overtime (restriction). Considering the inventory politicy, the number of units expected to close at the end of 2020 of safety stock is of 100 units. During 2021, a safety stock policy must be implemented in the different strategies. Stocking one pair of skates has a cost of 2 per month (holding cost). The skates are sold exclusively to a specialized retailer on sport materials. The cost of selling on delay (delay cost) the skates to this firm is of 4 per unit per month (pair of skates) until reached 500 units and of 7 per unit if the sold quantity is over 500 units. To apply this penalty you should take into account the units not delivered at the end of each month. June January 12,950 20 February 6,405 20 March 7,840 21 April 9.240 21 May 11,060 July 18,690 August 7.840 18,060 September October November December TOTAL 8,750 9.380 12.540 15,580 138,335 24 22 24 265 23 23 23 21 23 PRODUCTION PLAN: LEVEL STRATEGY Demand forecast Number of working days Regular Production hours Overtime production hours Night shift Production hours Full-time workers Part-time workers Change in part time workers Final Stock Delay units Total units to be produced Cost workers regular time Overtime hours cost Extra cost night shift Hiring and layoff cost Cost of idle workers Holding cost Delay cost TOTAL COST