Archive for the ‘D.I.Y.’ Category

How to use Microsoft Excel to manage finances

In a previous article, I mentioned how to use online sites to see and keep track of how your money is spent. Another way of managing finances and tracking expenses is done using Microsoft Excel. On the Microsoft Office site a search for “Personal Finance” in templates will yield a list of different templates that can be used. In this article, I will show how to use this template (Download Here) to create a monthly budget. Even without an overall grasp of Excel, this template makes life easier as you don’t have to do any calculations, just plug it in and the spreadsheet does the rest.

Excel_Overall

The template is divided into four main areas:

  1. Income
  2. Itemized Amounts
  3. Total Cost
  4. Balance

Income

Excel_1

At the top of the spreadsheet is the Income Section, the money in (link) part of the equation. It is divided into the Projected Monthly Income and Actual Monthly Income. The value of the Expected Income is placed into the Income 1 box (shown here as $1000). If more than one source of income is expected, the other amount(s) can be placed in Extra Income. This is especially useful if paychecks arrive biweekly or when income is generated from more than one source. The total is automatically summed in the Total Monthly Income.  Once the actual paycheck is received, these values can be placed in the Actual Monthly Income section.

Itemized Amounts

This is the best feature of the spreadsheet. This section is divided into

  • Housing
  • Transportation
  • Insurance
  • Food
  • Pets
  • Personal Care
  • Entertainment
  • Loans
  • Taxes
  • Savings or Investments
  • Gifts & Donations
  • Legal

Each of these sections is further sub-divided into other areas.

Excel_2

Most areas of spending are covered in the sections and sub-sections and the names can be changed to better reflect your own costs. Just like the income section, the amounts are divided into Projected and Actual. In addition, when the Actual value is below the Projected value, the Difference shape will remain a green circle. However when the Actual value exceeds the Projected value, the Difference shape changes to either a yellow triangle (difference is less than $20) or a red diamond (difference is greater than $20). Finally, the total is automatically summed up at the bottom of each section.

Total Cost

Excel_3

After all values are placed in the Cost Section, the respective totals are calculated at the bottom of the spreadsheet. If the Total Projected Cost is less than the actual value, the difference in cost shows up red and in parenthesis to denote a negative value (as in the graphic shown above).

Balance

Excel_4

This section is the difference between the Income and Expenses. If the Projected Cost is greater than the Projected Income, the Projected Balance will be negative and will appear in red. The same applies to the Actual Balance. The above Actual Balance here is negative because while I was making this spreadsheet, I didn’t put in a value for the Actual Income.

Overall, this spreadsheet helps to better plan your finances and make sense of how your money can be spent in the coming month. Like planning and organizing time, organizing finances in this manner help to get a better picture of where you can spend less and cut costs. I hope this tool can be as useful to you has it has been to me this past year.

Note: The Savings Section of the spreadsheet (although subtracted from the income) is still part of your Networth, but is here as an expense because it’s coming out of the paycheck.