Financial Statement Analysis Project

Due date: see each individual item

Why this project

This project has three objectives: 1) to allow you to become more familiar with the financial statements and regulatory filings of actual companies, especially those associated with business combinations; 2) to review, study, and reinforce materials related to consolidation elimination entries, foreign currency transactions, and translation of foreign subsidiary financial statements; and 3) to gain some exposure to business segments, Big Data analysis, and databases.

What's the grade of this project

The project includes 10 items that account for 5% of the total grade and one item that counts as 1% extra credit. It is graded on both effort and correctness.

Sources of financial statements

The most comprehensive source is SEC's EDGAR database. This can be accessed at http://www.sec.gov/edgar/searchedgar/companysearch.html. Most large companies now make periodic filings available on their websites (often under a link for 'investors' or 'investor relations'), but may not include all of the SEC filings.

Company selection

Select one of the following companies and an acquisition it has undertaken.

Amazon (acquired Whole Foods on 8/28/2017)

AT&T (acquired Time Warner on 6/14/2018)

CVS (acquired Aetna on 11/28/2018)

Expedia (acquired Orbitz 09/17/2015, and HomeAway on 12/15/2015)

Raytheon Technologies (formerly UTC, acquired Raytheon on 4/2/2020)

Walt Disney (acquired 21st Century Fox on 3/20/2019. Focus on the pro forma statements

involving Disney)

For the selected company and the acquisition transaction, choose two sets of filings: (1) one or more Form 8-K filings or 425 filings (shown as "425" on Edgar) that described the business combination; and (2) a 10-K report for any year after 2015.

If you select a different company, avoid companies not listed in the US and not-for-profits, which may not have accounts needed to finish the project.If you select US financial institutions or utilities, be aware that they are subject to unique regulations and accounting rules. The company of your choice should also meet the following criteria (you can verify these by searching the company's financial statements).

1) The acquisition you focus on was after 2010. You can infer the acquisition from the filing of 425 or 8-K forms to SEC Edgar (see the link above).

2) The acquisition you select is significant enough for the acquirer to file 8-K or 425 to describe the combination and disclose the target company's financial statements and pro forma consolidated financial statements.

2) Has international transactions and thus, transactions in foreign currencies.

3) Has at least one foreign subsidiary, and thus, description of the translation of the subsidiary's financial statements.

You will need to use both the financial statements and footnote disclosures to answer the questions related to annual financial statements.

Requirements

a) The answers should be concise and clear, easy for readers to follow. More words may only confuse readers. For all of the questions, if you find answers in the financial reports, do NOT merely copy them, summarize the main points.

b) You should proofread your work and approach this assignment as if you were preparing it as part of your professional job: if you would be embarrassed to give it to a supervisor at work, you probably need to fix it up before submitting it in to me.

c) Explain how you produced calculations and determined answers. You also need to provide the link to the filings, and point out the relevant pages (and ONLY those pages) from the filings that support your answers. Thus, your work may be verified. Not providing necessary materials for verifying the answers you provide may result in partial or no credit for your answers.

d) You are required to work on this project independently.

Misrepresenting someone else's work as one's own is a serious offense in any academic setting and it will not be condoned.

A student who knowingly assists another student in committing an act of academic misconduct shall be equally accountable for the violation. Violators shall be subject to the sanctions and other remedies described in The Student Code at http://community.uconn.edu/the-student-code-appendix-a/.

Questions and Analyses and due date

The system will mark your submissions after the due time as "late." Late submissions will likely receive 0 credit. Submissions through emails will not be accepted.

Due by September 13 noon: item 1). Submit in HuskyCT under "Take Quizzes, FSA project, and Exams"

Item 1) Submit the following:

company name,

date of the 8-K or 425 filing(s) on business combinations,

link to the filing(s),

date of the 10K filing,

and link to the 10k filing.

Make sure that the link directs whoever clicking the link to the filing itself, not to SEC Edgar.

Note: once a 425 filing is opened, it is usually shown as Form 8-K. Double check the content to make sure that it is about the merger & acquisition transaction, and not about related conference call, restructuring, or supplemental information. (5 points)

Due by October 11 noon: Items 2) to 5). Submit in HuskyCT under "Take Quizzes and FSA project." At the beginning of your answer, write down company name, year, the links to the company's filings your answers are based on. They are likely different from what you provided for item 1), that is fine.

2) In Form 8-K or 425 filing(s) you identified for item (1) or in those the acquirer filed on different dates, or in a Press Release by the acquirer, find

the date the merger & acquisition was announced,

entities involved in the transaction (i.e., which companies were involved),

relationship among the entities before the acquisition (e.g., supplier, customer, competitor, business partner)

the transaction value (i.e., how much the acquirer paid for the acquisition), and

purposes of the acquisition provided by the acquirer. (5 points)

3) For the merger & acquisition transaction, describe the consideration transferred, as listed below in a) and b)

a) the total purchase price, and what the acquirer pays, such as its own shares, cash, other

valuables, or a combination of various forms; and

b) what items the acquirer receives in return and the associated fair value or purchase price. These items are usually a list of target company's assets and liabilities.

The above information is often disclosed in 8-K, Press Release, Proxy Statements (DEF14A, or PRE14A), 10-Q or 10-K filed subsequent to the initial announcement of the acquisition or around the completion of the acquisition.

(10 points)

4) In the 10K filing you chose in item 1), find the Principles of Consolidation provided by the company and briefly describe them. (5 points)

5) According to the 10K filing you chose in item 1), how many segments does the company report? Briefly describe three of them if it has more than three segments, or all the segments if it has less than three segments. Include any accounting information the firm provided for the segments, such as assets, sales revenue etc.. (10 points)

Refer to Chapter 13 (no class coverage for this chapter. Read it when needed) for this question.

Due by November 15 noon. Items 6 to 10: Submit in HuskyCT under "Take Quizzes and FSA Project". At the beginning of your answer, write down company name, year, the links to the company's filings your answers are based on. They are likely different from what you provided for item 1), that is fine.

Find the "Unaudited Pro Forma Combined Balance Sheet" and "Unaudited Pro Forma Combined Statement of Operations (or Income Statement)" for the acquisition transaction you identified for items 1) to 5). The pro forma statements are often based on assumptions or hypothetical future situations, not reality. They are likely included in the forms filed right after the completion of an acquisition, such as Form 8-K, 8-K/A (more likely than the other filings), or 10-Q.

Items (6) to (8) are all based on the column titled "Pro Forma Merger Adjustments" on the "Unaudited Pro Forma Combined Balance Sheet" and the associated notes to those adjustments.

6) Find information related to the acquired company's Common Stock and Retained Earnings, explain with adjustment journal entries how they are adjusted for consolidation purpose. The amounts are usually provided in the notes to the Adjustment column. (15 points)

7) Choose a current assets item that is adjusted, explain with journal entries how the adjustments are made for this item. (10 points)

8) Choose a long-term assets item that is adjusted, explain with journal entries how the adjustments are made for this item. (10 points)

Question (9) is based on the column titled "Pro Forma Merger Adjustments" on the "Unaudited Pro Forma Combined Statement of Operations" (or Income Statements) and the associated notes to those adjustments.

9) Choose any income statement item that is adjusted, explain with journal entries how the adjustments are made for the two items. (15 points)

10) According to the 10K filing, how does the company record transactions in foreign currency AND translates foreign subsidiary's financial statements (note this question asks for two distinct items). If net income or other comprehensive income is affected by the recording of foreign currency transactions or foreign currency translations, find out the amount of this effect for the current year. If the company does not separately report these amounts, state so in your answer.

(15 points)

Due by November 29 noon. Item (11) Extra credit questions: Submit in HuskyCT under "Take Quizzes and FSA Project"..

In order to earn partial or full extra credit, you are required to answer all four questions. Not answering all four questions will result in receiving no extra credit.

(total points are 100 points, weight of 1%, i.e., total extra credit is 1 point)

All questions below are for the same company you selected for items (1) to (10).

Question a): Find the balance of Goodwill for the latest fiscal year; (10 points)

Question b): Find the balance of total assets for the latest fiscal year, and calculate the ratio of goodwill to total assets for the latest fiscal year. (10 points)

To answer c) and d), you need to carefully go over "Tutorial for FSA project item (11) on how to use SAS and Stata to collect data and analyze it" on the next page.

In your answers to c) and d), provide (1) the calculation results, (2) the year you used for all public companies (i.e., year#### below), and (3) the period for the company of your choice (e.g., 2000 to 2020).

Question c): For all public companies in the U.S. between 2000 and year####, calculate the average, median, the 25th and 75th percentiles for the ratio of goodwill to total assets. (40 points)

year#### is the year that matches your first name (see the chart below, and to be used in the program).

First letter of first name year####

A 2012

B to C 2013

D to H 2014

I to J 2015

K to L 2016

M to N 2017

O to S 2018

T to Z 2019

Question d): For the company you choose and the period between 2000 and 2020 (or the latest fiscal year possible), calculate the average, median, the 25th and 75th percentiles for the ratio of goodwill to total assets. (40 points)

This item serves as an introduction of Big Data, a commonly-used database, and two commonly-used data analysis software. Next two pages provide a separate tutorial on how to collect goodwill and total assets for all public companies, and on how to calculate the average, median, 25th percentile and 75th percentile of the ratios.

Tutorial for FSA project item (11) on how to use

SAS and Stata to collect data and analyze it

All videos are under HuskyCT/Course Packet/FSA project item (11)-videos

1. Watch the video titled "1_Introduction to WRDS.mp4" to learn about the database (Compustat). Then collect Goodwill and total assets for all public companies listed in the U.S. from Compustat.

While watching the video, logon to https://wrds-web.wharton.upenn.edu/wrds/.

Username: acctf21

Password (case sensitive): ACCTfall2021

This account can accommodate up to 15 simultaneous users. So try not to finish this part at the last moment to avoid account cramming.

2. Watch the video titled "3_Download Compustat data from WRDS". Before watching this, you can also watch "2_Compustat Complete Financial Statements", although it is not necessary.

Notice the following differences from the procedures in the video:

You will need to select the date range between 2000-01 and 20XX-12, where "01" is for January, "12" is for December, and 20XX is the most recent year possible.

You will need to select "search the entire database".

Under "screening variables", uncheck all the boxes in front of "Output" on the right hand side.

You will only need to select two variables "GDWL-goodwill" and "AT-Assets-Total".

In "Step 4: Select query output", choose "Output Format" as "STATA file(*.dta)." or "SAS Windows_64 dataset (*.sas7bdat)". If you are going to use Stata in the next step, select ".dta", if you are going to use SAS in the next step, select ".sas7bdat".

If you like to use Stata to analyze your data, go to "3" below. If you like to use SAS to analyze your data, go to "4" below. You can directly install and launch both on your computer. Download either one at UConn https://software.uconn.edu/software.

3. Download and install Stata to your computer.

After opening Stata, under "File", open your Stata file saved under "2." above, or type "use "C:\your folder name\your_data_name.dta"". Below is a sample of the program. Copy and paste them to the box at the bottom under "Command" after revising the codes as explained below.

** Start of the program

**** note that everything between the asterisk and the next line serves as **** explanations only, not part of the program

*** What you need to do: replace C:\your folder name\your_data_name.dta

*** with your actual folder and dataset name

use "C:\your folder name\your_data_name.dta"

gen gw_at = gdwl/at

**** What you need to do: replace year####with the year that matches your **** first name (refer to the chart under item 11))

summarize gw_at if fyear>=2000 & fyear<=year####, detail

*** What you need to do: replace gvkey_of_your_compay with the actual gvkey *** of your company. This then calculates statistics between 2000 and the

*** latest year possible.

*** one of the videos demonstrates how to find gvkey for a company

summarize gw_at if gvkey== "gvkey_of_your_compay" & fyear>=2000 & fyear<=2020, detail

** End of the program

4. If you like to use SAS to analyze your data, watch "4_Introduction to Using SAS".

Under SAS editor, use the following program to calculate the required statistics.

** Explanation: everything between the asterisk and semicolon serves as explanation only,

not part of the program ;

** What you need to do: Replace P:\SAS_Tutorials\Data with the path to the folder where you saved your goodwill and total assets data ;

libname Husky "P:\SAS_Tutorials\Data";

** Explanation: import data if needed, "Husky" here is the libname that refers to the path above.

** What you need to do: Replace "Comp4" with the name of the dataset you saved. Calculate the ratio of goodwill to total assets (gw_at = GDWL/AT) ;

data Comp4;

set Husky.Comp4;

gw_at=GDWL/AT;

run;

** Explanation: "Proc means" procedure calculates the average ratio (mean),

median ratio (p50), ratios at 25 and 75 percentile (p25, p75);

** What you need to do: replace year#### with the year that matches your first name (refer to the chart under item 11));

proc means data=comp4

n mean p50 p25 p75;

vars gw_at;

where 2000<=fyear<=year####;

run;

** statistics for the company you choose between 2000 and the latest year possible;

*** one of the videos demonstrates how to find gvkey for a company;

proc means data=comp4

n mean p50 p25 p75;

vars gw_at;

where 2000<=fyear<=2020 and gvkey= "gvkey_of_your_company";

run;

** End of the program ;