Manual

In this chapter, you learn how to install the Excel accounting template. Don’t worry, this is extremely simple. But before you do that, please make sure your computer and business software meet the requirements.

Because of the complexity of the formulas within the Excel bookkeeping template, you will need to utilize our software within Microsoft Excel. Once again, don’t worry, you do not have to worry about these formulas. All you need to do is fill in the blanks.

The Excel bookkeeping template works with Microsoft Excel 2007 for Windows (or newer) and Microsoft Excel 2011 for Mac (or newer). We recommend using the latest version of Microsoft Excel, which currently is Excel 2016 within the Office 365 suite (both PC and Mac).

If you do not have a copy of Microsoft Excel you can download a free trial via the link below:

https://products.office.com/en-us/try

The Excel bookkeeping template may not work 100% with open source software such as OpenOffice, Google spreadsheets or Numbers for Mac.

Installation is very simple. The Excel bookkeeping template is offered per download. You only need to save the Excel bookkeeping template to a location on your hard drive. After completion of your order you will receive an email with your download details. Save the product to your hard drive in a folder that you can quickly find.

Important
You must first extract the ZIP file before working in Excel. Locate the folder where you saved the ZIP file. Right-click on the ZIP file and select Unzip all. Once everything has been unpacked, then you open your Excel accounting template.

If you open an Excel-sheet directly from a ZIP file, you will often get a "Read Only" error message.

No macros – better security
The Excel bookkeeping template was deliberately designed without any macros. Only formulas are used to help you process your transactions, create your business documents and draw up professionally designed dashboards and reports. The absence of macros makes your template much more secure.

Tip 1
Please save your file using a logical filename for easy reference.
For example: Bookkeeping_YourCompanyName_2018-001.xlsx

Tip 2
When saving your worksheet always use a new serial or reference number. In case something goes wrong you can always continue working in a previously saved template. You have not lost all your work right away.

Since you are installing the template on a local hard drive, please make sure to backup your files regularly. You can manually back up your files on an external hard drive or an USB stick. I personally prefer the cloud. Since you are working with Microsoft Excel you most likely have OneDrive installed as well (Office 365 come with 1 TB free space). Once you save your files in the OneDrive directory of your computer all your files are automatically uploaded to the cloud.

You can literally get started with the Excel bookkeeping template within minutes.

The first sheet contains fixed data such as your business name, address, bank account, payment term and your VAT number and CoC number. This information is shown on your invoices. Next, you enter the applicable calendar year.

Fiscal year
If your fiscal year deviates from a calendar year, then enter the fiscal year starting month too. For instance, if your fiscal year starts at April 1^st then enter the number 4.

If applicable, change the vat rates. We recommend not to change the text in the payment status table and transaction types table. If you do change them, make sure you have not used them yet. If you have, you need to reselect the payment status or transaction type in the Sales & Income and Purchases & Expenses tab.

Navigate the Excel bookkeeping template using the icons at the top of each tab:

Or use the tabs at the bottom: Settings, Invoice, Sales & Income, Purchases & Expenses, Relations, Categories, Dashboard, Opening Balance and Tax(-alt).

All Sales & Income and Purchases & Expenses are allocated to a category. You can adjust the list of categories (ledger accounts) to your own discretion.

Attention
If you make adjustments to his table, please make sure to reselect the correct category in the Sales & Income and Purchases & Expenses table.

You can quickly and easily create beautiful invoices with the Excel bookkeeping template. Go the ‘Invoice’ tab. The data entered in the ‘Settings’ tab is automatically shown on your invoice.

You will find a logo at the top left. You can change the logo by right-clicking it and select Change image… Then select your own logo from your hard drive. You can adjust the size of the logo by dragging the corners.

Enter the invoice number (cell J13) and select a customer (cell C18). If you cannot find your customer, please at him at the ‘Relations’ tab. After selecting the customer, the address will automatically be added to your invoice.

Now enter the invoice date (cell J17). The due date is automatically calculated based on the applicable payment term (see ‘Settings’ tab).

Next, you enter all sold products and rendered services. In each line you enter a description, the quantity, the price, any discount percentages and the applicable vat rate (cells C24:I37).

The line number appears automatically if you enter a description. The amount (excluding vat) is automatically calculated per line. For example:

You can change the text below the headline ‘Terms & Notes’.

Always double check your invoices. Did you select the right customer? Is the invoice date correct? Are the prices correct? Did you change the terms and notes?

Now save the invoice as a PDF-file. Click File, Save as…, select PDF (*.pdf) as document type.

To process your invoice please copy 1, 2 or 3 invoice lines beneath your invoice (cells C47:K49) to the ‘Sales & Income’ tab. ALWAYS use Paste Special, Values. Otherwise you will accidentally copy the cell properties too.

You will be notified when the invoice number already exists. A warning message appears beneath the invoice lines ‘Invoice already exists in sales record’.  Simply change the invoice number. No two invoices with the same number can exist. Invoices should be numbered consecutively.

Note: If there are multiple VAT rates on one invoice, these will be broken down for each VAT rate in the invoice lines under the invoice. As a result, it can seem as if there are multiple invoices with the same invoice number.

Before you create a new invoice, you can clear the previous invoice. Click on the button "Clear invoice". Now all cells are selected that you can clear by pressing the 'Del'-key. Now you can create a new invoice. The other data on your invoice will of course remain.

In the ‘Sales & Income’ tab you record all sales invoices and other income such as cash deposits, owners’ contributions etc. As a rule, all bank and cash receipts are recorded here.

Enter all sales invoices. Last year’s invoices too. Enter the date, transaction type and a description of the products sold/services rendered. Select a customer. Enter the amount excluding VAT and the applicable VAT rate. VAT is automatically calculated. In the ‘Status’ column, select whether the invoice is still ‘Outstanding’ or has been paid by the customer via Bank or Cash. Then select a category (which in almost all cases should be a sales account. Range: 2000 – 2030. Don’t worry. You can always change the category later. All sales are presented in the Profit & Loss statement of the ‘Dashboard’.

The Excel bookkeeping template assists you in avoiding (fatal) mistakes when processing your transactions as much as possible. A warning will appear in the Check column when you make a mistake. The following messages may appear:

Incomplete data
This message shows up when you forget to enter a mandatory field. Simply enter any missing data. For instance, a relation or date.

Date outside financial year
All sales and income outside the financial year are not calculated in the reports. In that case, you will see this message. You can change the financial year at the ‘Settings’ tab.

On opening balance
The only exception to this rule is outstanding balances from last year. They will show up as accounts receivable on the opening balance. Has the invoice been paid? Then this line belongs to a previous year.

In the ‘Purchases & Expenses’ tab you record all purchase invoices and receipts, but also other expenses such as owners’ withdrawals, insurance collections etc. As a rule, all bank and cash expenditures are recorded here.

Enter all purchase invoices. Last year’s invoices too. Enter the date, transaction type, invoice number, and a description. Then select a supplier. Enter the amount excluding VAT and the applicable VAT rate. VAT is automatically calculated. In the ‘Status’ column, select whether the invoice is still ‘Outstanding’ or has been paid to your supplier via Bank or Cash. Then select a category (which in most cases would be a cost account. Range: 2200 – 3310. Don’t worry. You can always change the category later.

The Excel bookkeeping template assists you in avoiding (fatal) mistakes when processing your transactions as much as possible. A warning will appear in the Check column when you make a mistake. The following messages may appear:

Incomplete data
This message shows up when you forget to enter a mandatory field. Simply enter any missing data. For instance, a relation, category or date.

Date outside financial year
All purchases & expenses outside the financial year are not calculated in the reports. In that case, you will see this message. You can change the financial year at the ‘Settings’ tab.

On opening balance
The only exception to this rule is outstanding balances from last year. They will show up as accounts payable on the opening balance. Has the invoice been paid? Then this line belongs to a previous year.

In the ‘Dashboard’ tab totals of all sales and purchases are shown for the current year as well as all years. This report is automatically recalculated when you make any changes in sales or purchases. Outstanding amounts are also shown.

Next you will see the Profit & Loss statement. Amounts are totalled per category. At the bottom you can see if you made a profit or not. The Profit & Loss statement is automatically recalculated after each addition or change to the sales and purchases. All cells contain formulas; hence you cannot make any changes.

The Balance Sheet is also automatically generated. Assets and Equity & Liabilities are show per category. The amounts change after each addition or change in the sales or purchases tables.

Entrepreneurs who are already active before the beginning of the year, will also have to input the opening balance. The opening balance is equal to the ending balance of last year. The opening balance only contains assets and equity & liabilities categories (no sales or costs categories). The opening balance is included in the ending balance of the current year. Last year’s outstanding invoices – which you inputted in Sales & Income and Purchases & Expenses are automatically included in Accounts receivable and Accounts payable.

Finally, all sales tax amounts or your invoices and receipts are automatically processed in the tax report. You will see taxable sales and their sales tax as well as tax on purchases. You instantly see if you must pay or will receive money. You can use these amounts for your sales tax return. This report is also available per month.

The payment/receipt of the sales tax is processed using the category 1155 VAT paid/received.

The last step is preparing next year’s template. Use these steps to create yours:

To create next year’s opening balance, you will this year’s ending balance. Use a new empty template for the next year. Copy your settings and relations tot his empty sheet. Always use Paste Special, Values. Now you can start inputting the opening balance. Use the same categories as the ending balance.

1. First, make sure you do not see any error messages in the Checks column of the ‘Sales & Income’ tab and the ‘Purchases & Expenses’ tab. The message ‘On opening balance’ is the only exception (ignore this).
2. Next year’s opening balance is based on this year’s ending balance.
3. Print the ending balance.
4. Use a new template for the next year and save this file using a logical name, for instance: boekkeeping-businessname-2018-001.xlsx.
5. Copy your Settings and Relations from the old template. Always use Paste Special, Values! Change the financial year and the vat rates when necessary.
6. Go to the Opening balance and enter the values from last year’s value ending balance.
7. Add up all amounts of the equity accounts (1300, 1310 and 1320) and add the profit or deduct the loss. This total is entered at 1300 Capital.
8. We recommend summing all tax accounts (1140, 1141, 1150 en 1155) and enter this amount at account 1155 VAT paid/received.
9. Enter all outstanding invoices in your new template. Always use Paste Special, Values.