PLEASE SUBMIT YOUR EXECUTIVE REPORT AND A SUPPORTING EXCEL SPREADSHEET Background The Global Canned Seafood Market is expected to reach $42.1 billion by 2028. BC Premium Seafood is a company based in Vancouver, British Columbia, Canada, and specializes in conditioned and canned table-ready seafood products. The company was founded in the late seventy by Mr. Nemo as a local West Coast family business. The mission statement of the company reads as follows: "We provide our customers with high quality, straightforward and sustainable fish products by working with all stakeholders. We take all the necessary steps to ensure that our customers are satisfied with our products and services. We also take pride in purchasing our products and materials directly from the most reputable suppliers and sustainable seafood sourcing. BC Premium Seafood will continue to be Canada's premier seafood source destination." Over the years, Mr. Nemo has built a successful business and established stable business relationships with Canada's major food retail chains. Today the company owns a manufacturing plant in Vancouver and two distribution centers: one in Vancouver and another in the Calgary urban region. However, BC Premium Seafood pays a 15% penalty of the sale price (production cost plus profit margin) for any portion of unfulfilled orders received from their customers. Figure 1 shows the company's supply chain, and figure 2 shows the geographic location of each facility in this supply chain. The company produces canned seafood packed in a sealed container, which are then packaged in cardboard boxes measuring about 30.48 cm x 30.861 cm x 60.96 cm (approximate volume of each box is 57,341.61 cm3 or 2.025 ft3). Each box contains 200 cans. The average production cost per box of 200 cans is $300/box. The company profit margin is 65% of the production cost. The company owns a fleet of trucks for transportation between its two warehouses and third-party distribution centers. The average transportation cost by truck is $0.25/box/hour. The ordering cost from any warehouse to the plant is $5,000/order. At any warehouse, the annual holding cost is estimated at $200/box. The company operates 260 days/year with a maximum production capacity of about 400,000 cans/day. Assume an average of 5 business days/week and 21.7 days/month. The factory production setup usually costs around $375,000 to restart the production. The company owns a fleet of trucks for transportation between its two warehouses and third-party distribution centers. Table 1 provides the following information: Average transportation time to move the product between two points in the supply chain. Lead time for orders including the average transportation time. Standard deviation of the lead time. During the last few years, the company started experiencing significant problems. Freshly equipped with your MBA credential, Mr. Nemo Junior, VP of Supply Chain and Operations, hired you to join his company to help improve the situation. A few months on the job, Mr. Nemo Jr. invited you for an important meeting in his office, and the following conversation took place: Mr. Nemo Jr. You know, Son/Daughter! The situation is not good... Our operation costs are very high, and our customers are complaining about stockouts, but our logistics costs are high. We are not compromising on our operations. How is this possible? Things started to get bad during the last few years, you know! You I see! I learned that so many factors and uncertainties would impact the supply chain performance. We have good supply arrangements with local suppliers, but we are probably missing the boat- so to speak-on the production and demand management. Mr. Nemo Jr. You are right. Year in and year out, our forecasting is very poor despite huge investment in a sophisticated ERP system. We run into stockouts, and we have to pay penalties for any portion of unfulfilled order. We are losing a lot of money, you know! There are times where our warehouses are full, and we run out of space for the new production. You Yes, Sir! I learned a few techniques to improve demand forecasting and reduce demand uncertainties. I also think that we should review our production and inventory management approach. Mr. Nemo Jr. What do you mean? You Forecasting is not about applying the right technique, but the forecasting process should be coordinated along the supply chain. In my opinion, when the factory production, the warehouse and distribution centers forecasts are not well coordinated, it creates opportunities for the uncertainty to increase. It is known as the bullwhip effect. I also believe that we need to review the inventory management approach and if required the factory production. Mr. Nemo Jr. I see. What can you do about it? You I will examine the data and apply forecasting approaches and... Mr. Nemo Jr. I am sure you will do. Is two weeks sufficient to prepare your first report? You I trust so. Can I come back to ask you additional questions or obtain clarifications? Mr. Nemo Jr. Absolutely, you can reach out any time. Mr. Nemo Jr. secretary Sir, your next appointment is here. Mr. Nemo Jr. Tell them to enter, and for you son/daughter, please do your best. I look forward to reading your report as soon as possible. You Thank you for the time and your trust, Sir. I will do my best. Having submitted your first report on forecasting, Mr. Nemo Jr. invites you to a meeting again to further discuss the inventory management. Mr. Nemo Jr. Thank you for your first report on demand forecasting. I now have a better understanding of the next year demand. I was impressed that you captured the seasonality pattern and trend of demand over the years. I also believe that we need to review the inventory management approach and if required the factory production. For the next step, please examine our inventory management policies. You What exactly do you want me to examine? Mr. Nemo Jr. I want you to explore better production and inventory management solutions. You I see. I can examine the optimal order quantity for each warehouse and the optimal production quantity for the plant. Mr. Nemo Jr. Once you examine the production, I want you to tell me how we should prevent any stockout. You It is impossible to prevent all stockouts, nonetheless I will examine how to improve the situation. What would be an acceptable service level? Mr. Nemo Jr. I target a 99% service level, but please examine other service levels such as 95% and 90%. I need to know how much safety stock we should keep at each distribution centre. You Yes, sir! I will do. Mr. Nemo Jr. I am sure you will do. Is two weeks sufficient again to prepare your second report? You I trust so. Can I come back to ask you additional questions or obtain clarifications? Mr. Nemo Jr. Absolutely, you can reach out any time. Mr. Nemo Jr. secretary Sir, your next appointment is here. Mr. Nemo Jr. Tell them to enter, and for you son/daughter, please do your best. I look forward to reading your report as soon as possible. You Thank you for the time and your trust, Sir. I will do my best. Again, you left Mr. Nemos office excited with this opportunity to demonstrate your skills, but also worried if you do not deliver to lose a golden opportunity of a golden job. It is your time to shine again. Questions In the first step, you should prepare an executive report that includes the following analyses: 1. Apply the forecasting principles and techniques to calculate monthly demand forecasts for the factory in 2022/2023. 2. Report the patterns observed in the demand forecasts along with the forecast errors. 3. Suggest a policy to reduce the cost of inventory: a. Knowing that the Vancouver warehouse is co-located with the factory, suggest an inventory management policy for each warehouse (Vancouver and Calgary). b. Based on your previous answers, what is the total cost of inventory? 4. What is the optimal production quantity for the plant? 5. Suggest a safety stocks policy to reduce the cost of stockouts: a. What are the safety stock quantities required at each distribution centre for each service level? Which service level would you recommend to Mr. Nemo? Why? You should submit a legible Excel Spreadsheet with an executive summary report structured according to the questions above. Your report should be well-structured summary (including charts and tables) for Mr. Nemo Jr. based on your analysis with all appropriate references to the Excel Spreadsheet showing your calculations in an easy way to comprehend.