Spreadsheet123 - The Ultimate Guide to The World of Excel

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.

Using a one-person example, here’s a sample 3 step guide that can get you going, but before we go any further and for the sake of having a good example right before your eyes of everything that we are about to explain in this article, I recommend downloading either Personal Budget Spreadsheet or Personal Monthly Budget, both of this templates can help you to understand the basic principals of budgeting and even help you to get started in no time at all.
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.

1. Determine your Income

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.
Personal Budget Budget Income

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.
Personal Budget Budget Expenses
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.
2. gifts, souvenirs, etc.
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.
Personal Budget Budget 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]
Rating: 5.0/5 (1 vote cast)
How to create budget in Microsoft Excel, 5.0 out of 5 based on 1 rating

{ 0 comments… add one now }

Leave a Comment

Next post: