Managing your personal finance with Excel
There are numerous accounting softwares, but most of them are too complicated for people who only want to track the status of their income and expenditures, without having to deal with VAT issues, depreciation of property, loan repayment....
For users who are new to Excel, it is not always easy to create a spreadsheet to store their data, calculate their expenses or monthly totals, get a summary of their expenditure/revenue and possibly illustrate all this data with a simple graph.
This article aims to provide answers to these questions!.
You must first define which information you want to save in the spreadsheet.
- The following columns are required: The date of the transaction, designation, a column for income and another one for expenses.
- For better visibility, it is recommended to add a column for the balance (after each data input).
- You can also add a column (or 2 separate columns) indicating the type of revenue (incentives, salary,...) or expenses (repair, maintenance and insurance)
- Optionally, if you plan on comparing your transactions with your bank statement, an additional column will be required.
- Finally, if you need to make monthly statistics, create another column.
Creating the database:
- As for any database Line 1 is reserved for the column headings (field names).
- We will use Line 2 to display the totals each column (it is more convenient than putting the totals at the end of the table).
- Line 3 contains the starting balance (when you start tracking your income/expenses)
- From now on, each line will contain a unique entry (either an income or an expenditure)
- Try to input data in a chronological order.
- Be careful not to leave any empty row or column within the table!
The Excel tools you will need to manage your database consist of a few functions and commands:
- The main functions are: MONTHS, IF, SUM and Data Validation
- Calculations can be performed using: SUM, SUMIF...