Additional funds needed problems and solutions

FIN4934 George Washington Forecasting Additional Financing Needed Project
Subject
Business Finance

Course
FIN4934 Financial Modeling

Question Description
use AFN equation to solve problem, please do not use Excel, show your calculations. Just use calculator to get answer, put to word document.

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. If you take this approach, you should also adjust for any financing feedbacks. Or if you assume less equity, will the firm pay less in dividends? b. Adjusting cash as necessary. c. A combination of plugs. 6) Management assesses the plan, and may call for changes. An additional way to forecast would be to analyze the historical changes in assets as sales grow (or decline). This would best be accomplished through a regression methodology. Style's Pro Forma Financial Statements Interest rate on new debt Let's assume Income Statement: Sales Op costs 2019 10% Assumed growth rate in (Sales, Op. Costs, Cash, A/R, Inv, Net PPE, A/P, Accruals) 15% Dividend payout ratio 45% Pro forma 2018 2019 Adjusted $ 36,000 $ $ 32,440 $ - 2019 Initially keep Interest = Constant and N/P = Constant LT Debt =Constant Equity = Constant Let's forecast 2019 DEMONSTRATE HOW GROWTH HAS FINANCIAL IMPLICATIONS line items Q1. What changed by the same percentage as Sales? 1 EBIT Interest $ $ 3,560 560 $ $ - 2 3 EBT Taxes (40%) $ $ 3,000 1,200 $ $ - 4 Q2. What are the funding sources? Net inc $ 1,800 $ - Divs (45%) Add to RE $ $ 810 990 $ $ - Balance Sheet: Assets: Cash A/R Inv Cur assets Net PPE Total assets Transfer cells Ds to Es 1 2 $ $ $ 1,080 6,480 9,000 $ $ $ - $ $ $ 16,560 12,600 29,160 $ $ $ - (and therefore into equation d)), we can break this down into 2 equations and 2 unknowns (AFN and addition to R/E). Ultimately, we can define AFN (incorporating financing feedbacks) as: AFN = g ( A * − L*) − ( EBIT )(1 + g )(1 − T )(1 − d ) + I 0 (1 − T )(1 − d ) 1 − i (1 − T )(1 − d ) Where A* = assets that increase proportionally with sales L* = liabilities that increase proportionally with sales 2018 2019 $ Adjusted - 4,320 2,880 2,100 $ $ $ - $ $ $ $ 12,860 $ 12,860 Cur liabilities LT debt Common stock RE $ $ $ $ 9,300 3,500 3,500 12,860 Total L&E $ 29,160 $ - $ - g = growth rate in sales EBIT = operating income T = tax rate d = dividend payout ratio I0 = interest expense (ignoring any additional financing) i = interest rate on additional funds borrowed AFN Assets - L&E $ (12,860) NOTES:Use Tools Solver (add-in) to set Difference cell equal to 0 by changing the AFN cell. Note that total interest relies on the amount of AFN, thus impacting addition to RE. 3.51% 6.44% 78.24% NI ) TA NI 1 − b( ) TA b( Internal Growth rate Ratio of Liabilities to Equity 4 Q3. HOW CAN WE ACCOUNT FOR ADDED INTEREST? 1) Estimate the following system of equations (NOTE: the following process assumes that all new funds are borrowed at the beginning of the yr) a) Additional interest = additional funds borrowed (AFN) x cost of new borrowing b) (1+g) x Total assets = (1+g) x (A/P + Accruals) + (N/P + AFN) + LT debt + common stock + (R/E + Addition to R/E) c) NI = (Sales – Operating costs) x (1-T) – Interest x (1-T) d) Addition to R/E = NI x (1 – dividend payout ratio) In equation a), additional interest depends upon AFN (unknown). In equation b), we have 2 unknown variables, AFN and addition to R/E. Eq. d) shows that addition to R/E depends on NI (which is affected by how much interest is paid. So, substituting equation a) into c) Liabilities & Equity: A/P $ Accruals $ N/P $ Internal growth rate: Sustainable growth rate: