Finance excel question

profilehamedajjan
SU24_f_Excel.xlsx
Home>Business & Finance homework help>Finance excel question

Royal Mail

Date RMG Adjusted Close FTSE 100 Adjusted Close RMG Return FTSE 100 Return Instructions:
12-Jul-15 452.92 6,775.08 -1.08% 1.51% Based on the given data, calculate the Royal Mail's Regression Beta.
5-Jul-15 457.81 6,673.38 4.57% 1.32%
28-Jun-15 437.37 6,585.78 -3.88% -2.52%
21-Jun-15 454.69 6,753.70 4.38% 0.64%
14-Jun-15 435.20 6,710.45 -0.89% -1.10%
7-Jun-15 439.10 6,784.92 2.86% -0.29%
31-May-15 426.72 6,804.60 -6.16% -2.61%
24-May-15 453.82 6,984.43 3.99% -0.67%
17-May-15 436.07 7,031.72 3.66% 1.02%
10-May-15 420.39 6,960.49 1.35% -1.23%
3-May-15 414.76 7,046.82 2.20% 0.87%
26-Apr-15 405.76 6,985.95 6.07% -1.21%
19-Apr-15 381.85 7,070.70 -1.06% 1.08%
12-Apr-15 385.92 6,994.63 -0.98% -1.35%
5-Apr-15 389.73 7,089.77 1.70% 3.68%
29-Mar-15 383.15 6,833.46 0.09% -0.32%
22-Mar-15 382.81 6,855.02 -0.99% -2.41%
15-Mar-15 386.62 7,022.51 3.74% 4.10%
8-Mar-15 372.41 6,740.58 1.17% -2.51%
1-Mar-15 368.08 6,911.80 1.09% -0.50%
22-Feb-15 364.10 6,946.66 -0.83% 0.45%
15-Feb-15 367.13 6,915.20 -5.19% 0.60%
8-Feb-15 386.70 6,873.52 -2.06% 0.29%
1-Feb-15 394.76 6,853.44 4.67% 1.53%
25-Jan-15 376.74 6,749.40 -1.37% -1.23%
18-Jan-15 381.94 6,832.83 2.25% 4.22%
11-Jan-15 373.45 6,550.27 2.16% 0.75%
4-Jan-15 365.48 6,501.14 -0.71% -0.72%
28-Dec-14 368.08 6,547.80 0.45% -0.94%
21-Dec-14 366.44 6,609.93 2.25% 0.98%
14-Dec-14 358.30 6,545.27 4.98% 3.81%
7-Dec-14 340.89 6,300.63 -2.73% -6.78%
30-Nov-14 350.33 6,742.84 -1.64% 0.30%
23-Nov-14 356.12 6,722.62 -3.02% -0.42%
16-Nov-14 367.03 6,750.76 -8.22% 1.44%
9-Nov-14 398.48 6,654.37 0.99% 1.32%
2-Nov-14 394.56 6,567.24 4.80% 0.32%
26-Oct-14 376.06 6,546.47 -3.69% 2.44%
19-Oct-14 390.21 6,388.73 5.87% 1.24%
12-Oct-14 367.97 6,310.29 8.00% -0.47%
5-Oct-14 339.67 6,339.97 0.98% -2.92%
28-Sep-14 336.34 6,527.91 -1.36% -1.84%
21-Sep-14 340.95 6,649.39 -8.23% -2.80%
14-Sep-14 370.18 6,837.92 2.54% 0.45%
7-Sep-14 360.89 6,806.96 -0.52% -0.70%
31-Aug-14 362.77 6,855.10 -4.77% 0.52%
24-Aug-14 380.49 6,819.75 -0.76% 0.65%
17-Aug-14 383.39 6,775.25 3.21% 1.28%
10-Aug-14 371.29 6,689.08 2.11% 1.84%
3-Aug-14 363.53 6,567.36 -0.37% -1.69%
27-Jul-14 364.90 6,679.18 -2.99% -1.67%
20-Jul-14 375.98 6,791.55 -6.71% 0.62%
13-Jul-14 402.06 6,749.45 -1.10% 0.88%
6-Jul-14 406.49 6,690.17 2.79% -2.59%
29-Jun-14 395.32 6,866.05 -4.77% 1.59%
22-Jun-14 414.64 6,757.77 3.31% -0.99%
15-Jun-14 401.12 6,825.20 -2.93% 0.70%
8-Jun-14 413.07 6,777.85 0.26% -1.18%
1-Jun-14 411.99 6,858.21 -3.01% 0.20%
25-May-14 424.59 6,844.51 -2.60% 0.42%
18-May-14 435.79 6,815.75 -8.30% -0.59%
11-May-14 473.52 6,855.81 2.48% 0.60%
4-May-14 461.91 6,814.57 3.38% -0.12%
27-Apr-14 446.57 6,822.42 3.59% 2.02%
20-Apr-14 430.81 6,685.69 2.04% 0.91%
13-Apr-14 422.11 6,625.25 2.79% 0.96%
6-Apr-14 410.50 6,561.70 -10.45% -2.02%
30-Mar-14 455.69 6,695.55 -2.60% 1.20%
23-Mar-14 467.72 6,615.58 -3.06% 0.89%
16-Mar-14 482.23 6,557.17 2.70% 0.45%
9-Mar-14 469.37 6,527.89 -3.64% -2.79%
2-Mar-14 486.79 6,712.67 -2.19% -1.44%
23-Feb-14 497.57 6,809.70 0.00% -0.42%
16-Feb-14 497.57 6,838.06 0.92% 2.58%
9-Feb-14 493.01 6,663.62 0.93% 1.39%
2-Feb-14 488.45 6,571.68 -1.52% 0.94%
26-Jan-14 495.91 6,510.44 4.36% -2.33%
19-Jan-14 474.76 6,663.74 -5.44% -2.45%
12-Jan-14 501.30 6,829.30 3.62% 1.32%
5-Jan-14 483.47 6,739.94 0.52% 0.14%
29-Dec-13 480.98 6,730.67 0.00% -0.30%
22-Dec-13 480.98 6,750.87 -1.88% 2.16%
15-Dec-13 490.11 6,606.58 0.25% 2.55%
8-Dec-13 488.86 6,439.96 -0.84% -1.72%
1-Dec-13 493.01 6,551.99 6.88% -1.49%
24-Nov-13 460.25 6,650.57 2.93% -0.36%
17-Nov-13 446.98 6,674.30 -3.82% -0.29%
10-Nov-13 464.40 6,693.44 -1.59% -0.22%
3-Nov-13 471.86 6,708.42 -0.87% -0.39%
27-Oct-13 476.01 6,734.74 3.37% 0.20%
20-Oct-13 460.25 6,721.34 9.94% 1.48%
13-Oct-13 416.72 6,622.58 9.93% 2.07%
6-Oct-13 377.32 6,487.19

Whole Foods

WACC 9.0% Instructions:
g 3.0% Perform a one-way sensitivy analysis of Sales Growth on intrinsic equity value per share in highlighted cells C50 to C60.
WHOLE FOODS MARKET
Deutsche Bank Model (millions of USD, except per share figures)
Actual Actual Actual Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast
At Fiscal Year End 2011 2012 2013 Assumptions 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023
Store Growth 4.0% 7.7% 8.1% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0% 5.0%
Sales Growth 12.2% 15.7% 10.4% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0%
EBITDA Margin 8.5% 9.0% 9.5% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0% 7.0%
Tax Rate 38.1% 38.4% 38.8% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 21.0% 21.0% 21.0% 21.0% 21.0%
Current Asset Turnover 7.0 5.6 6.5 6.2 6.2 6.2 6.2 6.2 6.2 6.2 6.2 6.2 6.2 6.2
Current Liabilities Turnover 10.5 10.9 10.7 10.5 10.5 10.5 10.5 10.5 10.5 10.5 10.5 10.5 10.5 10.5
Net PP&E / Store 6.4 6.5 6.7 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6 6.6
Annual Dep. & Amort. / Store 1.00 0.93 0.94 0.95 0.95 0.95 0.95 0.95 0.95 0.95 0.95 0.95 0.95 0.95
Stores 311 335 362 380 399 419 440 462 485 509 535 562 590
Sales 10,108 11,699 12,917 13,950 15,066 16,272 17,573 18,979 20,498 22,137 23,908 25,821 27,887
EBITDA 859 1,055 1,222 977 1,055 1,139 1,230 1,329 1,435 1,550 1,674 1,807 1,952
Dep. & Amort. 311 311 339 363 381 400 420 441 463 486 511 536 563
EBIT 548 744 883 614 674 739 810 887 972 1,063 1,163 1,271 1,389
Taxes 209 286 343 184 202 222 243 266 204 223 244 267 292
NOPAT 339 458 540 429 471 517 567 621 768 840 919 1,004 1,097
Shares Outstanding 350 364 372 572 572 572 572 572 572 572 572 572 572
Earnings per Share 0.97 1.26 1.45 0.75 0.82 0.90 0.99 1.09 1.34 1.47 1.61 1.76 1.92
Current Assets 1,453 2,103 1,980 2,250 2,430 2,624 2,834 3,061 3,306 3,571 3,856 4,165 4,498
Current Liabilities 880 977 1,088 1,236 1,334 1,441 1,557 1,681 1,816 1,961 2,118 2,287 2,470
Net Working Capital 573 1,126 892 1,014 1,096 1,183 1,278 1,380 1,491 1,610 1,739 1,878 2,028
Net PP&E 1,997 2,193 2,428 2,493 2,617 2,748 2,886 3,030 3,181 3,341 3,508 3,683 3,867
EBIT 614 674 739 810 887 972 1,063 1,163 1,271 1,389
Dep&Amor 363 381 400 420 441 463 486 511 536 563
Tax 184 202 222 243 266 204 223 244 267 292
OCF 792 853 917 987 1,062 1,231 1,326 1,429 1,541 1,660
CAPEX (NCS) 428 506 531 558 585 615 645 678 712 747
Change in NWC 122 81 88 95 102 110 119 129 139 150
FCFF 242 266 299 335 375 506 562 623 690 763
Terminal Value 13,097
FCFF+TV 242 266 299 335 375 506 562 623 690 13,860
Enterprise Value $8,250.74
Intrinsic Equity Value per Share $14.42
Sales Growth Intrinsic Equity Value per Share
5.0%
6.0%
7.0%
8.0%
9.0%
10.0%
11.0%
12.0%
13.0%
14.0%

Victoria Chemical

DCF Analysis of Merseyside Project
(financial values in millions of British pounds)
Tank Car Investment £ 3.00
Assumptions Cannibalization Rate 30% Instructions: Calculate the Payback Period in highlighted cells in green C54 using the Lookup Function. Perform a two-way senstitivity analysis of changing canniblation rate and discount rate on Payback Period in highlighted cells (B60 to M67).
Annual Output (metric tons) 200,000 Discount rate 10.0%
Output Gain/Original Output 7.0% Tax Rate 20%
Price/ton (pounds sterling) 675 Investment Outlay (mill.) 12.0
Inflation Rate (prices and costs) 3.0% Depreciable Life (years) 15
New Gross Margin (ex. Deprec.) 12.25% Salvage Value 0
Old Gross Margin 11.5% WIP Inventory/Cost of Goods 3.0%
Energy Savings/Sales Yr. 1-5 1.00% Months Downtime, Construction 2
Yr. 6-10 0.50% Preliminary Engineering Costs 0.5
Yr. 11-15 0.0% Overhead/Investment 3.5%
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Year Now 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022
1. Estimate of Incremental Gross Profit
New Output (tons) 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000 214,000
Lost Output--Construction (35,667)
New Sales (Millions) 120.38 148.78 153.25 157.84 162.58 167.46 172.48 177.66 182.98 188.47 194.13 199.95 205.95 212.13 218.49
New Gross Margin 13.3% 13.3% 13.3% 13.3% 13.3% 12.8% 12.8% 12.8% 12.8% 12.8% 12.3% 12.3% 12.3% 12.3% 12.3%
New Gross Profit 15.95 19.71 20.31 20.91 21.54 21.35 21.99 22.65 23.33 24.03 23.78 24.49 25.23 25.99 26.77
Old Output 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000 200,000
Old Sales 135.00 139.05 143.22 147.52 151.94 156.50 161.20 166.03 171.01 176.14 181.43 186.87 192.48 198.25 204.20
Old Gross Profit 15.53 15.99 16.47 16.96 17.47 18.00 18.54 19.09 19.67 20.26 20.86 21.49 22.13 22.80 23.48
Lost Rotterdam Output 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00 4200.00
Lost Rotterdam Sales 2.84 2.92 3.01 3.10 3.19 3.29 3.39 3.49 3.59 3.70 3.81 3.92 4.04 4.16 4.29
Lost Rotterdam Gross Profit 0.33 0.34 0.35 0.36 0.37 0.38 0.39 0.40 0.41 0.43 0.44 0.45 0.46 0.48 0.49
Incremental Gross Profit 0.10 3.39 3.49 3.59 3.70 2.98 3.06 3.16 3.25 3.35 2.48 2.55 2.63 2.71 2.79
Incremental Depreciation 1.60 1.39 1.20 1.64 1.38 0.57 0.51 0.45 0.40 0.35 0.50 0.67 0.70 0.32 0.32
Incremental EBIT -1.50 2.00 2.29 1.95 2.32 2.41 2.56 2.71 2.85 3.00 1.98 1.88 1.93 2.39 2.47
Incremental Tax -0.30 0.40 0.46 0.39 0.46 0.48 0.51 0.54 0.57 0.60 0.40 0.38 0.39 0.48 0.49
Incremental Project OCF 0.40 2.99 3.03 3.20 3.24 2.49 2.55 2.61 2.68 2.75 2.08 2.18 2.24 2.23 2.30
2. Estimate of Incremental WIP inventory
New WIP inventory 3.13 3.87 3.99 4.11 4.23 4.38 4.51 4.65 4.79 4.93 5.11 5.26 5.42 5.58 5.75
Old WIP inventory 3.58 3.69 3.80 3.92 4.03 4.16 4.28 4.41 4.54 4.68 4.82 4.96 5.11 5.26 5.42
Lost Rotterdam WIP inventory 0.08 0.08 0.08 0.08 0.08 0.09 0.09 0.09 0.10 0.10 0.10 0.10 0.11 0.11 0.11
Incremental WIP inventory -0.53 0.10 0.11 0.11 0.11 0.14 0.15 0.15 0.15 0.16 0.19 0.20 0.20 0.21 0.22
Change in NWC 0.53 -0.63 -0.00 -0.00 -0.00 -0.03 -0.00 -0.00 -0.00 -0.00 -0.03 -0.01 -0.01 -0.01 0.21
3. Estimate of Incremental Depreciation
New Depreciation 1.60 1.39 1.20 1.04 0.90 0.78 0.68 0.59 0.51 0.44 0.57 0.57 0.57 0.57 0.57
New Tank Car Depreciation 0.60 0.48 0.38 0.31 0.25 0.20 0.16 0.13 0.25 0.25
Old Tank Car Depreciation 0.60 0.48 0.38 0.31 0.25 0.20 0.16 0.13 0.25 0.25
Incremental Depreciation 1.60 1.39 1.20 1.64 1.38 0.57 0.51 0.45 0.40 0.35 0.50 0.67 0.70 0.32 0.32
4. Cash Flow Adjustments
Less Capital Expenditures CAPEX (NCS) -14.00 -3.00 3.00
5. Incremental Project Cash Flow -14.00 0.93 2.36 3.03 0.20 3.23 5.46 2.55 2.61 2.68 2.74 2.05 2.17 2.24 2.22 2.51
Cum CF -14.00 -13.07 -10.72 -7.69 -7.49 -4.25 1.21 3.76 6.37 9.05 11.79 13.84 16.01 18.25 20.47 22.98
NPV = 4.26
IRR= 14.7%
Payback Period=
Shares Outstanding= 88.23 million
Cannibalization Rate
0% 10% 20% 30% 40% 50% 60% 70% 80% 90% 100%
7%
Discount 8%
Rate 9%
10%
11%
12%
13%

AutoZone

Assumptions Instructions:
2007 Calcualte highlighted cells in B18, B33, B65, B68, B72, B73, and B74.
Interest Rate 6.00%
Effective Tax Rate 21%
Beginning Shareholder's Equity $ 469,528
Beginning Debt $ 1,857,157
Income Statement Without Stock Repurchase
2007
Net sales $ 4,935,843
Cost of sales $ 2,484,443
Gross profit $ 2,451,400
SG&A $ 1,607,187
Operating profit $ 844,213
Interest expense, net
Income before income taxes $ 844,213
Income tax expense $ 177,285
Net income $ 666,928
Dividends $ - 0
Retained Earning $ 666,928
Wt. Avg Shares for Basic EPS 70,021
Basic EPS $ 9.52
Balance Sheet Without Stock Repurchase
2007
Assets
Current assets:
Excess Cash (Off-setting plug)
Cash and cash equivalents $ 69,323
Accounts receivable $ 47,901
Merchandise inventories $ 1,605,944
Other current assets $ 93,196
Deferred income taxes $ - 0
Total current assets $ 1,816,364
Property and equipment:
Land $ 500,794
Buildings and improvements $ 1,376,138
Equipment $ 624,159
Leasehold improvements $ 146,881
Construction in progress $ 68,465
Gross property and equipment $ 2,716,436
Less: Accumulated depreciation and amortization $ 974,162
Net property and equipment $ 1,742,274
Goodwill $ 242,116
Deferred income taxes $ 17,065
Other long-term assets $ 25,949
Total assets $ 3,843,767
Liabilities and Stockholders’ Deficit
Current liabilities:
Accounts payable $ 1,496,534
Accrued expenses and other $ 246,106
Income taxes payable $ 20,354
Deferred income taxes $ 65,722
Short-term borrowings $ - 0
Total current liabilities $ 1,828,716
Long-term debt (Plug)
Other long-term liabilities $ 143,997
Total Shareholder's Equity
Total liabilities and stockholders’ equity $ 1,972,713
Trial Assets
Trial Liabilities+Equity
Trial Plug
ROIC Without Stock Repurchase
2007
Net income $ 666,928
Adjustments
Interest expense $ - 0
Rent expense $ 122,018
Tax effect $ 25,624
After-tax return $ 814,570
Average debt $ 928,579
Average equity $ 469,528
Rent x 6 $ 732,110
Average capital lease obligations $ 22,035
Pre-tax invested capital $ 2,152,252
ROIC 37.85%

CPK

CALIFORNIA PIZZA KITCHEN
Pro Forma Tax Shield Effect of Recapitalization Scenarios
(Dollars in thousands, except share data; figures based on end of June 2007)
M&M Case II - Corporate Tax but No Bankruptcy Cost Instructions:
Debt/Total Capital 1. Calcualte highlighted cells in Rows 22, 38, 42, 44, 45, and 56.
Actual (0%) 10% 20% 30% 40% 50% 60% 70% 80% 90% 2. Use a scenario analysis to show the effect of leverage (0% Debt/Total Capital vs. 90% Debt/Total Capital) on ROE. In the best scenario, assume EBIT=$60,116. In the normal scenario, assume EBIT=$30,058. In the worst scenario, assume EBIT= -$30,058.
Interest rate 5.26% 5.26% 5.26% 5.26% 5.26% 5.26% 5.26% 5.26% 5.26% 5.26%
Tax rate 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
Earnings before income taxes and interest (EBIT) 30,058 30,058 30,058 30,058 30,058 30,058 30,058 30,058 30,058 30,058
Interest expense 0 1,391 58 4,174 5,566 6,957 8,349 9,740 11,132 12,523
Earnings before taxes 30,058 28,667 30,000 25,884 24,492 23,101 21,709 20,318 18,926 17,535
Income taxes 9,017 8,600 9,000 7,765 7,348 6,930 6,513 6,095 5,678 5,260
Net income 21,041 20,067 21,000 18,119 17,144 16,170 15,196 14,222 13,248 12,274
Book value:
Debt 0 25,000 50,000 75,000 100,000 125,000 150,000 175,000 200,000 225,000
Equity 250,000 225,000 200,000 175,000 150,000 125,000 100,000 75,000 50,000 25,000
Total capital 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000 250,000
Market value:
Debt 0 25,000 50,000 75,000 100,000 125,000 150,000 175,000 200,000 225,000
Equity 721,625 704,125 686,625 669,125 651,625 634,125 616,625 599,125 581,625 564,125
Market value of capital 721,625 729,125 736,625 744,125 751,625 759,125 766,625 774,125 781,625 789,125
ROE
M&M Case III - with both Corporate Tax and Bankruptcy Cost
WORKSHEET FOR ESTIMATING RATINGS/INTEREST RATES
BD/(BD+BE) 0% 10% 20% 30% 40% 50% 60% 70% 80% 90%
BD/(BD+ME) 0.00% 3.43% 6.79% 10.08% 13.30% 16.47% 19.57% 22.61% 25.59% 28.51%
BD/ME 0.00% 3.55% 7.28% 11.21% 15.35% 19.71% 24.33% 29.21% 34.39% 39.88%
$ Debt $0 $25,000 $50,000 $75,000 $100,000 $125,000 $150,000 $175,000 $200,000 $225,000
Risk-free rate 3.20% 3.20% 3.20% 3.20% 3.20% 3.20% 3.20% 3.20% 3.20% 3.20%
Beta 1.00 1.02 1.05 1.07 1.10 1.13 1.16 1.19 1.22 1.25
Market risk premium 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00% 6.00%
Cost of Equity 9.20% 9.34% 9.48% 9.63% 9.79% 9.96% 10.14% 10.32% 10.52% 10.73%
EBITDA $73,489 $73,489 $73,490 $73,489 $73,491 $73,489 $73,492 $73,489 $73,493 $73,489
Depreciation $48,431 $48,431 $48,432 $48,431 $48,433 $48,431 $48,434 $48,431 $48,435 $48,431
Interest
Taxable Income $25,058 $25,058 $25,058 $25,058 $25,058 $25,058 $25,058 $25,058 $25,058 $25,058
Tax $7,517 $7,517 $7,517 $7,517 $7,517 $7,517 $7,517 $7,517 $7,517 $7,517
Pre-tax Int. cov
Likely Rating
Interest Rate
WORKSHEET FOR CALCULATING WEIGHTED AVERAGE COST OF CAPITAL
BD/(BD+BE) 0.00% 10.00% 20.00% 30.00% 40.00% 50.00% 60.00% 70.00% 80.00% 90.00%
BD/(BD+ME) 0.00% 3.43% 6.79% 10.08% 13.30% 16.47% 19.57% 22.61% 25.59% 28.51%
BD/ME 0.00% 3.55% 7.28% 11.21% 15.35% 19.71% 24.33% 29.21% 34.39% 39.88%
$ Debt $0 $25,000 $50,000 $75,000 $100,000 $125,000 $150,000 $175,000 $200,000 $225,000
Tax Rate 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0% 30.0%
Cost of equity 9.20% 9.34% 9.48% 9.63% 9.79% 9.96% 10.14% 10.32% 10.52% 10.73%
Cost of debt 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00% 0.00%
WACC
Interest cov Interest cov RATING Interest rate
Low High
-100000 0.199999 D 24.96%
0.2 0.649999 C 21.46%
0.65 0.799999 CC 17.96%
0.8 1.249999 CCC 14.96%
1.25 1.499999 B- 12.46%
1.5 1.749999 B 10.96%
1.75 2.199999 B+ 9.96%
2.2 2.749999 BB 9.21%
2.75 3.299999 BBB 8.21%
3.3 4.249999 A- 7.76%
4.25 5.499999 A 7.16%
5.5 6.499999 A+ 5.96%
7.0 8.499999 AA 5.66%
9.5 100000 AAA 5.26%

Rosetta

Assumptions Instructions:
Marginal Tax Rate 21% 1. Calcualte highlighted cells in green;
WACC 9.2% 2. Based on the DCF valuation with Constrant growth model as the terminal value, conduct a one-way sensitivity analysis of offre price per share by changing the Teriminal Growth Rate from 2% to 5%.
Terminal Growth Rate 3.0%
Shares Oustanding 25.00 (million)
Value of Debt 2008 $32 (million)
Cash and Equivalents $20 (million)
EV/EBITDA Trailing Multiple 15.40
EV/EBITDA Forward Multiple 13.40
ROSETTA STONE, INC.: PRICING THE 2009 IPO
Financial Forecast for Rosetta Stone (in millions of dollars except percentages)
2008A 2009E 2010E 2011E 2012E 2013E 2014E 2015E 2016E 2017E 2018E
Revenue growth 72.0% 30.5% 29.5% 27.0% 22.5% 20.7% 18.9% 16.2% 11.7% 9.0% 4.5%
Gross margin 73.7% 77.4% 77.4% 76.5% 75.6% 74.7% 73.8% 72.9% 72.0% 71.1% 70.2%
SGA exp / Revenue 52.0% 52.0% 52.0% 56.7% 56.7% 56.3% 56.3% 56.3% 56.3% 56.3% 56.3%
R&D exp / Revenue 7.8% 9.0% 9.0% 8.5% 8.5% 8.5% 8.5% 8.0% 8.0% 8.0% 8.0%
Capital expenditures 7.0 5.0 8.0 9.0 9.5 10.0 11.0 11.0 9.0 8.0 5.0
NPPE turnover 13.5 16.0 16.2 16.4 16.6 16.8 17.0 17.2 17.4 16.8 17.3
NWC turnover 8.6 8.8 8.8 8.8 8.3 8.3 7.8 7.8 7.8 7.8 7.8
Sales Revenue 164.8 215.0 278.5 353.7 433.2 522.9 621.8 722.5 807.0 879.7 919.2
Gross profit 121.4 166.4 215.5 270.6 327.5 390.6 458.9 526.7 581.1 625.4 645.3
SGA expense 85.7 111.8 144.8 200.5 245.7 294.1 349.7 406.4 454.0 494.8 517.1
R&D expense 12.9 19.4 25.1 30.1 36.8 44.4 52.9 57.8 64.6 70.4 73.5
EBIT 22.9 35.3 45.7 40.0 45.1 52.0 56.3 62.5 62.5 60.3 54.7
Net working capital 19.2 24.4 31.6 40.2 52.2 63.0 79.7 92.6 103.5 112.8 117.9
Net PPE 12.2 13.4 17.2 21.6 26.1 31.1 36.6 42.0 46.4 52.4 53.1
Discounted Cash Flow Valuation with Constant Growth Model as Terminal Value Terminal Growth Rate Intrinsic Equity Value per Share
Year 2008A 2009E 2010E 2011E 2012E 2013E 2014E 2015E 2016E 2017E 2018E
EBIT $ 22.86 $ 35.25 $ 45.65 $ 39.97 $ 45.06 $ 52.03 $ 56.27 $ 62.50 $ 62.54 $ 60.26 $ 54.69 2.0%
Depreciation $ 7.50 2.5%
Tax $ 4.80 $ 7.40 $ 9.59 $ 8.39 $ 9.46 $ 10.93 $ 11.82 $ 13.12 $ 13.13 $ 12.65 $ 11.49 3.0%
OCF 3.5%
CAPEX $ 5.00 $ 8.00 $ 9.00 $ 9.50 $ 10.00 $ 11.00 $ 11.00 $ 9.00 $ 8.00 $ 5.00 4.0%
Change in NWC $ 5.28 $ 7.21 $ 8.54 $ 12.01 $ 10.81 $ 16.71 $ 12.91 $ 10.84 $ 9.31 $ 5.07 4.5%
FCFF $ (10.28) $ (15.21) $ (17.54) $ (21.51) $ (20.81) $ (27.71) $ (23.91) $ (19.84) $ (17.31) $ (10.07) 5.0%
Terminal Value (TV)
FCFF + TV $ (10.28) $ (15.21) $ (17.54) $ (21.51) $ (20.81) $ (27.71) $ (23.91) $ (19.84) $ (17.31) $ (10.07)
Enterprise Value (million)
Intrinsic Equity Value (million)
Intrinsic Equity Value per Share
Direct Market Multiples Approach (Relative Valuation): Use EV/EBITDA Forward Multiple
Enterprise Value (million) Use EV/EBITDA forward multiple given in B9
Equity Value (million)
Offer Price Per Share