Formula
Average deposits = mean of the first column; Average withdrawals = mean of the second; Average monthly net cash flow = Average deposits − Average withdrawals.
Deposit trend = (last month deposits − first month deposits) ÷ first month deposits. Deposit volatility = sample standard deviation of deposits ÷ Average deposits.
Months with withdrawals above deposits = count of rows where column 2 exceeds column 1. Lowest month-end balance = minimum of the third column.
Room for additional daily debit = (Average deposits × Maximum total debits as share of deposits) ÷ 21 business days − Existing daily financing debits.
Worked examples
Example 1: the six default months
Inputs: six rows of deposits, withdrawals and ending balance: 82,000 / 79,500 / 6,100; 91,000 / 88,000 / 9,100; 76,000 / 80,500 / 4,600; 88,000 / 84,000 / 8,600; 95,000 / 90,000 / 13,600; 84,000 / 86,500 / 11,100. Existing daily financing debits $400; Maximum total debits as share of deposits 15%. Deposits sum to 516,000, so Average deposits = 516,000 ÷ 6 = $86,000. Withdrawals sum to 508,500, Average withdrawals = $84,750. Net cash flow = 86,000 − 84,750 = $1,250 a month. Trend = (84,000 − 82,000) ÷ 82,000 = 2.4%, shown as 2%. Volatility: the sample standard deviation of the six deposit figures is $6,782; 6,782 ÷ 86,000 = 7.9%, shown as 8%. Months where withdrawals exceeded deposits: month 3 (80,500 vs 76,000) and month 6 (86,500 vs 84,000), so 2 of 6. Lowest month-end balance = $4,600. Debit room: 86,000 × 0.15 = $12,900 a month ÷ 21 = $614.29 a day, less the existing $400 = $214.29. A new advance debiting $214 a day for 160 business days at a 1.3 factor would be about $26,000, which is the estimate the tool shows.
Example 2: a declining account with no room
Inputs: four rows: 60,000 / 58,000 / 5,000; 52,000 / 55,000 / 2,000; 45,000 / 47,000 / 1,500; 40,000 / 44,000 / 900. Existing daily debits $600; cap 12%. Average deposits = 197,000 ÷ 4 = $49,250. Average withdrawals = 204,000 ÷ 4 = $51,000. Net cash flow = −$1,750 a month. Trend = (40,000 − 60,000) ÷ 60,000 = −33%. Volatility: standard deviation $8,694 ÷ 49,250 = 17.7%, shown as 18%. Three of four months spent more than they took in. Lowest balance $900. Debit room = 49,250 × 0.12 = 5,910 ÷ 21 = $281.43 − 600 = −$318.57. The negative room says the existing $600 daily debit already exceeds what a 12 percent cap allows on this deposit base by more than $300 a day. Note that the average alone, $49,250, would have supported a modest advance; the trend and the balance line show the business is burning its cushion at a rate that makes any new position a likely default within the term.
Example 3: a steady account under a conservative cap
Inputs: three rows: 120,000 / 110,000 / 30,000; 118,000 / 112,000 / 36,000; 125,000 / 115,000 / 46,000. Existing daily debits $0; cap 10%. Average deposits = 363,000 ÷ 3 = $121,000. Average withdrawals = 337,000 ÷ 3 = $112,333. Net cash flow = $8,667 a month, and the balance rose from 30,000 to 46,000, which is consistent with that net. Trend = (125,000 − 120,000) ÷ 120,000 = 4.2%, shown as 4%. Volatility: standard deviation $3,606 ÷ 121,000 = 3%. Zero months negative. Lowest balance $30,000. Debit room = 121,000 × 0.10 = 12,100 ÷ 21 = $576.19 a day with nothing existing. At 160 business days and a 1.3 factor that supports roughly $70,900 of new funding under the 10 percent cap. Only three months of data is a limitation: this could be the peak of a seasonal cycle, so an underwriter would ask for six to twelve months or the prior year's same quarter before funding at the full figure.