Financial and market data search and manipulation assignment (Apple)
Farah1988
istm654companyfinancialandmarketdataextractionandmanipulationproject-2.docxThe purpose of this individual project is to:
· develop your competencies and comfort with using large, corporate, financial and market databases that are available to you to support your learning within the MBA program at Pepperdine;
· 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.
The assignment requires that you choose a publically traded company in an industry of interest to you. It is important that you choose a publically traded company to enhance the likelihood that the data you need will be available in the databases. The name of your chosen company must begin with the same first letter as your last name/ surname/ family name).
First step: email me as soon as possible with your first and second preference choices for a consumer-oriented company that you would like to study. Note that to get the required 15 years of data, the company you choose must have been a publicly traded company for at least 15 years. Before submitting your choice of company, please make sure that the Annual Income Statements for the past 15 years are available in the database (see below on how to do this!). I will reply as soon as possible to approve your company assignment.
While waiting for me to reply, visit http://infoguides.pepperdine.edu/GSBMintro 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, our use the online consultation service at http://pepperdine.libanswers.com/.
If you are not particularly confident about your Excel skills, some learning resources that might be of use to you to further develop these include:
http://office.microsoft.com/en-us/training-FX101782702.aspx
http://exceltutorial.info/excel_tutorial_1.htm?PHPSESSID=4222285c66a158623f6c7540e6baf956
http://www.gcflearnfree.org/excel
Multiple videos on YouTube: search for “excel 2016 tutorial” or “excel 2013 tutorial”, check out the ratings and make your own choice!
Mergent Database
Once I have approved or assigned your company choice (note that every student will complete this exercise for a different company), proceed as follows (of course you could start practicing while waiting for approval):
To extract and download the Annual Income Statements for the past 15 years for your chosen/assigned company:
Open http://library.pepperdine.edu/ in your browser
Within the “Research” section, choose “Access Databases and E-Journals”
Under “By Subject,” select “Business/Economics” (note the extensive list of business-related databases that are available to you as part of your tuition during your time in the MBA program, which you can choose to use to augment your course projects and general learning).
The two databases that we will be using for this project are the Mergent Online database for company specific data, and the Passport database (formerly the Global Market Information Database for historical and forecasted market size data.
Select the Mergent Online database and if necessary log in with your Pepperdine ID and password.
Enter your company name, click “Go” and select your company from the list of companies displayed
On the main screen, note the Sector name and Industry/ SIC code of your chosen company in the top banner (you’ll need this for the search within the second database).
Click on the “Company Financials” tab
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. Note these are specific requirements: pretend I am your new boss: if you don’t give me what I asked for, I won’t have a very positive first impression!
When you are confident that you have the correct data, click on the “Download” button to download the data to an excel file. Save the file on your laptop using a filename that follows the naming convention: mylastname_myfirstinitial_companyname_rawdata_2016_mm_dd where mm is the month and dd is the day you create the file (note: there are NO spaces, NO dots, use the official company name, and use the underscores exactly as I have stated, but insert your lastname and first initial at the obvious places, for example “krogh_e_kroger_rawdata_2016_09_26”). 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 Years Across the columns: 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_companyname_comparisons_2016_mm_dd
Passport Database
Next you are going to identify some global market data that is related to your firm’s industry.
Switch to the Passport database (formerly Global Market Information Database).
Review and accept the terms and conditions of use. “Watch an Overview” to learn what Passport does.
** Note that the Passport database only offers data on consumer product industries. If your company is not a consumer products company, you may have to opt for an unrelated industry for this part of the assignment, which is fine for the purpose of the assignment.
On the first screen, select “Industries” from the top-level menu (on the top right-hand side of the screen) and choose an industry sector that most closely relates to the industry of your chosen/assigned company. If none of the available sectors is related, pick a sector of personal interest to you.
On the next screen, under “Search Tree” select your industry and click “Go”.
On the next screen click on the “Geographies” tab.
From the appropriate region select Canada, China, India, Mexico, and the USA.
“Run Search”.
Under “Popular Statistics”, select the “Market Sizes” report (but for future projects in other subjects, take a look at the very comprehensive range of reports to which you have free access)
Change the Time Series to select the last 10 years of data (scroll down and select “Apply” to generate the revised report)
Convert the currency to US$ fixed exchange rates, and the data type to “Retail Value RSP” to get a standard comparison.
Export the report to Excel (current view only), and save on your laptop in a file called: mylastname_myfirstinitial_countrymarketdata_2016_mm_dd
Convert the data to show the values “per household”. Export the revised report to Excel, and save on your laptop in a file called: mylastname_myfirstinitial_households_2016_mm_dd
Click the “Back to Results” tab and select “Company Shares”. Select 10 years of data. View the statistics.
Export this report to Excel, and save on your laptop in a file called: mylastname_myfirstinitial_companyshares_2016_mm_dd
Modify Existing Excel Files
Next, you will make modifications to the excel files you just created.
Open your mylastname_myfirstinitial_companyname_rawdata_2016_mm_dd file in Excel. To protect your original raw data file, immediately save a new version of this file using the filename mylastname_myfirstinitial_companyname_withcalcs_2016_mm_dd
If present, remove 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, delivery & administrative expenses as a % of Gross Profit
% Year over Year change in Net Income for the past 14 years
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, delivery & administrative expenses as a % of Gross Profit” on the vertical axis, with time (years) across the horizontal axis
Save your updated file and ensure that the filename is mylastname_myfirstinitial_companyname_withcalcs_2016_mm_dd
Open the mylastname_myfirstinitial_companyname_comparisons_2016_mm_dd file . To protect your work, immediately save a new version of this file using the filename mylastname_myfirstinitial_companyname_top50peers_2016_mm_dd.
Delete the rows containing the preamble about your chosen company so that the first row in your 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 numeric values, not value that appear to be numeric)
At the bottom of the table, add rows and construct formulas for the following:
1. Calculate the average values for each of the columns. Then verify that the Mergent provided values are correct. To do this, compare the Mergent “Peer avr" 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
Sort the companies in decreasing order of revenue per employee.
Resave your file using the filename mylastname_myfirstinitial_companyname_top50peers_2016_mm_dd.
Finally, upload the eight (8) files you have created to the “IS Skills Exercise 1” assignment in Sakai.
1
