Expense and Profit Spreadsheet - How to use
If you’ve downloaded our business expenses and profits template spreadsheet and are looking for help on how to best utilize it then you’re in the right place. Looking for the spreadsheet in question? Click Here to go to that page!
This blog will walk you through each of the tabs on the spreadsheet to ensure you’re able to utilize them well. If this feels daunting or you’re looking to hire someone to handle this for you then reach out to us, we’re more than happy to handle this for you!
General Rules
This spreadsheet contains formulas to help automate parts of the process. Any cells with formulas are colored in a light yellow color to make it clear that those cells should not be edited.
For example, in the below example you should not edit anything in the “Amt Due” or “Overdue?” columns, as these have formulas and deleting or adjusting them will cause them to cease working.
Expenses tab of the business expenses and profits template spreadsheet
If you’re familiar with sheets formulas you may feel free to poke around and edit to your liking, but if you’re a novice or want the sheet to continue to work as originally intended then you should not touch anything in the light yellow cells.
Categories
The categories tab is where you can set what categories you want to be able to access on other tabs. There are some included by default, but you'll want to adjust these to fit your business. These then will reflect on their related tabs for you to select from a drop down. You can think of this similarly to a chart of accounts. You should not adjust the header categories (Expenses, Sales, Payment accounts), but you may add, delete, or adjust any cells below them.
Edit the highlighted cells to your businesses specifications.
Categories tab of the business expenses and profits template spreadsheet
Expenses
The expenses will show up on the Expenses Transactions tab for you to categorize your expenses. A list of common expenses has already been provided, but you may have specific ones you’re looking to include. For example if you were running a bakery, you may want to add an expense for baking supplies.
Sales
The sales section is where you can list the different types of sales you may receive. For example, if you are providing tutoring services and have an online course, you may want to list each of those so you can track how much revenue you are receiving from each.
Payment Accounts
The Payment Accounts are essentially your payment methods. Common options are listed, such as paying with your credit card or bank account. This can help with tracking down data if you need to find something down the line and will prepare you for the transition to bank reconciliations if you chose to switch to using an accounting software.
Expense Transactions
The expense transactions tab is where you will want to enter in each of your expenses on a separate line. Here is a run down of what each column is for.
Date: The date the transaction took place
Vendor: The vendor you purchased from, Ex: Staples
Expense: This pulls from the list of expenses you entered on the Categories tab. Select the most applicable expense to help categorize your transactions.
Full Amount: The full amount of the bill, whether it’s been paid or not
Amt Paid: The amount of the bill you’ve paid as a dollar value
Amt Due: A formula column that will subtract the amount paid from the full amount to show you the amount still due
Due By: The date the bill needs to be paid by
Overdue?:A formulas column that will compare the due by date to todays date and tell you if the bill is not due, due today, or overdue. If there is no Due By date then this will remain empty
Description: Additional information about the charge. For example, if this was the staples transaction mentioned in the vendor section, then we may want to say it was for printer paper
Account: This pulls from the list of accounts you entered on the Categories tab. You will want to select which account you used to pay for this transaction
% Business: You will want to list what percent of this transaction was a business expense. This often may be 100%, but an example could be if you work from home only a portion of your rent will be allotted as a business expense
Notes: Any additional notes you’d like to include
You will want to list out each transaction separately. Later tabs will look for transactions by date to break them out into each month for better comparison against your sales.
Vendors
The Vendors tab will pull each unique vendor out of the data you’ve entered under the Expenses Transactions tab and give you a quick summary of their account. The data under “Vendor, Total Expenses, Amt Paid, Amt Open, Amt Due Today, and Amt Overdue”, all will populate automatically with the data you’ve entered into the Expense Transactions tab. To the right, you can enter in the vendor's additional information if you would like to help make it easier to pay them or follow up with them. Filling out this additional information is optional, and columns H-N can be deleted if you prefer to track this information elsewhere.
Sales Transactions
The Sales transactions tab is where you will want to enter in each of your invoices/sales on a separate line. Here is a run down of what each column is for.
Date: The date the transaction took place
Customer: The Customer who purchased from you
Sales Category: This pulls from the list of Sales Categories you entered on the Categories tab. Select the most applicable category to help categorize your transactions.
Amount: The full amount of the invoice, whether it’s been paid or not
Amt Paid: The amount of the invoice your customer has paid as a dollar value
Open Amt: A formula column that will subtract the amount paid from the full amount to show you the amount still due
Notes: Any additional notes you’d like to include
You will want to list out each transaction separately. Later tabs will look for transactions by date to break them out into each month for better comparison against your expenses.
If your business has many transactions each day, you can simplify this process by entering one transaction per day that reflects all sales for the day.
If your customers are numerous and numerous or anonymous then you may wish to remove the tracking of customers. To do this, you can delete column B on the Sales Transaction Tab and delete the Customers tab entirely.
Customers
The Customers tab will pull each unique customer out of the data you’ve entered under the Sales Transactions tab and give you a quick summary of their account. The data under “Customer, Total Invoiced, Amount Paid, and Current Open”, all will populate automatically with the data you’ve entered into the Sales Transactions tab. To the right, you can enter in the vendor's additional information if you would like to help make it easier to pay them or follow up with them. Filling out this additional information is optional, and columns F-J can be deleted if you prefer to track this information elsewhere.
January - December Tabs
The January-December tabs are summary information pulled from the Expense Transactions and Sales Transactions tabs. They have formulas written to pull data that aligns with the period listed at the top. If you’d like your months to be for different periods (Such as if you operate on the 25th-25th of each month) you can adjust the periods at the top to meet your needs. The title will automatically adjust based on the date next to “Begin”. For example, if it says 1/1/2025, then the title will say January 2025.
January tab of the business expenses and profits template spreadsheet
You should not enter or adjust any data other than optionally adjusting the dates. The spreadsheet should pull all the data automatically.
The expenses side will show a total of all your expenses for the month as well as a breakdown by category. Only categories that have been used on the Expenses Transactions tab will show here.
The sales side will show a total of all your sales for the month as well as a breakdown by category. Similarly, Only categories that have been used on the Sales Transactions tab will show here.
To the right, there will be a pie chart showing the expenses and sales as slices so you can visualize better where your money is flowing.
Looking for more help?
Are you looking for more assistance with your bookkeeping? We can help! Here are a couple of different ways we may be able to help you.
Add to or adjust the spreadsheet to better fit your needs
Fix issues from adjustments or accidentally deleting something
Provide one on one training on how to use the spreadsheet
Assist with creating tangent spreadsheets
Manage your spreadsheet for you by providing virtual bookkeeping services
Review your data and provide consulting advice
If you’re looking for any service not listed here it doesn’t hurt to reach out and see if it’s something we can help with!
As thanks for using our tool, we’re happy to provide a free 15 minute minute session to help with any of these issues. To get in contact, feel free to fill out our form or email us at Bookkeeper@goldenhivebookkeeing.com.
Congrats on taking the next step in managing your business, each small step you take is towards something great. Cheers to you!