ACCT 6330 010 Financial Statement Analysis

ACCT 6330 010 Financial Statement Analysis

Spring 2017

Ongoing Individual Project Guidelines

You have been assigned a company for an ongoing analysis project. The project will be completed and submitted for a grade in five parts that follow the modules we will cover in the class sessions. The five parts are as follows:

-Part I – Business Environment and Company Financial Information

-Part II - Financial Analysis of Company’s Past and Present Performance

-Part III – Normalization and Adjustment of Financial Data

-Part VI – Forecasting and Predicting Future Company Performance

-Part V – Valuation of a Company

All your work should be submitted in an accumulated manner when the various parts are due. The finished project should contain all your research documents and analysis.

PART I: Business Environment and Company Financial Information

COMPANY RESEARCH AND INFORMATION

Research your company on its corporate website and/or other sources. Gather the following data:

Obtain a set of downloaded current financial statements and public disclosures. You must include three years of historical data. For the balance sheet you will need four years to derive threeyears of average balances. This may require you to pull more than one year’s 10K filing. Use the most current SEC filings data.
Income Statement
Balance Sheet
Statements of Cash Flows
Statement of Shareholder’s Equity
Obtain a copy of the financial footnotes to these statements, the Management Discussion and Analysis (MD&A), and the Auditor’s Opinion included in the SEC filing.
Gather industry financial standards for the company’s financial information and ratios.
Gather any pertinent financial press releases of the company, including the year-end release that coincides with your annual SEC filing and any releases subsequent to that period.
Review any company analyst calls or shareholder meeting videos from the company website or YouTube, if applicable.

All the research documents above should be maintained in a binder and brought to class.

POINTS AVAILABLE: 15 POINTS

COMPANY EXECUTIVE SUMMARY

Prepare a summary of the company:

What do they do?

What industry do they operate in?

Where are the located?

Basic statistical facts about their size, history, culture and leaders.

Identify their customers and suppliers

What is its current stock price?

How are they financed (debt vs equity)? What is their NBV? What is their market cap? How does NBV and market cap compare? Is their working capital strong? What are their largest assets and liabilities?

What is there credit rating?

What is their fiscal year-end?

What are the major accounting guidelines impacting the company and its industry?

Include any other information about your company that is pertinent to its industry or operations.

Include a summary of the initial financial assessment of the company’s profitability (Are they profitable? What are their profit trends? What are their margins? Comparison to competitors. What are their major expenses? Do they have unusual items?)

The Company Executive Summary should be completed and saved in a file named CompanySummary_”YourLastName”. Example: CompanySummary_White.

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 25 POINTS

COMPETITIVE ANALYSIS

Prepare an assessment of the business environment of the company:

Prepare the Porter Five Forces analysis
Prepare a SWOT analysis
Competitive analysis that includes market share data for the industry players. This can be presented in a chart or tabular format.

The Competitive Analysis should be completed and saved in a file named CompetitiveAnalysis_”YourLastName”. Example: CompetitiveAnalysis_White. It is acceptable to save this work in the same file as your Company Summary if you performed this work in the same type of file (ie. Both done in Word).

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 25 POINTS

FINANCIAL STATEMENT TEMPLATE

Prepare a financial statement template in EXCEL for the Income Statement, Balance Sheet and Statement of Cash Flows using the data obtained from your SEC filings. Must include current and two historical years of annual data.
Prepare a common-size income statement, using total revenues as the basis, for your company. Prepare a common-size balance sheet, using total assets as the basis, for your company. Review similar information for a competitor. Briefly summarize any conclusions from the trends in your Company Summary file.

The Financial Statement should be completed and saved in a file named FinancialAnalysis_”YourLastName”. Example: FinancialAnalysis_White. In the template, each financial statement should be in a separate tab.

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 35 POINTS

TOTAL POINTS AVAILABLE: 100 POINTS

PART II: Financial Analysis of Company’s Past and Present Performance

Prepare all the financial measures using financial data for the last three historical years in the company statements.

Gross Profit/Operating Profit as a % or Revenues
Net Income as a % of Revenues
Earnings per Share (EPS)
Effective Tax Rate (ETR)
Return on equity (ROE)
Return on assets (ROA)
Profitability and Productivity of ROA
Profit Margin
Asset Turnover
Net Operating Profit After Taxes (NOPAT)
Net Operating Assets (NOA)
Return on Net Operating Assets (RNOA)
Net Operating Profit Margin (NOPM)
Net Operating Asset Turnover (NOAT)
Net Non-operating Expense (NNE)
NNO Net Non-operating Obligations (NNO)
Financial Leverage (FLEV)
Spread
Non-operating Return
Working Capital
Credit risk ratios
Times interest
EDITDA coverage
Cash from Operations to Total Debt
Current ratio
Quick ratio
Debt to equity ratio
Accounts receivable (A/R) turnover (ART)
Average sales outstanding
Accounts payable (A/P) turnover) (APT)
A/P days outstanding
Free cash flow (FCF)

As part of your ratio analysis you will be required to segregate your income statement and balance sheet between Operating and Non-operating items. Show your work by highlighting the accounts on your finance statements that are Operating. For the balance sheet, create a separate Net Operating Asset (NOA) balance sheet in a schedule within your file.

The Financial Ratios should be completed and saved in the file named FinancialAnalysis_”YourLastName”.

The work should be performed on a separate tab in your Financial Analysis Excel file. The work should include the definition of the ratio and the calculated result of the ratio for the two historical years. When calculating the ratios, formulas must be used that reference cells in your financial statement tabs. Points will be deducted if calculations do not reference data and reflect numbers that are “hand keyed” or “hard coded”.

This file should be resubmitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 100 POINTS

For each financial measure, prepare a comparison across historical years and to your industry standards and financial norms. Discuss in your comparison what the ratio tells an analyst in general terms and reflects about your specific company. Based on your company’s ratios, describe any actions that could be taken to address improved performance, if warranted.

Your Ratio Analysis should be completed and saved in a file named RatioAnalysis_”YourLastName”. Example: RatioAnalysis_White

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 50 POINTS

TOTAL POINTS AVAILABLE: 150 POINTS

PART III: Normalization and Adjustment of Financial Statements

Understand the accounting policies of your company for potential normalization and adjusting. Determine if adjustments are necessary for items discussed in lectures and provided on the Normalization and Adjustment Listing. Make sure when deciding on adjustments to consider the Materiality concept.

Prepare a document for the adjustments you have determined that details the adjustment, including the debits and credits and proper accounts that will be adjusted. Give a brief explanation of the reasoning behind the adjustments you have included and any assumptions used to come up with the accounts and amounts. You must make adjustments to all three years of your forecasted financial statements.

Your Adjustment Analysis should be completed and saved in a file named AdjustmentAnalysis_”YourLastName”. Example: AdjustmentAnalysis_White

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 40 POINTS

Create a new set of adjusted financial statements that reflect the adjusting entries you determined. You must show adjustment for all three years of your forecasted financials.

The Adjusted Financials should be completed and saved in the file named FinancialAnalysis_”YourLastName”.

The work should be performed on a separate tab in your Financial Analysis Excel file. Make a separate tab for each adjusted financial statement.

POINTS AVAILABLE: 50 POINTS

4. For each year of your adjusted financial statements, complete a new set of financial ratio calculations.

The work should be performed on a separate tab in your Financial Analysis Excel file. You should be able to use all your formulas for the previous ratio tab. If you calculated a ratio incorrectly in Part II, you must correct the ratio for Part III.

POINTS AVAILABLE: 10 POINTS

This Financial Analysis file should be resubmitted in eCollege in the appropriate dropbox.

TOTAL POINTS AVAILABLE: 100 POINTS

PART IV: Forecasting and Predicting Future Company Performance

Using your adjusted financials statements, develop a set of forecasted future financial statements and the assumptions used for your company. Project three years of future financial statements.

The work should be performed on a separate tab in your Financial Analysis Excel file. Make a separate tab for each forecasted financial statement.

Must include an income statement, balance sheet and cash flow statement.Must have a summary of the assumption logic used for each assumption made. Format is as follows:

Historical
Account / Year
Name / 2014 / Assumption / 2015 / Assumption / 2016 / Assumption / 2017
Ex.
Revenue / 25,000 / 25,000 * 1.05 / 26,250 / 26,250 * 1.05 / 27,563 / 27,563 * 1.05 / 28,941

POINTS AVAILABLE: 80 POINTS

You must include the Financial Footnote for “Debt” and “PP&E” in a file copied from your 10K. You can also include any forward-looking statements made in your 10-K that you are using for assumptions copied into your file as a resource.

3.For each year of your forecasted financial statements, complete a new set of financial ratio calculations.

This file should be resubmitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 10 POINTS

4. Prepare a summary of the conclusions reached through your forecasted financial statements and ratio analysis. This should be a 1-2 page summary of the trends and what your projections reveal about the future of your company’s financial health.

Your ForecastAnalysis should be completed and saved in a file named ForecastAnalysis_”YourLastName”. Example: ForecastAnalysis_White

This file should be submitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 10 POINTS

TOTAL POINTS AVAILABLE: 100 POINTS

PART V: Financial Analysis of Company’s Past and Present Performance

Determine the following for your company:
Cost of debt capital using the After-Tax Cost of Debt model
Cost of equity capital using the CAPM model
Weighted cost of capital
Dividend Discount Framework

Use the first year of your forecasted financial statements to perform these calculations. You DO NOT have to do these calculations for all the years, only the first year of your forecasted financial statements.

Must show all work and calculations. Must provide summary or where information was gathered or calculated for assumptions.

The work should be performed on a separate tab in your Financial Analysis Excel file.

This file should be resubmitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 30 POINTS

For both of the valuation models below, develop a valuation for your company.
Discounted Cash Flow Model
Residual Operating Income Model

These models require multiple years of financial information;therefore, you will use more than just the first year of your forecasted financials statements. You will use all the years of your forecasted financial statements.

Must show all work and calculations. Must provide summary or where information was gathered or calculated for assumptions. This value would be what you would use to buy or sell the company.

The work should be performed on a separate tab in your Financial Analysis Excel file.

This file should be resubmitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 35POINTS

A) Prepare the company and equity intrinsic value for your company using all of the following Multiple Models:

NOPAT Multiple
Net Income Multiple
Net Operating Assets (NOA) Multiple
Book Value Multiple

B) Also calculate the Price per Earnings Ratio (P/E) for your company.

Must show all work and calculations. Must provide summary or where information was gathered or calculated for assumptions

The work should be performed on a separate tab in your Financial Analysis Excel file.

This file should be resubmitted in eCollege in the appropriate dropbox.

POINTS AVAILABLE: 35 POINTS

TOTAL POINTS AVAILABLE: 100 POINTS