S&P Capital IQ Equity Valuation Case

S&P Capital IQ Case

S&P Capital IQ Equity Valuation Case

Model Tutorial

MODEL OVERVIEW

The valuation model used to determine the fair price of the securities revolves around a discounted cash flow ("DCF") model that discounts the future unlevered free cash flows ("UFCF") / free cash flows to firm ("FCFF") at a calculated weighted average cost of capital ("WACC"). Given the nature of the trading simulation, there are several underlying assumptions when calculating the implied per share price of a given security.

Due to the nature of the competition, there are several over-arching assumptions implicit in the simplified DCF model:

? Calendarization will not be considered and all projected cash flows will be discounted by the same discount period throughout the trading period.

? Anything expressed as "this year" or "fiscal year 2015" will refer to the base year of Year 0, which are based off of Year 0 assumptions. Projections would only apply to Year 1 to Year 5. Hence, "next year" would refer to Year 1.

? Depreciation charges on the income statement ("I/S") and cash flow statement ("CFS") are assumed to be applied using the straight-line method in annual amounts.

? Merger impacts will only be reflected in a single line on the I/S with no balance sheet ("B/S") or cash flow statement impacts. As such, pro-forma merger models will not be required.

? Interest paid on debt and principal repayment rate will apply to the company's entire debt balance. This measure could be thought of as a blended average interest/repayment rate.

? The number of days in a year used to calculate certain operating ratios will be 365. ? Book value of equity will be used in the WACC calculation. ? Levered beta for each company will be directly stated and thus require no calculation. ? Manual adjustments can be used for a variety of news items. Manual adjustments to pre-tax

earnings on the income statement are assumed to be an all-cash impact that will have no adjustment on either the balance sheet or cash flow statement. These adjustments provide a flexible method of impacting valuation when a single growth/cost percentage does not fully capture the value impact from a particular news item. ? Changes in sales projections will require revenue schedules to project. The Adjusted Value of the sales growth in the model template is only the initial assumption and will not change.

Copyright ? Rotman School of Management, University of Toronto

Rotman International Trading Competition 2016 | 1

S&P Capital IQ Case

Before building the model, you must enable iterative calculations to calculate the circular references. For Windows Excel 2013, go to "File" -> "Options" -> "Formulas". In this window, check the "Enable iterative calculation" box. Set Maximum Iterations to 10,000 and click "OK".

It is important to keep in mind that given the nature of circular references, not all line items will be calculated right away. However, all circular references will be calculated once the entire model is completed. To prevent your model from being broken by a mislinked circular reference, it is recommended that you save at regular intervals.

OPERATING MODEL

In order to build the model, certain parameters must be considered. The assumptions for these parameters will naturally need to be adjusted as the case progresses. For the following sections on the operating model, Emeris will be used as an example.

It is recommended that the order of completing the financial statements should be: income statement, balance sheet, cash flow statement, and dynamically linking the 3-statements. When dynamically linking the 3-statements, certain line items that could not have been projected the first time will be completed.

INCOME STATEMENT

Starting with some of the line items, the following parameters will be used.

Parameter

Sales growth rate Cost of goods sold (% Sales)

R&D expense (% Sales) Marketing expense (% Sales)

General & Administrative expense (% Sales)

Annual depreciation rate Interest rate on debt Interest paid on cash Corporate tax rate

Adjusted Value 3% 50% 2%

1%

19%

3% 4% 1% 20%

Notes Annual growth rate

Percentage applied to all years. Eg. 1 = 1 ?

(% )

Annual depreciation rate Annual rate

Interest earned on cash balance Full cash charge

Copyright ? Rotman School of Management, University of Toronto

Rotman International Trading Competition 2016 | 2

S&P Capital IQ Case

Schedules such as:

Asset Write-downs

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5

Adjusted Value

$0 $0 $0 $0 $0 ($50)

would indicate that in Year 5, a $50 pre-tax charge will be applied.

The initial sales projections for American Railroad Corporation has been harded-coded, which will be explained in the Revenue Schedules Input.

Certain Year 0 figures will be directly linked to assumptions such as:

Parameter

Year 0 Sales Year 0 Depreciation Year 0 Interest payments on debt Year 0 Interest earned on cash

Adjusted Value

$4,725 ($350) ($168)

$10

The Net income (Profit after taxes) will be defined as:

= -

where,

Parameter Adjusted Sales

Gross Profit EBIT

EBT

Taxes

Definition

Sales + Manual adjustments (Sales) Sales ? |Cost of goods sold|

Gross Profit ? |Depreciation| ? |Research and development| ? |Marketing| ? |General & administrative|

EBIT ? |Interest payments on debt| + Interest earned on cash + Manual adjustments (EBIT) ? |Asset Write-down| ? |Goodwill impairment| + Pre-tax

accretion from acquisitions EBT x Corporate tax rate

Manual adjustments can have either a positive or negative impact on Sales or EBT, which will require appropriate judgement depending on the specific news item. Similarly, pre-tax accretion could have a negative impact on EBT, at which point the impact would be referred to as a dilution to earnings.

Copyright ? Rotman School of Management, University of Toronto

Rotman International Trading Competition 2016 | 3

S&P Capital IQ Case

The shares outstanding section as well as the depreciation, interest payments on debt, and interest payments on cash line items will be explained in the 3-Statements Revisited section and do not need to be projected yet.

BALANCE SHEET

Similar to some I/S items, there will be Year 0 line items that are linked directly to the corresponding Year 0 assumption such as:

Parameter

Year 0 Cash Year 0 Accounts receivable

Year 0 Inventories Year 0 Accumulated depreciation

Year 0 PP&E at cost Year 0 Goodwill

Year 0 Accounts payable Year 0 Deferred liabilities Year 0 Unearned revenues

Adjusted Value

$257 $900 $400 ($3,900) $11,200 $880 $360 $427 $180

To project out the current assets and current liabilities, the following parameters will be used:

Parameter

Days sales outstanding

Accounts receivable (% Sales)

Days inventories outstanding

Inventories (% COGS) Days payable outstanding

Accounts payable (% COGS)

Deferred liabilities (% G&A expense)

Unearned revenues (% COGS)

Adjusted Value 73

20.00% 65

17.81% 60

16.44% 50%

8%

Notes Average number of days a company takes

to collect revenue after a sale has been made

= Days sales outstanding / 365

Average number of days a company takes to turn inventory into sales

= Days inventories outstanding / 365 Average number of days a company takes

to pay its invoices

= Days payable outstanding / 365

Percentage applied to corresponding year's General & administrative expense on the Income Statement

Percentage applied to corresponding year's Cost of goods sold expense on the Income

Statement

The above parameters' values for Year 1 to Year 5 will naturally be the corresponding year's sales or cost of goods sold multiplied by the appropriate percentage.

Copyright ? Rotman School of Management, University of Toronto

Rotman International Trading Competition 2016 | 4

S&P Capital IQ Case

Other line items on the B/S will be projected following the completion of the CFS and explained in the 3Statements Revisited section

CASH FLOW STATEMENT

Some line items will be linked from the Income Statement and others from dedicated schedules in the assumptions sections. There are three main sections: Operating Activities, Investing Activities, and Financing Activities.

Parameter Cash Flow from Operations

Cash Flow from Investing Cash Flow from Financing

Adjusted Value for Year 0

$1,019.12 ($750)

($250)

Formula

= Net income + |Depreciation| + |Asset Write-down| + |Goodwill impairment| + Changes in operating assets & liabilities

= Capital expenditures = Proceeds from issuing common stock -

|Repurchases of common stock| |Dividends paid| + Borrowings from longterm debt - |Repayment of long-term debt|

Operating Activities: Net income is linked directly from the Income Statement. Under Non-cash Income Statement adjustments, depreciation, asset write-downs, and goodwill impairment will be added back since they are non-cash charges. Negative charges on the I/S will appear as a positive figure under the Operating Activities section. For example, if a $350 depreciation charge was incurred in Year 1, the amount would have a positive $350 cash flow impact under Operating Activities for Year 1. Note that depreciation on the income statement has not been projected yet at this point.

The Changes in operating assets & liabilities section includes the cash charges corresponding to the balance sheet changes of accounts receivable, inventories, accounts payable, deferred liabilities, and unearned revenue. Year 0 values for these parameters are linked to the following schedule:

Parameter

Year 0 Accounts receivable Year 0 Inventories

Year 0 Accounts payable Year 0 Deferred liabilities Year 0 Unearned revenue

Adjusted Value

$50 ($40) ($10) $10

$5

If the asset account increases, there is a negative cash charge. If the liabilities account increases, there is a positive cash charge.

Copyright ? Rotman School of Management, University of Toronto

Rotman International Trading Competition 2016 | 5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download