So from the microsoft word document that is my assignment the only questions I need done are 2-3, 2-6, 2-7, 2-8 filled into the excel spread sheet that I have attached all the math, tables, I have completed its just the questions submitted above
Project: DATA analytics using regression analysis for the Food industry. Due is May 12 at 10:00PM (50 pts) When you save the document, include your last name in the file name. Suppose, you are working at a consulting company and assigned to a job that requires an analysis of ten companies in food industry. The purpose of your analysis is to identify unusual observations and find a company needed attentions. You can use your HW work and build on to your work. You can include your answer in your excel sheet. 1. Cleaning data 1-1 Keep original data. 1-2 Copay data in the next sheet and transform it as a Table format. Ten cleaning data by dropping year observation if there are many missing data. 2. Make data to a table format and conduct analyses. 2-1 Create the pivot table including averages of ROA (IB/AT), ROS (IB/SALE), asset turned over (ATO=SALE/AT), a ratio of COGS to sales (COGS/SAL), a ratio of RD expenses to sales (RD/SAL), a ratio of SGA expenses to sales (SGA/SAL), sum of sales, sum of operating income (IB) and the numbers of observations (count function) by each firm. 2-2 Go to the next sheet and copy data for sale, COGS, IB and company name (CONM) and transform data as a Table format. 2-3 Create a Scatter graph of COGS (Y variable) versus Sale (X Variable) for each firm, observe any outliers, and examine if a liner line will fit well or not. The scatter graph displays visual presentation of the relationship between X and Y. Based on your scatter graph if there is outliers or problem in data set, read 10-k to identify any special incidences (mergers, acquisitions or changes in CEO) occurs to the company. Here is website to find 10-K. https://www.sec.gov/edgar/searchedgar/companysearch.html 2-4 Conduct a simple regression analysis (use DATA analysis under data) and examine whether the estimated slope coefficient and R square are reasonable given the Scatter graphs. 2-5 Using VLOOUP and pick up the value of a ratio of COGS to Sales from the Pivot table and include the value underneath of the estimated coefficient on Sale. 2-6 Compare the estimated slope coefficient and the value of a ratio of COGS to Sales. What kind of conclusion can be drawn from this comparison? Comment on. 2-7 Include SGA expenses to the trend graph you created in HW. Describe differences in trend between COGS and SGA expenses for each firm. 2-8 Based on your estimated slope coefficients on regression analysis and trend graphs what do you predict earrings performance for these firms? If you miss the class, this web site provides some information. https://www.wikihow.com/Run-Regression-Analysis-in-Microsoft-Excel Supplemental inforamtion about regression analyses The correlation coefficient measures the proximity of the data point to the regression line. It lies between 1, the closer the data point, and zero. T-statistic is used to test the significant coefficient, and this is directly related to P-value. The 95% confident level is associated with p-value of 0.1. Any value lower than 0.1 you can said I am 95% confident with 95%, this estimated value is close to this value.