Clearly log each expense.
Log date, vendor and description when you make a payment. On the same day. Then choose category and type of payment. Also write a reference to receipt or invoice, to be able to find the original. And write which client/project, if this is a purchase for them, in the same row. This way later you'll be able to understand why you spent money.
- Date, Vendor and Description. To explain the expense.
- Category, Type of payment. To understand the nature of the expense and verify with the statement.
- Reference to receipt. To easily find the original file.
- Write Net amount and Tax separately. The Excel template already has the addition formula in the column Total.
- Reimbursable, Client/Project, Notes. For later verification. Marking an expense reimbursable does not mean it is approved or reimbursed.
Replace these examples with your own records. One currency for one workbook. Separate different currencies. No addition without conversion. Don't overwrite total formulas in Excel. CSV is just a simple detail file; you need to verify totals yourself when you change amounts.
Excel summaries
First, you need to set a year in Excel's Monthly Summary sheet. The formulas will calculate monthly totals, category totals (annual), tax and reimbursable amounts based on expense dates for that year. Don't forget to verify the dates after a year change.
- Monthly total. Spending by month.
- Category total (annual). To see the spending distribution.
- Tax, reimbursable amount. Separate for extra verification.
- Expense sheet contains a dropdown for category. Names should be the same to classify.
- No macro. You can check calculation formulas in the sheets.
If you need to change a category name, please verify it in the Categories sheet, existing expense entries and the category label in Monthly Summary. Not all of the other names are updated by changing Categories only. Preset rows for expenses in Excel. Preset range for categories. CSV doesn't calculate these totals automatically.
Review the tracker every month
Please make some time every month to review the tracker. Compare your expense records with bank/credit-card statements. See if there's anything missing. Duplicate entries. No receipt. Unchosen categories. Having a total does not mean all the records are there.
- Add missing dates. Description. Receipt reference.
- Duplicate. Check net amount, tax, total.
- No category/inconsistent category.
- For project costs to be reimbursed by your client, please verify the agreement, approval and related invoice separately.
- Organize records. Don't forget original documents. For you to explain to the one who handles the books.
Arrangement of records. It is not telling you if you can claim any expense as tax-deductible. You have to look into each transaction and see what classification it has, which rules apply to it and what are the relevant facts.