# How to create budget in Microsoft Excel

by on April 30, 2014

Creating a personal budget should be simple enough so that a second grader, who knows addition, subtraction, multiplication and division, can understand it. It doesn’t have to be a jungle of incomprehensible figures.

Creation of any budget consists of three simple steps, step 1 is to determine your income,
step 2 is to outline all of the expenses that includes all subscriptions, utility and other bills and at last all day-to-day expenses, which leads us to the final step 3 in which you should determine categories where you can reduce your expenses and begin saving.

Person X, with no one to support but himself and his heavy credit card debts. He wants to clean up the mess he has gotten himself into without losing the essentials.

The first thing he does is calculates exactly how much money he makes every month. In his case, he makes about \$20,000 (after taxes) yearly. He has no other income or money coming from any other source. By dividing his annual income by 12, he gets his monthly salary: \$1,666.66. He also uses the same principle when calculating costs (expenses) he have had over a yearly period, just so that everything works on a monthly basis.

## 2. Outlining Expenses

To make it all just a little easier to comprehend, I have decided to split incurred expenses that you might have in two subcategories, essential expenses and non-essential. So lets get back to our example.

### Calculating Essential Expenses

The person X decides to make a list of those things that he absolutely needs to survive every month. In doing so, he comes up with the following categories:

1. Rent/Mortgage payment
2. Grocery
3. Clothing
4. Utility – heat, electricity, water
5. Car maintenance – gas, insurance

Some of the things are already a pre-set monthly expense such as rent or mortgage or car insurance for example, but most of everything else fluctuates monthly. However, the fluctuations are minor therefore Person X decides to note down every expense for anything he bought (even the small items) for a month or two to get a clear idea of how much he spends in all the categories above.
He also digs up some of his old bills for a period of a couple of months for things like heating, electricity and water and calculates the average but adds a little to cover times when it may be more than usual like in the winter time for instance. Now he has all figures to fill in these categories.
The spreadsheet that you might have downloaded already contains comprehensive list of all categories and items within them, of which we have talked about above in this article, but it is totally up to you to decide, which category out of all is essential for your specific circumstances and which is not.

Now, for the sake of argument Person X knows that every month, he must set aside \$1100 for all essentials. He doesn’t even have to use the credit card, since he knows this is how much he will need to survive, without getting into debt.

### Determine and Calculate Non-Essential Expenses

Person X lists the non-essentials-things that he spends on that aren’t as necessary:

1. recreation, this includes holidays, trips to restaurants, video rental or some other purchases.
3. long-distance calls

He thinks about his latest trips to restaurant and decides to start eating more at home, perhaps picking up some cooking skills by trial and error, to cut down his restaurant eating costs, or doing some coupon shopping to reduce the bill, or getting better vacation deals etc.

For gifts, he decides to allocate small amounts every month so when necessary, there will be enough for it. He also gets a better deal and reduces his long-distance calls bill by paying a flat rate for a certain number of hours every month. He also finds a credit card card with the offer not to charge interest for 12 month on any balance transfers, which reduces amount he has to pay every month to clear the balance of his credit card and helps to pay it all faster.

So the total amount of all the non-essentials our Person X has to spend on every month is approximately \$175.00. Of course paying balances off on the credit card is not necessarily is non-essential, but I have only brought it here for the sake of an example to show you what can actually be done to reduce your monthly spending.
The total of all essentials and non-essentials so far is now \$1275.00.

## 3. Debt Elimination, Charity and Savings

Since Person X is serious about giving to charity, eliminating his debts and wants to have some savings as well, he decides that these are categories that have to be included as well. After fiddling around a bit, he decides to allocate \$30.00 for charitable donations and put the rest of what he has got left towards savings.

## Some Things to Remember

1. Numbers are flexible. Experiment with getting cheaper options for things, even for essentials. If, for instance your rent is very high, consider moving. If you can, reduce your clothing bill to only what you really, really need. Be on the lookout for ways, especially, that you can increase the proportions of money you put in for debts, savings and charity.
2. Don’t spend unless you have it (so you can avoid interest and unnecessary borrowing).
2. Write down every expenditure. Keep receipts of all purchases.
3. Deal with averages.
4. Plan for extras
5. Plan for emergencies

VN:F [1.9.22_1171]
How to create budget in Microsoft Excel, 5.0 out of 5 based on 3 ratings

Me December 18, 2015 at 6:05 pm

I am looking for a couple things. I am wondering if you have template for a family budget that has a linked check registry. I would like to be able to set my budget and then enter my receipts into the check registry and “code” them and it be linked to my budget so that it will tell me how much I have spent in each category.

admin December 18, 2015 at 8:50 pm

At the moment we do not have a template that can suit the requirements outlined in your comment, but it should not be too difficult to create as we already have a Family Budget Template and Checkbook Register, which can be combined in one workbook.

Karim February 11, 2016 at 8:08 pm

Hey Alex, thank you very much for these awesome Excel templates that I’ve been using and recommanding since a while now.
I am really good in Excel, had to build and program complexe spreadsheets using advanced formulas, Pivot tables, PowerPivot, VBA and SQL, but I am not as good in spreadsheet design as you really are, so I am asking you please if there are any tutorials or advices that you can give to people like me, that want to gain some more expertize in Excel Spreadsheets Design.
Thanks a lot again.