Excel Personal Finance: How to Create Budget in Excel Template

"Money is a tool. Used properly it makes something cute- used wrong, it makes a mess!" – Bradley Vinson

It takes discipline to use coin properly. In this tutorial, we are going to look at how to use Excel for personal finance to properly manage our budget and finances. We will cover the following topics.

• Why manage Budget?
• Major components of a personal finance system
• Using Excel to set personal budgets, tape income and expenses
• Visualizing the data using charts

Why manage Budget?

Permit's confront it, the globe nosotros alive in is fuelled by money. We go to schoolhouse to get a adept chore, appoint in business concern and other related activities with the main goal of making money. If we do not manage our personal finances properly, and then all of our efforts go to waste.

Most people spend more than than they earn. In order to be financially successful, one needs to develop a habit of spending less than they earn and invest the surplus in business ventures that will multiply the invested money

Major components of a personal finance system

This is a basic personal finance system so nosotros will consider the following components;

1. Projected income – this is the money that you look to earn now and in the time to come.
2. Budget – this is a list of the items that you wait to buy, quantities and their respective prices
3. Actual income – this is the actual money that you earn equally time progresses
4. Actual expenditure – this is the coin that you actually spend buying things

The variance between the projected income and actual income gives usa the performance indicator of how authentic our estimates are or how hard we are working.

The variance betwixt the budget and the bodily expenditure give united states of america the performance indicator of how disciplined we are when it comes to sticking to a budget.

Since saving is a role of the goal of having a personal finance system, the actual income vs. the actual expenditure say on a monthly basis gives the states an idea of how much nosotros would save over a yr.

Using Excel to set personal budgets, and record income and expenses

We have looked at the components of a personal finance organisation, and we will now use what we have learnt so far to implement the above personal finance Excel workbook. We will create two workbooks for this Excel for personal finance tutorial, one for income and the other for budgets.

When you are done with this tutorial, your workbooks should look equally follows

Hither are the steps on how to create a personal upkeep in Excel:

Open Excel and create a new workbook

Income canvass

• Rename the Sheet1 to Income
• Enter the information shown below
• Projected Monthly Income

S/North	Description	Corporeality ($)
1	Salary	600.00
2	Freelance works	250.00
3	Others	180.00
Grand Total
Expected annual income:

Bodily Income

S/N	Description	Amount ($)
i	Salary	600.00
2	Freelance works	200.00
three	Others	150.00
Grand Full
Bodily almanac income:

Formulas for Income sheet

Nosotros will now demand to;

1. Summate the monthly income for both projected and actual income.
2. Compute the projected annual income and actual annual income based on the monthly total.
3. Find the variance between the bodily and project annual income
4. Show tips on how we are doing using logical functions
5. Use conditional formatting to highlight how well nosotros are managing our finances

Tutorial Exercise i

1. Observe the sum of all monthly income sources. Utilize the SUM function. Exercise this for both projected and actual monthly income to manage personal finance in Excel.
2. Find the annual projected and actual income past multiplying your answer in question 1 by 12.
3. Detect the income variance by subtracting the projected annual income from the bodily almanac income.
4. Add a comments row merely below the tables. Employ the IF functions to brandish "You need to work extra hard to meet your income targets" if the variance is less than zero (0) else display "Groovy job working smart and harder".
5. Utilize conditional formatting to change the text color to blood-red if the variance is less than 0 else alter the text color to greenish.

If you are stuck on what to practise, read the articles on formulas and functions, and Visualizing data using charts in Excel.

Expenditure sheet

Add a new sheet and rename it to Expenditure

Enter the data as shown below

Budget

S/N	Item	Qty	Cost	Subtotal
1	Rentals	12	210.00
2	Bills	12	100.00
3	Groceries	12	230.00
4	School	2	500.00
5	Miscellaneous	6	133.00
One thousand Total:
Expected Annual savings:

Actual income

Due south/N	Description	Qty	Toll	Subtotal
ane	Rentals	12	210.00
2	Bills	12	145.00
3	Groceries	12	240.00
4	Schoolhouse	2	500.00
v	Miscellaneous	6	233.00
1000 Total:
Actual Annual Savings:

Formulas for Expenditure sheet

We volition demand to;

1. Summate the subtotal
2. Calculate the m total based on sub totals
3. Compute the expected almanac savings. The expected annual savings is the difference between the projected almanac income and the budget total amount. This will be washed for the actual income and bodily expenditure too.
4. Compute the monthly savings variance
5. Add a comments row just below the tables. Employ the IF functions to brandish "You need to minimize your expenses." if the variance is less than nix (0) else display" Great task sticking to the budget".
6. Utilize conditional formatting to change the text colour to red if the variance is less than 0 else change the text colour to light-green.

Tutorial exercise ii

Write formulas that implement the above scenarios.

Visualizing the data using charts

Charts are a not bad way of visualizing our data. We will at present add a pie chart to our income sheet for the projected monthly income. The paradigm below shows our data

As marked in the higher up epitome our chart should merely show the data for

1. Particular column
2. Subtotal column

Highlight items one to 5 in Item column

Hold Ctrl button on the keyboard and highlight the subtotals from ane to 5 in subtotals column.

Your selection should look as shown in the image below

Click on INSERT tab from main menu

• Use the charts ribbon bar, click on the pie nautical chart drop down and select a pie chart of your selection.
• Your pie chart should now wait like to the 1 shown below

Tutorial do three

Create charts for the actual expenditure, projected income, and bodily income

Download Excel Budget Template

Summary

In this tutorial, nosotros have learnt and practical the knowledge gained in the previous tutorials. We have also been able to visualize our data using charts.

Source: https://www.guru99.com/case-study-managing-personal-finance-using-microsoft-excel.html