Introduction
Welcome to our all-inclusive Bookkeeping Template for Small Businesses. Our user-friendly bookkeeping template is designed specifically for small businesses, offering a convenient and efficient way to log transactions, generate monthly summaries, and compile annual reports. Whether you’re a seasoned entrepreneur or just starting out, our template will streamline your bookkeeping process, providing you with valuable insights into your business’s financial health. Download the template below to customise it for your own use, and read our guides and tips to learn how to best make use of the template!
Bookkeeping Template (empty)
This is an empty template that includes a general ledger and monthly & annual summaries. Download this to your own device to add details of your business transactions.
Read the next section for a detailed guide on how to make use of the template.
Guide to using the Bookkeeping Template
Listen to this explainer video that illustrates and demonstrates how to use our bookkeeping template. We have also written down detailed explanations of each tab and field of the template and how to use it, one by one, so continue reading to find out more!
For your additional reference, here is a version of the template with dummy data included so that you can see how the automated reflections and formulas work.
Explaining the Bookkeeping Template
1. Ledger (All transactions) tab
- The “Ledger” tab is where you log all individual transactions your business makes. We recommend you record transactions on a daily basis so as to not miss any.
- The “Date” logs the date of each transaction that occurred in the following format: DD/MM/YY. If the date is not recorded in the format, it will show a red alert because the cell has a data validation rule set up. Thus, make sure to follow the specific format.
- The “Account Type” column is for classifying what kind of transaction has been made. When you click the arrow button next to each cell, you will be able to see the dropdown choices. The choices are linked to the “Account Type List” tab, where you can manage and edit the choices you want to show. If you want to know more about how to work on the “Account Type List” tab, jump to the relevant section (#2. Account Type List) below.
- The “Description” column is where you add more information and context about the transaction so that it is easier for your team or auditors to make sense of the transaction that has taken place. We recommend you be descriptive.
- The “Amount” column is where you record the respective transaction amount in Hong Kong Dollars. If your business has some transactions in other foreign currencies, reflect the amount in Hong Kong Dollars. For example, if you have paid for a subscription with a credit card that costs 39 USD, it should have an actual explicit Hong Kong dollar value on your credit card statement.
- The “Transaction Method” column can be optional but we recommend you use this to properly keep track of the nature of a respective transaction e.g., if the transaction was done through a bank transfer, cash payment, etc., similar to the Account Type column, if you want to add more with the transaction method types, you can go “Transaction Method List” tab and add the types there. To read more, jump to the relevant section (#3. Transaction Method List) below.
- The “Payment processed?” column can be used to keep track of payments processed (made, or received), so that you can differentiate the payments incurred (per the transaction date) and the actual processed time of cash inflow or outflow.
- The “Source/Link” column is an optional column you can use to note the links to relevant invoices or bank statements. It is desirable to include relevant invoices, receipts, or links to cloud folders (e.g., Google Drive) to keep track of the relevant source and evidence of the respective transaction. This will become especially handy when you’re dealing with annual audits.
There are four columns filled in grey with the tag “(Automatic)” – these are automatically populated fields that you do not need to manage. To briefly explain the roles of the four columns:
- The “Month” column is calculated automatically from the “Date” column and enables you to see the summarised monthly and annual data in the “Transaction Summaries” tab.
- The “Financial Year” column is also calculated automatically from the “Date” column and enables you to see the summarised monthly and annual data in the “Transaction Summaries” tab.
- The “Balance” column shows the value in its actual nature. In other words, an expense will be shown as a negative number, and an income item will be shown as a positive item. This automatic conversion, derived from if the respective Account Type is an “Income” or an “Expense”, enables you to see proper monthly and annual totals.
- The “Income or Expense” column is an automatic labeling of an Account Type based on whether it is an “Income” or an “Expense”. If it is an expense, it will, among others, make the number negative in the “Balance” column. This allows for proper calculation of monthly and annual totals, such as the tab for Monthly Statements.
2. Account Type List tab
- The “Account Type List” tab is where you can manage the dropdown options that you see in the Account Type column in the Ledger tab. If you want to add a new account type, simply add it to the list of the types you already have on the “Account Type List” tab. For better record-keeping purpose and for easy comprehension of your team, make sure you are correctly classifying each account type as an income or an expense, and also write a short description. It is desirable to specify what items will be included in this account type (e.g., In the transportation account type, it could be included taxi fee, bus fee, or other transportation expenses, so type those items to classify easily). Note that you can also arrange these account types in a specific order through the filter button according to your preference.
3. Transaction Method List tab
- The “Transaction Method List” tab is where you can manage the dropdown options that you see in the Transaction Method column in the Ledger tab. The methodology to edit and update the items replicates the logic of how the Account Type List tab works.
4. Monthly Summaries tab
- When a transaction is recorded on the “Ledger (All transactions)” tab, the record is automatically reflected in the “Monthly Summaries” tab. Here, you can customise the data to include more or different variables (click anywhere on the table and the pencil icon to edit). You can also filter data through the “Filters” function.
- For example:
- If you want to see values of the whole year, not by each month, click the pencil icon, delete the original item in the column section, and then choose “Financial Year (Automatic)”. This will show you the values of the whole year. If you want to see the monthly values, delete the Financial Year (Automatic) item and choose the “Month (Automatic)” option.
- If you want to see values made within 2021, click the “Add” button next to the “Filters” section, and choose “Financial Year (Automatic)”. Then, click the status dropdown and only leave the FY21 item, which will show the values made in 2021. If you want to delete this filter, you can go back to the “Financial Year (Automatic)” section and click “X” to delete it.
5. Annual Statement tab
- The “Annual Statement” allows you to have a summarised view of your annual revenue, expense, and net profit in a formatted way. This will be especially helpful for you to prepare for your annual audit.
- In the “Annual Statement” tab, you can change the value in cells C5 and D5 to automatically summarize a different financial year (PS! If you haven’t yet, please indicate your financial year in the “Company Inputs” tab). Typically, you’d want to see your two latest years summarized. By clicking on cell C5 and D5 you get a dropdown option for financial years. By default, the template includes the financial years of 2021, 2022, 2023, 2024, 2025 (FY21, FY22, FY23, FY24, FY25). If you need any older or years after 2025, you can simply add additional dropdown choices by naming them exactly in the same format as the others. Such as “FY26”.
- Note that this version of the template includes the account items of the transactions that are added as examples only. This means that if you are customising the template to include or remove Account Types, you need to manually add or remove the relevant types as a row item in Column A.
- For example, let’s say you have a new “Expense – Warehouse” item on the “Account Type List” tab (which would then automatically appear as a dropdown choice that you can pick from on the “Ledger” tab). Then you should manually add this Account Type as a new row in this tab. As warehouse expense is part of operating expense, go to the last row of the operating expense, right-mouse click on the row number, and click “Insert 1 row below”. If the new column is created, write the account type name (”Expense – Warehouse”), in the exact same wording you used in the “Account Type List” tab, on Column A. Then on the empty cells on Column C and D of the same row, copy paste the formula that other cells of the same column has (e.g. “=sumifs(‘Ledger (All transactions)’!$I$2:$I,’Ledger (All transactions)’!$B$2:$B,$A20,’Ledger (All transactions)’!$H$2:$H,C$5)”. One last check for you is to make sure that the relevant Column C and D cells showing your annual “Gross Profit” reflect the change of the cell range, so that the range includes the new row that you created or removes the row that you deleted.
6. Company Inputs
- The “Company Inputs” tab will be used to let the system calculate financial years automatically. Your company’s financial year is what dictates the exact period covered in your annual financial statements, and the financial period may differ among companies, which is why you have to complete this section. You can choose the financial year period yourself. The most common is either Jan 1 – 31 Dec or April 1 – March 31”.
- By inputting this, you will easily get automatic real-time financial year overviews in the rest of the bookkeeping template (such as the tab “Annual Statement”) that helps you see and compare financial years – and it makes the job easier for a future auditor to assist.
- For example, if the financial period of your business is from January 1 to December 31, put 1, 1, 31, and 12 accordingly to make sure those four numbers represent the exact period of the financial year. For example:
- If your start date is the 1st of April then your financial year for 2021 (FY21) would be the transactions occurring in the time period of 01.04.2021-31.03.2022.
- If your start date is the 1st of January, then your financial year for 2021 (FY21) would be the transactions occurring in the time period of 01.01.2021-31.12.2021.
Relevant Templates
FSI has prepared for you the essential templates that facilitate your bookkeeping. Click the links below to check them out!
