Afn equation excel

Using the Plug: Forecasting Additional Financing Needed Go to Blackboard under class 4 materials and use the data from the spreadsheet we discussed in class. (The base year is in column C): 1) Use the “additional funds needed” (AFN) equation illustrated in class to find Style’s forecasted AFN under the following different scenarios (presuming AFN is borrowed or reflects debt repaid): • growth rate = 25% • growth rate = 0% • payout ratio = 20% (with growth rate = 15%) • payout ratio = 70% (with growth rate = 15%) • interest rate = 4% (with growth rate = 15% and payout ratio = 45%) • interest rate = 20% (with growth rate = 15% and payout ratio = 45%) 2) For each scenario, what are the AFNs if cash (not Notes Payable) is the balancing figure? g ( A * − L*) − ( EBIT )(1 + g )(1 − T )(1 − d ) + I 0 (1 − T )(1 − d ) 1 − i(1 − T )(1 − d ) OBS: In the above mentioned scenarios only g, d and i are allowed to vary. Show your calculations. 3) How does AFN vary with: • growth rate? • payout ratio? 4). Assuming that in 2018 (base year) fixed assets had been operated at only 75% capacity, under the scenario of 25% growth rate what can you say about the AFN? 5). If Sales would increase to $50,000 what would the Fixed Assets requirement be? 6). How would excess/deficit capacity affect the forecasted ratios? Developing Pro Forma Financial Statements 1) Develop a sales forecast. 2) Based on forecasted sales, create a pro forma income statement. 3) Develop a preliminary forecasted balance sheet. In doing so, you must predict how the forecasted change in sales will affect the need for additional assets (both current and fixed assets). Additionally, will changes in sales precipitate any additional liabilities? This must also be incorporated into the balance sheet forecast. Finally, leave capital accounts (i.e., equity and long-term debt) unchanged. 4) After the first 3 steps, you will notice that this pro forma balance sheet will be unbalanced (i.e., assets not equal to liabilities plus shareholders equity). 5) The “gap” in the balance sheet may be eliminated by using one or a combination of plugs: a. incorporating additional (or subtracting) financing (i.e., assuming more or less debt OR more or less equity) into the balance sheet forecast.