panera bread company
Transcript of panera bread company
Finanical MOdeling Project
Panera Bread Company
Li, Xin & Chen, XinBentley University
FI402A-001November 24, 2014
IntroductionIn this assignment, our primarily goal was to build a financial model for Panera Bread Company
(NASDAQ: PNRA), which could fairly value the company for year 2013. Panera Bread was founded in 1998 originally and it has grown into a company that operates in the retail bakery-café segment, which is the restaurant industry that belongs to the services sector. For the financial model, we mainly used the Benninga’s worksheets. We analyzed Panera Bread’s financial statements (Balance Sheets, Income Statements, and Cash Flow Statement) for the period of 2009-2013. Then, we created a pro forma for the next five years (2014-2018) to calculate the free cash flows. From there, we determined the WACC of the company for year 2013 and made the final valuation based on the WACC. According to our calculation, Panera Bread had a WACC of 7.65% in 2013. Therefore the stock price should be $184.23 per share.
Incorporation of Multi-years financialsBefore we could analysis the company, we had to incorporate the last five years data into
Benninga’s model from its most recent 10-K report. In order to better analysis the company, we added line items in Benninga’s model since Panera Bread’s financial statements reported differently. Here is a list how we broke down certain lines in both its financial statements specifically:
Balance sheets:
Receivables = trade accounts receivable, net + other accounts receivableOther current assets=prepaid expense and other + deferred income taxesOther assets = goodwill + deposits and otherOther liabilities = deferred rent + other long-term liabilities
Income statements:
Sales = bakery-café sales, net + franchise royalties and fees + fresh dough and other product sales to franchise
Cost of goods sold= cost of food and paper products + labor + occupancy +other operating expenses
We also added other line items that were necessary for the model, such as accumulated other comprehensive (loss) income, fresh dough and other product cost of sales to franchisees, and pre-opening expense. From there, we incorporated all the data into justified Benninga’s model according to its 2013 10-K report. Once we finished entering all the data, we checked the EBITDA, EBIT and EBT to find out any errors we made. In Benninga’s model, they were operating income before depreciation for EBITDA and pretax income for EBT. There wasn’t a line for EBIT. We knew we accomplished the incorporation process once the EBITDA and EBT matched with its 10-k’s numbers.
Analysis of historical financials After finished the company’ financial statement formation, we began to figure out the parameters
that were needed for the 5 year Pro froma. According to Benninga’s model, the future anticipated ratios were determined by their historical ratios. Hence for our first step was to find historical ratios. Most of required model parameters were calculated as a percentage of sales from the same fiscal year. The others, which were not calculated as a percentage of sales, were measured by their related line items. For example, tax expense were measured by the tax/EBT ratio. From there, we took the average value of
1
these five years’ ratios. For example: accounts payable/sales parameter = the sum of accounts payable/sales in five year and divided by 5. Sales growth rates were calculated by each segment and the total revenue was the sum of all segments together. In addition, there were some parameters we didn’t use because there were no information for the related line items. For example, the company does not payout dividends according to its dividend payout police indicated on the 10-K report. We also created few new parameters in order to complete the forecast model. For accumulated other comprehensive (loss) income, we took a ratio over the total asset since it was an item related to foreign transaction cost of the total asset and total liabilities. There was one parameter that we needed to change completely, which was the interest expense/debt. According to the company 10-K reports, Panera Bread did not have any long-term debt between 2009 and 2013. However, it did have interest expense. Therefore, we decided to change the parameter to interest expense/other liabilities and took the average of those ratios in the past of five years. Another important information we found out was the company just issued a $100 million dollar debt on June 11th 2014, which would be mature in 2019 according to its 2014 second quarter 10-Q report (Exhibit 5). In its 10-Q report, it indicates that the interest rate will between 1.00% and 1.50% plus the US 3 month LIBOR rate. Therefore, we decided to 1.25% plus the 3 month LIBOR rate, which was 0.23% according to Yahoo Finance. From there, we made an interest payment table in Excel and added them to interest expense.
The last step for this portion is to choose what is plug. In our case, we decided to use treasure stock as our plug, which was also what Benninga used in his model. We have the following reasons to make this decision: first of all, there was no reasonable calculation for treasury stock because the company’s managers have the authority to control how many stocks to issue in the future. Secondly, there was no current debt for the past year. Lastly, the minority interest were 0 for 2011, 2012 and 2013. Therefore, common stock and treasury stocks were our ideal choice. However, common stocks’ value is equal to par value plus paid in capital. There are two variables. Thus, we decided to choose treasury stock as our plug, where the treasury stock equals all current liabilities plus debt plus deferred taxes plus minority interest plus other liabilities plus common stock plus capital surplus plus retained earnings and finally minus total assets.
Conference CallsAlthough there are many exciting news for Panera Bread, the most recent conference call delivers
more negative signals than it expected to be. Panera Bread’s most recent conference call is on October 29, 2014, which is the third quarter of this year. The increasing investment, labor expense and depreciation expense apparently get its audiences’ attention. They concern the potential risk will impact the performance of stock price during the rest of quarter. Furthermore, the CFO was not even confident with the number (3%-3.5% growth rate in the fourth quarter) will be correct. On the other hand, that so-called good news is not such solid statement. The new opened stores-Panera 2.0-are likely won’t be profit in the rest of year; instead, they are more likely to increase cost and risk for the growth. Other good news, for instance, low tax rate is also not certain. We think the executives are knowledgeable regarding the daily operation and company concerns.
Determination of WACCIn order to determine the WACC of Panera Bread in 2013, we first computed the beta of the
company by using the historical data from the past five years. We acquired the past five year’s monthly historical prices from yahoo finance. From there, we computed the slope of the regression line, which is
2
the monthly returns of Panera Bread against the monthly returns of S&P 500 Index for the same period of time. We got the beta of 0.71. (Exhibit 3)
In Benninga’s model, he calculated the WACC by using four different approaches, which are Gordon per-share dividends model, Gordon equity payout and interest model, CAPM and tax-adjusted CAPM. However, we didn’t use the Gordon per-share dividends model because Panera Bread does not pay dividends. We didn’t use the Gordon equity payout and interest model either because the company treasury stock increased by almost $340 billion dollar amount and again it does not pay dividends. Therefore, the Gordon equity payout and interest model is not comparable for the calculation of Panera Bread’s beta. As a result, we use the CAPM and the tax-adjusted CAPM model. The CAPM is equation is E(r) =Rf+ (Rm-Rf). We choice the risk-free rate is 3.03% according to Bloomberg data (Exhibit 1) and the expected market return is 9.78%. Therefore, we got the cost of equity is 7.79%. For the tax-adjusted CAPM, the formula is E(r) =Rf (1-T) + (Rm-Rf (1-T)), where T is the tax rate, in our case which is 37.27%. We got 7.46% for cost of equity under this model.
For cost of debt. we took the interest expense from year 2013, which is $1,053 million, divided by the average other liabilities from year 2012 and 2013 since the company does not have any long-term debt in those two years. As a result, we got 0.96% for cost of debt.
From there, we got all the data we needed to calculate the WACC. The formula we used to determine our WACC was WACC=E/ (E+D)*Ce + D/ (E+D)*Cd*(1-T). We got 7.72% for WACC under CAPM model and 7.39% for WACC under tax-adjusted CAPM model. Therefore, the final WACC we used for our valuation process was 7.56%, which was an average of these two. From here, we used Bloomberg in the trading room to find the WACC of Panera Bread in year 2013 and we got 9.1%. (Exhibit 2)
Panera Bread Company’s WACC 2013
Calculated WACC 7.56%
Bloomberg WACC 9.10%
Final ValuationAfter we got the WACC, we did the final valuation process, which was discounted the following
5 years free cash flows by the WACC. For year 2018, we also calculated the terminal value by taking the assumption that the long term growth rate was 5%. We got $184.23 for the stock price under our model and the real stock price was $176.96 on December 31 st 2013 according to Yahoo Finance. Therefore, our rating is hold.
ConclusionIn this project, we first incorporated the data of Panera Bread Company from 2009 to 2013 into
Benninga’s model and made necessary adjustments. Then we analyzed the historical financial data to build financial models and also tried to make our model as accurate as possible by acquiring all information we though was important such as the most recent company’s conference calls. Next, we built the 5 year Pro froma for 2014-2018. After that we determined the WACC of the company, which allowed us to finish the final valuation process in the end.
3
Reference
4
Panera Bread Company. (n.d.). Retrieved from http://finance.yahoo.com/q?s=pnra&type=2button&fr=uh3_finance_web_gs_ctrl2&uhb=uhb2
Panera Bread Company (2013). From 10-K 2013. Retrieved from Panera Bread Company Website https://www.panerabread.com/en-us/company/financial-reports.html
Panera Bread Company (2013). From 10-Q 2014 Second Quarter. Retrieved from Panera Bread Company Website https://www.panerabread.com/en-us/company/financial-reports.html
Bloomberg L.P. (2013) Panera Bread Company 2013’s WACC. Retrieved Nov. 23, 2014 from Bloomberg database.
Bloomberg L.P. (2013) US Generic Govt 10Year Yield. Retrieved Nov. 23, 2014 from Bloomberg database.
5
Attachment
Exhibit 1
Exhibit 2
6
Exhibit 3
-16% -11% -6% -1% 4% 9%
-22%
-17%
-12%
-7%
-2%
3%
8%
13%
18%
23%
f(x) = 0.705969475289188 x + 0.0128125861217797
Computing the Beta for PPGusing monthly price data for PPG and the SP500
SP500 return
PPG
retu
rn
Exhibit 4
SUMMARY OUTPUT
Regression StatisticsMultiple R 0.416108R Square 0.173146Adjusted R Square 0.15864Standard Error 0.068734Observations 59
ANOVA
df SS MS FSignifica
nce F
Regression 10.0563
90.0563
911.935
97 0.001046
Residual 570.2692
890.0047
24
Total 580.3256
79
Coefficients
Standard
Error t StatP-
valueLower 95%
Upper 95%
Lower 95.0%
Upper 95.0%
Intercept 0.012813 0.0093 1.3669 0.1770 -0.00596 0.0315 - 0.0315
7
73 21 17 820.0059
6 82
X Variable 1 0.7059690.2043
423.4548
470.0010
46 0.2967821.1151
570.2967
821.1151
57
Exhibit 5
8
Financials
Panera Bread Company BALANCE SHEETS, 2009-2013 (Thousands $)ASSETS 2013 2012 2011 2010 2009Cash and Short-Term Investments 125,245 297,141 222,640 229,299 246,400Receivables 84,602 86,262 54,709 38,340 28,493Trade accounts receivable, net 32,965 43,843 30,700 20,378 17,317Other accounts receivable 51,637 42,419 24,009 17,962 11,176Inventories - Total 21,916 19,714 17,016 14,345 12,295Other Current Assets 70,953 75,725 58,754 48,701 34,896Prepaid expense and other 43,064 42,223 31,228 23,905 16,211Deferred income taxes 27,889 33,502 27,526 24,796 18,685Total Current Assets 302,716 478,842 353,119 330,685 322,084Property, Plant, and Equipment, Gross 775,932 662,693 571,921 512,857 470,946Depreciation, Depletion, and Amortization (Accumulated) 106,523 90,939 79,899 68,763 67,162Property, Plant, and Equipment, Net 669,409 571,754 492,022 444,094 403,784
Intangibles 79,768 88,073 67,269 48,402 19,195Other Assets 128,969 129,494 114,912 101,400 92,102Goodwill 123,013 121,903 108,071 94,442 87,481Deposits and other 5,956 7,591 6,841 6,958 4,621TOTAL ASSETS 1,180,862 1,268,163 1,027,322 924,581 837,165
LIABILITIESAccounts Payable 17,533 9,371 15,884 7,346 6,417Accrued Expense 285,792 268,169 222,450 204,170 135,842Other Current Liabilities 0 0 0 0 0
Debt 0 0 0 0 0Deferred Taxes 65,398 60,655 34,345 30,264 28,813Minority Interest 0 0 0 0 0Other Liabilities 112,247 108,109 99,567 87,193 69,057Deferred rent 65,974 59,882 54,055 47,974 43,371Other long-term liabilities 46,273 48,227 45,512 39,219 25,686
EQUITYCommon Stock 3 3 3 3 3Capital Surplus 196,908 174,690 150,093 130,005 168,288
9
Accumulated other comprehensive (loss) income -333 672 308 275 224Retained Earnings 1,049,884 853,715 680,267 544,315 432,449Less: Treasury Stock - Total Dollar Amount 546,570 207,161 175,595 78,990 3,928Total stockholders’ equity 699,892 821,919 655,076 595,608 597,036TOTAL LIABILITIES AND EQUITY 1,180,862 1,268,223 1,027,322 924,581 837,165
COMMON SHARES OUTSTANDING (million) 28,629 29,217 29,601 30,614 30,667
Balance sheet--historical ratiosASSETS 2013 2012 2011 2010 2009Cash and Short-Term InvestmentsReceivables/Sales 3.55% 4.05% 3.00% 2.49% 2.11%Inventories/Sales 0.92% 0.93% 0.93% 0.93% 0.91%Other Current Assets/Sales 2.97% 3.56% 3.22% 3.16% 2.58%Operating CA/Sales 12.69% 22.48% 19.38% 21.44% 23.80%
Property, Plant, Equipment, Gross/Sales 32.53% 31.11% 31.39% 33.25% 34.79%Annual depreciation/PPE 13.73% 13.72% 13.97% 13.39% 14.26%Property, Plant, Equipment, Net/Sales 28.07% 26.84% 27.00% 28.79% 29.83%
Other Assets/Sales 5.41% 6.08% 6.31% 6.57% 6.80%Other Assets, growth rate since 2009 6.23% <--=AVERAGE(B59:F59)
LIABILITIESAccounts Payable/Sales 0.74% 0.44% 0.87% 0.48% 0.47%Accrued Expense/Sales 11.98% 12.59% 12.21% 13.24% 10.04%Other Current Liabilities/Sales 0.00% 0.00% 0.00% 0.00% 0.00%Operating CL/Sales 12.72% 13.03% 13.08% 13.71% 10.51%
Debt Debt/equity 0.00% 0.00% 0.00% 0.00% 0.00% Debt/assets 0.00% 0.00% 0.00% 0.00% 0.00%
Accumulated other comprehensive (loss) income -0.03% 0.05% 0.03% 0.03% 0.03%Other Liabilities/Sales 4.71% 5.08% 5.46% 5.65% 5.10%
10
Other Liabilities, growth rate since 2009 12.91% <--=(B31/F31)^(1/4)-1
Balance sheet--model parametersASSETSCash and Short-Term Investments constantReceivables/Sales 3.04% <--=AVERAGE(B50:F50)Inventories/Sales 0.92% <--=AVERAGE(B51:F51)Other Current Assets/Sales 3.10% <--=AVERAGE(B52:F52)Operating CA/Sales 19.96% <--=AVERAGE(B53:F53)
Property, Plant, Equipment, Gross =accrued depreciation + net plant, property, equipmentAccrued depreciation =previous year's accrued + this year's depreciation from incomeProperty, Plant, Equipment, Net/Sales 28.07% <--=B57
Other Assets, annual growth rate 6.23% <--=B60
LIABILITIESAccounts Payable/Sales 0.60%Accrued Expense/Sales 12.01%Other Current Liabilities/Sales 0 <-- constant at current level
Debt/Assets 0% <-- approximately current levelDeferred taxes grows at sales growthAccumulated other comprehensive (loss) income 0.02%Minority Interest 0 <-- constant at current levelOther Liabilities, annual growth rate 12.91% <--=B74
EQUITYCommon Stock 3Capital Surplus 196,908
Retained Earnings=previous year's retained + this year's retentions from income statement
Treasury Stock - Total Dollar Amount PLUG
Panera Bread Company INCOME STATEMENTS, 2009-2013 (Thousands $)
2013 2012 2011 2010 2009Sales 2,385,00 2,130,05 1,822,03 1,542,48 1,353,49
11
2 7 2 9 4
Bakery-café sales, net2,108,90
81,879,28
01,592,95
11,321,16
21,153,25
5 Franchise royalties and fees 112,641 102,076 92,793 86,195 78,367 Fresh dough and other product sales to franchisees 163,453 148,701 136,288 135,132 121,872
Cost of Goods Sold1,695,43
41,498,84
81,285,93
91,071,98
5 959,586 Cost of food and paper products 625,622 552,580 470,398 374,816 337,599 Labor 625,457 559,446 484,014 419,140 370,595 Occupancy 148,816 130,793 115,290 100,970 95,996 Other operating expenses 295,539 256,029 216,237 177,059 155,396Fresh dough and other product cost of sales to franchisees 142,160 131,006 116,267 110,986 100,229Selling, General, and Administrative Expense 123,335 117,932 113,083 101,494 83,169Pre-opening expense 7,794 8,462 6,585 4,282 2,451Operating Income Before Depreciation 416,279 373,809 300,158 253,742 208,059Depreciation and Amortization 106,523 90,939 79,899 68,673 67,162Interest Expense 1,053 1,082 822 675 700Nonoperating Income (Expense) and Special Items -4,017 -1,208 -466 4,232 273Pretax Income 312,720 282,996 219,903 180,162 139,924Income Taxes - Total 116,551 109,548 83,951 68,563 53,073Minority Interest 0 0 0 -267 801Income Before Extraordinary Items 196,169 173,448 135,952 111,866 86,050Extraordinary Items and Discontinued Operations 0 0 0 0 0Net Income (Loss) 196,169 173,448 135,952 111,866 86,050Cash Dividends 0 0 0 0 0Retained 196,169 173,448 135,952 111,866 86,050
Dividends per share 0.00 0.00 0.00 0.00 0.00
Earnings Per Share (Primary) - Excluding Extraordinary Items 6.85 5.94 4.59 3.65 2.81Earnings Per Share (Primary) - Including Extraordinary Items 6.85 5.94 4.59 3.65 2.81Common Shares Used to Calculate Primary EPS 28629 29217 29601 30614 30667Earnings Per Share (Fully Diluted) - Excluding Extraordinary Items 6.81 5.89 4.55 3.62 2.78Earnings Per Share (Fully Diluted) - Including Extraordinary Items 6.81 5.89 4.55 3.62 2.78
12
Profit and loss historical ratios 2013 2012 2011 2010 2009COGS/Sales 71.09% 70.37% 70.58% 69.50% 70.90% Cost of food and paper products/Sales 26.23% 25.94% 25.82% 24.30% 24.94% Labor 26.22% 26.26% 26.56% 27.17% 27.38% Occupancy 6.24% 6.14% 6.33% 6.55% 7.09% Other operating expenses 12.39% 12.02% 11.87% 11.48% 11.48%Fresh dough and other product cost of sales to franchisees 5.96% 6.15% 6.38% 7.20% 7.41%SG&A/Sales 5.17% 5.54% 6.21% 6.58% 6.14%Pre-opening expense 0.33% 0.40% 0.36% 0.28% 0.18%Total costs/Sales (sum of above items) 82.55% 82.45% 83.53% 83.55% 84.63%
Depreciation/Gross fixed assets 13.73% 13.72% 13.97% 13.39% 14.26%Interest expense/Average other long-term liabilties 0.96% 1.04% 0.88% 0.86%Incomes Taxes/Pretax Income 37.27% 38.71% 38.18% 38.06% 37.93%Minority Interest/(Pretax - Taxes) 0.00% 0.00% 0.00% -0.24% 0.92%Dividends/Net Income 0.00% 0.00% 0.00% 0.00% 0.00%
Profit and loss--model parameters
COGS/Sales 66.47%=AVERAGE(B38:K38)
Cost of food and paper products/Sales 25.45% Labor 26.72% Occupancy 6.47% Other operating expenses 11.85%Fresh dough and other product cost of sales to franchisees 6.62%
SG&A/Sales 5.93%=AVERAGE(B44:F44)
Pre-opening expense 0.31%Depreciation/Gross fixed assets 13.73% =B48
Interest rate 0.94%=AVERAGE(B49:E49)
Tax rate 37.27% =B50Minority Interest/(Pretax - Taxes) 0.00% =B51Dividend growth 0.00% <-- See explanation in section 4.9
PNRA, FINANCIAL MODEL, 2014-2018Profit and loss assumptionsSales growth 15.21%Bakery-café sales, net growth 16.29%Franchise royalties and fees 9.49%
13
growthFresh dough and other product sales to franchisees growth 7.61%COGS/Sales #REF!Cost of food and paper products/Sales 25.45%Labor 26.72%Occupancy 6.47%Other operating expenses 11.85%Fresh dough and other product cost of sales to franchisees 6.62%SG&A/Sales 5.93%Pre-opening expense 0.31%Depreciation/Gross fixed assets 13.73%Interest rate 0.94%Minority interest as % of PAT 0.00%Growth rate of total dividends 0.00%Tax rate 37.27%
Balance sheet assumptionsAssetsCash and short-term investments constantReceivables/Sales 3.04%Inventories/Sales 0.92%Other Current Assets/Sales 3.10%Property, Plant, Equipment, Net/Sales 28.07%Investments and advances constantOther Assets/Sales 6.23%
LiabilitiesAccounts Payable/Sales 0.60%Accrued Expense/Sales 12.01%Other Current Liabilities/Sales constant
Debt/Assets 0.00%
Deferred Taxesgrowth at sales growth
Accumulated other comprehensive (loss) income 0.02% over total assetMinority Interest assumed constantOther liabilities, annual growth 12.91%Treasury stock plug
PROFIT AND LOSS 2013 2014 2015 2016 2017 2018
Sales2,385,00
22,751,6
323,176,1
703,667,8
984,237,5
954,897,7
83 Bakery-café sales, net growth 2,108,90 2,452,3 2,851,8 3,316,3 3,856,4 4,484,5
14
8 97 31 23 70 93 Franchise royalties and fees growth 112,641
123,335
135,045
147,866
161,905
177,276
Fresh dough and other product sales to franchisees growth 163,453
175,900
189,294
203,709
219,221
235,914
Cost of Goods Sold1,695,43
41,939,4
882,238,7
242,585,3
192,986,8
703,452,2
04
Cost of food and paper products/Sales 625,622
700,196
808,227
933,355
1,078,324
1,246,319
Labor 625,457735,27
5848,71
7980,11
41,132,3
451,308,7
57
Occupancy 148,816178,00
8205,47
2237,28
3274,13
8316,84
7
Other operating expenses 295,539326,00
9376,30
7434,56
6502,06
3580,28
1
Fresh dough and other product sales to franchisees growth 142,160
182,117
210,215
242,760
280,466
324,160
Selling, General, and Administrative Expense 123,335
163,111
188,276
217,425
251,195
290,330
Pre-opening expense 7,794 8,498 9,809 11,328 13,087 15,126
Operating Income Before Depreciation 416,279
458,418
529,145
611,066
705,977
815,963
Depreciation and Amortization 106,523121,95
8156,30
1198,29
4249,47
9311,69
4Interest Expense 1,053 1,118 1,262 1,425 1,609 1,817
Nonoperating Income (Expense) and Special Items -4,017 -4,017 -4,017 -4,017 -4,017 -4,017
Pretax Income 312,720339,35
9375,59
9415,36
4458,90
6506,46
9
Income Taxes - Total 116,551126,47
9139,98
6154,80
7171,03
4188,76
1Minority Interest 0 0 0 0 0 0
Net Income 196,169212,88
0235,61
3260,55
8287,87
1317,70
8Dividend 0 0 0 0 0 0
Accrued 196,169212,88
0235,61
3260,55
8287,87
1317,70
8
ASSETS 2013 2014 2015 2016 2017 2018Cash and Short-Term Investments 125,245
125,245
125,245
125,245
125,245
125,245
Receivables 84,602 83,597 96,494111,43
3128,74
1148,79
8Inventories - Total 21,916 25,407 29,327 33,867 39,127 45,223
Other Current Assets 70,953 85,247 98,399113,63
3131,28
2151,73
5
15
Total Current Assets 302,716319,49
5349,46
5384,17
9424,39
6471,00
2
Property, Plant, and Equipment, Gross 775,932
1,000,793
1,276,251
1,612,561
2,021,939
2,518,931
Depreciation, Depletion, and Amortization (Accumulated) 106,523
228,481
384,782
583,076
832,555
1,144,249
Property, Plant, and Equipment, Net 669,409
772,313
891,470
1,029,485
1,189,385
1,374,682
Investments and Advances 0 0 0 0 0 0Intangibles 79,768 79,768 79,768 79,768 79,768 79,768
Other Assets 128,969137,01
0145,55
1154,62
6164,26
6174,50
7
TOTAL ASSETS1,180,86
21,308,5
851,466,2
541,648,0
571,857,8
142,099,9
59
LIABILITIES 2013 2014 2015 2016 2017 2018Accounts Payable 17,533 16,494 19,039 21,987 25,402 29,359
Accrued Expense 285,792330,49
5381,48
6440,54
6508,97
2588,26
6Other Current Liabilities 0 0 0 0 0 0
Debt 0100,00
0 90,329 70,772 50,923 30,779
Deferred Taxes 65,398 75,348 86,812100,02
0115,23
8132,77
2Minority Interest 0 0 0 0 0 0
Other Liabilities 112,247126,74
1143,10
6161,58
5182,44
9206,00
8
EQUITYCommon Stock 3 3 3 3 3 3
Capital Surplus 196,908196,90
8196,90
8196,90
8196,90
8196,90
8
Accumulated other comprehensive (loss) income -333 291 326 367 413 467
Retained Earnings1,049,88
41,262,7
641,498,3
771,758,9
342,046,8
052,364,5
13
Less: Treasury Stock 546,570800,45
9950,13
21,103,0
651,269,3
001,449,1
16
Total stockholders equity 699,892659,50
7745,48
2853,14
7974,83
01,112,7
75TOTAL LIABILITIES AND EQUITY
1,180,862
1,308,585
1,466,254
1,648,057
1,857,814
2,099,959
16
FREE CASH FLOW (FCF) 2014 2015 2016 2017 2018
Profit after tax212,88
0235,61
3260,55
8287,87
1317,70
8
Add back depreciation121,95
8156,30
1198,29
4249,47
9311,69
4Change in net working capital Increase in operating current assets -16,779 -29,970 -34,713 -40,218 -46,606 Add increase in operating current liabilities 43,664 53,536 62,008 71,841 83,252
Subtract capital expenditures
-224,86
1
-275,45
8
-336,30
9
-409,37
9
-496,99
2Subtract increase in other assets -8,041 -8,542 -9,074 -9,640 -10,241Add back after-tax interest 701 792 894 1,009 1,140
FCF129,52
2132,27
1141,65
7150,96
4159,95
52% 7% 7% 6%
5.42%Valuing PNRAWACC 7.56%Long-term free cash flow growth rate 5%
Year 2014 2015 2016 2017 2018
FCF129,52
2132,27
1141,65
7150,96
4159,95
5
Terminal6,569,0
69
Total129,52
2132,27
1141,65
7150,96
46,729,0
24
Discounted value
5,326,758
#NAME?
Add back initial cash 125,245
Firm value5,452,00
3
Subtract total debt value, Dec.13 177,645#NAM
E?
Implied equity value5,274,35
8
Number of shares outstanding, Dec.13
28,629.00
Implied value per share 184.23
Market price per share, Dec. 13 176.96
17
PPG over or under-valued? ok#NAM
E?
DATA TABLE: SENSITIVITY OF PPG'S STOCK VALUATION
TO WACC AND LONG-TERM GROWTH RATELong-term FCF growth ˜
184.23 1% 3% 4% 5% 6% 7%
6% 106.50 167.25 243.19 471.01 nmf nmf7% 88.54 125.28 162.03 235.51 455.96 nmf
WACC → 8% 75.72 100.11 121.45 157.01 228.14441.53
9% 66.11 83.32 97.10 117.76 152.20221.07
10% 58.64 71.34 80.87 94.21 114.22147.58
11% 52.66 62.36 69.28 78.51 91.44110.83
12% 47.78 55.37 60.59 67.30 76.2588.7
7
13% 43.71 49.78 53.83 58.89 65.3974.0
7
14% 40.27 45.21 48.42 52.35 57.2563.5
6
COMPUTING THE WEIGHTED AVERAGE COST OF CAPITAL (WACC) FOR PNRA
Shares outstanding (thousand) 28,629Share price, end 2013 176.96Equity value, E 5,066,188Net debt, D 52,400
18
=IF(B96>B97,B111,"nmf")
Cost of debt, rD 0.96%PNRA's tax rate, TC 37.27%Market risk free rate, rf 3.03%Expected market return, E(rM) 9.78%
WACC based on classic CAPM and interest from financial statementsPNRA beta 0.7060Cost of equity, rE 7.79%WACC 7.72%
WACC based on tax-adjusted CAPM and interest from financial statementsPPG beta 0.7060Cost of equity, rE 7.46%WACC 7.39%
Estimated WACC? 7.56%
19