Small Business Solution: Manage Your Money in Excel

Tracking your cash flow is a smart way to help you ensure small business success. You can create a simple cash flow budget in Excel to project the amount of cash you’ll have in the bank throughout the year. Then, if you see a few lean months coming down the road, you have time to prepare for them.

Set up income and expenses categories, and then enter your budgeted expenditure and income based on your past history and future plans. (Click for larger image).

Create a Budget

Create a cash flow budget in Excel by listing your sources of income and categories of expenditure. You can find much of this information in your small business accounting software or end-of-year tax reports.

Open a new worksheet and enter these income sources and expense items into two separate groupings in column A. Leave two blank rows between income and expense items. Across the top of the worksheet, enter the months January to December.

Opposite each income and expense item for each month of the year, enter the amount you anticipate you will receive or payout in that month. When the amount is the same for all months, enter one month’s data and drag on the fill handle in the bottom right corner of the cell to enter the value into the remaining months of the year.

Enter all the estimates and then total the monthly income and expense amounts. Below the total expenses for the first month you can calculate the month-end bank balance. The balance is your starting bank balance, plus the total income and minus expenses for that month.

In the next cell to the right build another formula — in this case the starting bank balance will be the value in the cell to the left. Once you have built this second formula you can drag across the remainder of the cells in this row to fill them.

Now you can see your anticipated bank balances and you can see – and plan for — those months where cash flow might be low.

As each month ends, you can enter data into your actuals worksheet to track your progress. (Click for larger image).

Track Your Actuals

While this cash flow budget is useful for planning purposes, as the year progresses, the actual amounts of income and expenses will impact the remainder of the year’s budget. You can track these changed conditions by creating an “Actuals” worksheet. Copy the budget worksheet by right-clicking the Budget sheet tab and choose cash flow. Select the cash flow checkbox and click OK. Double-click this sheet’s tab and name it Actuals.

Each month, when you know your month’s income and expenditure, enter those values in the Actuals worksheet in place of the budget values. Color the columns that contain actual rather than budget data so you can see which is which.

Monthly Analysis

On a month-by-month basis, it helps to analyze budget and actual expenses side by side. Create a new sheet and copy the headings you are using to categorize income and expenses from column A of the budget worksheet.

Copy the budget and actual figures for that month from Budget and Actual worksheets into columns B and C of your sheet or, better still link to the cells containing the original data. Click in the cell you want to place the value, press the equal sign (=), and then navigate to the sheet and cell containing that data and press Enter. Once you link the contents of one cell, you can drag to fill the remainder of cells in that column.

In column D, write a formula to subtract the Budget expenditure from the Actual expenditure — it will look like this:

On a month-by-month basis, you can track the differences between budgeted and actual expenditures to better analyze your business progress. (Click for larger image).

=C4-B4

Copy this formula down column D and then total the results for income and for expenses so you can see how you are performing relative to your budget. To convert these variance values to a percent, divide the value by the budget amount using a formula such as:

=D4/B4

A positive difference in income or a negative difference in expenses is a good sign.

Visual Analysis

To highlight better-than-expected results, use conditional formatting . Select all the income values, and assuming they start in row 4, choose Format > Conditional Formatting and set Formula Is to

=$D4>0

Set a format for it such as a colored cell fill, and click OK. Income items where your actual income is more than your budgeted amount will be colored to highlight them. To highlight poorer-performing areas, use this formula:

=$D4<0

To do the same for expenses, select all expense values and, assuming they begin in row 10, use this formula to highlight actual expenses less than budget (good):

=$D10<0

Or use this to highlight actual expenses more than budget (not so good):

=$D10>0

Once you have created a sheet for the first month of the year, copy it to create a worksheet for the second month. Replace the budget and actual figures in the worksheet with the budget and actuals for the second month. Leave the formulas in place as they will recalculate automatically.

A simple tracking tool like this budget workbook can help you monitor your business expenses and income on a monthly basis. You will have a clearer picture of how your business is performing, and you will be able to see where problems might occur and have time to plan accordingly.

Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com