I have to come up with some numbers for a report I am writing on a stock. I am trying to estimate the future earnings and share price for Molson Coors (stock: TAP). I need to put together a Discounted Cash Flow (DCF) model to estimate the future price of the stock. Please see attached for the updated document. I have just changed the years referenced (2010A, 2011,2012,2013). You will also need a WACC to do this, which I was able to get from www.thatswacc.com (7.83%, also added to the attached) The professor provided us with an Excel tool we could use to make the model (attached). Not sure if you want to use it, or if you have your own method. Lastly, you have to make some assumptions to make this workand I would like to be a part of this. Can you email me when you need to make assumptions for growth? Each year should be 10% to 15% I think, but maybe you will find differently???Will you factor this in when working on the DCF and EPS estimates? I think each year should have slightly better growthbut I would like to hear what you think: something like 10%, 13% and 15%?? Please also provide me with a short document to outline your assumptions. I will need to present this to my class, so I will need to know what you did.Document Preview:Sheet1 BALANCESHEETS CASHFLOWSTATEMENTS CreditStatistics DEBTSCHEDULE INCOMESTATEMENTS opcase OPERATINGASSUMPTIONS ScenarioAnalysis SummaryFinancialResults VALUATIONANALYSIS WORKINGCAPITALASSUMPTIONS Historical Projected OPERATING ASSUMPTIONS Revenue Growth Cost of Goods Sold: COGS (Excl. Depn.) % Sales Depreciation Total COGS EBITDA Margin Operating Profit (EBIT) % of Sales Amortization Total SG&A Expense SG&A Expense (Excl. Amt.) SG&A Expense: Total Capital Expenditures INCOME STATEMENTS Gross Profit EBIT Interest & Other Expense / (Income): Revolver Term Loan Sr. Sub. Notes Total Interest Expense Financing Costs Amortization Pretax Income Net Income Shares Outstanding Earnings per Share (EPS) EBITDA Reconciliation: Plus: Depreciation Plus: Amortization EBITDA Less: Total COGS Less: Total SG&A BALANCE SHEETS ASSETS: Accounts Receivable Inventory Prepaid Expenses Current Assets PP&EGross Less: Accum. Depn. Net PP&E Intangibles Cap. Financing Costs Total Assets LIABILITIES & EQUITY: Accounts Payable Accrued Expenses Current Liabilities Revolver Term Loan Sr. Sub. Notes Total Debt Other Liabilities Total Liabilities Common Equity Liabilities & Equity Check Rate Less: Interest Income Less: Income Taxes Required Cash Excess Cash Total Cash WORKING CAPITAL ASSUMPTIONS Sales Total COGS Current Assets Accounts Receivable Inventory Prepaid Expenses Current Assets Current Liabilities Accounts Payable Accrued Expenses Current Liabilities Net Cash Impact Net Working Capital Cash (Used by) / Generated from Work. Cap. Ratios A/R % of Sales Days Receivable Inventory % of COGS Inventory Turns Prepaid % of COGS Accts Payable % of COGS Accrued % of COGS Required Cash % of COGS CASH FLOW STATEMENTS Operating Activities: Financing Costs Attachments: Examples-of-w.doc bby-12Comp.xls