Reference no: EM131403254

Assignment Project: FINANCIAL ANALYSIS OF A CORPORATION

Learning Objective:

1. Apply knowledge gained in the course. For example, be able to explain trends, use the correct terminology, etc.
2. Value a corporation.
3. Critically analyze the drivers in the business of the corporation and extrapolate out its consequences.

Audience:

This assignment is something that prospective employers might find interesting. With that in mind you should assume that you are an analyst "working" for an investment firm and your report may be sent by your "boss" to the firm's clients.

Brief Description:

You will be provided with the past ten years' balance sheets and income statements of a large publicly traded corporation (Note: I will email to you a spreadsheet containing the past 10 years' Income Statements and Balance Sheets of a firm.) Using this data and other information you gather from various sources, you are expected to complete the following:

Historic Analysis

1. Cash flow statements for the past four years
2. Ratio analysis for the past four years
3. Weighted average cost of capital for the past four years

Forecasting

1. Income statement, balance sheet, statement of cash flows and financial ratios for the three years from the end of the last balance sheet.
2. ‘What if' analysis in which you will change the assumptions used in your forecasting model to illustrate the potential deviations for the forecasts.

Detailed Description

Part I - Introduction

This part should include two elements.

a. Introduction (one page maximum)
b. Corporate history, products, etc. (one page maximum)

Part II - Historical Analysis

This part represents the historical analysis of your firm.

a. Statement of cash flows

You will write up an analysis of the statement of cash flows where you will explain on a year-by-year basis.

(1) Where did the firm's operating funds come from?
(2) On what did the firm spend its funds?
(3) Where did they get their financing and what did they pay off?
(4) What was the overall change in cash?

Please try to bring in the relevant information about the events that had significant effect on the cash inflows and out flows during the four-year period. Please make sure that you conduct and extensive analysis of the past event like mergers and divestitures, etc. and discuss the effects of such events on cash flows.

b. Ratio analysis

In this section calculate all the major ratios and then select atleast three ratios with which you can explain some of the major changes occurred during the past four years again on a year-by-year basis. You should show actual calculations and plot the ratios in a line graph format. You should then explain the changes in these ratios using the information you may gather from published sources. It is also important that you will compare the ratios of the firm with that of the industry. Written explanation for each of the three ratios should be a maximum of two pages. Thus, the maximum is six pages.

c. Weighted Average Cost of Capital (WACC)

In this section you will study the capital structure of the firm and determine the cost of capital of debt, preferred stock and common equity. You may use the following methodology for estimating the cost of capital for each of the three sources of capital mentioned above.

(1) Cost of debt: It is hard to obtain information about the individual costs of different types of debt the firm might have. First find the credit rating of the firm for the year and use the average interest of corporate debt of similar rating and use it as a proxy for the cost of debt.

(2) Cost of preferred stock: Most of the firms issue preferred stock with imbedded options, which makes it hard to estimate the cost complicated. You may use the perpetuity valuation model for preferred stock, even though this might underestimate the true cost of preferred stock.

(3) Common stock: You should estimate the cost of common equity using the following two methods:

- Capital asset pricing model (CAPM)
- Discounted cash flow model (DCF)

After estimating the WACC for each year separately you should have a write up on the significant changes in the WACC during the period of analysis. Please limit your discussions to a maximum of three pages.

d. Summary

This section is a fusion of all the material in the preceding three sections. It should not be a rehash of this material; rather it is the knowledge that you have learned about your company from putting it all together. As a suggestion, try to distill the single overall reason that was most responsible for the trends that you observed in your historical analysis of the company. Please try to restrict the length to three pages.

Part III - Forecasting

In this section you will forecast the following financial information of the firm for the next three years.

(1) Income statements, Balance sheets and Cash flow statements and Free cash flow statements
(2) Financial ratios

a. The Premises and Forecast: This section will include the planning premises on which you built the forecasting model. For instance, why do you think there will or will not be sales growth? Will there be reductions in expenses because of specific programs currently under implementation? Much, if not most, of the material used in this section will be derived from your analyses and subsequent research. If you take material from another source, it must be footnoted. Use an appropriate format for your footnotes.

Finally, after spelling out all of your planning premises, summarize what your model actually forecasted. Will the company continue to make a profit or a loss? Will it go bankrupt? Will there be dramatic changes in financing? Will there be proportionately large increases or decreases in any particular accounts? Also, are these forecasts logically consistent with the planning premises that you made? (Two pages maximum text).

b. The Model Section: Make a list of all the accounts in your income statement and balance sheet and next to each account print out the formula that existed in the corresponding cell for the first year forecasted. On the facing right page explain the interaction of the cell formulas. For instance, the text should describe issues such as how costs computed, how was interest expense determined, and so on, for the income statement. It should then describe how the individual accounts in the balance sheet were determined. It is especially important that you tell me how you calculated the level of retained earnings and the process and specific accounts that were used to make the balance sheet balance. (Three pages maximum text).

c. Valuation: In this section you will use the free cash flow statements and forecasted cost of capital to find the value of the firm. You may compare this with the current market capitalization of the firm and predict the stock prices for the next three years.

d. What if analysis: The last section of part III is the "what if" section which should discuss which variables interact when one of the model's inputs or assumptions (i.e., sales) was changed. Summarize what is forecasted to happen to your firm now. Print the new pro forma financial statements to illustrate. You may discuss the possible future scenarios for this firm based on this analysis. Please limit the discussion to a maximum of one page.

Format for Project

1. Title page

2. Table of contents. Please make sure that all pages of your paper are numbered, except for the appendixes. You should indicate the pages in which each of the sub-sections is presented.

3. The following are the sub-sections of your project. Use double space and 12 pt. font.

Part I - Introduction

a. Introduction (one page maximum)
b. Corporate history, products, etc. (one page maximum)

Part II - Historical Analysis

a. Cash flow analysis. Analyze the cash flows for four years and present it in chronological order.

b. Ratio analysis. Analyze the same three ratios for all the four years and present it in chronological order.

c. WACC. There must be a written analysis of the WACC, but this need not be divided into individual years.

d. Summary. In this section you will summarize your observations on the performance of the firm from the analysis you conducted in the previous three sections.

Part III - Forecasting

a. Premises and forecast. In this you will describe the premises on which you built the forecasting model and the analysis of the forecasted financial statements.

b. Model Section. In this section show the percentage growth rates you used in the forecasting of each variable in the Income Statement and Balance Sheet. Also print the formulas for the first year of forecast using the attached Exhibit A. as example and attach it as Appendix E.

c. What if analysis. In this section you will discuss the results of Income Statement and Balance sheet when the sale growth rate is higher and lower than the one you used in part a. of this section.

Bibiliography

Appendixes

A. Financial Statements: Five years of historical data plus three years of forecasted data. Use Aloca model that I emailed to you. This should also have Free Cash Flows, Cash Flows as well as ratios.

B. Weighted average cost of capital calculations.

C. Correlation estimates

D. Trend analysis

E. Forecast model

F. Financial statements from what-if analysis. Print only the Income statements and Balance sheets.

G. Three separate graphs of the three ratios you have analyzed. Include the three forecasted years also.

H. Four years Valueline report copies

I. Four years industry ratios

Note: When you print the spreadsheets make sure that all columns are in the same page.

Alcoa

Generated Annual Income Statement 39447 39813 40178

Currency

Auditor Status Final

Consolidated Growth rate

Scale

Total Revenues =F9*(1+$J9) =Sheet4!D9

Cost of Revenues =F10*(1+$J10) =Sheet4!D10

Gross profit =G9-G10

Selling, General & Administrative =F12*(1+$J12) =Sheet4!D12

Depreciation & Amortization =F13*(1+$J13) =Sheet4!D13

Research & Development =F14*(1+$J14) =Sheet4!D14

Other Indirect Expenses =F15*(1+$J15) =Sheet4!D15

EBIT =G11-SUM(G12:G15)

Interest Income =F17*(1+$J17) =Sheet4!D17

Interest Expense -384000 Do not forecast

Realized Capital Gains (Losses) =F19*(1+$J19) =Sheet4!D19

Foreign Exchange Gain (Loss) 40000 =Sheet4!D20

Other Income (Expense), net 150000 =Sheet4!D21

EBT =G16+G17+G18+G19+G20+G21

Income Taxes =G22*0.34 Do not forecast

Equity Earnings =F24*(1+$J24) =Sheet4!D24

Minority Interest =F25*(1+$J25) =Sheet4!D25

Discontinued Operations =F26*(1+$J26) =Sheet4!D26

Accounting Changes =F27*(1+$J27) =Sheet4!D27

Net Income =G22-G23+G24-G25+G26+G27

Alcoa

Generated Annual Balance Sheet
Currency
Auditor Status
Consolidated
Scale

Cash & Cash Equivalents =F39*(1+$J39) =Sheet4!D39
Receivables =F40*(1+$J40) =Sheet4!D40
Inventories =F41*(1+$J41) =Sheet4!D41
Other Current Assets =F42*(1+$J42) =Sheet4!D42
Total Current Assets =SUM(G39:G42)

Long-term Receivables 0 =Sheet4!D45
Investments and Advances =F46*(1+$J46) =Sheet4!D46
Land =F47*(1+$J47) =Sheet4!D47
Buildings =F48*(1+$J48) =Sheet4!D48
Machinery and Equipment =F49*(1+$J49) =Sheet4!D49
Other fixed assets =F50*(1+$J50) =Sheet4!D50
Gross Property, Plant & Equipment =SUM(G47:G50)
Accumulated Depreciation & Amortization =F52*(1+$J52) =Sheet4!D52
Net Property & Equipment =G51-G52
Intangible Assets =F54*(1+$J54) =Sheet4!D54
Other Assets =F55*(1+$J55) =Sheet4!D55
Total Assets =G43+G45+G46+G53+G54+G55

Accounts Payable =F59*(1+$J59) =Sheet4!D59
Taxes Payable =F60*(1+$J60) =Sheet4!D60
Accrued Expenses =F61*(1+$J61) =Sheet4!D61
Short-term Borrowings =F62*(1+$J62) =Sheet4!D62
Current Portion of Long-term Debt =F63*(1+$J63) =Sheet4!D63
Other Current Liabilities =F64*(1+$J64) =Sheet4!D64
Total Current Liabilities =SUM(G59:G64)

Long-term Debt =5910000+Sheet5!G84 Do not forecast
Deferred Income Taxes =F68*(1+$J68) =Sheet4!D68
Other Deferred Liabilities =F69*(1+$J69) =Sheet4!D69
Minority Interest =F70*(1+$J70) =Sheet4!D70
Other Liabilities =F71*(1+$J71) =Sheet4!D71
Total Liabilities =G65+SUM(G67:G71)

Preferred Stock =F74*(1+$J74) =Sheet4!D74
Common Stock 925000 Do not forecast
Additional Paid-In Capital 5817000 Do not forecast
Retained Earnings =G28+F77+$F125 Do not forecast
Treasury Stock =F78*(1+$J78) =Sheet4!D78
Other Stockholders' Equity -1233000 =Sheet4!D79
Stockholders' Equity =G74+G75+G76+G77-G78+G79

Total Liabilities & Stockholders' Equity =G72+G80

Balance sheet difference =G56-G82

Attachment:- Attachments.rar