I need help with excel formulas....here are the questions that must be embedded in excel with formulas?

4. Market capitalization Share price Number of shares outstanding

$35 280 million = $9.8 billion.

7. a. Book value Total assets Total debt Preferred stock

For Truly Good Coffee:

Book value $240M $115M $25M $100M

which is equal to common stockholders equity.

b. Book value per share

For Truly Good Coffee:

Book value per share

c. Earnings per share (EPS)

For Truly Good Coffee:

d. Dividend payout ratio

For Truly Good Coffee:

Dividend payout ratio

e. Dividend yield on common stock

For Truly Good Coffee:

Dividend yield on CS

f. Dividend yield on preferred stock

For Truly Good Coffee:

Dividend yield on PS

10. a. If Wilfred sells his stock on March 20, he will be selling after the stocks ex-dividend date (which will occur on March 19), and therefore, he will be the holder of record and is entitled to receive the dividend of $100 ($0.50 200 shares).

b. Wilfred will be credited with $100 that will be used in the dividend reinvestment plan to purchase more of the companys stock. A 5% discount on the $40 share price is $2, so the purchase price would be $38 per share ($40 $2); thus, Wilfred will be able to purchase 2.632 shares of common stock ($100/$38). Unfortunately, Wilfred will have to pay taxes on the $100 since its still treated as a cash dividend.

14. a. Ignoring the currency effect:

(1) Total return to Bayer AG:

(2) Total return to Swisscom:

Ignoring the currency effect, Bayer promises the higher total return. Based on total returns in foreign currency form, it is the better investment.

b. Note: The euro depreciated, while the Swiss franc appreciated. Considering the currency effect:

(1) Total return to Bayer in U.S. dollars:

	60.0 1.5		0.9852	1
53.25	1.1080

(1.1549)(0.8892) 1 0.027 or 2.7%

(2)Total return to Swisscom in U.S. dollars:

	76.0 Sf 1.5 Sf		0.85	1
7.15 Sf	0.75

0.2284 or 22.84%

Exchange rates worked against the German investment because the higher returns were offset by an appreciation in the dollar relative to the euro. Conversely, the dollar depreciated relative to the Swiss franc, yielding an even higher total return on the Swiss stock. Given exchange rates, select the stock issued by Swisscom.

Chapter 7

(4) Debt-equity ratio Long-term debt/Stockholders equity

$50/$200 0.25

(10) Price/earnings ratio Share price/EPS

$75/$3.50 21.43 times

18. a. For 2012

Profit margin

11.27%

Asset turnover

1.32

Using profit margin and asset turnover to calculate ROA:

Where Equity multiplier Total assets/Stockholders equity

$136.3/$109.6

1.24

ROE 14.88% 1.24

18.45%

b. For 2013:

ROA Profit margin Total asset turnover

13.65% .85

11.60%

ROE ROA Equity multiplier

11.60% ($303,940/$212,343)

11.60% .43

16.59%

c. Between 2012 and 2013, Otago Bay Marine Motors ROA and ROE measures both deteriorated. With respect to ROA, total assets have grown faster than net sales, thereby affecting total asset turnoverand consequently, ROAadversely: Whereas net sales grew 44%, total assets grew 123%. The second component of ROA, the profit margin, grew slightly. Thus, the decline in ROA is attributable mainly to the dramatic increase in total assets (ROA fell from 14.88% in 2012 to 11.60% in 2013).

The growth in total assets is also a main contributor to the decline in ROE, which is composed of ROA multiplied by the equity multiplier. The higher equity multiplier reflects the fact that total assets also outgrew stockholders equity (a large portion of new assets were debt-financed). However, the multiplier was not large enough to reverse the effects of the decline in ROA on ROE. Thus, through the sharp increase in total assets, ROA and ROE declined.

d. Generally, these changes from 2012 to 2013 do not appear fundamentally healthy for Otago Bay Marine Motors. The increased total assets account, a large percentage of which was financed by debt, appears to have reduced OBMMs profitability and its future ability to meet short- and long-term obligations. The decline in ROA is related to the large increase (115%) in PPE and the 107% increase in other long-term assets. A complete analysis would necessarily include an analysis of these assets. For example, the increase in PPE could indicate that the company is anticipating future growth or that the company has updated its PPE and will be much more profitable in the future due to the efficiencies of modern equipment.

nSolutions to Case Problems

Case 6.1Sara Decides to Take the Plunge

The purpose of this case is to provide the student with the opportunity to identify investment needs, establish investment goals, and design an investment program.

a. Since Sara is so successful, she can easily provide for the necessities of life. Other than a minimum savings account to meet emergency needs, her savings should not be held for the long haul in these accounts. Keeping money tied up in low yielding savings accounts is costly since the lower yields mean much slower growth of capitalin short, Sara will end up with a lot less money than if shed put it into something with a higher return (like stocks). As an aside, Sara should make sure that any money she keeps in savings (i.e., for emergency purposes) is put into higher yielding short-term investments such as money funds, short-term CDs, or MMDAs. In her effort to avoid risk, Sara is actually incurring a great deal of purchasing power risk, the risk that her investment returns will not keep pace with inflation. Although common stocks involve more risk and may not be a perfect hedge against inflation, they will generally earn higher returns than basic savings accounts, so Sara should seriously consider putting some of her money into stocks (or some other form of equity security). Because of the positive effects that such investments have on the long-run accumulation of capital, equity securities should be a part of most portfolios, especially for younger people.

b. Because of her high current income, Sara does not need investments to provide income now. She does need securities for capital accumulation and as a store of value (protect loss in purchasing power due to inflation).

1. North Atlantic Swimsuit Company (NASS) might meet Saras needs, but it is a highly speculative stock. The capital accumulation and storage of value benefits from this security are difficult to predict. Certainly, Saras income and personal position justify some risk but probably not as her only investment. If she purchases NASS, she needs to be able to monitor the firm closely. She should be prepared to sell if its growth prospects suddenly dim. And she should purchase other less risky securities as well.

2. Town and Country Computer best fills these needs. It is a classic growth firm: It has a long record of growth and is a quality firm with excellent prospects. The modest dividend is unimportant now, and the stock should provide for storage of value as well as capital accumulation.

3. Southeastern Public Utility Company does not meet Saras needs; it has a high current dividend and low growth prospects. Without at least average growth, the stock cannot serve as a store of value, since inflation will cause the value of the investment to fall. On the other hand, given the recent changes in the tax laws for dividends, this stock could represent a decent growth opportunity if the dividends are reinvested. This would also provide some income in the event her employment situation changes.

4. International Gold Mines may serve as a source of capital accumulation, but its price volatility makes it questionable as a store of value. Certainly, its defensive characteristics make it attractive during periods of high inflation when the price of gold is increasing. However, when inflation moderates, it may fail to provide the spectacular performance it has had in inflationary times. A key here should be Saras expectations concerning inflation. If she expects inflation to accelerate at a high rate, this stock may suit her.

Of the four options, Town and Country best serves her needs, and Southeastern Utilities is least attractive. The other two, more speculative stocks, fall in between with their appeal a function of their long-term growth prospects (NASS) and expectations about inflation (International Gold Mines).

c. Sara should follow a buy-and-hold or a quality long-term growth strategy. A high-income strategy does not fit her needs; she does not have the expertise to handle aggressive stock management (although her investment adviser might help her here); and the speculative, short-term strategy does not match her requirements or abilities. Given her new entry into the area of investments, she should probably begin with the buy-and-hold strategy and plan to adopt a quality long-term growth strategy as she develops a better understanding of the market and if she has the time to devote to investment management. Through such a program, common stocks should enable her to enjoy long-term growth in capital. For Sara, common stocks are an ideal investment vehicle since they should provide the type of investment return (capital gain) she is (or should be) seeking. One of the key attributes of common stock is its potential for capital appreciation, and this is precisely what Sara should go after.

Case 6.2Wally Wonders Whether Theres a Place for Dividends

This case illustrates the common practice of changing investments in reaction to market changes. This case should allow students to experience some of the steps involved in following an investment strategy, as it demonstrates that during an economic downswing, even an investor interested in long-term growth may have something to gain by going after stocks that offer attractive dividend yields.

a. Wallys existing plan is one of long-term growth, obtained by investing in quality issues. Given his high income and the limited time he has to devote to his security holdings, this strategy (of quality growth) seems appropriate for him.

b. 1. Expected dividends for Hydro-Electric:

Year	(1) Expected EPS	(2) Expected Dividend Payout Ratio	(3) (1) (2) Expected Dividend
2010	$3.25	40%	$1.30
2011	3.40	40	1.36
2012	3.90	45	1.76
2013	4.40	45	1.98
2014	5.00	45	2.25

2. Wally could purchase 100 shares of Hydro-Electric stock ($6,000 investment/$60 per share). Expected returns would be a function of dividends and capital gains. First, dividend income over the five years would be:

Year	Dividends per Share		100 Shares		Total
2010	$1.30		100		$130
2011	1.36		100		136
2012	1.76		100		176
2013	1.98		100		198
2014	2.25		100		225
	Total dividends for five years:	$865

Now assuming he can sell the stock for $80 per share in five years, his 100 shares will bring $8,000 and his capital gains would be: $8,000 $6,000 $2,000.

Therefore:

Total return Total dividends Capital gains

$865 $2,000

$2,865

3. If Wally joins the companys Dividend Reinvestment Plan, he can obtain shares at reduced prices and hence can achieve his goal of capital appreciation.

Wally will obtain additional shares as follows:

(1)	(2)	(3)	(4)	(5)	(6)
Year	No. of Shares Held at Beginning of Year	Dividends per Share	Total Dividends	Purchase Price per Share	No. of Shares Purchased (4) (5)
2010	100.00	$1.30	$130.00	$50	2.60
2011	102.60	1.36	139.54	55	2.54
2012	105.14	1.76	185.05	60	3.08
2013	108.22	1.98	214.28	65	3.30
2014	111.52	2.25	250.92	70	3.58
Number of additional shares purchased through the DRP:	15.10
Number of shares bought originally:	100.00
				Total:	115.10

Using the Dividend Reinvestment Plan, Wally would have accumulated 15.10 additional shares, for a total of 115.10 shares by the end of 2011. With the stock trading at $80 on December 31, 2014, his shares would be worth 115.10 $80 $9,208.

Note: The figure shown in column (2) is the number of shares held at the beginning of the year; this number will increase each year with the dividends reinvested. This increases the total dividends received each year. Compare this answer with 2b.. Thus, dividend reinvestment plans have a cascading effect.

c. Wally would not be going to a different investment strategy if he buys the shares of Hydro-Electric; he would merely be changing the thrust of it. This is a common strategy used by aggressive investors following a long-term growth program. Of course, when conditions change, Wally would go back to investing in long-term growth stocks. In that sense, his trading would be increased. However, since the number of trades should not be too many, this should not be a serious drain on his time.

nSolutions to Case Problems

Case 7.1Some Financial Ratios Are Real Eye-Openers

The objective of this case is to have students calculate and interpret ratios as part of the fundamental analysis of a firm.

1. All the ratios below for South Plains Chemical are computed according to the formulas in the chapter. South Plains Chemical (dollars in thousands)

Liquidity

a. Net working capital Current assets Current liabilities

$21,250 $10,000 $11,250

b. Current ratio Current assets/Current liabilities

$21,250/$10,000 2.12

Activity

c. Receivables turnover Sales/Accounts receivable

$50,000/$8,000 6.25

d. Inventory turnover Sales/Inventory

$50,000/$12,000 4.17

e. Total asset turnover Sales/Total assets

$50,000/$30,000 1.67

Leverage

f. Debt-equity ratio Long-term debt/Stockholders equity

$8,000/$12,000 0.67

g. Times interest earned Earnings before interest and taxes/interest

$10,000/$2,500 4

Profitability

h. Net profit margin Net profits after taxes/Sales

$5,000/$50,000 1 or 10%

i. Return on total assets Net profits after taxes/Total assets

$5,000/$30,000 0.166 or 16.67%

j. Return on equity Net profits after taxes/Stockholders equity

$5,000/$12,000 0.4167 or 41.67%

Common Stock Ratios

k. Earnings per share (Net profits after taxes Preferred dividends)/

# of shares of common stock outstanding

$5,000 0/5,000 $1/share

l. Price/earnings ratio Share price/EPS

$25/$1 25 times

m. Dividends per share Total common dividends paid/

Common shares outstanding

$1,250/5,000 $0.25/sh.

(n) Dividend yield Dividends per share/Share price

$0.25/$25.00 0.01 or 1%

(o) Payout ratio Dividends per share/EPS

$0.25 / $1.00 0.25 or 25%

(p) Book value per share Common equity/Common shares outstanding

$12 million/5 million $2.40

(q) Price-to-book value Share price/Book value per share

$25/$2.40 10.42

2. Comparing the ratios computed in Question 1 to the latest industry averages, we find:

a. Liquidity. South Plains is more liquid than the average firm in its industry.

b. Activity. Here South Plains is weak; the low ratios suggest poor utilization of assets. Since accounts receivable and inventory are close to the industry figures, the deviation seems to come from excess fixed assets.

c. Leverage. The firm uses more debt than average, and its ability to cover interest is much lower than the average firm.

d. Profitability. The results are mixed here. The firm has higher margins and higher ROE. The lower return on assets reflects the poor activity ratios mentioned above. (The fact that the firm can have low ROA and high ROE is due to its high leverage. Since a smaller percentage of South Plains is financed with equity, the ROE is magnified more than for the typical firm.)

e. Common stock. All the measures relative to dividends are low for this firm, yet its price/earnings ratio is higher than average. This suggests that the market anticipates above-average profitability and returns in the future. That is why, although its current dividend yield is low, investors are willing to pay a high price/earnings ratio. The same goes for price-to-book value; like the P/E ratio, its much higher than average and provides further support that the market is anticipating good things from South Plains.

3. The firm seems to have good prospects for attractive returns but also has high risk. For Jack to continue his evaluation of South Plains Chemical, he must feel the opportunities for the firm are promising and commensurate with the risk involved. Due to South Plainss strong profitability, he should now proceed with a more in-depth analysis. (Although not discussed in the case, fundamental analysis should not be performed alone; careful economic and industry analyses are also important.)

Case 7.2Doris Looks at an Auto Issue

This case allows the student to experience the three steps of security analysis.

a. Other economic information that Doris might find helpful includes government fiscal policy (taxes, spending, debt management); monetary policy (money supply growth, interest rates); consumer spending and the investment plans of businesses; energy situation (oil imports, cost and availability of supplies, domestic oil prospects); foreign trade and the balance of payments (especially the value of the dollar); and the inflation rate. Although each is important, the three most important are probably interest rates, foreign trade, and the cost of oil. Interest rates are important since most automobiles are purchased on credit. Foreign trade is important because of the impact that auto imports and exports have on auto sales; also foreign trade affects the value of the dollar, which in turn makes U.S. cars more or less expensive relative to imports. Finally, the cost of gas and oil is closely watched by drivers and can impact the demand for gas guzzlers versus more fuel efficient cars.

b. 1. Auto imports. Trends in auto imports more clearly define potential growth for domestic producers. The recent growth of imports has been at the expense of U.S. producers. Federal restrictions on foreign imports would greatly alter industry conditions for domestic firms.

2. The relationship of the United Auto Workers (UAW) and the auto makers is an important part of the analysis. A better relationship leads to lower costs of production; a poor relationship may mean strikes, slowdowns, higher wage contracts, and so on. One important date here is the expiration date of the existing contract.

3. Interest rates. As mentioned above, automobiles are usually purchased with borrowed funds. The cost and availability of these funds is quite important in the sale of new cars.

4. The cost of gas. Since most cars need gasoline to run, it is very important to assess the course of gasoline prices in the future. If gasoline prices become extremely high, the auto industry is bound to suffer.

Note to the instructor: The situation can be more complex. For example, if the gas price is very high, consumers will tend to get rid of their old gas guzzlers and buy small economy cars. Thus, the issue can also revolve around whether or not the company we are considering is planning to produce small cars. If this is so, then increasing gasoline prices can boost the sales of cars, at least in the short run.

c. 1. Sales can be found from the total asset turnover ratio:

Common shares outstanding

From the case:

1.5

Solving for sales, we have:

Sales 1.5 $25 billion $37.5 billion

2. Profits can be found from the net profit margin:

Net profit margin

From the case:

0.15

Solving for net profits, we have:

Net profits after taxes 0.15 $37.5 billion $5.625 billion

3. We can find current assets using the figure for current liabilities and the net working capital formula:

Net working capital Current assets Current liabilities

From the case:

$3.4 billion Current assets $5 billion

Solving for current assets, we have:

Current assets $3.4 billion $5 billion $8.4 billion

Therefore, the current ratio equals:

4. The price of the stock can be found from the P/E ratio:

Price/earnings ratio

From the case:

12.5

Solving for share price, we have:

Price per share 12.5 $3 $37.50