Sorry, I know this is a very long assignment. Both of my group members dropped the class last week so I have to do it by myself now. I have no idea where to start and could greatly use your help.
ACCT 403 – Analytics Assignment Fall 2022 This analytics assignment deals with analyzing earning per share. You are to work in groups of 3 or 4 during our regular class time on Friday, 10/13. You will work together to prepare the PivotTable in Part A and the Column Chart in Part B. (Do not complete the PivotChart portion of the assignment.) Work on this during class on Friday. I will answer any questions on Monday in class. The completed assignment will be due on Wednesday, 10/19. Each group member will submit the same group solution. See the attached Excel worksheet. There are five tabs: · Assignment information: Read this carefully, especially the background information so do understand the purpose of this analysis. · Part A instructions: Read carefully! · You are given detailed instructions. It looks complicated, but if you complete each step of the process it is not complicated. · Only complete the PivotTable. · DO NOT prepare the PivotChart. · After completing the PivotTable answer questions 3, 4, 5. (Do not complete questions 1 and 2.) · Part B instructions: Read carefully. Complete each step · Complete the Column Chart. · Complete questions 1, 2, 3, 4. · Data All Companies: This data will be used in Part A. · Data-AMEX: This data will be used in Part B. Assignment information Group Members working on this assignment: ____________________________________________________________________ Data Analytics Problem: Complete Part A (only PivotTable) and Part B. Detailed instructions are on the tabs at the bottow labeled Part A and Part B. The data is on the other two tabs. Carefully read the information below before starting the assignment. You have been given detailed instructions. Carefully follow each step. You will work in groups of 3 or 4. Each group member must contribute to the assignment. DAP17.1Expectations of Earnings for Companies Earnings per share (EPS) is one of the most highly visible standards of measurement for assessing management stewardship and predicting a company’s future value. EPS allows shareholders to predict the value of their shareholdings. Common shareholders want to know how much of a company’s available income can be attributed to the shares that they own. Two different EPS calculations are important to shareholders. Basic EPS looks at actual earnings divided by the actual number of common shares currently outstanding (pro-rated for the amount of time the shares have been outstanding). Diluted EPS accounts for the possibly of other potential common shares outstanding. Since many different financial instruments have an option to convert to common shares (convertible securities, options, warants, etc.), it is equally as useful for shareholders to understand the impact these instruments have on their holdings as well. In that 'what if' scenario where all financial instruments convert to common shares, this increased number of overall common shares outstanding would reduce earnings per individual share. The timing of this information is also especially useful, and so being able to predict EPS on a continuing basis is important. Nasdaq keeps an earnings calendar on its website. Earnings release dates (both future expected dates and past dates) and forecasted EPS (derived from a consensus by all analysts who made predictions) are posted. The data provided reflects earnings released on October 22, 2021. Note that the number of predictions that make-up a EPS forecast is not consistent. It simply represents the number of analysts who issued a prediction about earnings for each specific company. For instance, America Express Company had 16 estimates while Tompkins Financial Corporation just had 1 estimate. You are a newly hired data analyst for a local financial advisor and have been asked to do the following (using Excel): Part A.*Using company data provided, create a PivotTable to list the number of estimates made in forecasting the EPS and the surprise % in earnings for each one. *Use this PivotTable to create a PivotChart to visualize whether or not the number of estimates made contributes to a lower % surprise in earnings (if the consensus EPS forecast is closer to the actual EPS). Analyze the results. Do not do the PivotChart now. Part B.*Create a Clustered Column Chart to compare high, low, and consensus EPS forecasts for American Express Company for 3 years. Analyze the results. Part A Instructions Part A - Comparing the number of estimates to the surprise earnings % Refer to the Data-All Companies worksheet.Image 1: In column F, % Surprise, the difference between the forecasted EPS and the actual EPS is shown, expressed as a percentage. For example, Honeywell International Inc.'s forecasted EPS was $2.01 and its actual EPS was $2.02. The actual EPS was $0.01 higher than the forecast, or 0.5% ($0.01/$2.01). A positive percentage indicates an actual EPS that exceeded expectations (higher than forecast). A negative percentage indicates an actual EPS that failed to meet expectations (lower than forecast). Any variation between the forecasted EPS and the actual EPS is deemed an earnings surprise. Before creating a PivotTable using this data, add a new column to the Data-All Companies tab (column G), called % Surprise 2. In this column, show the % surprise. *Format this column for percentage. In this column, show the % surprise for each company but always expressed as a positive number (required when creating a PivotTable.) *To do so, use the ABS= Excel function (in cell G2, enter =ABS(F2) *Copy this formula down all rows. You should now see that for any negative % surprises, these same percentages now show as positive in column G. Using the Data-All Companies sheet create a PivotTable to list the number of estimates and % surprise for each company. *Highlight all of the data on the Data-All Companies sheet. *Under the Insert tab, click on Table/range. A “Create Pivot Table” box will appear with the range you highlighted. *Choose “Select table or range”. The range should show the cells you highlighted. *Choose "Existing worksheet". *Location: Go to Part A Instructions and scroll down and click below "Student Work Area". Click OK. *Place company in rows, place Number of estimates and % Surprise 2 in values (ensure SUM is being calculated). *Format the first value, Number of Estimates, to be a number with zero decimals. *Change the custom name to "Total number of estimates". *On Pivot Table Frields (on right side) click on drop down arrow beside "values". *Click "Value Field Settings" (last option in list after selecting this value), click on number format, choose number, and reduce to zero decimals. OK. *Still in Value Field Settings: In the Custom Name field, type in new name as "Total number of estimates". *Repeat these steps for the second value, % Surprise 2, but format this value to be a percentage with one decimal, and a custom name of "% Surprise Earnings". *Change "Row Labels" header to "Company" (modify this label by typing directly in the cell). DO NOT COMPLETE PIVOT CHART NOW. Create a PivotChart using this newly created PivotTable. *Highlight PivotTable *With your cursor on the PivotTable, click on Insert from the top menu, then PivotChart. *Choose a Combo Chart *Select 'Line with Markers' chart type for both values (See Attachment Image 1 for assistance). *Position your PivotChart beside your PivotTable. *Add a Chart Title ("Comparison of No. of Estimates to Surprise Earnings %"). *Add axis titles for the primary and seconday axis (Number of Estimates and Surprise %). *Move the legend to the bottom, remove the company name labels from the x-axis (they're too long!) and hide all field buttons (PivotChart Analyze/Field Buttons). Answer questions 3, 4, and 5 Part A QUESTIONS (using your PivotTable and PivotChart): 1) Using your PivotChart, does it appear that an increased number of estimates improves the accuracy of predicting the EPS? In other words, when the number of estimates increases (primary axis line), does the % surprise earnings decrease (secondary axis line)? 2) Add a trendline to your PivotChart to show the trend of the % Surprise Earnings data as the Number of Estimates increases (click Chart Elements, Trendline, % Surprise Earnings). Does this trendline suggest there is an improvement in accuracy (reduced surprise %) with an increased number of estimates? CompanyNumber of Estimates% Surprise Earnings 3) Which company had the largest % surprise in earnings? 4) Which company had the smallest % surprise in earnings? 5) Which company had the largest no. of estimates? Student Work Area (position PivotTable and PivotChart here): Part B Instructions Part B - Examining high, low, and consensus EPS forecasts for American Express Company Refer to the Data-All Companies and Data-AMEX worksheets.Image 1: A closer look at the 19 companies listed shows that 63% of them reported quarterly earnings that exceeded analysts' expectations. To see if was common to continually or frequently exceed expectations, American Express was examined in more detail using other sources. It was determined that American Express Company not only exceeded earnings expectations in 2021 (per Part B), it also had a positive earnings surprise for the past 3 quarters. Past performance is one of many factors analysts take into account when projecting future earnings. Earnings for American Express have now been projected out to 2023. These projections show a consensus of a continued increase in expectations for the next two years (2022 and 2023). That said, there is a range of forecasted EPS' values each year, given the number of different estimates that make up this consensus. Create a Column Chart to compare the highest, lowest, and consensus EPS for each of the 3 years, 2021, 2022, and 2023. *The Column Chart should be placed at the bottom of this sheet in "Student work area". *Highlight cells A1:D4, click Insert Chart, *Click Insert (at top) *Click on arrow at bottom right corner of the "charts" section for the "Insert Chart" box. *Click on Clustered Column Chart. Make sure you choose the clusted column chart with the year as the x-axis (not EPS type). EPS type should be the legend (and be the data displayed in each column). *To enhance the usefulness and presentation of the data, place the consensus EPS forecast column in the middle of the 3 columns, between the high and low EPSImage 2: forecasts. *To re-order the chart columns (as your default display likely shows this order: Consensus-High-Low; you want it to instead show High-Consensus-Low): *Click on the chart, choose Chart Design from the top menu, and Select Data. *Click on the Consensus EPS Forecast legend series option. *Click on the down arrow to move it down one line so it then sits in between the