Excel and Executive summary

profileyilinzhang
TILANTANG-BlaineKitchenwareCaseSpreadsheet_EBITWACC.xlsx
Home>Business & Finance homework help>Excel and Executive summary

Exhibit 1

BLAINE KITCHENWARE Draft Historical Income Statement
Project: BLAINE KITCHENWARE
Analysis: Historical Income Statement
Draft: Y
Footer: Harvard Business School Publishing
BLAINE KITCHENWARE Historical Income Statement
Case Exhibit 1
Operating Results: 2004 2005 2006
Revenue 291,940 307,964 342,251
Less: Cost of Goods Sold 204,265 220,234 249,794
Gross Profit 87,676 87,731 92,458
Less: Selling, General & Administrative 25,293 27,049 28,512
Operating Income 62,383 60,682 63,946
Plus: Depreciation & Amortization 6,987 8,213 9,914
EBITDA 69,370 68,895 73,860
EBIT 62,383 60,682 63,946
Plus: Other Income (expense) 15,719 16,057 13,506
Earnings Before Tax 78,101 76,738 77,451
Less: Taxes 24,989 24,303 23,821
Net Income 53,112 52,435 53,630
Dividends 18,589 22,871 28,345
Margins:
Revenue Growth 3.2% 5.5% 11.1%
Gross Margin 30.0% 28.5% 27.0%
EBIT Margin 21.4% 19.7% 18.7%
EBITDA Margin 23.8% 22.4% 21.6%
Effective Tax Rate (1) 32.0% 31.7% 30.8%
Net Income Margin 18.2% 17.0% 15.7%
Dividend payout ratio 35.0% 43.6% 52.9%
(1) Blaine's future tax rate is assumed to revert to the statutory rate of 40%.
Tax Rate
Harvard Business School Publishing

Exhibit 2

BLAINE KITCHENWARE Historical Balance Sheet
Project: BLAINE KITCHENWARE
Analysis: Historical Balance Sheet
Draft: Y
Footer: Harvard Business School Publishing
Harvard Business School Publishing
BLAINE KITCHENWARE Draft Historical Balance Sheet
Case Exhibit 2
Assets: 2004 2005 2006
Cash & Cash Equivalents 67,391 70,853 66,557
Marketable Securities 218,403 196,763 164,309
Accounts Receivable 40,709 43,235 48,780
Inventory 47,262 49,728 54,874
Other Current Assets 2,586 3,871 5,157
Total Current Assets 376,351 364,449 339,678
Property, Plant & Equipment 99,402 138,546 174,321
Goodwill 8,134 20,439 38,281
Other Assets 13,331 27,394 39,973
Total Assets 497,217 550,829 592,253
Liabilities & Shareholders' Equity:
Accounts Payable 26,106 28,589 31,936
Accrued Liabilities 22,605 24,921 27,761
Taxes Payable 14,225 17,196 16,884
Total Current Liabilities 62,935 70,705 76,581
Other liabilities 1,794 3,151 4,814
Deferred Taxes 15,111 18,434 22,495
Total Liabilities 79,840 92,290 103,890
Shareholders' Equity 417,377 458,538 488,363
Total Liabilities & Shareholders' Equity 497,217 550,829 592,253
Note: Many items in BKI's historical balance sheets, e.g. Property, Plant & Equipment
have been affected by the firm's acquisitions.
Harvard Business School Publishing

Exhibit 3

Exhibit 3
Home & Hearth Design AutoTech Appliances XQL Corp. Bunkerhill, Inc. EasyLiving Systems Blaine Kitchenware
Revenue $589,747 $18,080,000 $4,313,300 $3,671,100 $188,955 $342,251
EBIT 106,763 2,505,200 721,297 566,099 19,613 63,946
EBITDA 119,190 3,055,200 796,497 610,399 23,356 73,860
Net income $53,698 $1,416,012 $412,307 $335,073 $13,173 $53,630
Cash & securities $21,495 $536,099 $21,425 $153,680 $242,102 $230,866
Net working capital* 54,316 1,247,520 353,691 334,804 21,220 32,231
Net fixed assets 900,803 7,463,564 3,322,837 815,304 68,788 174,321
Total assets $976,613 $9,247,183 $3,697,952 $1,303,788 $332,110 $592,253
Net debt (1) $350,798 $4,437,314 $950,802 $238,056 ($64,800) ($230,866)
Total debt 372,293 4,973,413 972,227 391,736 177,302 - 0
Book equity $475,377 $3,283,000 $2,109,400 $804,400 $94,919 $488,363
Market capitalization 776,427 13,978,375 5,290,145 3,962,780 418,749 959,596
Enterprise value (MVIC) $1,127,226 $18,415,689 $6,240,947 $4,200,836 $353,949 $728,730
Equity beta 1.03 1.24 0.96 0.92 0.67 0.56
LTM Trading Multiples
MVIC/Revenue 1.91x 1.02x 1.45x 1.14x 1.87x 2.13x
MVIC/EBIT 10.56x 7.35x 8.65x 7.42x 18.05x 11.40x
MVIC/EBITDA 9.46x 6.03x 7.84x 6.88x 15.15x 9.87x
Market/Book equity 1.63x 4.26x 2.51x 4.93x 4.41x 1.96x
PE 14.46x 9.87x 12.83x 11.83x 31.79x 17.89x
Net Debt/Equity 45.18% 31.74% 17.97% 6.01% -15.47% -24.06%
Net Debt/Enterprise Value 31.12% 24.10% 15.23% 5.67% -18.31% -31.68%
* Net working capital excludes cash and securities
(1) Net debt is total long term and short term debt less excess cash.

Exhibit 4

Exhibit 4 Contemporaneous Capital Market Data: April 21, 2007
Yields on US Treasury Securities
Maturity Yield
30 days 4.55%
60 days 4.73%
90 days 4.91%
1 year 4.90%
5 years 4.91%
10 years 5.02%
20 years 5.26%
30 years 5.10%
Default (credit) spread Change in Default Spread
Seasoned corporate bond yields
Moody's Aaa 5.88% 0.86%
Aa 6.04% 1.02% 0.16%
A 6.35% 1.33% 0.31%
Baa 6.72% 1.70% 0.37%
Ba 7.88% 2.86% 1.16%
B 8.94% 3.92% 1.06%
Investment banking guidance for Blaine 5-Year 10-Year
S&P Rating Default (credit) spread Debt/ Equity (Book Value) EBIT/I Rate = UST + Default Spread Rate = UST + Default Spread
AA- 1.10% 20-40% >15X
A 1.33% 40-60% 10-15X
BBB- 2.23% 60-80% 5-10X
BB- 2.98% 80-120% 3-4X
B+ 4.23% Over 120% <2X

EBIT-EPS analysis

EBIT-EPS Analysis: Blaine Kitchenware
Year 2006
Current shares
Current EBIT
Other (interest) income
Tax rate
Repurchased shares 0
Repurchase price $ 18.50 At a premium to current price
Repurchase cost= #shr x price
Cash contribution
New debt Decision variable
Debt/equity New debt/(existing equity less repurchase)
Interest rate A function of leverage: UST + DRP; iterative process Exhibit 4
Interest
EBIT/I
Breakeven EBIT (before-tax) $ 74,142 Use Goal Seek on this cell to make the difference between EPS =0 by changing B19
No debt EPS (using b19) = (EBIT + Other interest income)*(1-t)/shares outstanding
With Debt EPS (using B19) =(EBIT- interest expense)*(1-t)/new shares outstanding (assume that interest income = 0 with cash used to fund share repurchase)
Difference - 0 Use Goal Seek on this cell to make the difference between EPS =0 by changing B14
Rating Agency Guidance
S&P Rating Buyback Shares Default spread Borrowing rate
Jonathan Scott: From Exhibit 4 based on maturity you select		 Debt/ Equity EBIT/I Breakeven EBIT Feasible?
AA- 14,000 1.10% 20-40% >15X Yes if current EBIT is larger than the breakeven EBIT
A 16,000 1.33% 40-60% 10-15X
BBB- 18,000 2.23% 60-80% 5-10X
BB- 20,000 2.98% 80-120% 3-5X
B+ 22,000 4.23% Over 120% <3X

WACC Worksheet

Blaine Kitchenware Alternative #1 Alternative #2 Alternative #3
Capital Structure Analysis Shares repurchased 14,000 18,000 20,000 Decision variable
Price $ 18.50 $ 18.50 $ 18.50
Total repurchase
Cash contribution 209,000 209,000 209,000
Net new debt Decision variable
Current Balance Sheet (2006) Borrowing rate
scottjon: Obtain from Exhibit 4 and EBIT-EPS analysis 		See iterative process Ex. 4
Cash+Mkt Securities 230,866
NWC 32,231
scottjon: Receivables + Inventory + Other current assets - current liabilities		 Restated Balance Sheet
NFA 174,321 Cash
Other, net 50,945
scottjon: Goodwill + Other assets - Deferred taxes - Other liabilities		 NWC 32,231
Total 488,363 NFA 174,321
Other, net 50,945
Debt 0 Total
Equity 488,363
Total 488,363 Debt
Equity (BV)
Total
Current income statement (2006)
EBIT 63,946
+ Other income 13,506
Jonathan Scott: Primarily interest earned on cash		 Restated Income Statement
-Interest expense EBIT 63,946 63, 946 63, 946
Earning before tax 77,451 + Other income 0 0 0
Taxes 23,821 -Interest expense
Net income 53,630 Earning before tax
Taxes
Shares outstanding 59052 Net income
EPS $ 0.908
ROE 11.0% Shares outstanding
EBIT/Interest NA EPS
ROE
Share price $ 16.25 EBIT/interest
Market cap 959,595
Debt/equity (MV) Share price $ 16.25 $ 16.25 $ 16.25
Market cap
Beta 0.56 Debt/equity (MV)
RUST 5.02% Debt/equity (BV)
MRP 7.00% Beta (levered) Betalevered = Betaunlevered(1+ (1-t)D/E)
Re 8.9400% RUST 5.02% 5.02% 5.02%
Rd 0.00% MRP 7.00% 7.00% 7.00%
wD 0.0% Re
wE 100.0% Rd
tax rate 31.0% wD
WACC 8.94% wE
Tax rate 31.0% 31.0% 31.0%
WACC