Ratio Analysis Template

1

BUS30100 Project Assignment

Ratio Analysis of Publicly Traded Non-Financial and Non-Regulated Companies

A. Summary of Project Assignment

1. You will use S&P NetAdvantage to complete a ratio analysis of two publicly traded non-

financial and non-regulated companies in this project assignment.

a. The project report is due at 11:59pm on December 3, 2023. For each day late, 25% of

the total points will be deducted.

b. The project report consists of two components, a data file and an analysis report. Note

that the analysis report must be consistent with the data presented in the data file.

Detailed tasks are shown in Section B of this file.

c. S&P NetAdvantage is a business database available via PFW Helmke Library,

(https://library.pfw.edu/az.php?a=s) and is the only database required for the

assignment. The instructions on how to use the database to obtain financial information

necessary for the assignment can be found in Section C of this file.

d. The grading reference is available in the folder/module of BUS30100 Project

Assignment on Brightspace.

2. You may choose to complete the project on your own or in a group of up to three members.

All projects are graded based on the same criteria and are subject to the same late policy as

specified in the course syllabus.

a. A group project is graded based on both group efforts and individual contributions. The

report is graded based on group efforts when all members make significant

contributions to the project. When failing to do so, a student is subject to a grade

deduction on the assignment per individual contributions. Each student must identify

the contributions of each group member in the peer evaluation that is available in the

folder/module of BUS30100 Project Assignment on Brightspace.

b. Each group should submit the project report only once on Brightspace while each group

member must submit his/her own peer valuation. Multiple submissions by a group will

be graded based on the earliest submission unless specific reasons are communicated

with the professor. The peer evaluation is due at 11:59pm on December 3, 2023. With

a missing peer evaluation, a 25% reduction will be applied to a student’s grade of the

project assignment.

2

B. Detailed Tasks

Conduct a ratio analysis on two comparable publicly traded companies that are non-

financial and non-regulated using S&P NetAdvantage.

1. Select two comparable publicly traded companies that are non-financial and non-regulated.

a. Non-financial and non-regulated companies are all companies not classified in the

industries of (1) Finance, Insurance, and Real Estate, (2) Public Administration, and

(3) Electric, Gas and Sanitary Services.

b. Comparable companies are the ones with the same Primary Industry Classification and

a similar size that is measured by the revenue.

c. The instructions on how to identify appropriate companies can be found in Section C.1

of this file.

2. Download the following ratios for the most recent four filings (2019 to 2022):

Category Specific Ratios

Financial Ratios

Quick ratio; Current ratio

Total debt ratio; Cash coverage ratio

Inventory turnover; Receivable turnover; Total asset turnover

Profit margin; Return on assets; Return on equity

Market Value Ratios P/E ratio; Market-to-book ratio

a. The instructions on how to download the required ratios are shown in Sections C.2 and

C.3 of this file.

b. All ratios must be obtained from S&P NetAdvantage. No ratios should be calculated.

Missing ratios should be labeled as N/A in the Ratio Analysis worksheet containing the

organized data.

3. Save and organize data in one Excel file.

a. The submitted data file includes two types of data, downloaded data and organized

data.

b. Include the downloaded data of each company in its own worksheet and the

downloaded data of an industry in one worksheet. Make sure to label each worksheet.

c. Highlight in the downloaded data all required ratios shown in the table above.

i. Each database may label and/or define a ratio differently. Identify and highlight the

required ratios with the definitions in S&P NetAdvantage having the closest

resemblance to the ones covered in the course.

d. Organize the downloaded ratios in the provided Excel template and save it in one Excel

worksheet labeled as Ratio Analysis.

3

i. The Excel template for Ratio Analysis is available in the folder/module of

BUS30100_Project Assignment on Brightspace.

ii. Use cell references to input the data in Panel A: Company-Level & Industry-Level

Data. The industry benchmarks include corresponding industry-level ratios. If

needed, one possible source for how to create cell reference is

https://support.microsoft.com/en-us/office/create-or-change-a-cell-reference-

c7b8b95d-c594-4488-947e-c835903cebaa.

ii. Use the IF statement to complete Panel B: Evaluation of Company Performance. A

favorable comparison between a company-level ratio and its industry benchmark

(i.e., its corresponding industry-level ratio) is labeled as "Good" while an

unfavorable comparison is labeled as "Bad". If needed, one possible source for the

IF function can be found at https://support.microsoft.com/en-us/office/if-function-

69aed7c9-4e8a-4755-a9bc-aa8bbff73be2.

4. Complete a two-page analysis report based on the organized data.

a. Discuss and compare the performance of the two selected companies over the sample

periods shown in your data file. Conclude the report with your answer to the questions

of “How can each of the two selected companies stabilize or improve its future

performance?” and “Which one of the two selected companies is considered a better

investment?”. The report must reflect your own thoughts.

a. Use normal margins, double spacing, and a font size 12 to format the report.

b. Include references for external materials, if any. The reference section does not count

towards the page limit.

5. Submit the project assignment on Brightspace by 11:59pm on December 3, 2023.

a. A complete project assignment consists of an Excel file containing all required data as

mentioned in Section B.3, a Word file containing the analysis report as mentioned in

Section B.4, and the peer evaluation (required for students working in a group) as

mentioned in Section A.2.

b. Each group should submit the data file and the analysis report once. Make sure to list

all group members in submitted files. Multiple submissions by a group will be graded

based on the earliest submission unless specific reasons are communicated with the

professor. In addition, each group member must submit a peer evaluation to identify

the contributions of each member by 11:59pm on December 3, 2023. Without a peer

evaluation, a student’s project assignment is deemed incomplete and is subject to a 25%

reduction in total points of the assignment. The peer evaluation form is available in the

folder/module of BUS30100_Project Assignment on Brightspace.

c. For each day late, 25% of the project’s total points will be deducted.

4

C. Instructions on S&P NetAdvantage

1. Search for a non-financial and not regulated company and its comparable company

a. Search for a company

➔ Access S&P NetAdvantage at https://library.pfw.edu/az.php?a=s using your PFW

login.

➔ Type company name in the search bar; when typing the company name, a search

list will appear right below the search bar.

➔ Click on the company’s name with a ticker symbol from the search result list.

b. Identify a company’s industry classifications

➔ Click on Industry Classifications under Company Summary.

➔ Scroll down to the bottom of the refreshed page to identity and record the Standard

Industrial Classification (SIC) Codes; this is an important step to ensure that your

selected company is a non-financial and non-regulated company.

Notes: Non-financial and non-regulated companies are the ones without the first

digit SIC code of 6 (Finance, Insurance, and Real Estate), the first digit SIC code

of 9 (Public Administration), or the first two digit SIC code of 49 (Electric, Gas and

Sanitary Services).

Industry Classifications

Search Bar

Stock Ticker Symbol

5

c. Identify a comparable company with the same Primary Industry Classification and a

similar size as the company you select.

➔ Scroll to the top of the Industry Classifications page for the company you select.

➔ Click on the industry classification listed under Primary Industry Classification.

➔ Click on Constituents under Industry Profile on the refreshed page.

SIC Codes

Primary Industry Classification for matching

Industry Constituents Identified Primary Industry Classification

6

➔ Scroll down for the window of Constituents.

➔ Click on Revenue (mm) multiple times until the variable is sorted from largest to

smallest.

Notes: Total revenue is used as the criterion of firm size in the determination of

comparable companies.

➔ Click on the name of the company with a similar size.

Notes: A US publicly traded company has a Stock Exchange listing of NasdaqGS

or NYSE.

2. Download ratios for a company

a. Download financial ratios

➔ Click on Ratios under Financials/Valuation.

Financial Ratios

Companies with the same primary Industry Classification

Stock Exchange listing

Firm size criterion

7

➔ Define the display options (skip if the options are defined appropriately).

o Restatement = Latest Filings

o Period Type = Annual

o Order = Latest on Right

o Decimals = Capital IQ (Default)

o Click on Go

➔ Drag the time period mark along the timeline to illustrate the ratios in the required

filings.

Notes: The timeline shows fiscal-year periods, except for the latest filing (labeled

with LTM). The date listed for each fiscal-year period indicates the end of the fiscal

period.

➔ Click on Download This Page to download financial ratios.

b. Download market value ratios

➔ Click on Key Stats under Financials/Valuation.

Download This Page

Key Stats

Download This Page

Display Options

Ratio Definitions

Time Period Mark

8

➔ Scroll to the bottom of the refreshed page to see market value ratios listed under

Valuation Multiples based on Current Capitalization.

➔ Click on the “i” symbol following a market value ratio for its definition.

➔ Click on Download This Page to download listed market value ratios.

c. Repeat Steps a and b to download the data of the comparable company.

3. Download the industry-level data

➔ Click on Industry Classifications under Company Summary.

➔ Click on the listed Primary Industry Classification for the company.

➔ Click on Key Stats & Ratios under Industry Profile.

➔ Click on the Export to Excel symbol to download the data.

Industry Classifications

Primary Industry Classification

Key Stats & Ratios

Export to Excel

Section of Market Value Ratios

Ratio Definition