Final essay

The project (8-10 pages excluding the tile and reference pages)

You will need to select a current publicly traded company on the stock exchanges. You will not receive a grade if you choose a company that is private, has been sold, or no longer traded on the exchanges. There will be no time to redo your analysis.

You will need 2 attachments:

A- Excel to do next year’s pro forma analysis and the financial ratios (total of 20). If you don’t use Excel, you will not receive a credit on these portions. Remember that Excel (as a spread sheet, but not used as a text) is a requirement in this class. If you want, you can add the EVA calculations to Excel as well.

B- Word document for the rest. There is no need for you to copy the tables from Excel into Word. You can refer to these tables in your write-up.

The total number of pages for Excel and Word documents should be 8-10 pages.

__________________

Please use sub-titles to organize your work.

First bullet: (word document) introduce the company, products, and major announcements/

Second bullet: (Excel and word) In excel show the latest financial data. In word analyze the financial data.

Third bullet: (Excel)

Pro Forma

1- Next to the financial statements, add extra two columns that you will use; one is used for the bases for your calculations ((i.e. sales increased by 10%, etc.), and the second is for the actual results in $ (label that column as “20xx Proforma”.

2- Follow the example (step-by-step) shown in chapter 3 of the text for both the balance sheet and the income statement.

What do I want to see in Excel?

a- When I click on your calculated numbers, I should see your detailed calculations, not hand entered numbers where you are supposed to calculate the correct numbers. That means, never to use your manual calculator. The simple rule here is to “show all calculations”.

b- You must show a complete income statement and a complete balance sheet (please do not reduce or condense them on your own)

Very, very important:

Pro forma: since you are making changes to the company’s financial data, your calculated pro forma balance sheet will not balance. This is expected but not acceptable. The balance sheet MUST balance.

To balance a pro forma balance sheet you have two options:

1- Add a new line item at the end of the liability section (before the total) and label it as “External financing required” (the text explain it as “plug figure”). This number must be positive.

OR, to:

2- Reduce the long-term liability (but never to increase it). Be sure to explain why? Next to the long-term liability figure

Note: you have to select either option (not both) as deemed necessary to be able to balance the balance sheet. You will need to explain your entry. Please do not change some numbers to balance, this will not be acceptable.

Fourth Bullet: (Excel for the calculations and word for analysis)

The financial ratios:

The ratios are to be posted in a new tab in Excel

2- You should use the numbers from the financial statements (not the pro forma). If different, explain why?

3- Write the formula before you attempt to solve for the ratio, i.e. Current ratio = current assets / current liabilities, follow that by the actual numbers, calculate the result, and write an explanation of each ratio’s impact on the company. Show the ratio calculations in a table

4- You need to calculate 20 financial ratios. See the final paper instructions.

	Ratio	1	2	3	4	5
1	Liquidity	Current Ratio	Quick Ratio
Formula	current assets/current liabilities	current assets – inventory/current liabilities

2	Financial Leverage	Degree of Financial Leverage	Degree of Operating Leverage	Degree of Total Leverage
Formula	% change in EPS / % change in EBIT	% change in EBIT / % change in sales	DFL*DOL

3	Asset Management or Efficiency ratios	Inventory turnover	Days sales outstanding	Assets turnover
Formula	cost of goods sold/average inventory	accts receivable/(credit sales/360)	revenues/total assets

4	Profitability	Net profit margin	Return on Assets	Return on Equity	Gross profit margin	Earning power
Formula	net income/revenue	net income/average total assets	net income/average stockholders’ Equity	revenue – COGS/revenue	EBIT/total assets

5	Market value	Price to cash ratio		Average Rate of return
Formula	Price per share / (Cash flow / Shares outstanding)		(current market value – earlier market value) / (earlier market value * the difference between current and earlier years)

6	Debt Ratios	debt ratio	leverage ratio	Times Interest earned ratio
Formula	Total debt/total assets	total assets/total equity	EBIT/interest (dollars)

7	Per- Share	Earnings Per Share	EPS	Book Value Per Share	Dividends Per Share
Formula	net income/no. of shares	net income/no. of shares;	total equity/no. of shares	total dividends/no. of shares

8	Relative value	P/E	P/B
Formula	price per share3/EPS	price per share/book value share

9	Activity	average collection period	average payment period	accounts receivable turnover
Formula	360/accounts receivable turnover ratio	accounts payable/(cost of revenue/360)	sales/average accounts receivable

10	Cash Flow	operating cash flow ratio	Price to cash flow ratio	cash flow margin ratio	cash flow to total liabilities
Formula	Cash flow from operations/ current liabilities	Price per share / Operations Cash flow / Shares outstanding)	Cash flow from operating cash flows / net sales (be careful; use net sales NOT gross sales)	Cash flow from operations/ total liabilities

Fifth Bullet: (Excel), if you haven’t already calculated ROE, you will need to add it

Sixth Bullet: Excel or word

EVA

See how to solve for EVA in week #5 guidance, no short cuts, please.

Seventh Bullet: (word)

Synopsis of your findings, recommendations, and rational to purchase the stock or not

Eight Bullet; (word)

This bullet is about the financial international risks

You should also add a conclusion for your paper.

Please be logical, neat, and make your paper very easy to follow. Never let your reader’s attention wander or trying to second guess your numbers.

Be sure to follow the same sequence and subtitles of the required topics.

As you have discovered by now, the text does not cover all the material, you will need other library or internet resources to be able to finish this task with flying colors.

If there is any conflict, the only directions that you should follow is the class's final paper instructions, all others are considered just “guidance”.

Final Paper Rubric

 Includes an Introduction to the Company, With a Thorough Financial Statement Review, and Pro forma Financial Statements (4 points)

 Includes a Ratio Analysis and Calculates the Return on Equity (ROE) Using the DuPont System (4 points)

 Assesses Management Performance By Calculating the Economic Value Added (EVA) (3 points)

 Includes a Synopsis of the Findings, Including Recommendations and Rationale for Whether or Not to Purchase the Company’s Stock (4 points)

 Evaluates the Financial Risks Associated with Operating Internationally (3 points)

 Written Communication: Control of Syntax and Mechanics (0.5 point)

 Written Communication: APA Formatting (0.5 point)

 Written Communication: Page Requirement (0.5 point)

 Written Communication: Source Requirement (0.5 point)

Total 20 points

Corrections to the Text table 11.3 (notice that in the real world we don’t use a negative sign in front of the numbers):

	2009	2010	2011
Revenue	$52,846.00	$50,280.00	$57,394.00
Cost of goods sold (COGS)	13,781.00	13,632.00	19,271.00
Selling, general, and administrative expense (SG&A)	15,569.00	14,050.00	13,035.00
Depreciation	13,250.00	13,250.00	13,250.00
Earnings before interest and taxes (EBIT)	10,246.00	9,348.00	11,838.00
Interest expense	1,830.00	1,642.00	1,701.00
Earnings before taxes (EBT)	8,416.00	7,706.00	10,137.00
Taxes (34%)	2,861.44	2,620.04	3,446.58
Net income (NI)	5,554.56	5,085.96	6,690.42
Dividends	2,000.00	2,000.00	2,500.00
Retained earnings	3,554.56	3,085.96	4,190.42

Good luck everyone and best wishes on your careers!