Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Income Statement, Balance sheet given, assist me how to do FCFF & DCF Model in excel. begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} hline multicolumn{11}{|l|}{ MediaCo-Income Stratement } hline multirow[t]{3}{*}{
Income Statement, Balance sheet given, assist me how to do FCFF & DCF Model in excel.
\begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{11}{|l|}{ MediaCo-Income Stratement } \\ \hline \multirow[t]{3}{*}{ (S in millions) } & \multicolumn{10}{|c|}{ Fiscal Year Ended December, } \\ \hline & \multicolumn{2}{|r|}{2011} & \multicolumn{2}{|r|}{2012} & \multicolumn{2}{|r|}{2013} & 2014 & 2015 & 2016 & 2017 \\ \hline & \multicolumn{2}{|r|}{ Actwal } & \multicolumn{2}{|r|}{ Actual } & \multicolumn{2}{|r|}{ Actial } & Actival & Projected & Projected & Projectid \\ \hline Retenue & s & 522.3 & s & 540.5 & s & 551.8 & S 535.1 & S 527.8 & S 522.3 & S 522.9 \\ \hline Growth & & & & 3.5% & & 2.1% & 3.0% & 1.4% & 1.0% & 0.1% \\ \hline Cost of Goods Sold & s & 306.1 & s & 313.1 & s & 328.5 & S 316.0 & S 313.3 & \$ 311.2 & S 306.1 \\ \hline Gross Profit & s & 216.2 & $ & 227.4 & s & 223.3 & \& 219.1 & $214.5 & $211.1 & S 216.8 \\ \hline Grass Margin & & 41.4% & & 42.1% & & 40.5% & 41.0% & 40.6% & 40.4% & 41.5% \\ \hline Selling, General \& Administrative & s & 155.4 & s & 162.5 & s & 154.8 & s 155.0 & $155.8 & $155.1 & s 154.8 \\ \hline Depreciation \& Amortization & & 14.8 & & 15.4 & & 14.9 & 16.6 & 16.0 & 16.0 & 14.2 \\ \hline Other Expenses / (Income) & & - & & - & & - & - & - & - & - \\ \hline Operating Income (EBIT) & $ & 46.0 & $ & 49.6 & $ & 53.6 & 47.6 & 42.8 & 40.0 & 47.8 \\ \hline Operating Margin & & 8.8% & & 9.2% & & 9.7% & 8.9% & 8.1% & 7.7% & 9.1% \\ \hline Interest Expense, Net & & 3.1 & & 2.1 & & 1.5 & 1.6 & 1.8 & 1.8 & 1.8 \\ \hline Other (Income) / Expenses & & 0.5 & & (1.6) & & 1.5 & 3.2 & 2.4 & (2.8) & 1.7 \\ \hline EBT & s & 42.3 & s & 49.1 & s & 50.6 & \begin{tabular}{ll} 5 & 42.7 \end{tabular} & $38.6 & 41.0 & $44.4 \\ \hline Taxes (1) & & 16.9 & & 19.6 & & 20.3 & 17.1 & 15.4 & 16.4 & 17.7 \\ \hline Net Income Arailable to Common & s & 25.4 & $ & 29.4 & $ & 30.4 & \& 25.6 & 23.1 & $24.6 & S 26.6 \\ \hline Add: Taxes & s & 16.9 & s & 19.6 & s & 20.3 & s 17.1 & 15.4 & 16.4 & S 17.7 \\ \hline Add: Interest expense & & 3.1 & & 2.1 & & 1.5 & 1.6 & 1.8 & 1.8 & 1.8 \\ \hline Add: Other (Income) / Expenses & & 0.5 & & (1.6) & & 1.5 & 3.2 & 2.4 & (2.8) & 1.7 \\ \hline Plus: Depreciation \& Amortization & & 14.8 & & 15.4 & & 14.9 & 16.6 & 16.0 & 16.0 & 14.2 \\ \hline Adjusted EBITDA & $ & 60.8 & $ & 64.9 & $ & 68.5 & \begin{tabular}{ll} $ & 64.1 \end{tabular} & \begin{tabular}{ll} $ & 58.7 \end{tabular} & 56.0 & $62.0 \\ \hline EBIIDA Margin & & 11.6% & & 12.0% & & 12.4% & 12.0% & 11.1% & 10.7% & 11.9% \\ \hline Capital Expenditures & 5 & 15.0 & 5 & 15.5 & 5 & 14.9 & \begin{tabular}{lr} 5 & 16.8 \\ \end{tabular} & 16.3 & 16.3 & \begin{tabular}{lr} 5 & 15.8 \\ \end{tabular} \\ \hline Footnotes: & & & & & & & & & & \\ \hline (1) Tax rate @ 40.0\% & & & & & & & & & & \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|c|c|c|c|c|} \hline \multicolumn{9}{|l|}{ MediaCo-Balanee Sheet } \\ \hline \multirow{2}{*}{\multicolumn{9}{|c|}{ Fiscal Year Ended December 31, }} \\ \hline & & & & & & & & \\ \hline & \multicolumn{2}{|c|}{2011} & \multicolumn{2}{|r|}{2012} & \multicolumn{2}{|c|}{2013} & \multicolumn{2}{|c|}{2014} \\ \hline \multicolumn{9}{|l|}{ CLRBENTASSETS } \\ \hline Cash and cash equivalents & $ & 33.3 & $ & 28.8 & $ & 34.9 & $ & 35.9 \\ \hline Accounts receivable & & 84.0 & & 94.5 & & 88.2 & & 85.3 \\ \hline Inventory & & 11.2 & & 12.6 & & 8.5 & & 9.9 \\ \hline Deferred tax asset and receivables & & - & & - & & - & & - \\ \hline Prepaid expenses & & 7.6 & & 12.4 & & 9.5 & & 12.1 \\ \hline Total Current Assets & * & 136.1 & $ & 148.3 & $ & 141.1 & $ & 143.2 \\ \hline \multicolumn{9}{|l|}{ LDNG TERMASSETS } \\ \hline Property and equipment & $ & 208.1 & $ & 222.8 & $ & 229.1 & $ & 243.9 \\ \hline Intangible assets & & 12.2 & & 12.2 & & 12.3 & & 12.2 \\ \hline Deferred compensation & & 14.8 & & 13.4 & & 16.8 & & 16.2 \\ \hline Other assets & & 25.8 & & 27.9 & & 30.2 & & 34.1 \\ \hline Total Long-Term Assets & & 260.9 & $ & 276.3 & $ & 288.4 & & 306.4 \\ \hline Total Assets & $ & 397.0 & $ & 424.6 & $ & 429.5 & & 449.6 \\ \hline \multicolumn{9}{|l|}{ CLURRENTLIABEILITIES } \\ \hline Accounts payable & $ & 17.5 & $ & 25.0 & $ & 22.0 & $ & 17.6 \\ \hline Acorued expenses & & 40.1 & & 36.8 & & 35.9 & & 37.4 \\ \hline Unearned revenue & & 15.6 & & 19.3 & & 17.2 & & 16.8 \\ \hline Other current liabilities & & 25.5 & & 29.1 & & 27.2 & & 25.9 \\ \hline Total Current Liabilities & & 98.7 & $ & 110.2 & $ & 102.3 & * & 97.7 \\ \hline \multicolumn{9}{|l|}{ LDNG-TERMLIABELITIES } \\ \hline Long Term Debt & $ & 114.6 & $ & 84.8 & $ & 63.5 & $ & 70.1 \\ \hline Deferred takes & & 97.1 & & 103.2 & & 106.3 & & 109.7 \\ \hline Other liabilities & & 47.6 & & 44.2 & & 43.4 & & 31.0 \\ \hline Total Long-Term Liabilities & $ & 259.3 & $ & 232.2 & $ & 213.2 & * & 210.8 \\ \hline Total Liabilities & * & 358.0 & * & 342.4 & * & 315.5 & * & 308.5 \\ \hline Paid-in capital & $ & 136.0 & $ & 149.8 & $ & 151.2 & $ & 152.6 \\ \hline Retained earnings & & (97.0) & & (67.6) & & (37.2) & & (11.5) \\ \hline Net stockholders' equity & & 39.0 & & 82.2 & & 114.0 & * & 141.1 \\ \hline Total Liabilities \& Stockholders' Equ & * & 397.0 & & 424.6 & & 429.5 & & 449.6 \\ \hline Net Working Capital & $ & 4.10 & $ & 9.30 & $ & 3.90 & $ & 9.60 \\ \hline Norsos & & & & 18% & & a & & t \\ \hline \end{tabular}Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started