Discounted Cash Flow Model for Bonkers

General Information

Company Name Bonkers

Latest Fiscal Year End 4/27/2019

Current Date 04/29/2019

Current Stock Price $56.35

Circular Reference Breaker

Off=1, On=0 1

Income Statement ($000s)

Actual Projected

2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024

04/27/14 04/27/15 04/27/16 04/27/17 04/27/18 04/27/19 04/27/20 04/27/21 04/27/22 04/27/23 04/27/24

Sales

641,135 645,825 704,785 826,918 975,734 1,014,105

Cost of Goods Sold

423,480 426,685 463,348 500,841 584,599 629,755

Selling, General, and Administrative Expense

153,220 145,157 148,384 163,600 186,947 204,415

EBITDA

64,435 73,983 93,053 162,477 204,188 179,935

Depreciation and Amortization

11,708 11,580 12,056 12,834 13,226 15,439

EBIT

52,727 62,403 80,997 149,643 190,962 164,496

Interest Expense

1,000 450 250 0 0 0

Pretax Income

51,727 61,953 80,747 149,643 190,962 164,496

Income Taxes

19,474 25,402 31,507 55,780 55,715 43,024

Net Income (Loss)

32,253 36,551 49,240 93,863 135,247 121,472

Cash Dividends

Retained

Income Statement Margin/Growth Analysis & Assumptions

Sales Growth

0.73% 9.13% 17.33% 18.00% 3.93% 3.00% 2.50% 2.00% 2.00% 2.00%

COGS/Sales

66.05% 66.07% 65.74% 60.57% 59.91% 62.10% 60.86%

SG&A/Sales

23.90% 22.48% 21.05% 19.78% 19.16% 20.16% 19.70%

Depreciation/Average Gross Fixed Assets

5.43% 5.49% 5.64% 5.36% 5.60% 5.50%

Interest Expense

5.00%

Income Tax/Pretax Income

37.65% 41.00% 39.02% 37.28% 29.18% 26.16% 26.16%

$ Dividend / per year

$130,000

Balance Sheet ($000s)

Actual Projected

2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024

04/27/14 04/27/15 04/27/16 04/27/17 04/27/18 04/27/19 04/27/20 04/27/21 04/27/22 04/27/23 04/27/24

ASSETS

Cash and equivalents

29,932 52,456 105,577 136,372 189,864 156,200

Accounts Receivables 58,205 59,951 61,046 71,319 84,360 84,841

Inventories 43,914 42,924 47,922 53,355 60,920 70,702

Prepaid and other assets 8,405 8,050 4,672 7,275 17,823 9,714

Deferred Tax Asset 2,685 4,348 4,454 0 0 0

Total Current Assets 143,141 167,729 223,671 268,321 352,967 321,457

Property, Plant, and Equipment, Gross 209,972 216,607 222,551 232,914 260,688 290,369

Depreciation, Depletion, and Amortization (Accumulated) 150,478 156,425 160,619 167,764 174,881 179,053

Property, Plant, and Equipment, Net 59,494 60,182 61,932 65,150 85,807 111,316

Intangibles 14,760 14,760 14,760 14,760 14,760 14,760

Other Assets 5,446 5,079 5,135 5,752 5,298 4,660

TOTAL ASSETS

222,841 247,750 305,498 353,983 458,832 452,193

LIABILITIES

Accounts Payable 45,606 44,896 49,391 58,100 74,853 66,202

Accrued Liabilities 18,873 21,257 26,195 29,017 29,718 30,433

Income Taxes Payable 44 98 28 89 99 402

Long-term Debt 30,000 10,000 0 0 0 0

Deferred Tax Liabilities 13,873 15,245 14,474 12,087 14,502 15,987

Other Liabilities 8,244 8,472 9,258 9,072 8,220 7,560

Total Liabilities 116,640 99,968 99,346 108,365 127,392 120,584

EQUITY

Preferred stock 390 270 150 150 150 150

Common stock 504 504 506 506 507 507

Additional paid-in capital 42,570 35,235 32,763 35,034 40,959 35,522

Retained Earnings 80,737 129,773 190,733 227,928 307,824 313,430

Treasury Stock - Total Dollar Amount (18,000) (18,000) (18,000) (18,000) (18,000) (18,000)

Total Stockholders Equity

106,201 147,782 206,152 245,618 331,440 331,609

TOTAL LIABILITIES AND EQUITY

222,841 247,750 305,498 353,983 458,832 452,193

Balance Check

Balance Sheet Ratio Analysis & Assumptions

2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024

ASSETS

Accounts Receivable Days

33 33 31 31 31 30 31

Inventory Days

37 36 37 38 38 40 39

Prepaid and other Assets/SG&A

5.49% 5.55% 3.15% 4.45% 9.53% 4.75% 5.49%

Deferred Tax Asset

$0

Net PP&E/Sales

9.28% 9.32% 8.79% 7.88% 8.79% 10.98% 11.00%

Intangibles

hold constant

Other Assets

hold constant

LIABILITIES AND EQUITY

Accounts Payable Days

39 38 38 42 46 38 42

Accrued Liabilities/SG&A

12.32% 14.64% 17.65% 17.74% 15.90% 14.89% 16.17%

Income taxes payable growth rate

(100.00%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! Grow with taxes

Long-term Debt

$0.0

Deferred Tax Liabilities growth rate

(100.00%) #DIV/0! #DIV/0! #DIV/0! #DIV/0! Grow with taxes

Other Liabilities

hold constant

Preferred Stock

hold constant

Common Stock

hold constant

Additional paid-in capital

hold constant

Accumulated other comprehensive income

hold constant

Treasury Stock

See repurchase schedule

Statement of Cash Flows ($000s)

2020 2021 2022 2023 2024

Cash flow from operating activities

Net Income

Add: Depreciation and Amortization

Changes in Working Capital

Accounts Receivable

Inventory

Prepaid and Other Current Assets

Accounts Payable

Accrued Liabilities

Income Tax Payable

Change in Deferred Tax Assets

Change in Deferred Tax Liabilities

Change in Other Assets

Change in Other Liabilities

Cash Flows from Operations

Cash flow from investing activities

Purchases of Fixed Assets--Capital Expenditures

Purchases of Intangible Assets

Net cash used in investing activities

Cash flow from financing activities

Change in Debt

Preferred Stock

Common Stock and Paid-In-Capital

Treasury Stock Repurchases

Cash Dividends Paid

Net cash from financing activities

Net increase in cash and cash equivalents

Repurchases Schedule

Calculation of Repurchases Cash at Beginning of Period Cash Flows Before Repurchases Minimum Cash Balance (15% Sales) 15% Total cash available for repurchases

Discounted Cash Flow Model ($000s)

2020 2021 2022 2023 2024

FREE CASH FLOW (FCFF)

Net Income

Add Back Non-Cash Charges

Depreciation and Amortization

Adjustments for Changes in Working Capital

Accounts Receivable

Inventory

Prepaid and Other Current Assets

Accounts Payable

Accrued Liabilities

Income Tax Payable

Proceeds for Sale or Purchase of Fixed Assets

Purchases of Intangible Assets

Changes in Deferred Tax Assets

Changes in Deferred Tax Liabilities

Change in Other Assets

Add (Subtract) After-Tax Interest Expense (Revenue)

FCFF using Net Income

VALUATION

WACC 8.0%

Long-Term Free Cash Flow Growth Rate 2.0%

2020 2021 2022 2023 2024

FCFF ($000s)

Terminal Value

Total

Enterprise Value from DCF

Add: Initial Excess Cash

Subtract: Debt

Debt

Other Liabilities

Implied Equity Value

Number of Diluted Shares Outstanding 46,917

Implied Value Per Share

Directions

all cells in the Template with green fill.

All assumptions have been provided for you in yellow fill cells.

Note: for AR, Inventory, and AP Days use a 360 day year.

Note: You will "close" the model by building a repurchases schedule.

Note: No cells should be hardcoded.

Make sure to "enable iterative calculation" (File-Options-Formulas for PC and Excel-Preferences-Calculation for Mac).