This expense form template in Microsoft Excel can be used to track your expenses off your business credit card and is good to use if you pay bills with your credit card for your small business or blog.
This Excel template is perfect for your small business credit card accounting … it helps you to make sure you are including all your business expenses (not just the ones off the normal bank account).
With this expense form template you can: -
Note: you need to have Microsoft Excel installed on your computer, or be subscribed to Excel online to be able to use this template.
Select the one that matches the financial year-end date of your business, the download will happen immediately when you click on it - if you don't see it on your browser downloading, then check your Downloads folder on your computer:-
Open the download and save the document to your computer - give it a name of your choice, or just use the name it has already.
The template can track the details of one credit card. If you have three business credit cards, then you need to save three copies of the template.
I recommend that before you start entering your proper data, you save a copy of this expense form template and practice entering information – change the headings, enter some numbers into the income and expenses and see how they change the balance columns and the totals on the reports. Play around so that you get a good feel for it.
This template is an Excel workbook containing 16 sheets/tabs. Here is a summary of what they are and a screenshot of each - we get more into the details after that.
Shows you an example of a completed page of expenses and balances.
This is where you enter in your expense names such as Stationery, Gas, etc. These are the "accounts" of your business as found in a Chart of Accounts.
12 named sheets representing each month of the year - these are where you enter your daily expense transactions – each column has a total at the bottom and each row has a total for the income and a total for the expenses, and the daily balance.
This is the summary report that tells you what your business profit or loss is, based on the data you entered into the monthly sheets. It updates automatically.
This information should be combined with your overall business Balance Sheet
The sample page of this expense form template shows you how to enter in your information, including:-
This page is linked to all the months and the two report sheets. If you change Head 5 (for example) renaming it to T-Shirt Stock Purchase this will change the column header for Head 5 on every month.
It will also update the account names on the P&L or Balance Sheet. The light yellow shaded cells are linked to the Balance Sheet. The green shaded cells are linked to the P&L.
Here is a before and after image:-
There are two “Payments Received” income columns and one “Other Funds” income column. Why? These represent funds that are not actual income from selling items or services, but are funds received from activities such as:
There are also four “Other Funds Paid Out” columns that are linked to the Balance Sheet for activities such as:-
The Sample page shows how the credit card balance changes with each transaction in this expense form template.
Money Received Decreases the Balance
If it is money received into the credit card account then the balance on the expense form template decreases.
For example, if your credit card balance is $1,040, and you make a payment against the credit card off your business bank account of $1,000, the credit card balance will go down to $40.
That is all that is left owing to the credit card company now. This is how it will show in this expense form template.
Money Spent Increases the Balance
If you buy something the credit card balance will increase.
For example, say your credit card balance is $1,195 and you pay for a top-up for your cell phone of $45, the credit card balance goes up to $1,290. This is how it will show in the expense form template.
The credit card balance is shown on the Balance Sheet under Liabilities in an account called Credit Card Balance Owing, because any balance on the credit card that is owed to a credit card company is like a loan from them.
All loans are a liability because it is money you owe to another business. In saying that, some bookkeepers will include the balance under the Assets section of a Balance Sheet as a negative balance, which is perfectly acceptable.
Some people make the mistake of thinking they must show their credit card limit amount on their reports but this is not the case.
The credit card limit has no effect on credit card accounting and should not be included in the business financial records.
Only the amount that you spent that is owed to the credit card company is what should be shown.
It can often take up to 5 or more days for a transaction to be processed and cleared on your credit card.
Say you bought a laptop on 31 January for $800, if you use Internet Banking and check your credit card details, you will most likely find that even though the credit card balance has increased by $800 (and even that might take a few days to occur), when you look at the details on the credit card transaction page there may be no details yet - it might just say Electronic Transaction or something similar and not yet show the name of the business you bought the laptop from. It will show it once the amount clears.
So how should you handle this in your expense form template?
Your aim is to make sure that the expense form template balance matches the credit card account balance, so whether you access it through internet banking, or you wait for a paper statement to arrive, make sure the template matches it.
You can use the last column Headed R to mark each transaction as Reconciled as you check them off your Credit Card Statement. R stands for Reconciled, C stands for checked, X stands for checked and the dash can mean anything you want! Select any one that you fancy using.
The expense form template for each month runs from the 1st of the month to the 31st (or 30th) of the month.
However, a lot of the time Credit Card Statements end with a closing balance on a 15th or something similar.
Still use all the actual dates of your transactions and simply mark as reconciled all the transactions up to the 15th of the month. All this means is that you have to wait a few weeks before seeing a finalized P&L figure for the previous month.
This report is basically made up of the totals of your Income, minus the totals of your expenses. This report will most likely show a loss because in most cases you will not have your income being paid into your Credit Card – your customers would usually pay into either your business bank account or your Paypal or Stripe account, or give you a check which you will deposit to your bank account.
You need to add the totals on this P&L to the overall P&L of your business that is calculated from the transactions off your business bank account.
If you use our Excel Cash Book template for the transactions off your business bank account, you will already have a Profit and Loss being calculated to which you can add these Credit Card amounts.
This report is made up of all transactions that are not a normal business income or expense.
We’ve explained some of them already further up this page and they include things like Owner’s Drawings and Capital, Loan repayments, Asset purchases and so on.
It also includes the Current Year Earnings, which is the Profit or Loss amount calculated within the Profit and Loss report.
The Total Net Assets must equal the Total Equity exactly. If it doesn’t then something has gone awry.
If you need a hand getting this right, use the Contact form to email me and tell me what the problem is - include a copy of your data template if you can (if you don’t want to that’s fine) so that I can help you get the balances correct.
If you use your personal credit card for business expenses (as well as all your personal stuff), don’t use this expense form template to record the credit card transactions and balance.
Your business does not ‘own’ this credit card debt, you do (even if you are a sole trader) and so it should not be included in the business accounts.
However, you will still need to bring in the business expenses off that personal credit card to your business accounts. So, this is what you should do: -
You may get tripped up if you are a sole proprietor and are using a credit card in your own name not your business name. So here is one way to identify if your credit card can be termed a business one or personal one: