Forecasting Balance Sheet, and Cashflows
The information we have calculated so far can be used to project Ideko’s balance sheet and statement of cash flows through 20Y0. While these statements are not critical for our valuation, they often prove helpful in providing a more complete picture of how a firm will grow during the forecast period.
Forecast Free Cash Flows
Info: Worksheet (and page JavaScript) may take time to load
Tip: Double click a cell to view its formula
Forecast Balance Sheet
Tip: Double click a cell to view its formula
For the balance sheet, we must begin by adjusting Ideko’s closing 20X5 balance sheet to account for the transaction. The transaction will affect the firm’s goodwill, stockholders’ equity, and its cash and debt balances.
20X5 ProForma B/S
We show these changes by constructing a new “pro forma” 20X5 balance sheet that represents the firm just after the transaction closes. Considering each change:
Goodwill
Calculate goodwill as the difference between the acquisition price and the value of the net assets acquired:
\[\begin{align*} & \text{New Goodwill} = \text{Acquisition Price} - (\text{Value of Net Assets Acquired} \end{align*}\]In this case, the acquisition price is the $150 million purchase price for Ideko’s existing equity. For the net assets acquired, we use Ideko’s existing book value of equity, $77.668 million, excluding any pre-existing goodwill ($0). Thus, new goodwill is 150 - (77.668 - 0) = $72.332 million.
Note:
The goodwill calculation is simplified somewhat. In particular, the book values of Ideko’s assets and liabilities may be “stepped up” to reflect current fair values, and a portion of the excess purchase price may be allocated to intangible assets as opposed to goodwill. In this case, there is no Non-Controlling Interest (NCI).
Stockholders’ Equity
Next, consider stockholders’ equity, which we calculate as:
\[\begin{align*} & \text{NewStockholders’Equity} = \text{EquityContributions} - (\text{ExpensedTransactionFees} \end{align*}\]In this case, we deduct the $5 million in advisory fee expenses from Kleiner Perkins’ initial equity contribution of $53 million to calculate new shareholders’ equity of $48 million.
Cash & Debt
Finally, we adjust Ideko’s cash and debt balances by deducting the excess cash of $6.5 million used to help fund the transaction, eliminating Ideko’s existing debt (which will be repaid), and adding the new debt incurred of $100 million.
B/S Going Forward
To construct the balance sheet going forward, adjust the cash balance to reflect the change in cash from the statement of cash flows. As we can see, Ideko’s book value of equity will decline in 20X6, as Ideko reduces its working capital and pays out the savings as part of a large dividend. The firm’s book value will then rise as it expands.
Current Assets & Liabilities
All other current assets and liabilities come from the net working capital spreadsheet (Ideko - Forecast Net Working Capital). The inventory entry on the balance sheet includes both raw materials and finished goods.
Property, Plant, & Equipment
Property, plant, and equipment information comes from the capital expenditure spreadsheet (Ideko - Capital Expenditure Plan), and the debt comes from here (Ideko - Capital Structure).
Stockholder’s Equity
Stockholders’ equity increases each year through retained earnings (net income less dividends) and new capital contributions (stock issuances net of repurchases).
Important:
As a best practice, it is usually better to calculate stockholders’ equity directly rather than use the whole line as a plug. A line below the balance sheet, can be included as a “check” line that tests whether total assets equal total liabilities and equity each year—and if the difference is non-zero, it can be displayed in a bold red font so the error is apparent.
Dividends
Dividends after 20X5 are equal to positive free cash flows to equity from Ideko - Forecast Free Cash Flows, whereas negative free cash flows to equity represent a stock issuance. As a check on the calculations, note that the balance sheet does, indeed, balance: Total assets equal total liabilities and equity!
Important:
Cash & Payout Policy
It is good practice that the firm’s cash and payouts match its intended policy. In this case, we forecast that Ideko would pay out all excess cash going forward. Thus, its cash balances in the balance sheet should match the minimum balances specified here (Ideko - Forecast NWC). In this example, free cash flow to equity (i.e., dividends) was an output of the model. We could have alternatively assumed Ideko would retain its excess cash. In that case, free cash flow to equity would be zero (no dividends would be paid), and changes in cash (and thus the firm’s net borrowing) would be outputs.
Feedback
Submit and view feedback