2. Determine whether or not the financing needed exceeds the limit on the revolver account.
2a. If the needed financing exceeds the revolver account limit, the revolver maxxes out and any additional needed financing is at the additional LT debt rate.
(please show any excel formulas needed)
I J K L M N O A 1 Without the project INCOME STATEMENT Revenue COGS* SGA** EBIT*** D E F G H Hist Proj Year 0 Year 1 Year 2 Year 3 Year 4 160.0 171.2 183.2 196.0 209.7 80.0 80.5 86.1 92.1 29.0 29.1 31.1 33.3 35.7 51.0 61.6 65.9 70.6 75.5 Required return = 12% Revenue growth = 7% COGS = 47% of sales SGA = 17% of sales CA = 57% of sales LTA, Debt, and C/S will experience no change. 8 2.1 9 - 10 # Revolver Limit Interest: Excess cash Interest: Revolver Interest: Debt Interest: Add'l LTD EBT**** 3.0% 8.0% 9.0% 0.0 0.0 0.0 19.8 0.8 0.0 19.8 1.5 0.0 19.8 2.2 0.0 19.8 31.8 41.8 47.0 52.2 57.9 40.0% Tax Net income 12.7 19.1 16.7 25.1 18.8 28.2 20.9 31.3 23.2 34.7 19 0.0 Hist Proj Year 0 Year 1 Year 2 Year 3 Year 4 27.5 48.9 72. 9 99.8 100.0 97.6 104.4 111.7 119.5 350.0 350.0 350.0 350.0 350.0 450.0 475.1 503.3 534.6 569.4 BALANCE SHEET Excess cash Current assets Long-term assets Total assets 21 | 22 23 0.0 220.0 0.0 220.0 0.0 220.0 0.0 220.0 0.0 220.0 Revolver Debt Additional LT Debt Common stock Retained earnings Total liabs and equity 100.0 130.0 450.0 100.0 155.1 475.1 100.0 183.3 503.3 100.0 214.6 534.6 100.0 249.4 569.4 31 I J K L M N O A 1 Without the project INCOME STATEMENT Revenue COGS* SGA** EBIT*** D E F G H Hist Proj Year 0 Year 1 Year 2 Year 3 Year 4 160.0 171.2 183.2 196.0 209.7 80.0 80.5 86.1 92.1 29.0 29.1 31.1 33.3 35.7 51.0 61.6 65.9 70.6 75.5 Required return = 12% Revenue growth = 7% COGS = 47% of sales SGA = 17% of sales CA = 57% of sales LTA, Debt, and C/S will experience no change. 8 2.1 9 - 10 # Revolver Limit Interest: Excess cash Interest: Revolver Interest: Debt Interest: Add'l LTD EBT**** 3.0% 8.0% 9.0% 0.0 0.0 0.0 19.8 0.8 0.0 19.8 1.5 0.0 19.8 2.2 0.0 19.8 31.8 41.8 47.0 52.2 57.9 40.0% Tax Net income 12.7 19.1 16.7 25.1 18.8 28.2 20.9 31.3 23.2 34.7 19 0.0 Hist Proj Year 0 Year 1 Year 2 Year 3 Year 4 27.5 48.9 72. 9 99.8 100.0 97.6 104.4 111.7 119.5 350.0 350.0 350.0 350.0 350.0 450.0 475.1 503.3 534.6 569.4 BALANCE SHEET Excess cash Current assets Long-term assets Total assets 21 | 22 23 0.0 220.0 0.0 220.0 0.0 220.0 0.0 220.0 0.0 220.0 Revolver Debt Additional LT Debt Common stock Retained earnings Total liabs and equity 100.0 130.0 450.0 100.0 155.1 475.1 100.0 183.3 503.3 100.0 214.6 534.6 100.0 249.4 569.4 31
