A Forumla For Cash Flow Balancing

Every once in a while, I prove the old adage that "you'll never use high school math in real life" wrong, by encountering some problem that I have to bust out algebra skills to solve. I'm writing a quick little post about this particular problem I encountered, and solved with actual algebra, because I thought maybe there's someone out there wrestling with the same problem.

For some background: We (a business I'm the accountant for) have two bank accounts, one for our main operations, and one for less-critical operations (i.e., entertainment and merchandising). So I break our budget into the more critical items and the less critical items, and from that I calculate a "target balance" for each bank account, with the target being two weeks worth of expected expenses in each account. Every monday, we have some cash which should be divvied up into each account. The problem is, how do I know how much to put in each account, to keep the balances approaching that target of two weeks worth of expenses?

It seems like a fairly simple problem on the surface, but after sitting there scratching my head for a long time and trying several inadequate formulas, I decided algebra was the only way.

So I started with the following equation:

Ca = Cp + Cm

Where:

• Ca = cash available to deposit
• Cp = cash to be deposited at account p
• Cm = cash to be deposited at account m

What this equation is saying is that the total cash to be deposited equals the cash deposited in p plus the cash deposited in m. So far, so good.

The second equation I needed was this:

(Bp + Cp) / Tp = (Bm + Cm) / Tm

Where:

• Cp and Cm are the cash amounts to deposit (same as above)
• Bp and Bm = The current balanaces of account p and m respectively
• Tp and Tm = The target balances of account p and m

So what this equation is saying is that the proportion of the new balance of account p (that is, Bp + Cp) to the target balance Tp must equal the proportion of the same quantities in account m. That is, after we deposit our cash, the balances should constitute an equal proportion of that account's expected expenses.

So to solve the problem, I had to solve for either one of the variables Cp or Cm. In other words, given the cash available to deposit, the two current balances, and the two target balances, how much do I deposit into account m (and therefore, the rest into p)? So I decided to solve for Cm. First, I substituted the first formula in place of Cp in the second formula:

Ca = Cp + Cm => Cp = Ca - Cm

(Bp + (Ca - Cm)) / Tp = (Bm + Cm) / Tm

So this is the part that had me reaching into the depths of my algebra skills: Solving the above for Cm. Here's where it ended up:

Cm = ( (Bp + Ca) * Tm - Bm * Tp ) / (Tm + Tp)

Now I understand why I couldn't just come up with a quick easy solution! I never would have come up with that.

Here's how it works:

Every Monday, I get the current balances Bp and Bm. Let's say, Bp = 1,000.00 and Bm = 10,000.00.

Then I plug in the target balance of each account from my budget spreadsheet... let's say Tp = 5,000.00 and Tm = 20,000.

Then I count the cash available to deposit from the week's sales, let's say Ca = 8,000.00.

The formula becomes:

Cm = ( (1000 + 8000) * 20000 - 10000 * 5000 ) / (20000 + 5000)
Cm = 5200.00

What this is telling me is that I should deposit 5,200.00 into account m, and 2,800.00 (i.e., 8,000.00 - 5,200.00 ) into account p. This results in the following new balances:

Bm = 10000 + 5200 = 15200
Bp = 1000 + 2800 = 3800

So now the proportion of each account to its target balance is the same:

Bm / Tm = 15200 / 20000 = 76%
Bp / Tp = 3800 / 5000 = 76%

This says that the new balance after depositing cash will cover 76% of the target balance in each account... if, for example that target balance is equivalent to two weeks worth of expenses, the new balance can be expected to last for 76% of 14 days, or 10.64 days.

I hope this is helpful to someone out there!