Corporate Financial Planning & FP&A
External Financing Needed (EFN) Sales Plug Formula
Forecast pro-forma balance-sheet funding gaps via External Financing Needed EFN = ((A* - L*) / S_0)*Delta_S - PM * S_0 * (1 + g) * (1 - d) and find the zero-EFN internal growth cutoff.
Why profitable fast-growing firms run out of cash: spontaneous working capital outpaces retained earnings!
FP&A teams building 3-statement budgets balance pro-forma assets and liabilities using the Percent-of-Sales External Financing Needed (EFN) equation! When Current Sales S_0 grow by rate g (Delta_S = g * S_0, Projected Sales S_1 = S_0 * (1 + g)), Spontaneous Assets A* (inventory, receivables, and full-capacity plant scaling proportionally with sales) require Incremental Asset Funding (A* / S_0) * Delta_S! Two internal sources partially offset that need: Spontaneous Liabilities L* (accounts payable and accrued wages — NEVER notes payable or long-term debt!) provide (L* / S_0) * Delta_S, and Retained Earnings from Net Profit Margin PM and Dividend Payout Ratio d provide PM * S_1 * (1 - d) — leaving External Debt/Equity Plug EFN = ((A* - L*) / S_0) * (g * S_0) - PM * S_0 * (1 + g) * (1 - d)!
Verify on Current_Sales S_0 = 5,000 ($k, B2), Spontaneous_Assets A* = 3,500 ($k, 70% of sales, B3), Spontaneous_Payables L* = 1,000 ($k, 20% of sales, B4 -> Net Spontaneous Capital Intensity = 50% of sales!), Net_Profit_Margin PM = 0.08 (8.0%, B5), Dividend_Payout_Ratio d = 0.25 (25.0% -> Retention b = 75.0%, B6), and Projected_Sales_Growth g = 0.20 (+20.0% -> Delta_S = 1,000 $k, S_1 = 6,000 $k, B7): Spontaneous Asset Increase is +700.00 ($k), Spontaneous Payable Offset is +200.00 ($k -> Net Asset Need = +500.00 $k), Projected Addition to Retained Earnings is 0.08 * 6,000 * 0.75 = +360.00 ($k), leaving External Financing Needed EFN = 500.00 - 360.00 = +140.00 ($k new debt/equity required — whereas at Self-Supporting Growth g_zero = (PM*b) / ((A* - L*)/S_0 - PM*b) = 0.06 / (0.50 - 0.06) = 0.1364 = 13.64%, EFN equals 0.00!)!
| Pro-Forma EFN Line (S_0=$5M, A*=$3.5M, L*=$1.0M, PM=8%, d=25%, g=20%) | Formula | Verified Output |
|---|---|---|
| Net Spontaneous Capital Need | ((A* - L*) / S_0) * (g * S_0) | +500.00 ($k) |
| Projected Retained Earnings Addition | PM * S_0 * (1 + g) * (1 - d) | +360.00 ($k) |
| External Financing Needed (EFN Plug) | Net_Asset_Need - Retained_Earnings | +140.00 ($k Deficit) |
| Zero-EFN Self-Supporting Growth Cutoff | (PM*(1 - d)) / (((A*-L*)/S_0) - PM*(1 - d)) | 0.1364 (13.64% Sales Growth) |
Pro-Forma External Financing Needed (EFN) Formula
Positive EFN > 0 signals an external borrowing/issuance requirement; negative EFN < 0 signals excess free cash available for debt paydown, buybacks, or cash reserves!
=LET(s_0, B2, a_sp, B3, l_sp, B4, pm_n, B5, d_po, B6, g_sl, B7, IF(OR(s_0 <= 0, a_sp < 0, l_sp < 0, d_po < 0, d_po > 1, g_sl <= -1), "Invalid FP&A Input", ROUND((a_sp - l_sp) * g_sl - pm_n * s_0 * (1 + g_sl) * (1 - d_po), 2)))Zero-EFN Self-Supporting Sales Growth Rate Formula
Solving EFN(g*) = 0 yields exact closed-form g* = (PM * (1 - d)) / (((A* - L*) / S_0) - PM * (1 - d))!
=LET(s_0, B2, a_sp, B3, l_sp, B4, pm_n, B5, d_po, B6, IF(OR(s_0 <= 0, a_sp <= l_sp, d_po < 0, d_po > 1), "Invalid FP&A Input", LET(rb_s, pm_n * (1 - d_po), cap_i, (a_sp - l_sp) / s_0, IF(cap_i <= rb_s, "Self-Funding At All Growth Rates", ROUND(rb_s / (cap_i - rb_s), 4)))))Try the example
Use the original values or change them to test your understanding.