Financial and Market Data Extraction and Manipulation Assignment

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Financial and Market Data Extraction and Manipulation Assignment

You have been assigned a consumer goods company (spreadsheet attached to this assignment) and you will perform various analyses on this company.

The goals of this individual project are:

· to develop your awareness about and competencies with using large, corporate, financial and market databases that are available to you to support your learning

· to develop skills in extracting and downloading data from these databases, importing the data into Excel, and manipulating it so as to create new information that can be used to support decision making  

If you would like information regarding the Pepperdine library databases, visit http://infoguides.pepperdine.edu/GraziadioIntro for an information guide to the online library and data resources available to Graziadio School students. Note the “Industry Research” and “Company Research” and “International Information” tabs. If you need assistance, you can consult with a librarian in person during business hours at the Drescher Library, or use the online consultation service at http://pepperdine.libanswers.com/.

To complete this assignment, you will need a reasonable level of excel skills. Excel is a widely used tool in business that continues to be used extensively today. Developing a high level of competency in using a wide range of Excel’s features and functionality will be very useful and valuable for your future career, especially at a time when business is obsessed with data-driven decision making. If you are not particularly confident about your Excel skills, some learning resources that will help you to [further] develop your excel skills include:

· LinkedIn Learning is available to you for free and is accessible via https://community.pepperdine.edu/it/tools/linkedinlearning. There are multiple Excel courses available. The additional advantage of this is that LinkedIn will keep track of courses completed and will provide verification of this.

· A more informal course offered as a series of YouTube videos at: https://www.youtube.com/user/ExcelIsFun 

Following are the instructions to complete the exercise.

NOTE: to receive full credit, follow the instructions precisely. Pay close attention to detail!

Mergent Online Database

From the Mergent Online Database, extract and download the Annual Income Statements for the past 15 years for your assigned company. To do this:

Open http://library.pepperdine.edu/ in your browser.

Choose “Databases and E-Journals”

Under “By Subject,” select “Business/Economics”.

Notice the extensive list of business-related databases that are available to you as part of your tuition during your time at Pepperdine, which you can choose to use to find company, economic, consumer, industry, and market data for a variety of course projects and for your general learning, and perhaps personal investing.

Select the Mergent Online database and if necessary log in with your Pepperdine ID and password.

Enter your company name and select your company from the list of companies displayed

Note all the tabs that reflect the scope of data available, including links to recent News articles about your chosen company and a comprehensive competitor analysis. Click on the “Company Financials” tab.

Next, you are going to ask Mergent to extract a specific set of historical financial data. Specify that you want the Annual Income Statement for the past 15 years/quarters reported in United States Dollar currency values, denominated In Thousands (note that these underlined elements are options you select).  Click on “Refresh” to request the data.

When you are confident that you have the correct data, click on the “Download” button to download the data to an excel file.  Save/rename the file on your laptop using a filename that follows the naming convention:

mylastname_myfirstinitial_companyticker_rawdata_2023mmdd

where companyticker is the ticker symbol under which the company stock trades, mm is the month and dd is the day you create the file (note: there are NO spaces, NO dots, use the official company ticker symbol, and use the underscores exactly as I have stated, but insert your lastname and your first initial at the obvious places, for example “krogh_e_KR_rawdata_20230123”).  Adhering to mandated filenames is often essential for automated data uploads: if you use a file name other than that specified, your data won’t be included by an automated data collection program.  For this assignment, you will have to upload this file and other files manually to Sakai to submit your assignment.

Next, click on the “Report Builder” tab.

Select “Comparison Reports” (now that you are getting familiar with the database, I am going to reduce the level of specificity of the instructions!)

Generate an Excel AND a PDF report that lists the Top 50, international, Active companies classified by the same SIC code, and lists the following Data Items (in the order specified) for the Latest year with the “Years Across” format:  the country of operations, the year the company was founded, total revenue, R&D, advertising, selling general & administrative expenses, inventory turnover, EBITDA, advertising as a percentage of Total Revenue, EBITDA Margin %, net income, and the number of employees.  The main part of the report should appear as a table, with one company per row, with each of the above data values in the columns.  Request that average values be included (i.e. click the “Include Average” box).  Save the EXCEL and PDF reports on your laptop using the naming convention mylastname_myfirstinitial_companyticker_top50_2023mmdd

Modify the downloaded Excel Files that you created

Next, you will make modifications to the excel files you created.

Open your mylastname_myfirstinitial_companyticker_rawdata_2023mmdd file in Excel.  To protect the original raw data (so that you can start again if necessary), immediately save a new version of this file using the filename mylastname_myfirstinitial_companyticker_withcalcs_2023mmdd

If present, delete the blank columns between the years of financial data.

At the bottom of the spreadsheet, add the following rows and formulas to complete the following calculations:

Revenue (or Net sales) per employee

Selling, general & administrative expenses as a % of Gross Profit

% Year over Year change in Net Income for the past 14 years

Be sure to include the formulas used to produce your numbers – DO NOT CALCULATE THE VALUES OUTSIDE EXCEL AND ENTER THEM MANUALLY – let Excel do the work!!  A full grade point will be deducted if formulas are not present!

Show all values with two decimal places, with the appropriate $ or % symbol shown in the cell.  You may need to adjust the formatting of the columns so that you can clearly see the values reported.

Create a line graph that plots “Revenue (or Net sales) per employee” and “Selling, general & administrative expenses as a % of Gross Profit” on the vertical axis, with time (years) across the horizontal axis. If you do not know how to create a line graph, please see Appendix 1: How To Create Your Chart below.

Save your updated file and ensure that the filename is mylastname_myfirstinitial_companyticker_withcalcs_2023mmdd

Open the mylastname_myfirstinitial_companyticker_top50_2023mmdd file.  To protect the original data (so that you can start again if necessary), immediately save a new version of this file using the filename mylastname_myfirstinitial_companyticker_top50calcs_2023mmdd.

Delete the rows containing the preamble about your chosen company so that the first row in your new spreadsheet becomes the row containing “* all results are stated in US Dollars”

Adjust the column width to allow for each reading of the main table (hint: numerical formulas and functions only work on cells containing actual numeric values; they do not work on cells containing non-numeric text (even if the text contains only digits).

At the bottom of the table, add rows and construct formulas to complete the following:

1. Calculate the average values for each of the columns. Then verify the consistency between the Mergent provided values and the values that you calculated. To do this, compare the Mergent “Peer avr" values to your calculated average.

2. Show the max value in each column

3. Show your company’s value in this column as a % of the max value, to two decimal places.

To the right of the table, add columns and construct formulas for the following:

1. Revenue (or Net sales) per employee

2. Selling, delivery & administrative expenses as a % of Total Revenue

3. Show each value in this column as to two decimal places.

Sort the companies and their associated data in decreasing order of revenue per employee. Be sure to recheck your work. Is everything correct after the sort? (Hint: make sure you include all the data associated with each company in the “sort range”)

Resave your file using the filename mylastname_myfirstinitial_companyticker_top50peers_2023mmdd

Finally, upload the six (6) files you have created to the “ISTM Skills Exercise 1” assignment in Sakai before the deadline for submission.

Appendix 1: How To Create Your Chart

Here is the Official Microsoft Tutorial

Create a secondary axis tutorial

This is what your chart should look like