Part A Forecasting (5 Points)

Background:

Your company has done well for four straight years staring in 2017.

ABC Corporation, launched a new sophisticated data collection device using unique, patented, semiconductors chips, supplied from China. As sales grew, ABC added the necessary manpower to keep pace with demand. The data is summarized below:

Part A 10 Points To be done using Excel

1. Given the data given above, develop a forecast for variable A and B using the exponential smoothing with an alpha value of 0.5. What is the forecasted value of 2021(A)____________? 2022 (B)______________?

Please round to a whole number. No decimals required.

1. Using the same method, but with an alpha value of 0.2, forecast the anticipated manpower. What is the forecasted value of

2021 (M1)____________? 2022 (M2)______________?

Please round to a whole number. No decimals required.

1. On your spreadsheet, calculate the % change for each variable.

Part A (Continued)

1. Create a line graph for each data set in the same Excel spreadsheet.

The graph must be able to automatically reflect any changes in data.in other words, I want a working graph, not a picture. I will test the graphs capability.

1. Comparing the data, is the plant more or less efficient in 2020 Vs 2017?

Explain Your Reasoning:

Input your answer in this space.

Part B. (10 Points)

The forecast data calculated above, once approved by management, is automatically loaded into the companys ERP system and will automatically release orders to each raw material and component supplier giving them a 6 months lead time to procure raw materials, fabricate components or sub-assemblys, and ship the products to arrive at your Toronto, ON., location one (1) week prior to your shipment to your customer.

However, January 2020, Covid 19 rapidly spreads across the globe wth devastating effects on manufacturing, particularly related to manpower and delivery lead times. Six (6) month lead times quickly become 12 months lead times. The impact to global manufacturing effectiveness is as follows:

Based on your limited knowledge of Operations Management and Supply Chain Management, how do you think this situation would impact your company?

Input your answer in this space.

How does it affect your customer?

Input your answer in this space.

What if anything would suggest you might do?

Input your answer in this space.

Please submit this document with your Excel spreadsheet attached to the submission.

ABC Corporation Sales History/Forecast \begin{tabular}{|c|c|c|c|c|} \hline & Sales(D)(Milliont) & Fcst(f)(Millions) & Manpower & Fcst (f) \\ \hline 2017 & $30 & $25 & 60 & 55 \\ \hline 2018 & $35 & $33 & 70 & 65 \\ \hline 2019 & $55 & $45 & 80 & 74 \\ \hline 2020 & $70 & $60 & 100 & 85 \\ \hline 2021 & A & & M1 & \\ \hline 2022 & B & & M2 & \\ \hline \end{tabular} Global Mfg. Effectiveness \begin{tabular}{|c|c|c|} \hline \multicolumn{2}{|c|}{ Output } & \\ \hline 2017 & 98% & \\ \hline 2018 & 100% & \\ \hline 2019 & 110% & \\ \hline 2020 & 50% & Covid 19 Outbreak \\ \hline 2021 & 65% & \\ \hline 2022 & 80% & \\ \hline \end{tabular}