comparable analysis

Tuleenhb
DCFAnalysis_Template2.xls
Home>Business & Finance homework help>comparable analysis

Cover

Investment Banking
Valuation, Leveraged Buyouts,
and Mergers & Acquisitions
DCF Analysis - Template
JOSHUA ROSENBAUM
JOSHUA PEARL

DCF

ValueCo Corporation
Discounted Cash Flow Analysis
($ in millions, fiscal year ending December 31) Operating Scenario Base
Operating Scenario 1
Mid-Year Convention Y Historical Period CAGR Projection Period CAGR
2005 2006 2007 ('05 - '07) 2008 2009 2010 2011 2012 2013 ('08 - '13)
Sales -0 -0 -0 - -0 -0 -0 -0 -0 -0 -
% growth NA -0 -0 -0 -0 -0 -0 -0 -0
COGS -0 -0 -0 -0 -0 -0 -0 -0 -0
Gross Profit -0 -0 -0 - -0 -0 -0 -0 -0 -0 -
% margin -0 -0 -0 -0 -0 -0 -0 -0 -0
SG&A -0 -0 -0 -0 -0 -0 -0 -0 -0
EBITDA -0 -0 -0 - -0 -0 -0 -0 -0 -0 -
% margin -0 -0 -0 -0 -0 -0 -0 -0 -0
Depreciation & Amortization -0 -0 -0 -0 -0 -0 -0 -0 -0
EBIT -0 -0 -0 - -0 -0 -0 -0 -0 -0 -
% margin -0 -0 -0 -0 -0 -0 -0 -0 -0
Taxes -0 -0 -0 -0 -0 -0 -0 -0 -0
EBIAT -0 -0 -0 - -0 -0 -0 -0 -0 -0 -
Plus: Depreciation & Amortization -0 -0 -0 -0 -0 -0 -0 -0 -0
Less: Capital Expenditures -0 -0 -0 -0 -0 -0 -0 -0 -0
Less: Increase in Net Working Capital -0 -0 -0 -0 -0
Unlevered Free Cash Flow -0 -0 -0 -0 -0
WACC - %
Discount Period 0.5 1.5 2.5 3.5 4.5
Discount Factor 1.00 1.00 1.00 1.00 1.00
Present Value of Free Cash Flow -0 -0 -0 -0 -0
Enterprise Value Implied Equity Value and Share Price Implied Perpetuity Growth Rate
Cumulative Present Value of FCF -0 Enterprise Value -0 Terminal Year Free Cash Flow (2013E) -0
Less: Total Debt -0 WACC -0
Terminal Value Less: Preferred Securities -0 Terminal Value -0
Terminal Year EBITDA (2013E) -0 Less: Noncontrolling Interest -0
Exit Multiple - x Plus: Cash and Cash Equivalents -0 Implied Perpetuity Growth Rate - %
Terminal Value -0
Discount Factor 1.00 Implied Equity Value -0 Implied EV/EBITDA
Present Value of Terminal Value -0 Enterprise Value -0
% of Enterprise Value - % Implied Share Price -0 LTM 9/30/2008 EBITDA -0
Enterprise Value -0 Implied Share Price NA Implied EV/EBITDA - x
Enterprise Value Implied Perpetuity Growth Rate
Exit Multiple Exit Multiple
0.0 -1.0x -0.5x 0.0x 0.5x 1.0x - % -1.0x -0.5x 0.0x 0.5x 1.0x
WACC -1.0% 0 0 0 0 0 WACC -1.0% - % - % - % - % - %
-0.5% 0 0 0 0 0 -0.5% - % - % - % - % - %
0.0% 0 0 $0 0 0 0.0% - % - % - % - % - %
0.5% 0 0 0 0 0 0.5% - % - % - % - % - %
1.0% 0 0 0 0 0 1.0% - % - % - % - % - %
Assumptions
Sales (% growth) NA - % - % - % - % - % - % - % - %
COGS (% sales) - % - % - % - % - % - % - % - % - %
SG&A (% sales) - % - % - % - % - % - % - % - % - %
Depreciation & Amortization (% sales) - % - % - % - % - % - % - % - % - %
Capital Expenditures (% sales) - % - % - % - % - % - % - % - % - %
Tax Rate - % - % - % - % - % - % - % - % - %
Working Capital (% sales) - % - % - % - % - %
ValueCo Corporation
Sensitivity Analysis
($ in millions)
Enterprise Value Implied Equity Value
Exit Multiple Exit Multiple
0.0 -1.0x -0.5x 0.0x 0.5x 1.0x 0.0 -1.0x -0.5x 0.0x 0.5x 1.0x
WACC -1.0% 0 0 0 0 0 WACC -1.0% 0 0 0 0 0
-0.5% 0 0 0 0 0 -0.5% 0 0 0 0 0
0.0% 0 0 $0 0 0 0.0% 0 0 $0 0 0
0.5% 0 0 0 0 0 0.5% 0 0 0 0 0
1.0% 0 0 0 0 0 1.0% 0 0 0 0 0
Implied Share Price PV of Terminal Value as % of Enterprise Value
Exit Multiple Exit Multiple
NA -1.0x -0.5x 0.0x 0.5x 1.0x - % -1.0x -0.5x 0.0x 0.5x 1.0x
WACC -1.0% NA NA NA NA NA WACC -1.0% - % - % - % - % - %
-0.5% NA NA NA NA NA -0.5% - % - % - % - % - %
0.0% NA NA NA NA NA 0.0% - % - % - % - % - %
0.5% NA NA NA NA NA 0.5% - % - % - % - % - %
1.0% NA NA NA NA NA 1.0% - % - % - % - % - %
Implied Perpetuity Growth Rate Implied Enterprise Value / LTM EBITDA
Exit Multiple Exit Multiple
- % -1.0x -0.5x 0.0x 0.5x 1.0x - x -1.0x -0.5x 0.0x 0.5x 1.0x
WACC -1.0% - % - % - % - % - % WACC -1.0% - x - x - x - x - x
-0.5% - % - % - % - % - % -0.5% - x - x - x - x - x
0.0% - % - % - % - % - % 0.0% - x - x - x - x - x
0.5% - % - % - % - % - % 0.5% - x - x - x - x - x
1.0% - % - % - % - % - % 1.0% - x - x - x - x - x
Calculation of Implied Share Price
Enterprise Value -0
Less: Total Debt -0
Less: Preferred Securities -0
Less: Noncontrolling Interest -0
Plus: Cash and Cash Equivalents -0
Implied Equity Value -0
Options/Warrants
Number of Exercise In-the-Money
Tranche Shares Price Shares Proceeds
Options 1 -0 -0 -0 -0
Options 2 -0 -0 -0 -0
Options 3 -0 -0 -0 -0
Options 4 -0 -0 -0 -0
Options 5 -0 -0 -0 -0
Total -0 -0 -0
Basic Shares Outstanding -0
Plus: Shares from In-the-Money Options -0
Less: Shares Repurchased -0
Net New Shares from Options -0
Plus: Shares from Convertible Securities -0
Fully Diluted Shares Outstanding -0
Implied Share Price -0

NWC

ValueCo Corporation
Working Capital Projections
($ in millions)
Historical Period Projection Period
2005 2006 2007 2008 2009 2010 2011 2012 2013
Sales -0 -0 -0 -0 -0 -0 -0 -0 -0
Cost of Goods Sold -0 -0 -0 -0 -0 -0 -0 -0 -0
Current Assets
Accounts Receivable -0 -0 -0 -0 -0 -0 -0 -0 -0
Inventories -0 -0 -0 -0 -0 -0 -0 -0 -0
Prepaid Expenses and Other -0 -0 -0 -0 -0 -0 -0 -0 -0
Total Current Assets -0 -0 -0 -0 -0 -0 -0 -0 -0
Current Liabilities
Accounts Payable -0 -0 -0 -0 -0 -0 -0 -0 -0
Accrued Liabilities -0 -0 -0 -0 -0 -0 -0 -0 -0
Other Current Liabilities -0 -0 -0 -0 -0 -0 -0 -0 -0
Total Current Liabilities -0 -0 -0 -0 -0 -0 -0 -0 -0
Net Working Capital -0 -0 -0 -0 -0 -0 -0 -0 -0
% sales -0 -0 -0 -0 -0 -0 -0 -0 -0
(Increase) / Decrease in NWC -0 -0 -0 -0 -0 -0 -0 -0
Assumptions
Days Sales Outstanding -0 -0 -0 -0 -0 -0 -0 -0 -0
Days Inventory Held -0 -0 -0 -0 -0 -0 -0 -0 -0
Prepaids and Other CA (% of sales) - % - % - % - % - % - % - % - % - %
Days Payable Outstanding -0 -0 -0 -0 -0 -0 -0 -0 -0
Accrued Liabilities (% of sales) - % - % - % - % - % - % - % - % - %
Other Current Liabilities (% of sales) - % - % - % - % - % - % - % - % - %

WACC

ValueCo Corporation
Weighted Average Cost of Capital Analysis
($ in millions)
WACC Calculation Comparable Companies Unlevered Beta
Target Capital Structure Predicted Market Market Debt/ Marginal Unlevered
Debt-to-Total Capitalization - % Company Levered Beta(4) Value of Debt Value of Equity Equity Tax Rate Beta
Equity-to-Total Capitalization 100.0% CompCo1 -0 -0 -0 - % - % -0
CompCo2 -0 -0 -0 - % - % -0
CompCo3 -0 -0 -0 - % - % -0
Cost of Debt CompCo4 -0 -0 -0 - % - % -0
Cost of Debt - % CompCo5 -0 -0 -0 - % - % -0
Tax Rate - %
After-tax Cost of Debt - % Mean -0 - % -0
Median -0 - % -0
Cost of Equity ValueCo Relevered Beta
Risk-free Rate(1) - % Mean Target Target
Market Risk Premium(2) - % Unlevered Debt/ Marginal Relevered
Levered Beta -0 Beta Equity Tax Rate Beta
Size Premium(3) - % Relevered Beta -0 - % - % -0
Cost of Equity - %
WACC Sensitivity Analysis
Pre-tax Cost of Debt
WACC - % Debt-to-Total Capitalization 0.0 -1.0% -0.5% 0.0% 0.5% 1.0%
-20.0% 0.2% 0.1% 0.0% -0.1% -0.2%
-10.0% 0.1% 0.1% 0.0% -0.1% -0.1%
0.0% 0.0% 0.0% 0.0% 0.0% 0.0%
10.0% -0.1% -0.1% 0.0% 0.1% 0.1%
20.0% -0.2% -0.1% 0.0% 0.1% 0.2%
(1) Interpolated yield on 20-year U.S. Treasury
(2) Obtained from Ibbotson SBBI Valuation Yearbook
(3) Low-Cap Decile size premium based on market capitalization, per Ibbotson
(4) Sourced from Barra

A1

`
Assumptions Page 1 - Income Statement and Cash Flow Statement
Projection Period
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
Income Statement Assumptions
Sales (% growth) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
Cost of Goods Sold (% sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
SG&A (% sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
Depreciation & Amortization (% sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
Cash Flow Statement Assumptions
Capital Expenditures (% of sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0

A2

Assumptions Page 2 - Balance Sheet
Projection Period
Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018
Current Assets
Days Sales Outstanding (DSO) -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Base 1 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Management 2 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Upside 3 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 1 4 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 2 5 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Days Inventory Held (DIH) -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Base 1 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Management 2 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Upside 3 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 1 4 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 2 5 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Prepaid and Other Current Assets (% of sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
Current Liabilities
Days Payable Outstanding (DPO) -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Base 1 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Management 2 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Upside 3 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 1 4 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Downside 2 5 -0 -0 -0 -0 -0 -0 -0 -0 -0 -0
Accrued Liabilities (% of sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0
Other Current Liabilities (% of sales) - % - % - % - % - % -0 -0 -0 -0 -0
Base 1 - % - % - % - % - % -0 -0 -0 -0 -0
Management 2 - % - % - % - % - % -0 -0 -0 -0 -0
Upside 3 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 1 4 - % - % - % - % - % -0 -0 -0 -0 -0
Downside 2 5 - % - % - % - % - % -0 -0 -0 -0 -0

Rights

Copyright © 2009 by Joshua Rosenbaum and Joshua Pearl. All rights reserved.
Published by John Wiley & Sons, Inc., Hoboken, New Jersey.
Published simultaneously in Canada.
No part of this publication may be reproduced, stored in a retrieval system, or transmitted in
any form or by any means, electronic, mechanical, photocopying, recording, scanning, or
otherwise, except as permitted under Section 107 or 108 of the 1976 United States Copyright
Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per-copy fee to the Copyright Clearance Center, Inc., 222
Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 750-4470, or on the web
at www.copyright.com. Requests to the Publisher for permission should be addressed to the
Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030,
(201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/permissions.
Limit of Liability/Disclaimer of Warranty: While the publisher and author have used their
best efforts in preparing this book, they make no representations or warranties with respect
to the accuracy or completeness of the contents of this book and specifically disclaim any
implied warranties of merchantability or fitness for a particular purpose. No warranty may
be created or extended by sales representatives or written sales materials. The advice and
strategies contained herein may not be suitable for your situation. You should consult with a
professional where appropriate. Neither the publisher nor author shall be liable for any loss
of profit or any other commercial damages, including but not limited to special, incidental,
consequential, or other damages.
For general information on our other products and services or for technical support, please
contact our Customer Care Department within the United States at (800) 762-2974, outside
the United States at (317) 572-3993 or fax (317) 572-4002.
Wiley also publishes its books in a variety of electronic formats. Some content that appears
in print may not be available in electronic books. For more information about Wiley
products, visit our web site at www.wiley.com.
Library of Congress Cataloging-in-Publication Data:
ISBN-13 978-0-470-44220-3
Printed in the United States of America
10 9 8 7 6 5 4 3 2 1