How to Keep Track of Small Business Expenses in Excel (2 Ways)

In this article, we will discuss expense tracking and its components, as well as methods for small businesses to keep track of expenses in Excel. The outline of a small business Expense Tracker will vary from business to business, depending on the specifics of the business. But in general, small businesses can either build a custom Expense Tracker in an Excel worksheet, or use an Excel Template.

Tracking Business Expenses

Keeping track of each expense, including its purpose and receipts, is known as expense tracking. An Expense Tracker helps businesses to identify money leaks, track utility bills, find entry errors, make profit-worthy investments, and file tax returns. Thus it plays a crucial part in business sustainability, particularly for small businesses.

Components of a Business Expenses Tracker

A business expense tracker consists of numerous components which may differ from business to business. However the following components are more or less mandatory for every Business Expense Tracker:

(i) Name: The name of the employee or person who makes the expenses.

(ii) Employee ID: The ID number of the employee.

(iii) Department: Name of the department in which the employee works.

(iv) Manager: Name of the person in charge who oversees the expenses.

(v) Time Period: The time frame within which the expenses were incurred.

(vi) Description: The purpose of the incurred expenses.

(vii) Expense Category: Insert recurring expenses in separate columns to form Expense Categories.

(viii)Total: Use formulas like the Sum function to find Total amounts.

(ix) Weekly or Monthly Tracking: Make different worksheets for different time periods such as weeks and months.

(x) Receipts: Add receipts to verify the expenses (images or links).

(xi) Authorized By: The name of the person who monitors the expenses.

Method 1 – Using a Template to Keep Track of Small Business Expenses in Excel

Excel offers numerous templates for financial-related tasks, including for expense tracking. In an Excel workbook, go to File > New > Type Expense Tracker in the Search Bar. Then select a template that fits your need.

Method 2 – Creating a Custom Expense Tracker Using Excel Worksheets

To build an expense tracker from scratch, the following steps should be considered:

Step 1 – Open a Financial Account or Bank Account

Instead of handling petty cash or other means of payment, paying expenses via a financial account (i.e., a bank account) makes it easy to verify expenses against bills or receipts.

Step 2 – Compartmentalize Expenses Into Periods

Each expense tracker should have specific time periods within which it holds itemized expenses, such as days, weeks, or months.

Step 3 – Insert Fixed and Non-fixed Expenses

The fixed expenses (such as Rent, Internet, Salaries) should be stored separately from variable or one-off expenses such as production costs or purchase of equipment..

Step 4 – Maintain a Specific Time for Inputting the Entries

It is good practice to follow a fixed time schedule to enter incurred expenses, such as at the end of a day, week, or month. This practice offers protection against double entry, ensures cross-matching, and maintains a sequence.

Step 5 – Use Formulas to Perform Operations

To find total expenses or other calculations, use Excel formulas. For example, in the expense tracker below, we use the SUM function to find the accumulated expenses.

Step 6 – Attach Receipts’ Images or Links

Compile or attach the receipts for expenses to the expense tracker by inserting images or image links. These should be inserted in a manner that they can be easily linked back to corresponding line items in the expense tracker.

Step 7 – Cross-Check the Incurred Expenses

After inputting all the entries, cross-check incurred expenses with their receipts. Any mismatch in the entries should be addressed and re-entered accordingly.

Download Excel Workbook

Use the file below to practice or as a Template.

Tracking of Small Business Expenses.xlsx

<< Go Back to Tracker in Excel | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!