How To Create Your First Working Budget With Quicken And Excel

Written by Sam on April 8, 2010 – 9:23 am -

To start off, if you haven’t viewed Emily’s screencast on using Quicken with your zero-based budget, you’ll want to do that first.

You can receive a copy of the Working Budget excel file shown in the screencasts below by subscribing to my newsletter on the right.

Step 1 – Set up your accounts

If you choose to use Quicken to keep track of your money, I’m going to assume you already use Quicken and know how to set up your accounts. If you don’t already use Quicken, I would recommend using You Need A Budget software. You can read my You Need A Budget Review or watch my screencast You Need A Budget Overview.

Step 1 is to simply set up your accounts in Quicken so do that first.

Step 2 – Allocate existing account balances

Allocate any existing account balances. There are three general ways to make your allocations.

  1. Funds are reserved for a specific purpose. If you already have a specific use in mind for the funds in your accounts, allocate them accordingly. For example, you may have some money reserved for a large purchase or vacation.
  2. Use funds for this month’s budget. If you’re starting your budget mid-month or are tight financially, you may need to use some or all of the money in your account for this month’s budget. If that’s the case start allocating the money according to the priorities you established in your Master Budget.
  3. Use the funds for next month’s budget. If funds are neither reserved for a specific purpose nor are needed for this month’s budget, put them in a “buffer” category and start building up a month’s worth of expenses so you don’t have to live from paycheck to paycheck. It will also will act as an insurance policy to ensure you don’t overdraw on your account

You’ll want to allocate funds in your Working Budget and within Quicken. Watch the screencasts below for more details.

Step 3 – Budget your income

As you receive your paychecks, enter them and allocate the total amount into your budget categories according to the priorities you established using your Master Budget. Allocate your income in both your Working Budget excel document and in Quicken. See the screencasts below.

Step 4 – Print your budget.

If you have a buffer of one full month’s worth of expenses and can budget the whole month at once, print the Working Budget once all the funds are allocated. If you are allocating paycheck to paycheck, print out your Working Budget each time a new paycheck is received and allocated. Put the print outs into your financial binder.

Step 5 – Entering expenses.

You don’t need to enter expenses into your Working Budget until the end of the month when you’re ready to reconcile your budget. You can, if needed, enter transactions throughout the month into Quicken. You should do this if you are very tight and need to keep tabs on your accounts to ensure you don’t overdraw.

Reconciling

In a couple of weeks I’ll detail how to reconcile your budget at the end of the month. Until then you don’t need to worry too much about entering transactions unless you are very tight financially and risk overdrawing your account.

Conclusion

That’s it you are now up and running on your first working budget using Quicken and Excel. If you want a copy of the Working Budget spreadsheet, just subscribe to the newsletter and you’ll be automatically emailed a link to it.


Tags: , , , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting | Comments Off on How To Create Your First Working Budget With Quicken And Excel

Zero-based budgeting with Quicken

Written by Sam on April 8, 2010 – 9:21 am -

For years my wife and I had to make due with trying to make Quicken work using a zero-based budget. While Quicken simply isn’t made for zero-based budgeting, we did find a cool hack that lets us keep track of our money in a way very similar to a zero-based budget. It’s kind of like zero-based accounting.

Using this method combined with a zero-based budget spreadsheet, we were able to get the control we needed. In the screencast below, my wife Emily takes you through an overview of this hack and how it works.

For those ready to create their working budget, you need to watch this video first before you read about how to create your first working budget using Quicken and Excel


Tags: , ,
Posted in 12 Weeks to Fiscal Fitness, Budgeting | 1 Comment »

Holiday Hangover?

Written by Sam on February 5, 2010 – 4:44 pm -

The dust has settled from the holidays, the bills have come in, and the damage has been done. How did you fare financially during the holidays? Did you overspend or did you stay on budget?

After reviewing our holiday spending we actually came out almost exactly on target. That’s not to say we spent exactly as we’d planned. We overspent on gifts for some and underspent on others. It definitely helped to have a plan because we would have grossly overspent without one. This year our plan was a little more casual than other years. My wife and I did a quick and dirty plan one evening, but we’ve had enough experience creating these plans that we were able to implement effectively with only one short meeting.

In December I posted about strategies to reduce spending during the holidays. One strategy we relied upon this Christmas was that of buying items on the fly if we found a good deal, but then returning the items that were over our budget or that just weren’t as compelling as gifts. A couple of days before Christmas, we laid out all the gifts on the kitchen table (after our sons were asleep) and sorted through them decided which were really worth it and which could be returned.

This process was so useful and actually had an unexpected benefit this year. As we looked at the gifts all together, it gave us a great overall sense of “too much stuff.” We realized that we simply had too much stuff for our son. We’ve experienced over and over at Christmas the point of diminishing returns where each additional gift just doesn’t add that much. A 5 year old can only really enjoy a small handful of gifts. More than that and you start entering that spoiled mode where the child is like “is that all there is?” “Aren’t there any more gifts?”

By looking at all the gifts at once we could very clearly see how many gifts constituted “too much” and how many were just right.

So what was the result on Christmas morning?

I’m happy to report that this Christmas was probably the most rewarding in terms of enjoyment-per-gift for our son. Not only did our activity of looking at all the gifts at once lower our Christmas bill, it also resulted in a happier and better-balanced child. There was virtually no spoiled response and our son had just enough a variety of gifts that he was thrilled.

So how is your holiday hangover? Did you have one or were you able to remain sober?


Posted in Budgeting, Shopping | Comments Off on Holiday Hangover?

Generosity doesn’t have to break the bank

Written by Emily on July 11, 2007 – 10:00 am -

Get Rich Slowly offers practical suggestions for gift-giving on a budget in the post How to Escape the Gift Trap. We’ve all tried drawing names and going homemade, but I loved the idea of giving an experience. Making and sharing a meal, or inviting someone on a family outing. By doing this, you’re not just giving money or a gift, you’re making memories and history together.

Budgeting for gifts is tricky. We made a list of every occasion and person we wanted to give to, with the amount we would like to spend. We made sure to include Mother’s day, Father’s day, Valentine’s day, our anniversary, and any other easily-forgotten occasion for gift giving. When we added it all up, we were astounded–it cost as much as a vacation to Disney World! Now the tough question: what do we value most? Yes, we want (and need) to give, but we’re also saving to adopt another child, not to mention our goals to pay off debt and finish our basement. We didn’t eliminate gifts altogether, but had a great perspective to pare down our spending.

When the time comes to buy gifts, we consult our plan. If one of us wants to give more, we spend our personal money doing so. In a way, that makes getting a nicer gift even more meaningful. Another thing we’ve done is to give “our favorite things.” We created a list of our favorite items–things that make our lives easier and make us happy every time we use them. This ranges from the practical (Pampered Chef rubber scraper) to the luxurious (Mary Kay Satin Hands Pampering Set). We don’t feel the need to spend a lot, because we are giving something that we love and know is great.


Posted in Budgeting, Relationships | Comments Off on Generosity doesn’t have to break the bank

The Easiest Way to Organize Your Budget

Written by Sam on February 3, 2007 – 9:06 pm -

personal budgetIt can take a lot of up-front work to establish a working budget. The good news is, once your budget is established it really doesn’t take a ton of work to maintain. You can make budgeting even easier by organizing it the right way. This article will review a few different ways to organize your budget. Then I will explain the easiest way I’ve found to organize our budget.

Fixed/Variable Model

In my post about how to create a personal budget I addressed how to create your first budget. In that article I advocated using the fixed/variable model; first allocate your fixed and semi-fixed expenses, and next allocate your variable expenses. For first-time budgeters, this makes a lot of sense and helps you first determine your hard landscape (fixed expenses) before you fill in the rest (variable expenses). This is still one of the best ways to create your first few budgets, but isn’t the most efficient way to group categories once your budget is established.

Necessity Model

The next model is the necessity model. Using this model you budget your necessities first (food, housing, clothing) and proceed from most essential to least essential. This ensures you can pay for shelter, food and clothing before you pay for your new HDTV. This can also be a useful model for novice budgeters. But it is particularly relevant if you’re in an emergency financial situation or if you’re facing bankruptcy. Give priority to the necessities and let the other expenses take a back seat. However, this model is not very useful for normal month-to-month budgeting.

Usage Situation Model

There are really only a few ways to “use” or manage money. By grouping budget categories by how the money is used you can accelerate the budgeting process.

I’ve found it most helpful to group the categories as follows:

  • Automated categories – Expenses paid automatically (electronically)
  • Cash (Debit) categories – Expenses that will be paid for in cash or with a debit card
  • Accumulated categories – Periodic or irregular expenses that need to be saved for (these may be automated or paid for using cash or a debit card)

Grouping categories by usage situation also makes it easier to manage and keep track of your money. For example, by grouping automated categories, it’s easier to identify how much money you need in your bank account to cover those expenses and the timing of those withdraws. If you want to review your automated expenses to ensure they’re behaving as expected, you have them all at your fingertips. By grouping cash categories you can quickly calculate how much money to withdraw every month for cash expenses. By grouping accumulated categories, it’s easier to track and manage the balance of each category and where those funds reside.

Let’s look at each group individually and how to address them in your budget.

Automated Categories

When reviewing your budget, first address the automated categories. This group is pretty self-explanatory. These are all the categories that are paid automatically on a monthly basis. They are typically fixed (with a few exceptions) and the amount rarely changes. The great news is, this is the category that should take almost no time to address. You just copy the amounts from one month to the next and you’re done. You typically won’t need to have big discussions with your spouse about these categories unless the rest of your budget is very tight.

Here are some examples of Automated categories:

  • Utilities
  • Mortgage
  • Some charitable donations
  • Retirement or other long-term savings
  • Subscription Services (satellite)
  • Some insurance expenses
  • Some medical expenses

Automated categories tend to be difficult to change without a lot of effort. For example, your mortgage payment cannot be easily or quickly changed. If you decide to switch satellite or cable providers, it’s typically a multi-week process. That’s not to say these expenses can’t change, but often it takes more time and effort to do so than other expenses.

Dealing with variable automated expenses

Some automatic expenses are variable such as utilities (gas, electric). In these cases, find out if there’s a fixed payment option. Many utility companies offer a fixed payment option that calculates a fixed average payment for 6 months or a year. If you end up paying too much, the fixed payment is adjusted down at the end of the period. Pay to little and it’s adjusted up. These programs can provide a lot of stability and predictability to a budget and should be used if possible.

If a fixed payment program is not available, you’re still ok. Simply look at how much you pay on average for that expense and budget a high average for that category. For example, our telephone provider doesn’t provide a fixed payment program so we looked at our bills and found that, on average, the bill is $80 (including internet service). It tends to regularly fluctuate up to $85 and down to $75, and very rarely goes up above $90 (my wife didn’t realize calling Canada was so expensive). We decided to budget on the high end of the typical range at $85.

By budgeting on the high side, you give your budget more stability and provide a little cushion. If we go over $80 a little, we’re covered. If we go under, we let the extra funds accumulate to cover months that go over. Don’t worry too much about the exact budget amount. Over time, it will become apparent if you’re budgeting too much or too little. If you budget too much, feel free to re-all ocate the overage and treat yourself to dinner. That will act as a cue that you can reduce the monthly allocation. On the other hand, if you find you’re always short on that category, that’s a cue to increase the allocation. I should also mention that I’m assuming you already have an emergency fund establish so that if you happen to unexpectedly go over a large amount, you’ll have the money to cover it. You can check out my article about ways to save a little cash for an emergency.

Cash (Debit) Categories

Next, address your cash (or debit) categories. These expenses are usually depleted every month; you budget $100, you spend $100. Uncontrolled, cash categories tend to be highly volatile. This is where much of your overspending can occur which is why it’s good to use cash. Fortunately, as your budget matures this group is fairly easy to address because your needs won’t change much from month to month. When you first start budgeting as a couple, there is typically a lot of discussion about how much to allocate to categories like “grocery,” but over a few months, you should settle into a sweet spot. After that, you typically won’t make major adjustments unless there’s a significant change in your financial situation.

Example cash (debit) categories are:

  • Grocery (food or non-food)
  • Eating out
  • Clothing
  • Some Medical
    • Prescriptions
    • Over-the-counter medications
  • Babysitting
  • Personal Money
  • Entertainment

A good rule to follow is that any category where you tend to overspend should be cash. One benefit of grouping your cash categories is that it’s easier to calculate how much cash you need to withdraw every month because they’re all in the same place.

Accumulated Categories

Finally, look at your “accumulated” categories. “Accumulated” budget categories are expenses that may occur less frequently than monthly or that may be unpredictable. In other words, these are categories where you “accumulate” funds over time until they are needed. Accumulated categories are the least homogeneous of the three groups. Some are semi-fixed like Insurance and car registration payments. Others can be wildly variable like vacations and gifts. Some accumulated categories are easy to change while others may be difficult. For example, changing your clothing budget is not difficult to do (even though your wife might have issues with it). On the other hand, quarterly or yearly life insurance payments may take considerable effort to change, particularly if you need to research insurance companies, get a new quote, get a blood test, etc.

Unfortunately this is where a lot of the arguing, negotiating, compromising, and pleading takes place. In a more established, mature budget, this group won’t take long. But if your just starting out, or if you’ve had some changes in your financial situation, expected or unexpected, you’ll want to plan on spending some time here.

Here are some examples of Accumulated categories:

  • Insurance
  • Car maintenance/registration
  • Savings for large purchases
  • Household maintenance
  • Medical
  • Gifts
  • Travel
  • Subscriptions
  • Memberships
  • Car replacement (we still aren’t funding this one)
  • Clothing (if not taken out in cash)

There are 2 ways to deal with accumulated expenses

  1. Make equal payments
  2. If your $100 car registration is due in 6 months, allocate $17 a month to this category (100 divided by 6). By the time the registration is due, you’ll have the money. If you use this method, be careful not to simply divide all yearly expenses by 12 months unless you really have 12 months before the payment is due. When we first started budgeting, our next life insurance payment was only 4 months away so we had to allocate more money up front so we’d have enough. Once we made the payment, however, we then had 12 full months until the next payment and reduced the monthly allocation accordingly.

    You can track how much money has accumulated in a number of ways ranging from a sheet of paper to a spreadsheet to financial management software. In future posts I will address different ways of tracking these categories. In the mean time I would recommend either a basic sheet of paper, or keep track of the accumulated amount right in your budget spreadsheet next to the category name. If funds from different categories reside in more than one bank account, also note next to the category name what bank account the funds reside in.

  3. Use windfall money to fill in the gaps
  4. Most people don’t have enough money to fund all their accumulated categories in full every month because this is where many of the “wish list” categories reside. If you can’t fund every category you can rely on (or hope for) financial windfalls to fill in the gaps. When we first started budgeting, we were so tight that we simply didn’t have enough to allocate every month for gifts and vacations. But because the rest of our budget was under control, when we received a bonus or financial gift we could allocate it to these categories accordingly. As we’ve refined our budget and as our income has increased, we’ve been able to fully fund many of these categories on a monthly basis.

Speeding up the budgeting process — An example

Using these groups, here’s how our budgeting meeting usually goes.

  1. We look at automated categories. No surprises there. All the expenses are the same as always. Gas and electric are on an equal-payment system. The phone bill is $5 more than usual but we’ve been under a few months and have a little extra allocated to cover it. Time spent – 1 minute.
  2. We look at cash categories. Over time we’ve settled into a comfortable amount for each of these. For categories like “personal money” we’ve had many heated discussions in the past about how much should be allocated, but now that we’ve reached an agreement that we both feel comfortable with it doesn’t change from month to month. Emily calculates how much total cash she needs to withdraw this month. Time spent – 3 minutes.
  3. We look at accumulated categories. We’re on track to have our next life insurance and car registration payments saved by the time they’re due. We haven’t had any major medical expenses so we’ve built up a nice little balance in that category. We discuss if we need to reduce the amount we’re allocating every month to medical but decide that we’d rather have the funds ready just in case of a medical emergency. Time spent – 5 minutes.
  4. If there is any money not yet allocated, we first look to see if there are any known expenses coming up that aren’t on track to be fully funded. Typically this ends up being “gifts,” “vacation,” or a similar category. We determine which category to fund.

    If we allocated more than we have in income, we discuss which categories to take money out of. Time spent – 10 to 30 minutes

    This last step is where the bulk of our conversation takes place. By grouping the categories, we were able to get through the bulk of the budget in a matter of minutes. Sometimes you can’t avoid lengthy budget conversations when you have an unusual month or your financial situation changes. But on an average month we can literally get through our budget as a couple in 15 minutes.

Note: the times indicated don’t include the time spent to reconcile last month’s budget.

Little distinctions like this can really streamline the budgeting process over time. What tricks do you have for streamlining your budget?


Posted in Budgeting, Cash, Personal Finance | 5 Comments »

Join my FREE newsletter and get
Exclusive content for the "12 Weeks to Fiscal Fitness" course.

12 Weeks to Fiscal Fitness


Get exclusive content by entering your First Name and Email below:


I hate SPAM and won't share your email address with anyone!

GFD Marketplace