I have been assigned General Mills in a group project and I need help with the following 4 questions. All questions are focused around General Mills

I have been assigned General Mills in a group project and I need help with the following 4 questions. All questions are focused around General Mills. Please show work. 
Q1. Forecasting 
• List of Specific Assumptions used in forecasts 
• Forecasts for a 5 year time horizon 
Need explanation for time and specific assumption used in forecast. Must be done on word doc. Need to show references. 
Q2. Valuation 
• Components of the WACC (with sources referenced) 
• DCF Model – Optimistic, Pessimistic & Expected 
• DAE Model – Optimistic, Pessimistic & Expected 
• DAROE Model – Optimistic, Pessimistic & Expected 
I will need a written explanation to why the company is Optimistic, Pessimistic & Expected for each of the models and the WACC analysis. Must be done on word doc. Need to show references. 
Q3. Assessment of Solvency 
• Calculation of Altman’s Z 
• Estimate of Bond Rating based on ratios 
• Actual Bond Rating (if applicable) 
Will need explanation for each item. Must be done on the word doc. Need to show references.
Q4. Spreadsheets
• Buffet spreadsheet 
Must substitute Nike with General Mills answer the questions highlighted in yellow. Instructions are in the first 3 spreadsheets and the fourth is the actual spreadsheet to complete. 
FYI: year 1 is last year and year 8 is 8 years ago. Government bond is based on long term. 
• Valuation spreadsheet (DCF&EVA)
This assignment has 2 parts. First Spreadsheet FCFF valuation, second spreadsheet feeds off the first spreadsheet, please check to make sure that second spreadsheet is reflecting the answers from the first spreadsheet.

Finding Stocks

Finding Stocks the Warren Buffett Way by John Bajkowski Like most successful stockpickers, Warren Buffett thinks that the efficient market theory is absolute rubbish. Buffett has backed up his beliefs with a successful track record through Berkshire Hathaway, his publicly traded holding company. Maria Crawford Scott examined Warren Buffett’s approach in the January 1998 issue of the AAII

Journal.

Table 1

below provides a summary of Buffett’s investment style. In this article, we

develop a screen to identify promising businesses and then use valuation models to measure the attractiveness of stocks passing the preliminary screen. Buffett has never expounded extensively on his investment approach, although it can be gleaned from his writings in the Berkshire Hathaway annual reports. Many books by outsiders have attempted to explain Buffett’s investment approach. One recently published book that discusses his approach in an interesting and methodical fashion is “Buffettology: The Previously Unexplained Techniques That Have Made Warren Buffett the World’s Most Famous Investor,” by Mary Buffett, a former daughter-in-law of Buffett’s, and David Clark, a family friend and portfolio manager [published by Simon & Schuster, 800-223-2336; $27.00]. This book was used as the basis for this article. Monopolies vs. Commodities Warren Buffett seeks first to identify an excellent business and then to acquire the firm if the price is right. Buffett is a buy-and-hold investor who prefers to hold the stock of a good company earning 15% year after year over jumping from investment to investment with the hope of a quick 25% gain. Once a good company is identified and purchased at an attractive price, it is held for the long-term until the business loses its attractiveness or until a more attractive alternative investment becomes available. Buffett seeks businesses whose product or service will be in constant and growing demand. In his view, businesses can be divided into two basic types: Commodity-based firms, selling products where price is the single most important factor determining purchase. Buffett avoids commodity-based firms. They are characterized with high levels of competition in which the low-cost producer wins because of the freedom to establish prices. Management is key for the long-term success of these types of firms. Consumer monopolies, selling products where there is no effective competitor, either due to a patent or brand name or similar intangible that makes the product or service unique. While Buffett is considered a value investor, he passes up the stocks of commodity-based firms even if they can be purchased at a price below the intrinsic value of the firm. An enterprise with poor inherent economics often remains that way. The stock of a mediocre business treads water. How do you spot a commodity-based company? Buffett looks for these characteristics: The firm has low profit margins (net income divided by sales); The firm has low return on equity (earnings per share divided by book value per share); Absence of any brand-name loyalty for its products; The presence of multiple producers; The existence of substantial excess capacity; Profits tend to be erratic; and The firm’s profitability depends upon management’s ability to optimize the use of tangible assets. Buffett seeks out consumer monopolies. These are companies that have managed to create a product or service that is somehow unique and difficult to reproduce by competitors, either due to brand-name loyalty, a particular niche that only a limited number companies can enter, or an unregulated but legal monopoly such as a patent. Consumer monopolies can be businesses that sell products or services. Buffett reveals three types of monopolies: Businesses that make products that wear out fast or are used up quickly and have brand-name appeal that merchants must carry to attract customers. Nike is a good example of a firm with a strong brand name demanded by customers. Any store selling athletic shoes must carry Nike products to remain competitive. Other examples include leading newspapers, drug companies with patents, and popular brand-name restaurants such as McDonald’s. Communications firms that provide a repetitive service that manufacturers must use to persuade the public to buy the manufacturer’s products. All businesses must advertise their items, and many of the available media face little competition. These include worldwide advertising agencies, magazine publishers, newspapers, and telecommunications networks. Businesses that provide repetitive consumer services that people and businesses are in constant need of. Examples include tax preparers, insurance companies, and investment firms. Mary Buffett suggests going to your local 7-Eleven or White Hen Pantry to identify many of these “must-have” products. These stores typically carry a very limited line of must-have products such as Marlboro cigarettes and Wrigley’s gum. However, with the guidance of the factors used to identify attractive companies, we can establish a basic screen to identify potential investments worthy of further analysis.

The rules used for our Buffett screen are identified and discussed in

Table 2

. AAII’s Stock

Investor Professional was used to perform the screen. Consumer monopolies typically have high profit margins because of their unique niche; however, a simple screen for high margins may highlight weak firms in industries with traditionally high margins, but low turnover levels. Our first screening filters looked for firms with both gross operating and net profit margins above the median for their industry. The operating margin concerns itself with the costs directly associated with production of the goods and services, while the net margin takes all of the company activities and actions into account. Understand How It Works As is common with successful investors, Buffett only invests in companies he can understand. Individuals should try to invest in areas where they possess some specialized knowledge and can more effectively judge a company, its industry, and its competitive environment. While it is difficult to construct a quantitative filter, an investor should be able to identify areas of interest. An investor should only consider analyzing those firms operating in areas that they can clearly grasp. Conservative Financing Consumer monopolies tend to have strong cash flows, with little need for long-term debt. Buffett does not object to the use of debt for a good purpose–for example, if a company uses debt to finance the purchase of another consumer monopoly. However, he does object if the added debt is used in a way that will produce mediocre results–such as expanding into a commodity line of

business. Appropriate levels of debt vary from industry to industry, so it is best to construct a relative filter against industry norms. We screened out firms that had higher levels of total liabilities to total assets than their industry median. The ratio of total liabilities to total assets is more encompassing than just looking at ratios based upon long-term debt such as the debt-equity ratio. Strong & Improving Earnings Buffett invests only in a business whose future earnings are predictable to a high degree of certainty. Companies with predictable earnings have good business economics and produce cash that can be reinvested or paid out to shareholders. Earnings levels are critical in valuation. As earnings increase, the stock price will eventually reflect this growth. Buffett looks for strong long-term growth as well as an indication of an upward trend. In the book, Mary Buffett looks at both the 10- and five-year growth rates. Stock Investor Professional contains only seven years of data, so we examined the seven-year growth rate as the long-term growth rate and the three-year growth rate for the intermediate-term growth rate. For our screen, we first required that a company’s seven-year earnings growth rate be higher than that of 75% of the stocks in the overall database. Stock Investor Professional includes percentile ranks for growth rates, so we specified a percentile rank greater than 75. It is best if the earnings also show an upward trend. Buffett compares the intermediate-term growth rate to the long-term growth rate and looks for an expanding level. For our next filter, we required that the three-year growth rate in earnings be greater than the seven-year growth rate. This further reduced the number of passing companies to 213. Not surprisingly, the companies passing the Buffett screen have very high growth rates–as a group, nearly three times the median for the whole database. Consumer monopolies should show both strong and consistent earnings. Wild swings in earnings are characteristic of commodity businesses. A examination of year-by-year earnings should be performed as part of the valuation. The earnings per share for Nike are displayed in the Buffett valuation spreadsheet. Note that earnings per share growth has been strong and consistent with only one year in which earnings did not increase from the previous period. A screen requiring an increase in earnings for each of the last seven years would be too stringent and not be in keeping with the Buffett philosophy. However, a filter requiring positive earnings for each of the last seven years should help to eliminate some of the commodity- based businesses with wild earnings swings. A Consistent Focus Companies that stray too far from their base of operation often end up in trouble. Peter Lynch also avoided profitable companies diversifying into other areas. Lynch termed these diworseifications. Quaker Oats’ purchase and subsequent sale of Snapple is a good example of this common mistake. Companies should expand into related areas that offer high return potential. Nike’s past development of a line of athletic clothing to complement its athletic shoe business is an example of a extension that makes sense. This factor is clearly a qualitative screen that cannot be done with the computer. Buyback of Shares Buffett views share repurchases favorably since they cause per share earnings increases for those who don’t sell, resulting in an increase in the stock’s market price. This is a difficult variable to screen as most data services do not indicate this variable. You can screen for a decreasing number of outstanding shares, but this factor is best analyzed during the valuation process. Investing Retained Earnings A company should retain its earnings if its rate of return on its investment is higher than the investor could earn on his own. Dividends should only be paid if they would be better employed in other companies. If the earnings are properly reinvested in the company, earnings should rise over time and stock price valuation will also rise to reflect the increasing value of the business.
An important factor in the desire to reinvest earnings is that the earnings are not subject to personal income taxes unless they are paid out in the form of dividends. The use of retained earnings delays personal income taxes until the stock is sold. Buffett examines management’s use of retained earnings, looking for management that have proven it is able to employ retained earnings in the new moneymaking ventures, or for stock buybacks when they offer a greater return. Good Return on Equity Buffett seeks companies with above average return on equity. Mary Buffett indicates that the average return on equity over the last 30 years has been around

12%. We created a custom field that averaged the return on equity for the last seven years to provide a better indication of the normal profitability for the company. During the valuation process, this average should be checked against more current figures to assure that the past is still indicative of the future direction of the company. Our screen looks for average return on equity of 12% or greater. Inflation Adjustments Consumer monopolies can typically adjust their prices quickly to inflation without significant reductions in unit sales since there is little price competition to keep prices in check. This factor is best applied through a qualitative examination of a company during the valuation stage. Reinvesting Capital In Buffett’s view, the real value of consumer monopolies is in their intangibles–for instance, brand-name loyalty, regulatory licenses, and patents. They do not have to rely heavily on investments in land, plant, and equipment, and often produce products that are low tech. Therefore they tend to have large free cash flows (operating cash flow less dividends and capital expenditures) and low debt. Retained earnings must first go toward maintaining current operations at competitive levels. This is a factor that is also best examined at the time of the company valuation although a screen for relative levels of free cash flow might help to confirm a company’s status. The above basic questions help to indicate whether the company is potentially a consumer monopoly and worthy of further analysis. However, stocks passing the screens are not automatic buys. The next test revolves around the issue of value. The

Price

is Right (Using the Spreadsheet) The price that you pay for a stock determines the rate of return–the higher the initial price, the lower the overall return. The lower the initial price paid, the higher the return. Buffett first picks the business, and then lets the price of the company determine when to purchase the firm. The goal is to buy an excellent business at a price that makes business sense. Valuation equates a company’s stock price to a relative benchmark. A $500 dollar per share stock may be cheap, while a $2 per share stock may be expensive. Buffett uses a number of different methods to evaluate share price. Three techniques are highlighted in the book with specific examples and are used in the buffet spreadsheet template. Buffett prefers to concentrate his investments in a few strong companies that are priced well. He feels that diversification is performed by investors to protect themselves from their stupidity. Earnings Yield Buffett treats earnings per share as the return on his investment, much like how a business owner views these types of profits. Buffett likes to compute the earnings yield (earnings per share divided by share price) because it presents a rate of return that can be compared quickly to other investments. Buffett goes as far as to view stocks as bonds with variable yields, and their yields equate to the firm’s underlying earnings. The analysis is completely dependent upon the predictability and stability of the earnings, which explains the emphasis on earnings strength within the preliminary screens. Nike has an earnings yield of

5.7%

(cell C13, computed by dividing earnings per share of

$2.77 (cell C9) by the price

$4

8.2

5

(cell C8)). Buffett likes to compare the company earnings yield to the long-term government bond yield. An earnings yield near the government bond yield is considered attractive. With government bonds yielding around 6% currently (cell C17), Nike compares very favorably. By paying $48 dollars per share for Nike, an investor gets an earnings yield return equal to the interest yield on bonds. The bond interest is cash in hand but it is static, while the earnings of Nike should grow over time and push the stock price up. Historical Earnings Growth Another approach Buffett uses is to project the annual compound rate of return based on historical earnings per share increases. For example, earnings per share at Nike have increased at a compound annual growth rate of

18.9%

over the last seven years (cell B32). If earnings per share increase for the next 10 years at this same growth rate of 18.9%, earnings per share in year 10 will be $

15.58

. [$2.77 x ((1 + 0.189)^10)]. (Note this value is found in cells B49 and E39) This estimated earnings per share figure can then be multiplied by the average price- earnings ratio of

1

4.0

(cell H10) to provide an estimate of price [$15.58 x 14.0=$2

17.4

3]. (Note this value is found in cell E42) If dividends are paid, an estimate of the amount of dividends paid over the 10-year period should also be added to the year 10 price [

$217.43

+ $1

3.29

= $230.72

]. (Note this value is found in cell E43) Once this future price is estimated, projected rates of return can be determined over the 10- year period based on the current selling price of the stock. Buffett requires a return of at least 15%. For Nike, comparing the projected total gain of $230.72 to the current price of $48.25 leads projected rate of return of

16.9%

[($230.72/$48.25) ^ (1/10) – 1]. (Note this value is found in cell E45) Sustainable Growth The third approach detailed in “Buffettology” is based upon the sustainable growth rate model. Buffett uses the average rate of return on equity and average retention ratio (1 average payout ratio) to calculate the sustainable growth rate [

ROE

x ( 1 – payout ratio)]. The sustainable growth rate is used to calculate the book value per share in year 10 [

BVPS

((1 + sustainable growth rate )^10)]. Earnings per share can be estimated in year 10 by multiplying the average return on equity by the projected book value per share [ROE x BVPS]. To estimate the future price, you multiply the earnings by the average price-earnings ratio [

EPS

x P/E]. If dividends are paid, they can be added to the projected price to compute the total gain. For example, Nike’s sustainable growth rate is

19.2%

[

22.8%

x (1 –

0.15

9)].(Sustainable growth rate is found in cell H11) Thus, book value per share should grow at this rate to roughly $

65.94 in 10 years [

$11.38

x ((1 +

0.19

2)^10)]. (Note this value is found in cell B64) If return on equity remains 22.8% (cell H6) in the tenth year, earnings per share that year would be $

15.06

[ 0.228 x $65.94]. (Note this value is found in cell E54) The estimated earnings per share can then be multiplied by the average price-earnings ratio to project the price of

$210.23

[$15.06 x 14.0]. (Note this value is located in cell E56) Since dividends are paid, use an estimate of the amount of dividends paid over the 10-year period to project the rate of return of

16.5%

[(($210.23 + $

12.7

2)/ $48.25) ^ (1/10) – 1]. (Note this return estimate is found in cell E60) Conclusion The Warren Buffett approach to investing makes use of “folly and discipline”: the discipline of the investor to identify excellent businesses and wait for the folly of the market to buy these businesses at attractive prices. Most investors have little trouble understanding Buffett’s philosophy. The approach encompasses many widely held investment

principles.

Its successful implementation is dependent upon the dedication of the investor to learn and follow the principles.
John Bajkowski is editor of Computerized Investing and senior financial analyst of AAII. (c) Computerized Investing – January/February 1998, Volume XVII, No.1

Table 1

level of spending needed to maintain current operations

earnings per share figure and the average growth in earnings per share over

Table 1. The Warren Buffett Approach
Philosophy and style
Investment in stocks based on their intrinsic value, where value is measured by the ability to
generate earnings and dividends over the years. Buffett targets successful businesses–those
with expanding intrinsic values, which he seeks to buy at a price that makes economic sense,
defined as earning an annual rate of return of at least 15% for at least five or 10 years.
Universe of stocks
No limitation on stock size, but analysis requires that the company has been in existence for a
considerable period of time.
Criteria for initial consideration
Consumer monopolies, selling products in which there is no effective competitor, either due to
a patent or brand name or similar intangible that makes the product unique. In addition, he
prefers companies that are in businesses that are relatively easy to understand and analyze,
and that have the ability to adjust their prices for inflation.
Other factors
A strong upward trend in earnings
Conservative financing
A consistently high return on shareholder’s equity
A high level of retained earnings
		Low
Profitable use of retained earnings
Valuing a Stock
Buffett uses several approaches, including:
Determining firm’s initial rate of return and its value relative to government bonds: Earnings
per share for the year divided by the long-term government bond interest rate. The resulting
figure is the relative value-the price that would result in an initial return equal to the return
paid on government bonds.
Projecting an annual compounding rate of return based on historical earnings per share
increases:		Current
the past 10 years are used to determine the earnings per share in year 10; this figure is then
multiplied by the average high and low price-earnings ratios for the stock over the past 10
years to provide an estimated price range in year 10. If dividends are paid, an estimate of the
amount of dividends paid over the 10-year period should also be added to the year 10 prices
Stock monitoring and when to sell
Does not favor diversification; prefers investment in a small number of companies that an
investor can know and understand extensively. Favors holding for the long term as long as the
company remains “excellent”–it is consistently growing and has quality management that
operates for the benefit of shareholders. Sell if those circumstances change, or if an
alternative investment offers a better return.

Table 2

12%.

Table 2. Translating the Buffett Style Into Screening

Questions to determine the attractiveness of the business:

Consumer monopoly or commodity?

Buffett seeks out consumer monopolies selling products in which there is no effective

competitor, either due to a patent or brand name or similar intangible that makes the product

unique. Investors can seek these companies by identifying the manufacturers of products that

seem indispensable. Consumer monopolies typically have high profit margins because of their

unique niche; however, simple screens for high margins may simply highlight firms within

industries with traditionally high margins. For our screen, we looked for companies with

operating margins and net profit margins above their industry norms. Additional screens for

strong earnings and high return on equity will also help to identify consumer monopolies.

Follow-up examinations should include a detailed study of the firm’s position in the industry

and how it might change over time.

Do you understand how it works?

Buffett only invests in industries that he can grasp. While you cannot screen for this factor, you

should only further analyze the companies passing all screening criteria that operate in areas

you understand.

Is the company conservatively financed?

Buffett seeks out companies with conservative financing. Consumer monopolies tend to have

strong cash flows, with little need for long-term debt. We screened for companies with total

liabilities below the median for their respective industry. Alternative screens might look for

low debt to capitalization or to equity.

Are earnings strong and do they show an upward trend?

Buffett looks for companies with strong, consistent, and expanding earnings. We screened for

companies with seven-year earnings per share growth greater than 75% of all firms. To help

indicate that earnings growth is still strong, we also required that the three-year earnings

growth rate be higher than the seven-year growth rate. Buffett seeks out firms with consistent

earnings. Follow-up examinations should include careful examination of the year-by-year

earnings per share figures. As a simple screen to exclude companies with more volatile

earnings, we screened for companies with positive earnings for each of the last seven years and

latest 12 months.

Does the company stick with what it knows?

A company should invest capital only in those businesses within its area of expertise. This is a

difficult factor to screen for on a quantitative level. Before investing in a company, look at the

company’s past pattern of acquisitions and new directions. They should fit within the primary

range of operation for the firm.

Has the company been buying back its shares?

Buffett prefers that firms reinvest their earnings within the company, provided that profitable

opportunities exist. When companies have excess cash flow, Buffett favors shareholder-

enhancing maneuvers such as share buybacks. While we did not screen for this factor, a follow-

up examination of a company would reveal if it has a share buyback plan in place.

Have retained earnings been invested well?

Earnings should rise as the level of retained earnings increase from profitable operations. Other

screens for strong and consistent earnings and strong return on equity help to the capture this

factor.

Is the company’s return on equity above average?

Buffett considers it a positive sign when a company is able to earn above-average returns on

equity. Marry Buffett indicates that the average return on equity for over the last 30 years is

approximately 12%. We created a custom field that calculated the average return on equity

over the last seven years. We then filtered for companies with average return on equity above

Is the company free to adjust prices to inflation?

True consumer monopolies are able to adjust prices to inflation without the risk of losing

significant unit sales. This factor is best applied through a qualitative examination of the

companies and industries passing all the screens.

Does company need to constantly reinvest in capital?

Retained earnings must first go toward maintaining current operations at competitive levels, so

the lower the amount needed to maintain current operations, the better. This factor is best

applied through a qualitative examination of the company and its industry. However, a screen

for high relative levels of free cash flow may also help to capture this factor.

buffett valuation

Averages

22.8%

:

$48.25

–

:

$2.77

:

14.0

$11.38 Sustainable Growth 19.2%

17.4

5.7%

Price P/E Ratio Payout
Year EPS DPS BVPS High Low High Low ROE Ratio

4.0

12.7

0.15

8.2

0.19

6

22.5 10.9

9.1

EPS DPS BVPS

18.9% 22.8% 22.1%

Year EPS DPS
Current $2.77

15.58

Year 1 3.29

Year 2

Year 3

$217.43

Year 4

$230.72

Year 5

Year 6

16.9%

Year 7

Year 8

15.58

Year BVPS EPS DPS
Current $11.38

15.06

Year 1

Sum of dividends paid over 10 years

Year 2

Year 3

$210.23 Projected price (Average P/E * EPS)

Year 4

Total gain (Projected Price + Dividends)

Year 5

Year 6

16.5%

Year 7

[(Total Gain / Current Price) ^ (1/10)] – 1

Year 8

Year 9

Year 10 65.94 15.06

Buffett Valuation Worksheet (January/February 1998, Computerized Investing, www.aaii.com)
Enter values into shaded cells
Date of Analysis:	12/31/97
Current Stock Data	Seven			Year
Company:	Nike, Inc.	Return on Equity:
Ticker:	NKE		Payout	Ratio	15.9%
Price:		P/E Ratio		High	18.4
EPS:	P/E Ratio-Low:	9.5
				DPS	$0.48	P/E Ratio:
BVPS:
P/E:	(ROE * (1 – Payout Ratio))
Earnings Yield:
Dividend Yield:	1.0%
P/BV:	4.2
Gv’t Bond Yield:	6.0%
Historical Company Data
		Year 8	0.80	0.09	2.62	8.70	3.20		10.9	30.5%	11.3%
		Year 7	0.94	0.13	3.39	11.98	6.00	6.4	27.7%	13.8%
		Year 6	1.07	4.35	18.94	8.78	17.7	24.6%	14.0%
		Year 5	1.18	5.33		22.5	13.75	1	9.1	11.7		22.1%	16.1%
		Year 4	0.99	0.20	5.77	22.31	10.78	17.2%	20.2%
		Year 3	1.36	0.24	6.68	19.13	11.56	14.1	8.5	20.4%	17.6%
		Year 2	1.88	0.29	8.28	35.19	17.19	18.7	22.7%	15.4%
		Year 1	2.68	0.38	10.63	64.00	31.75	23.9	11.8	25.2%	14.2%
High Price	Low Price
Annually Compounded Rates of Growth (7 year)	[(Year 1 / Year 8) ^ (1/7)] – 1
33.0%	38.8%
Annually Compounded Rates of Growth (3 year)	[(Year 1 / Year 4) ^ (1/3)] – 1
39.4%	23.9%	22.6%	42.1%	43.3%
Projected Company Data Using Historical Earnings Growth Rate
0.44	Earnings after 10 years
0.52	13.29		Sum of dividends paid over 10 years
3.91	0.62
4.65	0.74		Projected price (Average P/E * EPS)
5.53	0.88		Total gain (Projected Price + Dividends)
6.57	1.05
7.81	1.24	Projected return using historical EPS growth rate
9.28	1.48		[(Total Gain / Current Price) ^ (1/10)] – 1
11.03	1.76
	Year 9	13.11	2.09
	Year 10	2.48
Projected Company Data Using Sustainable Growth Rate
2.60	0.41	Earnings after 10 years (BVPS * ROE)
13.57	3.10	0.49	12.72
16.17	3.69	0.59
19.28	4.40	0.70
22.98	5.25	0.84	$222.96
27.39	6.26	1.00
32.66	7.46	1.19	Projected return using sustainable growth rate
38.93	8.89	1.42
46.41	10.60	1.69
55.32	12.64	2.01
2.40

Using Average ROE
Using Average Dividend Payout Ratio

Sheet:FCFF Valuation

Sheet:

EVA

Valuation

INPUTS FOR VALUATION

Current Inputs

Enter the current revenues of the firm =

1240

6.0

Enter current capital invested in the firm =

2000

0.0

{ As a naïve estimate, you can use BV of debt + BV of Equity)

Enter the current depreciation =

23

3.0

Enter the current capital expenditures for the firm =

29

8.0

Enter the change in Working Capital in last year =

11

5.0

Enter the value of current debt outstanding =

0.0

Enter the number of shares outstanding =

1500.0

High Growth Period

Your Inputs

Enter the growth rate in revenues for the next 5 years =

0.25

What will all operating expenses be as a % of revenues in the fifth year?

0.7

(Operating expenses include depreciation: This is equal to (1-Pre-tax Operating Margin))

How much debt do you plan to use in financing investments?

0.0

Enter the growth rate in capital expenditures & depreciation

0.25

Enter working capital as a percent of revenues

0.075

Enter the tax rate that you have on corporate income

0.36

What beta do you want to use to calculate cost of equity =

1.25

Enter the current long term bond rate =

0.06

5

Enter the market risk premium you want to use =

0.05

5

Enter your cost of borrowing money =

0.085

Stable Period

Enter the growth rate in revenues =

0.06

Enter operating expenses as a % of revenues in stable period =

0.75

Enter capital expenditures as a percent of depreciation in this period

2.0

See capital expenditure worksheet (capex.xls) for details.

How much debt do you plan to use in financing investments?
0.05

Enter interest rate of debt in stable period =

0.075

What beta do you want to use in the stable period =

1.1

ESTIMATED CASHFLOWS

Base

Growth in Revenue

0.25
0.25
0.25
0.25
0.25

0.212

0.174

0.136

0.098

0.06

Growth in Deprec’n

0.25
0.25
0.25
0.25
0.25
0.212
0.174
0.136
0.098
0.06

Revenues

12406.0

15507.5

19384.375

24230.46875

30288.0859375

37860.107421875

45886.4501953125

53870.69252929687

61197.10671328125

67194.42317118282

71226.08856145378

Operating Expenses

% of Revenues

0.7
0.7
0.7
0.7
0.7
0.7

0.71

0.72

0.73

0.74

0.75

– $ Operating Expenses

8684.199999999999

10855.25

1356

9.0

625

16961.328125

21201.66015625

26502.0751953125

32579.379638671875

38786.89862109375

44673.887900695314

49723.87314667529

53419.566421090334

EBIT

3721.8

00000000001

4652.25

5815.3125

7269.140625

9086.42578125

11358.0322265625

1330

7.0

70556640625

15083.793908203123

16523.21881258594

17470.55002450753

17806.52214036345

Tax Rate

0.36
0.36
0.36
0.36
0.36
0.36
0.36
0.36
0.36
0.36
0.36

EBIT (1-t)

2381.9520000000007

2977.44

3721.8
4652.25
5815.3125
7269.140625

8516.52515625

9653.628101249999

10574.860040055002

11181.15201568482

11396.174169832608

+ Depreciation

233.0

291.25

36

4.0

625

455.078125

568.84765625

71

1.0

595703125

861.80419921875

1011.7581298828125

1149.357235546875

1261.9942446304688

1337.713899308297

– Capital Expenditures

298.0

372.5

465.625

582.03125

727.5390625

909.423828125

1262.6246222233187

1615.8254163216375

1969.0262104199564

2322.227004518275

2675.427798616594

– Change in WC

115.0

232.61249999999998

290.765625

363.45703125

454.3212890625

567.901611328125

601.9757080078125

598.818175048828

549.4810637988285

449.79873434261737

302.3749042703224

= FCFF

2201.9520000000007

2663.5775

3329.471875

4161.83984375

5202.2998046875

6502.874755859375

7513.729025237619

8450.742639762346

9205.710001383091

9671.120521454395

9756.085366253988

Terminal Value (in ’05)

167812.58398749254

COSTS OF EQUITY AND CAPITAL

1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0

10.0

Cost of Equity

0.13375

0.13375
0.13375
0.13375
0.13375

0.1321

0.13045

0.1288

0.12715

0.1255

Proportion of Equity

1.0
1.0
1.0
1.0
1.0

0.99

0.98

0.97

0.96

0.95

After-tax Cost of Debt

0.054400000000000004

0.054400000000000004
0.054400000000000004
0.054400000000000004
0.054400000000000004

0.05312

0.051840000000000004

0.05056

0.04928

0.048

Proportion of Debt

0.0
0.0
0.0
0.0
0.0

0.010000000000000009

0.020000000000000018

0.030000000000000027

0.040000000000000036

0.050000000000000044

Cost of Capital

0.13375
0.13375
0.13375
0.13375
0.13375

0.13131020000000002

0.12887780000000001

0.1264528

0.12403520000000001

0.121625

Cumulative

WACC

1.13375

1.2853890625000002

1.4573098496093753

1.6522250419946292

1.8732101413614108

2.119181739665606

2.392297220073882

2.6948099019844407

3.029061187139061

3.39747075402485

Present Value

2349.35170893054

2590.2444420402867

2855.8373120620577

3148.6629680948818

3471.5137465213693

3545.5802985652567

3532.480232327219

3416.0888286049662

3192.778198907477

52264.958909031935

FIRM VALUATION

Value of Firm

80367.496645086

– Value of Debt

0.0

Value of Equity

80367.496645086

Value of Equity per Share

53.57833109672399

Value of Firm by year

80367.496645086

88453.07182136623

96954.19830247399

105759.98248167988

114703.08033391708

123541.7425727191

132250.30447305375

140843.69012310874

149448.0591001251

158313.7584787665

$ Value of Debt

0.0
0.0
0.0
0.0
0.0

1235.417425727192

2645.0060894610774

4225.310703693266

5977.9223640050095

7915.6879239383325

Base
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0

Terminal Year

EBIT (1-t)
2381.9520000000007
2977.44
3721.8
4652.25
5815.3125
7269.140625
8516.52515625
9653.628101249999
10574.860040055002
11181.15201568482
11396.174169832608

12079.944620022565

– WACC (CI)

2675.0

2716.979109375

2769.4529960937502

2835.0453544921875

2917.0358024902343

2964.443232060147

3038.767822353046

3133.697769006417

3243.6086661588956

3364.2378030482064

3524.0724796560758

EVA

302.44000000000005

1004.8208906250002

1882.7970039062498

2980.2671455078125

4352.104822509766

5552.081924189853

6614.860278896953

7441.162271048585

7937.543349525924

8031.936366784401

8555.87214036649

Terminal EVA

138837.68179093694

PV

266.76074972436606

781.725097824224

1291.9675279837875

1803.7900829235225

2323.3404124893027

2619.9177825427746

2765.066239843168

2761.293947142231

2620.463192763336

43229.10446947356

PV of EVA

60463.42950271027

+

Capital Invested

20000.0

+ PV of Chg Capital in Yr 10

-95.93285762427642

This reconciles the assumptions on stable growth,

ROC

and Capital Invested

= Firm Value

80367.496645086
WACC
0.13375
0.13375
0.13375
0.13375
0.13375
0.13131020000000002
0.12887780000000001
0.1264528
0.12403520000000001
0.121625
0.121625
ROC

0.148872

0.18321478743887334

0.22467918335413278

0.27435118300402606

0.3332998373772976

0.37724001913001687

0.4094219843172778

0.42672291977188725

0.427565890104183

0.4119981299033129

0.41691062624047676

Capital Invested
20000.0

20313.8625

20706.190625

21196.60078125

21809.6134765625

22575.879345703124

23578.675476715507

24781.56093820316

26150.710976875074

27660.7424711055

28974.90219655561

(Adjusted to reflect terminal ROC)

Calculation of Capital Invested

Initial

20000.0
20000.0
20313.8625
20706.190625
21196.60078125
21809.6134765625
22575.879345703124
23578.675476715507
24781.56093820316
26150.710976875074
27660.7424711055

+ Net Cap Ex

81.25

101.5625

126.953125

158.69140625

198.3642578125

400.82042300456874

604.067286438825

819.6689748730814

1060.2327598878062

1337.713899308297

+ Chg in WC

232.61249999999998
290.765625
363.45703125
454.3212890625
567.901611328125
601.9757080078125
598.818175048828
549.4810637988285
449.79873434261737
302.3749042703224

Ending

20000.0
20313.8625
20706.190625
21196.60078125
21809.6134765625
22575.879345703124
23578.675476715507
24781.56093820316
26150.710976875074
27660.7424711055

29300.83127468412

Cumulated WACC

1.13375
1.2853890625000002
1.4573098496093753
1.6522250419946292
1.8732101413614108
2.119181739665606
2.392297220073882
2.6948099019844407
3.029061187139061
3.39747075402485