Question.

Microchips Demand Forecasting (19 marks) A computer chip is a critical component composed of micro-sized transistors that act as tiny switches, controlling the flow of electrons. Despite their small size (no larger than a human fingernail), these chips play a crucial role in various industries, including appliances, airplanes, machine tools, healthcare equipment, transportation, smartphones, and electronic payment systems.

Due to the combined demand from multiple industries and the disruptions caused by the COVID19 pandemic, there is currently a global shortage of computer chips. Factors contributing to this shortage include limited worldwide factory capacity, trade wars, hoarding, production shutdowns, shipping and seaport bottlenecks, and other supply chain issues. Building new chip factories to increase production capacity takes several years, and even after completion, it takes additional months to ramp up production. Additionally, lead times for new chip orders range from six months to a year.

The average electric vehicle relies on approximately 2,000 computer chips, responsible for controlling and monitoring various functions such as fuel consumption, braking, airbags, driver assistance, music channels, navigation, communications, and electronic displays. Here we focus on a specific chip, PVENT902, which is used in vehicle airbags. The following data represents quarterly sales demand for this chip in 100,000 units over the past five years. While there is an overall increasing trend in sales, the time series pattern suggests that the demand is influenced by the quarter of the year. The objective is to forecast the future demand for this chip.

a) Determine the best moving average and simple exponential smoothing models that can be used to forecast the future demand for the PVENT902 chip. Assess the models using the Mean Squared Error (MSE) evaluation metric. (Hint: Try a range of values for the number of periods and smoothing constant to determine the best models)

b) Compare the performance of a simple linear trendline forecast with the best moving average and exponential smoothing models using the MSE metric.

c) As the sales of the chip appear to be influenced by the quarter of the year, develop a multiple regression model to forecast the sales and compute the MSE. How does this model compare to the models discussed in questions 1 and 2? (Note: if you are using Excel to build the regression mode, you should place time series and quarter variables side by side).

d) Summarize your results and recommendations in a short memo to the company.

\begin{tabular}{|c|c|c|c|} \hline Time Series & Year & Quarter & Sales* \\ \hline 1 & 1 & 1 & 4 \\ \hline 2 & 1 & 2 & 9 \\ \hline 3 & 1 & 3 & 8 \\ \hline 4 & 1 & 4 & 11 \\ \hline 5 & 2 & 1 & 11 \\ \hline 6 & 2 & 2 & 9 \\ \hline 7 & 2 & 3 & 13 \\ \hline 8 & 2 & 4 & 15 \\ \hline 9 & 3 & 1 & 14 \\ \hline 10 & 3 & 2 & 12 \\ \hline 11 & 3 & 3 & 17 \\ \hline 12 & 3 & 4 & 16 \\ \hline 13 & 4 & 1 & 16 \\ \hline 14 & 4 & 2 & 19 \\ \hline 15 & 4 & 3 & 22 \\ \hline 16 & 4 & 4 & 23 \\ \hline 17 & 5 & 1 & 20 \\ \hline 18 & 5 & 2 & 25 \\ \hline 19 & 5 & 3 & 28 \\ \hline 20 & 5 & 4 & 31 \\ \hline times [100,000] & & & \\ \hline \end{tabular}