Budget Planning Tool

I wish to share with you an Excel tool I created and use for years to manage our family budget - Cash Flow Planner.

Cash Flow Planner - Description

The Excel contains several tabs (pre-formatted for proper printing, so please avoid changing column width):

• Cash Flow - budget tracking, planning, and cash flow forecast ledgers:
  • family expenses for 2009-2010 + 30-years forecast
    • detailed by categories and items
    • includes permanent and variable items (the last marked in yellow)
  • independent business expenses (for optional use)
  • income and balance
    • income ledger
    • balance sheet
    • cash flow (supports 2 accounts)
• Withdrawal - details variable expenses (ledgers)
• Benefits - cash equivalent of savings and benefits deducted from salary
• Averages - planned and actual average family spending by category
  • Deviations from planned category spending percentage are shown by color.

Planned average spending by category is set according to following recommendations (the totals are more important than specific distribution by category, which can vary):

• Necessary expenses + insurance: 50%
• Life improvement, vacation, education - 30%
• Long-term savings - 20%.

Initial Setup Advice

1. Set current year (just change $E2 if it's not 2009)
2. Customize permanent expenses and benefits (don't change the overall structure)
3. If you have 2 accounts, allocate each expense and income to relevant account (Acc1 or Acc2)
4. If you are paying mortgage(s), and wish to track average distribution by category, I recommend to split each payment into interest payment and capital repayment parts and allocate capital repayment to Savings & Investments category (monthly interest payment can be roughly calculated as <average interest rate> * <loan amount> / 24)
5. Populate permanent monthly income & expense items (actual & forecast) from current year start till end of next year
6. Check the average yearly balance for this and next year (lower yellow cell), and spread the amount between variable ledgers (in yellow) according to desired expenses percentage per category, so that, if possible, Balance will be just below zero (but - be frank with yourself).
7. Choose the date in month for which monthly balance will be drawn; this usually should be the first date after all payments and income for the preceding month are withdrawn
8. As part of one-time setup activity, populate Acc1 and (if applicable) Acc2 cash amounts (in the lines with green cells at the bottom) for the month of start balance date (e.g., if start balance date is April 11, then populate for April column)
9. Review the cash flow forecast; if it falls below desired amount for some months / accounts, you may need to re-distribute expenses between the accounts; if doesn't help - consider either spending reduction, or a loan / short-term savings withdrawal, to stay within the account limits you (or your bank) have set.

Monthly Audit Process

Once a month, after balance date reached, perform the audit process:

1. Modify forecast figures for the last month' permanent expenses / income according to actual figures (permanent doesn't mean there is no fluctuation from month to month, year to year)
2. Complete the Withdrawal sheet for the past month, so that all variable expenses are listed (you may track using your credit card data, or by saving the receipts, or both combined)
3. Update the variable expenses for the last month according to actual totals, so that cash amounts for the current month will correspond to actual amounts on the accounts 
4. Update the bi-yearly forecast where needed
5. Check the yearly average balance and cash flow for Acc1 / Acc2 for not falling below your targets; if it does - make corrections to your spending plan for upcoming month(s)
6. Check the expense spread by category; if it starts to deviate strongly from your targets - make corrections to your spending plan (especially, variable ledgers).

Yearly Setup Process

At the beginning of a new year, do the following setup:

1. Archive the budget excel with name like Budget-YYYY (last year)
2. Change the year ($E2)
3. Copy the figures from next year to the current year
4. Modify cash flow figures (green cells) for January this year according to January audit

---

I hope these explanations will help you to get around with the Excel. In any case - don't hesitate to comment here in the blog.

To expand your knowledge, I recommend this excellent article in Get Rich Slowly blog: 13 tools to build better personal budget. It mentions additional budget tools (including Excels and on-line Web tools).

To finalize: no matter how you plan your budget - please do it! This is one of the foundations of your personal productivity.

This article was mentioned by the following blog carnivals: Money Hacks Carnival, Carnival of Financial Planning, Carnival of Wealth, Money, and Life.