exercise for DB#2 Imaginary Company Current Share Price 50.00 Market Cap ($m) 1,000.0 Current Shares Outstanding (m) 20 Notes: Key Modeling Assumptions Forecast Period Terminal Year...

how much will it cost to complete the questions and spread sheet



exercise for DB#2 Imaginary Company Current Share Price50.00 Market Cap ($m)1,000.0 Current Shares Outstanding (m)20 Notes: Key Modeling AssumptionsForecast PeriodTerminal Year 201920202021202220232024202520262027202820292030 Modeling Framework: Focus: Assumptions: Revenue Growth Rate5.0% Operating Profit Margin25.0% Average tax rate20.0% Debt/Invested Captital [Excluding cash]50.0% Average Interest Rate on Debt5.0%5.0% Depreciation60.065.00assumes stright-line depreciation for the sake of simplicity Amortization- 0- 0 ActualForecastForecast PeriodTerminal Year INCOME STATEMENT ($m)201920202021202220232024202520262027202820292030 Revenues500.0525.0forecast using assumption about growth rate Operating Profit 125.0131.3forecast using assumption about operating profit margin Interest Expense25.025.0formula: interest expense = total debt * assumed interest rate Pretax Profit100.0106.3 Income Taxes20.021.3formula: income taxes = pretax income * assumed tax rate Net Income80.085.0 average tax rate20%20% Average shares outstanding (m)20.020.0should only change if predicting share issuance or buybacks End Period Shares Outstanding20.020.0 reference: change in share count- 0 Earnings Per Share4.00formula: net income/average shares outstanding Dividend Per Share1.50formula: dividends/average shares outstanding Key Non-GAAP Measures EBIT125 EBITDA185 Forecast PeriodTerminal Year BALANCE SHEET ($m)201920202021202220232024202520262027202820292030 ASSETS Cash & Equiv Receivables Inventories Prepaid Expenses other current assets Total Current Assets Gross PP&E Net of Accumulated Depreciation Net PP&E Total Assets Liabilities Accounts Payable Notes Payablethisis short-term debt Accrued Expenses Taxes Payable Current Portion of Long-term Debtthisis short-term debt Other Current Liabilities Total Current Liabilities Long-Term Debt Total Liabilties Common Shareholder's Equity Paid-In Capital Retained Earnings Less treasury stock Common Shareholder's Equity Key Non-GAAP Measures Cash & Cash Equivalents50.050.0year to year change needs to be consistent with predicted change in cash (at the bottom of the cash flow statement) Net Working Capital (Excluding Cash)150.0165.0year to year change needs to be consistent with Net Working Capital Investment (on the cash flow statement) Total Net Working Capital200.0215.0 Gross PP&E1,200.01,300.0year to year change needs to be consistent with Capital Investment (on the cash flow statement) Accumulated Depreciation(400.0)(465.0)year to year change needs to be consistent with Depreciation (on the cash flow statement) Net Property, Plant & Equipment800.0835.0year to year change needs to be consistent with Capital Investment - Depreciation (on the cash flow statement) TOTAL: Net Assets1,000.01,050.0year to year change needs to be consistent with total investment spending - net noncash adjustments from the cash flow statement excluding cash950.01,000.0 Interest Bearing Debt475.0500.0year to year change needs to be consistent with net debt issued or retired (on the cash flow statement) Common Shareholder's Equity525.0must be consistent with BOTH balance sheet identity AND predictions for equity issuance, share buybacks & dividend payments on the cash flow statement Invested Capital (est)1,000.0 For reference: Net Equity Issued (or Repurchased)Pulled directly from cash flow statement Change in Retained EarningsFormula: Change in Retained earnings = Net Income - Dividends Change in Shareholder's Equity Forecast PeriodTerminal Year CASH FLOW [Non-GAAP]201920202021202220232024202520262027202820292030 Step 1: Estimate Operating Cash Flow EBIT * (1-tax rate)100.0Need to calculate this using balance sheet data Plus Depreciation & Amortization60.0See assumptions box for this information Operating Cash Flow160.0 Step 2: Estimate Investment Spending Net Working Capital Investment(10.0)Need to calculate this using balance sheet data Capital Investment (CAPX)(90.0)Need to calculate this using balance sheet data Total Investment Spending(100.0) Step 3: Estimate FCF to the Firm60.0 Step 4: Estimate Cash paid to Creditors Net Debt Issued (or Retired)- 0Need to calculate this using balance sheet data Less After-Tax Interest Expenses(25.0)Need to calculate this using income statement data Cash From Creditors(25.0) Step 5: Estimate FCF Available to Shareholders35.0 Step 6: Estimate Cash paid to Shareholders Net Equity Issued (or Repurchased)- 0we get to decide what to predict here Less Dividends on Common Stock(30.0)we get to decide what to predict here Cash From Shareholders(30.0) Step 7: Change in Cash Balance5.0the answer to this this will affect the forecast for cash on the balance sheet! Free Cash Flow Estimates FCF to the FIrm FCF to EquityDefinition: OCF - Investments - Payments to Creditors - Payments to Preferred Shareholders Reference: FCF to Equity LESS DIvs & Buybacks *estimation error or cash banked for later? ADDITIONAL DETAIL: Revenues & COGSERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Revenues COGS Gross Profits Useful Ratios: Revenues/Invested Capital Gross Profit Margin *Note: you may also enter any other data here that helps you forecast/understand revenues!*you may add as much detail to this as is helpful! Suggestions: detail on revenues by product or division, data on sales volumes & pricing, estiamtes of market size & market share. ADDITIONAL DETAIL: Operating ExpensesERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Operating Expenses SG&A (ex depreciation)*you may add as much detail to this as is helpful! Depreciation & Amortization Operating Expenses Useful Ratios: Operating Expenses/Revenues Operating Expenses (ex D&A) /Revenues Note: if there's something interesting going on with expenses, feel free to model them in detail! ADDITIONAL DETAIL: TaxesERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Pretax Profits Income Taxes Average Tax Rate Note: if there's something interesting going on with taxes, feel free to model them in detail! *you may add as much detail to this as is helpful! Examples: deferred tax assets, etc ADDITIONAL DETAIL: Net Working Capital ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Reference: GAAP inputs current assets cash accounts receivable inventories other current assets total current assets current liabilities (ex debt) accounts payable accruals other current liabilities (ex debt) total current liabiliities (ex debt) Our Conversion: net working capital cash required to support operatiions accounts receivable inventories other current assets LESS accounts payable LESS accruals LESS other current liabilities (ex debt) net working capital excess cashnote: just zero this out unless you have a strong opinion on the suject cash LESS cash required to support operations excess cash Tracking change % change in net working capital % change in revenues % change in COGS Ratio Analysis Aggregate Ratios Net Working Capital/Revenues Ratios for Line Items Inventory Turnover Accounts Receivable Turnover Accounts Payable Turnover Cash Cycle Days of Inventory Days of Receivables Days of Payables Cash CycleDefinition: Days of Inventory + Days of Receivables - Days of Payables ADDITIONAL DETAIL: PP&E, DepreciationERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! From Balance Sheet: Gross PP&E LESS Accumulated Depreciation Net PP&E Changes in Balance Sheet Values: Change in Gross PP&E Change in Accumulated Depreciation Change in Net PP&E Connecting Growth with Investment Requirements: Maintenance CAPX - Depreciation Growth CAPX CAPX - Depreciation A closer look at maintenance CAPX: The simplest assumption is that Maintenance CAPX = Depreciation, so that Net PP&E tracks changes in production capacity Implication: Maintenance CAPX - Depreciation = 0 A closer look at growth CAPX: Net PP&E at end of previous period Desired growth CAPX required to support that growth Net PP&E at end of period Depreciation Schedule can either model in detail or assume depreciation is a fixed % of PP&E (user's choice) ADDITIONAL DETAIL: Goodwill & IntangiblesERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Goodwill Intangibles Amortization of Intangibles Amortization Schedule either model in detail or assume amortization is a fixed % of Intangibles (user's choice) ADDITIONAL DETAIL: Net Other AssetsERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! *Enter details on deferred tax liabilities, underfunded pensions, etc (whatever is appropriate here) ADDITIONAL DETAIL: Debt & Interest ExpenseERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Debt Detail Short-Term Debt Long-Term Debt Total Debt Interest Rates Short-Term Debt Long-Term Debt Total Debt Interest Expense For Short-Term Debt For Long-Term Debt For Total Debt Ratios: Use of Debt St Debt/Net Working Capital LT Debt / Net PP&E & Intangibles Total Debt/Invested Capital We can forecast how debt will change over time using either approach below: Estimate Level: Total Debt = [target debt/IC ratio] x Invested Capital Calculation: Total Debtthis is easiest Estimate Change: Net New Debt = [target debt/IC ratio] x change in IC Calculation: Net New Debtthis feels more intuitive to me Implies Total Debt = Previous Level + Change ADDITIONAL DETAIL: Shareholder's EquityERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Preferred Stock Common Shareholder's Equity Contributed CapitalSimplified: Defined as Common Stock + Paid-In Capital - Treasury Shares Retained Earnings Common Shareholder's Equity Total Shareholder's Equity Connecting Calculation: Net Income Less Dividends Plus Net Equity Issuance (Buybacks) Addition to Retained Earnings Modelng Check: Is Addition to Retained Earnings = Change in Retained Earnings from the balance sheet? ...it should be! Forecast PeriodTerminal Year Ratio Analysis: 201920202021202220232024202520262027202820292030 Returns ROIC Definition: EBIT x (1 - average tax rate) / Invested Capital ROEDefinition: Net Income Available to Common Shareholders / Shareholder's Equity Profit Margins Gross Profit MarginDefinition: Gross Profit/Revenues Operating Profit MarginDefinition: Operating Profit/Revenues Net Profit MarginDefinition: Net Income Available to Common Shareholders / Revenues Asset Efficiency Revenues/Net Working Capital Revenues/PP&E Revenues/Invested Capital inventory turnover receivables turnover payables turnover Cash Cycle Days Inventory Days Receivables Less Days Payables Cash Cycle Use of Debt Operating Profit/Interest Expense Interest Expense/Interest Bearing Debt Debt/Invested Capital Debt/Equity Ratio Shareholder Payouts Dividend Payout Ratio Dividends & Buybacks/Net Income Free Cash Flow to Equity/Net Income Reminder: Dupont Formulas ROIC = [Revenues/Invested Capital] x [Operating Profit Margin] x [1 - average tax rate]reference: algebra & common sense ROE = ROIC + (Debt/Equity Ratio) * [ROIC - {(average interest rate on debt) * (1 - average tax rate)}]reference: texbook (chapter 3) Forecast PeriodTerminal Year Growth Rates201920202021202220232024202520262027202820292030 Income Statement % change in revenues % change in operating profits % change in net income Cash Flow Statement % change in Operating Cash Flow % change in Free Cash Flow to the Firm % change in Free Cash Flow to Equity Balance Sheet % change in net working capital % change in net PP&E % change in invested capital % change in total debt % change in shareholder's equity Cost of Capital Estimates Cost of Equity Risk Free Rate (for equity) Beta Market Risk Premium Cost of Debt Risk Free Rate Credit Risk Premium Credit Rating Average Maturity of Debt Market Value of Equity Market Value of Debt Weighted Average Cost of Capital *Note: feel free to add more lines to estimate these (or estimate on a sepate tab & just pull your numbers) Forecast PeriodTerminal Year Valuation Ratios201920202021202220232024202520262027202820292030 For Equity Price/Earnings Price/Book Value Price/ Sales Dividend yield Free Cash Flow Yield For Firm EV/EBITDA EV/Invested Capital EV/ Sales Free Cash Flow Yield Other (esoteric) Forecast Period Terminal Year DCF Model201920202021202220232024202520262027202820292030Note: you can value either equity or the firm by using different free cash flow estimates (& discount rates) For Reference1.02.03.04.05.06.07.08.09.010.011.0Terminal Year 2018.0 Free Cash Flow Estimates Free Cash Flow to the Firm Free Cash Flow to Equity Discount Rate Cost of Equity WACC PV of Cash Flow from Forecast Period Discount Factor PV Estimates for each year PV of Cash Flow from forecast period Terminal Value Terminal Cash Flow Terminal Growth Rate Terminal Value Estimate PV of Terminal Value Final Year of Forecast Discount Factor PV of Terminal Value Estimated Value PV of Cash Flow from forecast period PV of Terminal Value Value of Unproductive Assets (optional) Total Estimated Value Implications Estimated Value of Firm Estimated Value of Debt Estimated Value of Equity Implied Vaue of Equity/Share Name: _____________________________________ Discussion Brief #2: Forecasting Exercises The following assignment asks you to work through some of the elements of our forecasting problem so that we can discuss how to build a set of connected forecasts next week. It is meant to be used with the spreadsheet that accompanies this assignment, but may also be worked by hand using the data in Appendix 1&2. Questions: 1) One of the most important aspects of financial modeling is understanding how the financial statements connect together. This exercise asks you to use