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: 3 #DIV/0! Sustainable Growth Rate b( ROE ) 1 − b( ROE ) Style's Pro Forma Financial Statements Interest rate on new debt 10% Assumed growth rate 6.44% Dividend payout ratio 45% Rate of equity increase 1.13% Pro forma Income Statement: 2018 2019 Adjusted Sales $ 36,000 $ Op costs $ 32,440 $ EBIT Interest $ $ 3,560 560 $ $ (560) EBT Taxes (40%) $ $ 3,000 1,200 $ $ 560 224 Net inc $ 1,800 $ 336 Divs (45%) Add to RE $ $ 810 990 $ $ 151 185 Balance Sheet: Assets: Cash A/R Inv 2018 2019 1,080 6,480 9,000 $ $ $ - $ $ $ 16,560 12,600 29,160 $ $ $ - Liabilities & Equity: A/P $ Accruals $ N/P $ 4,320 2,880 2,100 $ $ $ - Cur liabilities LT debt Common stock RE Total L&E AFN Assets - L&E $ $ $ $ $ 9,300 3,500 3,500 12,860 29,160 $ - $ - $ - Additional interest-bearing liabilities Additional liabilities Ratio of liabilities to equity 78.24% The firm will borrow ST and LT Debt to keep a constant Liabilities/Equity ratio Lets' forecast 2015 line items Adjusted $ $ $ Cur assets Net PPE Total assets HOW MUCH ASSETS CAN GROW BY MAINTAINING A CONSTANT DEBT TO EQUITY FINANCING MIX? $ $ $ $ 13,045 $ 13,045 $ (13,045) $ (5,600) $ (12,800) -6926.41% Sustainable growth rate allows the user to ascertain how much assets can grow while still maintaining a stable debt ratio. Style's Pro Forma Financial Statements Interest rate on new debt Assumed growth rate Dividend payout ratio Pro forma Income Statement: 2018 2019 Sales $ 36,000 $ 41,400 Op costs $ 32,440 $ 37,306 EBIT Interest $ $ 3,560 560 $ $ 4,094 644 EBT Taxes (40%) Net inc $ $ $ 3,000 1,200 1,800 $ $ $ 3,450 1,380 2,070 Divs (45%) Add to RE $ $ 810 990 $ $ 932 1,139 Balance Sheet: Assets: Cash A/R Inv Cur assets Net PPE Total assets 2018 $ $ $ 1,080 6,480 9,000 $ $ $ 1,242 7,452 10,350 $ $ $ 16,560 12,600 29,160 $ $ $ 19,044 14,490 33,534 4,320 2,880 2,100 $ $ $ 4,320 2,880 2,100 9,300 3,500 3,500 12,860 29,160 $ $ $ $ $ 9,300 3,500 3,500 13,999 30,299 $ 3,236 Liabilities & Equity: A/P $ Accruals $ N/P $ Cur liabilities LT debt Common stock RE Total L&E AFN 2019 $ $ $ $ $ 10% 15.00% 45% HOW MUCH ASSETS CAN GROW IF FIRM HAS LIMITED ACCESS TO CAPITAL AND IT WILL USE ONLY INTERNALLY GENERATED FUNDS? In this case, we assume the growth rate is 15% and we will keep A/P,Accruals, N/P, LT Debt, Equity constant. Modified example - Revised IGR Style's Pro Forma Financial Statements Interest rate on new debt Assumed growth rate Dividend payout ratio Pro forma Income Statement: 2018 2019 Sales $ 36,000 $ 41,400 Op costs $ 32,440 $ 37,306 10% 15.00% 45% HOW MUCH ASSETS CAN GROW IF I ALLOW FOR CERTAIN WORKING CAPITAL LIABILITIES TO GROW SPONTANEOUSLY? We allow A/P and Accruals to grow spontaneously and Cash does not grow EBIT Interest $ $ 3,560 $ 560 $ 4,094 560 Use Revised IGR formula EBT Taxes (40%) $ $ 3,000 $ 1,200 $ 3,534 1,414 Revised IGR Net inc $ 1,800 $ 2,120 Divs (45%) Add to RE $ $ 810 $ 990 $ 954 1,166 Balance Sheet: Assets: Cash A/R Inv 2018 2019 $ $ $ 1,080 $ 6,480 $ 9,000 $ 1,080 7,452 10,350 $ $ $ 16,560 $ 12,600 $ 29,160 $ 18,882 14,490 33,372 Liabilities & Equity: A/P $ Accruals $ N/P $ 4,320 $ 2,880 $ 2,100 $ 4,968 3,312 2,100 Cur assets Net PPE Total assets Cur liabilities LT debt Common stock RE Total L&E $ $ $ $ $ 9,300 3,500 3,500 12,860 29,160 AFN Assets - L&E Internal growth rate: 5.02% $ $ $ $ $ 10,380 3,500 3,500 14,026 31,406 $ 1,966 (1 − d ) * NI ( A * − L*) − EBIT * (1 − T )(1 − d ) Style's Pro Forma Financial Statements Interest rate on new debt 10% Assumed growth rate 15.0% Dividend payout ratio 45% Pro forma Income Statement: 2018 2019 2020 2021 2022 Sales $ 36,000 $ 41,400 $ 47,610 $ 54,752 $ 62,964 Op costs $ 32,440 $ 37,306 $ 42,902 $ 49,337 $ 56,738 EBIT Interest $ $ 3,560 $ 560 $ 4,094 $ 4,708 $ 5,414 $ 6,226 560 $ 560 $ 560 $ 560 EBT Taxes (40%) $ $ 3,000 $ 1,200 $ 3,534 $ 4,148 $ 4,854 $ 5,666 1,414 $ 1,659 $ 1,942 $ 2,267 Net inc $ 1,800 $ 2,120 $ 2,489 $ 2,913 $ 3,400 Divs (45%) Add to RE $ $ 810 $ 990 $ 954 $ 1,120 $ 1,311 $ 1,530 1,166 $ 1,369 $ 1,602 $ 1,870 Balance Sheet: Assets: Cash A/R Inv 2018 2019 2020 2021 2022 $ $ $ 1,080 $ 6,480 $ 9,000 $ 1,242 $ 1,428 $ 1,643 $ 1,889 7,452 $ 8,570 $ 9,855 $ 11,334 10,350 $ 11,903 $ 13,688 $ 15,741 $ $ $ 16,560 $ 12,600 $ 29,160 $ 19,044 $ 21,901 $ 25,186 $ 28,964 14,490 $ 16,664 $ 19,163 $ 22,037 33,534 $ 38,564 $ 44,349 $ 51,001 Liabilities & Equity: A/P $ Accruals $ N/P $ 4,320 $ 2,880 $ 2,100 $ 4,968 $ 5,713 $ 6,570 $ 7,556 3,312 $ 3,809 $ 4,380 $ 5,037 2,100 $ 2,100 $ 2,100 $ 2,100 Cur assets Net PPE Total assets Cur liabilities LT debt Common stock RE Total L&E AFN Assets - L&E $ $ $ $ $ 9,300 3,500 3,500 12,860 29,160 $ $ $ $ $ $ $ 10,380 3,500 3,500 14,026 31,406 $ $ $ $ $ 11,622 3,500 3,500 15,395 34,017 $ $ $ $ $ 13,050 3,500 3,500 16,997 37,047 $ $ $ $ $ 14,693 3,500 3,500 18,867 40,560 $ $ $ 2,128 $ 4,547 $ 7,301 $ 10,441
Purchase answer to see full attachment