EXCEL ANALYTICS PROJECT (Part 1) – BENCHMARKING STUDY Critical thinking is one

EXCEL ANALYTICS PROJECT (Part 1) – BENCHMARKING STUDY
Critical thinking is one of many skills employers are looking for from graduate students. In this assignment, students will work on developing this vital skill using Microsoft Excel as a tool.
Prerequisites Skills for this assignment, students should be familiar with the following skills using Microsoft Excel
1. Open, save, close, and navigate through a workbook and worksheet
2. Plan and create a workbook
3. Create tables
4. Enter text, dates, and numbers data
5. Enter basic excel formulas such as average and standard deviation
6. Create line charts in excel
7. Create box-plots in excel
8. Use AutoFill and Flashfill
Case Scenario
One day at a family party, your sister and your brother got into a debate about which phone device is better. Your sister has been a loyal fan of Apple products while your brother has been a loyal fan of Samsung products. To end their debate, you mentioned that there are multiple ways that one can compare the performance of different companies and you will bring in your analysis to the next family party.
Project Requirements
1. Please watch the “What is Benchmarking?” video to learn about Benchmarking, an approach used by many organizations to measure and compare with others. The video is available at https://www.youtube.com/watch?v=AkGwJSlkpfY
2. Visit https://investor.apple.com/sec-filings/default.aspx where you will find all of Apple Inc. fillings. Select Annual Filings in the SEC Groupings. Select Annual Report from years 2011 – 2022. Look for Financial Statements on the filings (Consolidated Statement of Operations).
For Samsung Electronics Co., Ltd, the annual financial statements can be found at https://www.samsung.com/global/ir/financial-information/audited-financial-statements/. You will use the Income statements for the full year, years 2011 – 2022 . (Make sure to only use the numbers for USD).
3. Using Microsoft Excel, please conduct a benchmarking study comparing the performance of the two companies. Save the Excel worksheet as LastName_FirstName_Part1 and name the worksheet Part1.
4. For each company, you will create a table. Name the table Performance Indicators and include the following financial data for each company from the years 2011 – 2022:
Use clear titles, labels, and other appropriate spreadsheet formats (e.g., font type, size, colors, borders, etc.) so users can easily locate the information.
A. Revenue/ Net Sales
B. Cost of Sales
C. Net Income/ Profit
5. Create another table below for each company and label it Measures of Dispersion. Write an Excel formula that will calculate the Mean, Range, Mean Absolute Deviation, and Standard Deviation of each financial data.
6. Create a Box-Plot for each performance indicator (financial data) using a five-number summary (Min, Q1, Q2, Q3, and Max). Before resolving the five-number summary, make sure to arrange your data from smallest to greatest (you may need copy your table to avoid dislocating your original data).
7. Create ONE Line Chart for each company that plots the Net Sales, Cost of Sales, and Profit over the years 2011 – 2022.
8. Use the information from your analysis to answer the following questions:
A. According to the data, which company had the highest average sales?
B. According to the data, which company had the highest average profits?
C. According to the data, which company had the highest average expenses?
D. According to your spreadsheet, which performance indicator do you think is the most important for this analysis? Please explain your answer.
E. Analyze and interpret your findings on each Measure of Dispersion.
F. Analyze and interpret your graphs (Box-plot and Line chart). (Keep in mind the relationship between the financial data.)
G. Based on your analysis and interpretations, which company shows a better performance?
9. Save the Excel file and submit it as an upload file in Canvas.