By September 27, 2011 5 Comments Read More →

How To: Create Personal Finances Spreadsheet Using Microsoft Excel

imageApplies To

  • Personal Finances Management
  • Microsoft Excel

Overview

This How-To walks you through step-by-step procedures of creating personal finances management spreadsheet using Microsoft Excel. This How-To requires Microsoft Excel as a prerequisite. Use this spreadsheet to plan and monitor your monthly spending to make sure you live below your means. This can help you avoid getting into debt in first place and even save some extra cash.

Summary of Steps

  • Step 1 – Basic settings
  • Step 2 – Plan monthly spending for key categories
  • Step 3 – Estimate spending for the month

Step 1 – Basic Settings

  1. Run Microsoft Excel.
  2. Name cells A1, B1, C1, D1, and E1 Date, Visa, Income, Debit, Balance, these will serve to capture daily spending and income plan and actual execution:
    image
  3. Double click on the tab at the bottom and change the name to current month:
    image
  4. Type the first day of the month in A2 cell, in our case it is 9/1/2011. Depending on your locale the format may vary. For example, Europe format would be 1/9/2011.
  5. Drag the corner of the cell downwards until it fills the rest of cells up to the last day of that month, in our case it is 9/30/2011.
    image
  6. Specify the following formula in the E2 cell, right under the Balance, =C2-D2.
  7. Specify the following formula for the E3 cell =E2+C3-D3. Drag this formula for the rest cells in the Balance (E) column. It will calculate daily balance based on the previous day balance and daily expenses.
  8. Name G1, H1, I1, J1 cells Category, Plan, Actual, Saved these will hold aggregated view on planned vs. actual spending.
  9. Specify key spending categories under the Category, here are mine:
    image
  10. For each category define formula for Saved column as Plan Actual. For example, for Rent it will be =H2-I2, for Utilities it will be =H3-I3.
  11. Specify the formula for the TOTAL row under Plan and Actual as the sum of all categories, =SUM(H2:H10) and =SUM(I2:I10) respectfully.
  12. This is how the end result should look like:
    image
  13. Next, you will plan total spending for each category.

Step 2 – Plan Monthly Spending For Key Categories

  1. Review each spending category and provide your guestimates for each one.
  2. Here is how it may look (I have chosen arbitrary but close to reality numbers for the family with 3 kids and one big dog):
    image
  3. If the total planned amount is above or close to your actual net income revise your spending and make it smaller. Don’t cut on unexpected category – those will come unexpectedly so you better keep some reserve there.
  4. Once you are happy with planned monthly spending and it is below your means you can now do  a testing of your plan.

Step 3 – Estimate Spending For The Month

  1. Now look at each category and specify when during the month and how (debit or visa) it will be covered.
  2. The rent would probably be paid once.
  3. The food would probably be paid weekly using credit card, same with the gas, but car insurance may actually paid using scheduled wire transfer similar to EasyPay.
  4. Each time you specify planned spending add it to the related category in the Actual column.
  5. This is how the monthly plan may look. Notice how daily planned expenses sum up to relevant categories on the right in Actual column. It is now easy to see if the plan is reasonable or not.
    image
  6. Notice that right before the paycheck on September 15 you are running the risk of getting into overdraft. It’s a good insight to have right in the beginning of the month. That way you can adjust your spending behavior or make sure you have enough funds beforehand.
  7. In the income you will specify any deposits into your bank account. First day deposit is the balance from the previous month, then paychecks on 15th of each month and last day of the month and others.
  8. When you receive a credit card statement that usually gets charged from your account in the next month go to the next month and specify expected expense. In our case there is 1500 debit expense – this is actually the statement from previous month, August.

Now you have your monthly plan. As the month starts record your spending in the spreadsheet. It should show you how realistic or not you planned your month.

Related

image by Images_of_Money

Posted in: Money

About the Author:

This blog is dedicated to share simple practices I that get me results.

5 Comments on "How To: Create Personal Finances Spreadsheet Using Microsoft Excel"

Trackback | Comments RSS Feed

  1. Via Practice This >> How To: Create Personal Finances Spreadsheet Using Microsoft Excel http://t.co/cqg5A7ga

  2. How To: Create Personal Finances Spreadsheet Using Microsoft Excel http://t.co/mTyCXgY1 via @alikl

  3. How To: Create Personal Finances Spreadsheet Using Microsoft Excel – http://t.co/52yrlFNL

Post a Comment