A computer chip is essentially a series of micro-sized transistors that function as tiny switches to control the flow of electrons. Millions, sometimes billions, of transistors are packed into chips no bigger than a human fingernail. These chips are used in many industries such as appliances, airplanes, machine tools, health care equipment, transportation, smartphones, and even electronic money. The combined demand from multiple industries plus the COVID-19 pandemic disruptions triggered a global shortage of computer chips. Lack of worldwide factory capacity, trade wars, hoarding, production shutdowns, and shipping and seaport bottlenecks have contributed to the shortage. Intel, a world-class computer chip manufacturer, recently announced an investment in a new \($20\) billion chip factory which will be located outside Columbus, Ohio. But these new factories, called foundries, take years to build and many more months to ramp-up production. Lead times on new chip orders take six months to a year. The average electric vehicle uses about 2,000 computer chips controlling and monitoring everything such as fuel consumption, braking, airbags, driver assistance, music channels, navigation, communications, and electronic displays. One chip that Champion has been producing, named PVENT902, is used in vehicle airbags. Exhibit 9.19 shows quarterly sales demand in 100,000 units for the last five years. We see that sales have been increasing, but the time series pattern suggests that it is influenced by quarter. Champion wants to forecast demand in the future.

Case Questions for Discussion:

(Use appropriate Excel formulas, Excel templates, or Data Analysis tools in your analyses.)

1.What are the best moving average and exponential smoothing models that would forecast future PVENT902 chip demand? Use MSE to evaluate the models.

2.How well would a simple linear trendline forecast sales in comparison with the best moving average and exponential smoothing models? Modify the case data spreadsheet to compute MSE using formula 9.1 to evaluate the model.

3.Since sales appear to be influenced by the quarter of the year, develop a multiple regression model for forecasting sales and compute MSE. How does this model compare to those in questions 1 and 2?

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

Transcribed Image Text:

EXHIBIT 9.19 Champion Microchips, Inc. Chip Demand Time Series Year Quarter Sales* 1 4 2 1 2 9 3 1 3 8 4 1 4 11 5 2 1 11 6 2 2 9 7 2 3 13 8 2 4 15 9 3 1 14 10 3 2 12 11 3 3 17 12 3 4 16 13 4 1 16 14 4 2 19 15 4 3 16 4 4 17 5 1 18 5 2 NARA 22 23 20 25 19 5 3 28 20 5 4 31 *times (100,000) Source Microsoft Corporation