Corporate and Industry Financial Analysis and Presentation

Please review attached instructions for the requirements. An excel file and power point presentation is required. 

052721Page 1

I. Corporate and Industry Financial Analysis and Presentation

II. Introduction

Measurement, analysis, and interpretation (Excel): Accurately computes
all ratios and other financial metrics relevant to the industry. Apply
financial statement analysis to evaluate a firm’s financial performance

Research: research and present current information on the firm and the
industry.

Decision making: Engage critical thinking skills to select most relevant
ratios, identify all data outliers, select 5 qualitative factors impacting the
firm, identifies internal weaknesses and external: threats, incorporate
relevant current news, accurately identify limitations/shortcomings in the
analysis, and highlight data in which the group found
questionable/suspicious.

You’re one of the top three candidates for a position at Benson, Cundiff, & Gilbert
a financial accounting and brokerage firm in the heart of Washington, DC. You’ve
always wanted to live in the District, as locals call it.

Sasha, the head of Human Resources at Benson, Cundiff, & Gilbert called this
morning. After a brief discussion, Sasha says, “in preparation for your third
interview you will be making a presentation to our Board of Directors and at least
one of the three principals, but don’t worry about that, they are eager to meet
you.” Easy for her to say, you thought. Further, Sasha says, “we also want to
learn how well each candidate works as a team member, so you will prepare and
make the presentation with the other three candidates. This will be very useful to
those making the hiring decision. Each candidate will be assessed on the process
used to create the presentation file and the actual presentation. We’ve done this
type of interviewing in the past and sometimes more than one candidate is hired:

Levin Page 2

not for the same position but in related jobs. Are you willing to partake in this
group interview?” Without giving it a lot of thought because you didn’t want to
sound hesitant, you say “Absolutely; what time and where?”

III. Steps to Completion

Conduct a comprehensive financial analysis of a publicly traded corporation and

make a presentation to the Board of Directors (professor). Financial analyses will
be performed in Excel and the presentation will be prepared in PowerPoint. The
Excel file must support the figures in your presentation.

The results of the financial analysis will be presented in a professional quality
PowerPoint presentation, with the presenter’s notes under each slide.

Step 1: Prepare Excel file

All cells requiring computations must have a formula. Cell formulas should
reference other sheets where appropriate. Complete all calculations in one Excel
file; multiple sheets are encouraged.

Step 2: Prepare PowerPoint presentation

A. Introduction to the industry in which the corporation operates – this section may

require multiple slides

Common issues/risks all corporations face in the industry
Critical success factors in the industry
Current economic health of the industry

B. Introduction to the corporation – this section may require multiple slides

A brief history
Briefly describe the corporation’s strategy
Identify the corporation’s major competitors

in Page 3

C. Financial analysis – this section will require multiple slides

Compute the accompanying financial ratios for the 5 most recent years
for which a Form 10-K has been filed.
In addition, you should obtain the industry average ratios for the most
recent year for the SIC code of the industry in which the corporation
operates. The corporation’s ratios for the most recent year will be
compared to the industry ratios.
Compute any ratios or metrics which are industry-specific for the 5-year
period
Prepare a 5-year trend analysis (also known as a horizontal analysis) of
each of the ratios computed in A and C. Note: the corporation data will
represent a 5-year trend, the industry data for the most recent period will
represent a point in time.
Create tables, graphs, or other media to present your findings. All
tables/graphs/etc. included from external sources, those you did not
create, must be cited.
Be sure to address any data/ratio outliers (red flags) discovered in your
analysis.
Summarize your findings by these financial ratio categories: Liquidity
ratios, Efficiency ratios, Leverage ratios, Profitability ratios, Market Value
Indicators.
Some ratios may not be applicable to your corporation. For example,
inventory ratios may not be applicable for corporations in the service
industry corporations. If a ratio is not applicable and not computed for
your corporation, explain the reason the ratio is not applicable for this
analysis.

D. Qualitative factors

Address issues of a qualitative nature that may impact the financial health
of the corporation. For example, pending litigation, disclosures in its annual
report, presence or lack of a corporate code of ethics/professional behavior,
a corporate hotline, foreign operations, legislation, recent employee strikes,
political issues, SASB/environmental issues in the annual report, etc. Check
for news articles within the past five (5) years.

E. Risks

Discuss internal weaknesses and external threats to the financial health the
corporation faces. See Michael Porter’s Competitive Advantage to learn
more about a SWOT analysis.

haron Levin Page 4

F. Summary of analysis

Summarize your findings.
Highlight any items from your analysis for which you are skeptical or
have doubt.
Identify any shortcomings in your analysis.
Conduct an online search for any late-breaking information/news, which
may be available since the filing of the most recent Form 10-K may
have been many months ago. Often, this will be in a subsequent filing of
Form 8-K.

G. References

Sources of information used in the project. Use strict APA Style for the
Reference list.

IV. Deliverables

A. Excel
Include as many sheets as necessary to perform the required financial
statement analysis. Save the file with the Group name as part of the file name.
Example: Group 1 Project.xlsx.

B. PowerPoint
Prepare the file in APA Style. You may find this video helpful in determining
how to apply APA Style to presentation files: APA formatting for PowerPoint.

0527

• Course Outcomes Assessed in this Project:
•  Measurement, analysis, and interpretation (Excel): Accurately computes all ratios and other financial metrics relevant to the industry. Apply financial statement analysis to evaluate a firm’s financial performance
•  Research: research and present current information on the firm and the industry.
•  Decision making: Engage critical thinking skills to select most relevant ratios, identify all data outliers, select 5 qualitative factors impacting the firm, identifies internal weaknesses and external: threats, incorporate relevant current news, ac…
•  Communication: Create an audio enhanced Power Point presentation and effectively delivers information tailored to the intended audience

Sheet1

All items are presented as a % of Net Sales

20X2

Sales

Sales
$1,300,000

Cost of Goods Sold
600,000

of Net Sales

Gross Profit
700,000
600,000

Operating Expenses:

General & Administration Expenses
125,000

Selling Expenses
100,000

54%

Total Operating Expenses
225,000

50%

Operating Income
475,000

6%

Interest Expense
10,000
5,000

Income Before Taxes
465,000

4%

Income Taxes
139,500

4%

Net Income After Taxes
$325,500

4%

																																																																Vertical Analysis of Income Statement	Horizontal Analysis of Income Statement
	All items are presented as a % of Net		Sales
Notice the vertical analysis is analyzin one year only	Notice the horizontal analysis is analyzing the change between two years
	20X2	What does this mean?	20X1	% Change from 20X1 to 20X2	What does this mean? From 20X1 to 20X2:
	$1,300,000	100.0%	Sales or Net Sales is the denominator for all accounts below in a Vertical Analysis.	$1,000,000	30%	Sales increased 30% from 20X1 to 20X2. The formula is (20X2 Sales – 20X1 Sales) divided by 20X1 Sales. I memorize the formula like this: (New – Old)/Old
	Cost of Goods Sold			600,000	46.2%	Divide cell B6 by $B$5. The formula is: =SUM(B6/$B$5)	400,000		50%	COGS increased 50%, which is 20% more than net sales. What would cause this to occur? Did vendors increase prices? Was there inventory shrinkage?
	Gross Profit		700,000	53.8%	Gross profit is ~	5			4%	17%	Notice you cannot derive Cell J7 by subtracting 50% from 30%. Gross project increased 17%, almost 1/2 of the increase in Net Sales
By using $B$5 instead of B5 as the denominator, you can copy and paste the formula down. The $ sign anchors a specific cell; in this case, B5 Total Sales
	Operating Expenses:
	General & Administration Expenses		12	5,000	9.	6%	Divide cell B10 by $B$5	85,000	47%	Why did G&A expenses increase 47% in one year?
	Selling Expenses		100,000	7.7%	65,000	Why did Selling expenses increase 54%?
	Total Operating Expenses		225,000	17.3%	150,000	Notice again, Cell J12 cannot be derived by subtracting J11 from J10.
	Operating Income		475,000	37%	450,000	Continuing using the formula (New – Old)/Old
	Interest Expense		10,000	0.8%	100%	The company must have borrowed more funds bc interest expense doubled. Why? What was the intent of the borrowing?
	Income Before Taxes		465,000	35.8%	445,000
	Income Taxes		139,500	10.7%	133,500	Income taxes are an unavoidable expense, but can be managed through effective tax strategies
	Net Income After Taxes		$325,500	25.0%	Net Income is 25% of Net Sales	$311,500
Since Gross Profit is 53.8% and Net Income is 25% of Net Sales, Operating Expenses, Interest Expense, and Income Tax Expense must be 28.8% of Net Sales (53.8% – 25%) OR (9.6% + 7.7% + .8% + 10.7% = 28.8%)