Data Cleaning And Also Regression

PROJECT 1: Intel’s Semiconductor Chip Aggregate Production Plan Intel is the leading U.S. producer of semiconductor chips that are in high demand due to their use in the automotive industry as well as consumer electronics. There are currently huge global shortages of semiconductor chips due to increased demand and the pandemic impact on supply chains (read articles posted in Project 1 Brightspace module). Intel currently captures about 30% of the chip sales in the U.S. market. They have recently upgraded some of the equipment and processes in their current U.S. plant located in Hillsboro, Oregon to meet U.S. demand better over the next year, which could allow them to capture even more of the U.S. market. By investing in the new equipment, more smaller chips can be produced. Reducing the size of the chips means more processors can be popped out of a single wafer, thereby increasing the potential units and associated revenue without incurring massive additional costs. Last year Intel produced 700 million chips which were all sold due to the market shortage. With the new equipment upgrade, Intel anticipates it could produce approximately 850 million of these new chips this year. This new smaller semiconductor chip has recently replaced its existing chip on the market. Mr. Chip, a hypothetical Chief Operating Officer at Intel, uses aggregate forecasts to assist in its production planning for the semiconductor chips. The ChipData worksheet in the Fall21ProjectData.xlsx file posted in the Brightspace Project 1 module shows monthly semiconductor sales in the Americas from 2017 to 2021 (in billion U.S. dollars). Intel would not change the unit price for its chips which has been $40 on average over the last five years. Besides being able to produce more units within the same time period (which could increase revenues assuming market demand exists), the chips will also be less expensive to manufacture than with the existing process. Intel currently operates its plant 4 weeks every month throughout the year with the new weekly plant capacity of 16.6 million units. Mr. Chip would like to forecast the aggregate monthly semiconductor chip product line demands for this upcoming year so that he can adjust scheduled productions and workforce requirements appropriately and make any necessary subcontracting arrangements. He has asked you to analyze the historical data to identify recent patterns that Intel should take into account when it develops the semiconductor chip product lines’ production plan for the 2021-2022 year (i.e. July 2021-June 2022). Intel currently employs 250 workers to run the upgraded automated assembly line 5 days per week, 8 hours per day, who are paid $18.50 per hour. The material and other variable costs are $27.50 per chip on average. Every worker scheduled to support the production line allows the plant to produce an incremental 1,500 chips per hour, up to the new equipment’s weekly capacity. Since Intel’s workforce is unionized, Intel cannot resize its workforce freely as new employees will become part of the union. It costs Intel $5,000 to hire and train a new worker, and $10,000 to lay an employee off given the union restrictions. If Intel asks their employees to work overtime, the extra hours are scheduled on weekends or evenings to get additional capacity time if necessary. Employees are paid time and a half when they work overtime. Intel currently does not have any inventory of its chips. The holding cost for each chip is $1.25 per month. In the past, Intel has just rejected chip orders if they could not meet the current monthly demand. The company started backordering demand this year as needed to help car and electronic manufacturing companies during the pandemic. It costs $1.50 per chip per month to process backorder requests. At the end of June 2021, Intel currently has 500,000 chips backordered in the U.S. Historically, Mr. Chip’s aggregate planning strategy has been to maintain a steady, well-trained workforce throughout the year in order to control costs, ensure quality production and keep the union happy. The workforce works full-time and produces as many chips as possible each month. Starting July, Intel decided overtime labor may be considered as an option to address the economic manufacturing impact of chip shortages if their capacity is fully utilized during the workday. Government regulations limit employee overtime to no more than 20% of the regular production level in any particular month. Mr. Chip also has a longstanding relationship with a Vietnamese manufacturer that subcontracts work from American companies. The Vietnamese manufacturer insists that Intel subcontract at least five million chips every month next year in the contract; in return, this subcontractor will manufacture Intel’s chips for $30.00 per unit, which includes the cost of the production, material, labor and shipping. Mr. Chip is unclear as to what type of demand he should expect for the chips next year. He believes that the pandemic is likely to continue for the near future, but he is not sure what impact that might have on the demand for semiconductor chips. He recognizes he may have to make adjustments to his usual production strategy in order to control costs with the recent expansions and improve product availability simultaneously. The union will not allow him to schedule the workforce to work a shorter week though (less than 5 days per week). He can resize the workforce and/or use the subcontractor. While he is willing to use backordering now to help the U.S. economy, he does not want to see a plan that has any unfilled orders at the end of June 2022. He also does not want to have an inventory larger than 15 million chips at the end of June 2022 due to design changes that could occur over the next year. In order to help Intel identify a good aggregate plan (Part C), it is first necessary to identify a good forecast (Part A and Part B). 1. Open the Fall21ProjectData.xlsx spreadsheet. You will need to clean the data by performing the following steps: a. Make sure that the data for number of Covid cases and semiconductor sales are numeric so that they can be used in your regression analysis. Make sure that the dates data is all formatted as dates that they can be manipulated or filtered on. b. Use a data cleaning technique to convert the daily covid case data into a table that lists the total number of cases for each month up through June 2021. For example, you could use the Text to Columns menu option to divide the month/day/year for each date into separate columns and then use the Sumif(s) formulas on this modified data to convert the daily cases into monthly case estimates for 2020 and 2021 so that you have data points for your regression analysis. You could also research the eomonth() function! c. Insert the semiconductor monthly chip sales data as a third column in the table you created in step 1b. In this table you should convert the monthly $ sales in Americas to # of units sold and then convert it to projections for Intel’s # chips sold based on Intel averaging 30% of past America sales. Assume that the average sales price of a chip is $40 for all manufacturers. 2. Plot a scatterplot of Intel’s Monthly Unit Chip Sales in the Americas versus # Monthly Cases of Covid in U.S. Make sure your graph and axes are properly labeled. 3. Run a simple linear regression of Intel’s Unit Chip Sales in the Americas on the # Cases of Covid in U.S. since Jan 2020. In a textbox on your spreadsheet, describe the relationship between Intel’s chip demand in the Americas and the # of Covid cases in the U.S. Write the linear regression model you would use to predict demand each month next year for Intel. Experts are predicting that there will be 4,800,000 Covid cases this September 2021. What is your forecast for Intel chip orders in September if that is the case? 4. Do you think this is a good model to predict each month’s future demand for 2021-2022? Defend your answer with statistics from your regression analysis here: 5. What is the difficulty of using this model to predict Intel’s demand over the next 12 months? Type 1-2 sentences to answer this question here: 6. What other independent variable do you think could be used to explain the monthly demand for Intel’s chips next year? Defend why you think this variable should be considered and what type of data you would need to collect to measure it. Type your answers here: