Modeling

Question 1

Build Net Working Capital Schedule

Answer 1

Layout historical sales and COGS at the top.

Start next section to show current asset components and current liabilities components with subtotal lines. Insert line under both sections for total net working capital. Insert line below that showing change in net working capital

Build out ratios and assumptions table. Show days in the period, DSO, DIO or turns, other as % of sales, DPO, other as % of COGS or sales. Once historicals are built, project for the next five years

Project individual asset and liability items off of assumptions table. Once done with all line items, link to the balance sheet and CF statement

Question 2

Build the Depreciation Schedule

Answer 2

Layout sales, depreciation, and capital expenditures. Show depreciation and capital expenditures as a % of sales. Project depreciation as a % of sales or capital expenditures

For the waterfall, if you can, breakout maintenance and growth capex. Assume some useful life for the waterfall (10 is fine). On the left side, list expected capex going down for 5 rows (five projected years). Show the numbers 1-5 on left. For each cell, if the number to the left of that year’s capex is less or equal to the current year, divide the capex by the useful life, if not, then show nothing

Show sum rows on the bottom of the waterfall for total depreciation. This will be your new depreciation

Now, showing the PPE schedule, start with beginning PPE, add in new capital expenditures, subtract the new depreciation from the waterfall, subtract the “legacy” depreciation which is the projected depreciation less the new depreciation. This will get you to your ending PPE

Link back to the balance sheet and cash flow statement

Question 3

Build the Shareholder’s Equity Schedule

Answer 3

Beginning balance + net income + stock-based compensation expense - repurchase of equity - dividends + options proceeds from exercise

To project repurchase of equity, you have to assume a P/E off of projected earnings to find the price and then assume some amount of shares will be repurchased

Question 4

How do you forecast dividend payments?

Answer 4

Assume some dividend payout ratio as a percentage of net income. You could also assume some fixed or per share amount

Question 5

How do you setup the debt & interest schedule?

Answer 5

Start with
"Cash Flow Available for Debt Repayment"
- less repurchase of equity
- less dividends
- plus option proceeds
- plus beginning cash balance
- less minimum cash balance (assumption)

“Cash Available for Debt Repayment”

• plus long-term debt issuance
• less long-term debt (repayment)

Question 6

Discuss the revolver calculation

Answer 6

The revolver is the negative minimum of the excess cash available from the revolver or the beginning balance. It should borrow if there is a cash deficit and pay down itself with free cash flow