PLEASE SHOW STEPS OF HOW TO ANSWER THIS QUESTION USING EXCEL !!

SUPPLY CHAIN MANAGEMENT

Question 1 Holcim (M) Sdn Bhd, a building material specialist based at Pasir Gudang. Recently, Holcim has employed a brilliant student from UniKi Mitec known as Rose to support the Head of Supply Chain Planning to manage the end-to-end supply chain activities. Refer Below. Tablet Sales Date Table 1: Sales Data SALES 2016 2017 2018 JAN 51,347.00 60,137.00 51,909.00 FEB 45,472.00 46,463.00 45,472.00 MAR 56,877.00 62,449.00 63,759.00 APR 62,329.00 70,034.00 70,178.00 MAY 51.917.00 65,741.00 83,460.00 JUNE 51,417.00 71,186.00 80,464.00 JULY 55,150.00 55,150.00 34,054.00 AUG 56,025.00 48,344.00 62,055.00 SEPT 41,059.00 48,402.00 56,007,00 OCT 52,573.00 61,708.00 53,011.00 NOV 56,551.00 57,751.00 50,788.00 DEC 60,336.00 56,611.00 62,556.00 TOTAL 641,053.00 703,976.00763,713.00 Note: Sales growth in 2019 and 2020 is recorderd at 3% and 2% respectively a) Based on the information in Table 1. assist Rose to calculate the dispatch requirement for the year 2021 and year 2022 using the following method. i. Moving Average ii. Exponential smoothing Hi. Holt's Model iv. Statics v. Winter (50 marks) (b) Analyze the results in (a), to identify the difference between the applications in determining the accuracy of the forecasting output (10 marks) JLB42032 SUPPLY CHAIN MANAGEMENT Page 1 of 4 Question 2 Holcim is supplying its product to three distributors near Johor Bahru and maintains consignment inventory (owned by Holcim) at each location. Holcim uses Kontena Nasional transportation to deliver separately to each customer. Each truck costs $800 plus $250 per stop. Thus, the delivery to each customer will cost them $1,050 per truck. Holcim is considering aggregating deliveries to Johor Bahru on a single truck. Demand at the large customer is 600 tons a year, demand at the medium customer is 240 tons per year, and demand at the small customer is 120 tons per year. The product cost for Holcim is $ 320 per ton, and it uses a holding cost of 25 percent. Truck capacity is 40 tons. Based on the information assist Rose to calculate the following (a) Annual transportation and holding cost if Holcim ship a full truckload each time a customer is running out of stock. (5 marks) (b) Annual transportation and holding cost if Holcim ships separately to each of the customer (5 marks) (c) Annual transportation and holding cost if Holcim aggregates shipments to each of the three customers on every truck that goes to Johor Bahru. (5 marks) (d) Annual cost based on a tailored policy that has lower costs than the answer in (b) or (c) (5 marks) Question 1 Holcim (M) Sdn Bhd, a building material specialist based at Pasir Gudang. Recently, Holcim has employed a brilliant student from UniKi Mitec known as Rose to support the Head of Supply Chain Planning to manage the end-to-end supply chain activities. Refer Below. Tablet Sales Date Table 1: Sales Data SALES 2016 2017 2018 JAN 51,347.00 60,137.00 51,909.00 FEB 45,472.00 46,463.00 45,472.00 MAR 56,877.00 62,449.00 63,759.00 APR 62,329.00 70,034.00 70,178.00 MAY 51.917.00 65,741.00 83,460.00 JUNE 51,417.00 71,186.00 80,464.00 JULY 55,150.00 55,150.00 34,054.00 AUG 56,025.00 48,344.00 62,055.00 SEPT 41,059.00 48,402.00 56,007,00 OCT 52,573.00 61,708.00 53,011.00 NOV 56,551.00 57,751.00 50,788.00 DEC 60,336.00 56,611.00 62,556.00 TOTAL 641,053.00 703,976.00763,713.00 Note: Sales growth in 2019 and 2020 is recorderd at 3% and 2% respectively a) Based on the information in Table 1. assist Rose to calculate the dispatch requirement for the year 2021 and year 2022 using the following method. i. Moving Average ii. Exponential smoothing Hi. Holt's Model iv. Statics v. Winter (50 marks) (b) Analyze the results in (a), to identify the difference between the applications in determining the accuracy of the forecasting output (10 marks) JLB42032 SUPPLY CHAIN MANAGEMENT Page 1 of 4 Question 2 Holcim is supplying its product to three distributors near Johor Bahru and maintains consignment inventory (owned by Holcim) at each location. Holcim uses Kontena Nasional transportation to deliver separately to each customer. Each truck costs $800 plus $250 per stop. Thus, the delivery to each customer will cost them $1,050 per truck. Holcim is considering aggregating deliveries to Johor Bahru on a single truck. Demand at the large customer is 600 tons a year, demand at the medium customer is 240 tons per year, and demand at the small customer is 120 tons per year. The product cost for Holcim is $ 320 per ton, and it uses a holding cost of 25 percent. Truck capacity is 40 tons. Based on the information assist Rose to calculate the following (a) Annual transportation and holding cost if Holcim ship a full truckload each time a customer is running out of stock. (5 marks) (b) Annual transportation and holding cost if Holcim ships separately to each of the customer (5 marks) (c) Annual transportation and holding cost if Holcim aggregates shipments to each of the three customers on every truck that goes to Johor Bahru. (5 marks) (d) Annual cost based on a tailored policy that has lower costs than the answer in (b) or (c)