Battey Unlimited Financial Planning and Forecasting Assignment Based on information provided in the Excel template, construct a financial model by projecting balance sheet and income statement data for the company for the next five years. In building your pro-forma financial forecasts, identify operating assumptions on the basis of the most recent threeyear average of balance sheet and income statement data. Estimate the AFN needed for each of the next five years on the basis of the following financing plans: a) AFN obtained by issuing long term debt b) 50% of AFN obtained by issuing long term debt and balance 50% AFN obtained by issuing short term debt. c) AFN obtained by issuing equity d) 50% AFN obtained by issuing long term debt and 50% by equity Compare the AFN needed, Debt/Asset Ratio, TIE, ROE, Earnings per share and dividends per share for each of the above four financing plans. Using the Financing Plan Requirement criteria provided in the Excel spreadsheet, which of the four financing plans would you recommend? Assumptions \begin{tabular}{|l|r|r|r|r|} \hline Operating Assumptions & Base Case Model & \multicolumn{2}{c|}{ Historical Analysis } \\ \hline Growth & 20.00% & 2017 & 2018 & 2019 \\ \hline \end{tabular} Operating Margins Cash/Sales Accounts Receivables/Sales Inventory/Sales Payables/Sales Accruals/Sales Net PPE/Sales \begin{tabular}{|l|l|} \hline Financing Plan Assumptions & \\ \hline Dividend Payout & 40.00% \\ \hline Interest Rate on Short and Long Term debt & 10.00% \\ \hline \end{tabular} Financing Plan Requirements \begin{tabular}{|l|l|l|l|l|l|} \hline Metric & Requirement based on five year average \\ \hline Debt/assets & Lower than 10% & \\ \hline TIE & Above 25 & \\ \hline EPS & Above 8 \\ \hline DPS & above 3.0 & \\ \hline ROE & Above 28% & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|} \hline Retained Earnings & $2,133 & $2,930 & $3,880 \\ \hline Total Shareholder Equity & $3,268 & $4,065 & $5,015 \\ \hline Total Liabilities +Equity & $6,580 & $7,822 & $8,983 \\ \hline \end{tabular} Proj Assets Proj Liab AFN Battey Unlimited Financial Planning and Forecasting Assignment Based on information provided in the Excel template, construct a financial model by projecting balance sheet and income statement data for the company for the next five years. In building your pro-forma financial forecasts, identify operating assumptions on the basis of the most recent threeyear average of balance sheet and income statement data. Estimate the AFN needed for each of the next five years on the basis of the following financing plans: a) AFN obtained by issuing long term debt b) 50% of AFN obtained by issuing long term debt and balance 50% AFN obtained by issuing short term debt. c) AFN obtained by issuing equity d) 50% AFN obtained by issuing long term debt and 50% by equity Compare the AFN needed, Debt/Asset Ratio, TIE, ROE, Earnings per share and dividends per share for each of the above four financing plans. Using the Financing Plan Requirement criteria provided in the Excel spreadsheet, which of the four financing plans would you recommend? Assumptions \begin{tabular}{|l|r|r|r|r|} \hline Operating Assumptions & Base Case Model & \multicolumn{2}{c|}{ Historical Analysis } \\ \hline Growth & 20.00% & 2017 & 2018 & 2019 \\ \hline \end{tabular} Operating Margins Cash/Sales Accounts Receivables/Sales Inventory/Sales Payables/Sales Accruals/Sales Net PPE/Sales \begin{tabular}{|l|l|} \hline Financing Plan Assumptions & \\ \hline Dividend Payout & 40.00% \\ \hline Interest Rate on Short and Long Term debt & 10.00% \\ \hline \end{tabular} Financing Plan Requirements \begin{tabular}{|l|l|l|l|l|l|} \hline Metric & Requirement based on five year average \\ \hline Debt/assets & Lower than 10% & \\ \hline TIE & Above 25 & \\ \hline EPS & Above 8 \\ \hline DPS & above 3.0 & \\ \hline ROE & Above 28% & \\ \hline \end{tabular} \begin{tabular}{|l|l|l|l|} \hline Retained Earnings & $2,133 & $2,930 & $3,880 \\ \hline Total Shareholder Equity & $3,268 & $4,065 & $5,015 \\ \hline Total Liabilities +Equity & $6,580 & $7,822 & $8,983 \\ \hline \end{tabular} Proj Assets Proj Liab AFN
