FAQ: Bookkeeping System

MULTI-YEAR BOOKKEEPING SYSTEM

Frequently asked questions.

Find answers to common questions about using your Multi-Year Bookkeeping System, including the Auto-Mapping version.

Using your template

Categories, credit cards, sales tax, paying yourself, accruals, the Balance Sheet and sharing your reports.

Most asked
How does the category roll up structure work?

When you record a transaction in the Transaction Log, you assign it a main category and a sub-category. The main category is the high level grouping, and the sub-category captures the specific detail underneath it. Every transaction flows into both levels automatically, so nothing is ever lost. It simply rolls up into the broader category on high level reports.

Here’s how the roll up shows up throughout the template:

  • Profit & Loss: the Summary view shows main categories only for a clean, high level snapshot across all 12 months. The Detailed view breaks every main category into its sub-categories by month, so you can see exactly what’s driving each line.
  • Dashboards: key metrics and charts show at the main category level for a quick performance review. Sub-category detail is available further down each dashboard if you want to drill deeper.
  • Transaction History: filter and view all transactions at the sub-category level across any time period, similar to a general ledger in professional accounting software.

This layered approach lets you understand performance at a high level, while always being able to drill into the full detail when you need it.

Can I use this template for personal budgeting?

Yes. Enter your name instead of a company name on the Inputs tab and customize the categories for personal use.

I need Cost of Goods Sold for my business. How do I set it up?

No problem. Set up Cost of Goods Sold as its own main expense category in Section 1.3 of the Inputs tab. Under it, create sub-categories for the direct costs of producing your goods or services.

For clarity, you can name the sub-categories with a COGS prefix, for example:

COGS: Raw MaterialsCOGS: FreightCOGS: Packaging

This keeps your Cost of Goods Sold clearly separate from your operating expenses in your reports.

Can I add more than 15 main categories or 75 sub-categories?

The template is intentionally designed with up to 15 main categories and 75 sub-categories (for both income and expenses) to keep your financial reporting clear, structured and easy to analyze as your business grows. For most small businesses, this is more than enough detail and avoids overcomplicating your dashboards and financial statements.

If you need more categories, send me a message and we can talk about a custom version.

How do I record credit card purchases and payments?
  1. Set up your credit card on the Inputs tab. In Section 1.2, add your credit card as an account and enter the opening balance as a negative amount. Use the balance as of your start date. For example, if you start in January 2025, use the balance as of January 1, 2025.
  2. Enter your credit card purchases in the Transaction Log. In the From Account column, select the credit card used, and complete the rest as normal. These flow into your Profit & Loss and Dashboards, and show as outflows on the account reconciliation tabs.
  3. Record credit card payments as a Transfer. When you pay your credit card, select Transaction Type Transfer and complete both fields:
    • From Account: the account you’re paying from (for example, your bank chequing account)
    • To Account: the credit card you’re paying (for example, MasterCard XXX)

On the account reconciliation tabs, your bank account shows an outflow and your credit card shows an inflow, reducing the outstanding balance.

You can also use Transfer to move money between bank accounts, like from Chequing to Savings.

How do I record sales tax?

Always enter the total amount you received or paid in the Transaction Log, including any sales tax. Then enter the sales tax rate for that transaction. The template works backwards from the total to split out the tax and the net amount for you. It never adds tax on top.

  • Example: you received $113 from a customer and the tax rate is 13%. Enter $113 as the amount and 13% as the rate. The template records $100 as income and $13 as sales tax.
  • Only the net amount flows to your Profit & Loss, so your income and expenses aren’t inflated by tax.
  • Enter a year on the Sales Tax Reporting tab to see the sales tax you collected and paid, to help with your sales tax filings.

Don’t collect or pay sales tax? Leave the rate blank and the full amount flows to your Profit & Loss.

How do I record money I pay myself?

It depends on how your business is set up:

  • Sole proprietors and freelancers: money you take out for yourself isn’t a business expense. Record it as Owner’s Withdrawal / Other Withdrawal and select the account it came from. It reduces your cash but doesn’t affect your Profit & Loss.
  • Incorporated businesses that run payroll: your salary is a business expense. Record it as an Expense under your salaries or payroll category.

Putting money into the business works the same way in reverse. Record it as Owner’s Contribution / Other Deposit.

Not sure which applies to you? Check with your accountant.

Can I use accrual basis accounting? How do I record accruals?

Watch the full accruals tutorial on YouTube

Yes. The template is built for cash basis (you record income and expenses when the cash moves), which keeps your bank reconciliation simple and works well for most small businesses. But you can easily convert it to accrual basis using the accounts section.

With accrual basis, you record income when it’s earned and expenses when they’re incurred, even if the cash hasn’t moved yet. For example, you finish a project in August but your client pays in September, or your August water bill doesn’t arrive until September. Accruals match your income and expenses to the month they actually belong to.

How to record accruals in 4 steps:

  1. Set up your accrual accounts. On the Inputs tab, add Accrued Revenue and Accrued Expenses as accounts in Section 1.2, alongside your bank accounts and credit cards (up to 10 accounts in total).
  2. Record the accrual in the month it relates to. Use the last day of the month and select the accrued account instead of a bank account.
    • Income: Accrued Revenue as the To Account
    • Expense: Accrued Expenses as the From Account. If you don’t have the bill yet, use your best estimate (your Detailed Profit & Loss history helps here).
  3. Record the cash as normal when it actually comes in or goes out, using your bank account or credit card.
  4. Reverse the accrual in the same month the cash moves. Enter the same transaction as a negative amount, with the same accrued account, category and sub-category. This removes the double count and keeps both months clean.

Built-in check: use the Accounts Reconciliation tab on your accrued accounts. At month end, the balance should equal your accruals. Once the cash comes in and you’ve reversed, it should be zero. If it shows unreconciled, you probably forgot a reversal.

How does the Balance Sheet work?

The template includes a manual monthly Balance Sheet. At the end of each month, once all your transactions are recorded and your books are complete, enter your closing balances for accounts like bank accounts, credit cards, loans, sales tax payable and accrued liabilities.

Since this is a multi-year template, once you finish a fiscal year, save or export a copy of the Balance Sheet tab for your records, then clear the balances to begin the next year.

How do I share my reports with my accountant?

Hide the helper notes first (on the Inputs tab) for a clean view, then:

  • Excel: go to File, then Save As (or Print) and choose PDF. Use landscape and fit to one page for the dashboards.
  • Google Sheets: go to File, then Download, then PDF. Or click Share and add your accountant’s email, set to Viewer or Commenter so nothing gets changed by accident.

Your accountant will usually want your Summary or Detailed Profit & Loss, your Balance Sheet and your account reconciliations.

Getting started and year end

Starting partway through the year and what to do when your year wraps up.

Start here
I’m starting partway through the year. What do I enter?

Important: your start month on the Inputs tab should always match the first month of your fiscal year, not the month you start using the template.

For example, if your fiscal year runs January to December and you start using the template in May, your start month should still be January.

  1. On the Inputs tab, select January as your start month and enter the current year as your Start Year.
  2. Enter your account opening balances as of January 1, not May 1.
  3. Catch up on January to April by pasting in those months from your bank and credit card CSV statements, then carry on as normal.

This keeps your full year Profit & Loss, dashboards and year over year comparisons complete and accurate.

What do I do at the end of the year?

Since this is a multi-year template, you keep using the same file. There’s no need to start a new one. At year end:

  • Close out your last month as usual: all transactions entered, the warning counter at zero and your accounts reconciled.
  • Save a copy of your Balance Sheet for your records, then clear the balances to start the new year.
  • Add next year’s budget on the Budget tab.
  • Save a backup of your whole file, just in case.

Then keep entering transactions as normal. Your dashboards and 5 Year Dashboard will show the new year alongside your past years.

Auto-Mapping version

Questions about the Mapping Setup tab and keyword rules.

Auto-Mapping only
Do I have to use the Mapping Setup tab?

No, it’s completely optional. If you prefer to enter transactions manually or don’t import from a bank CSV, you can skip and hide the Mapping Setup tab. Just select your Transaction Type, Category and Sub-Category in the Override columns of the Transaction Log.

What if a keyword maps a transaction to the wrong category?

Use the Override columns in the Transaction Log to fix that specific row. Your override always takes priority over the auto-mapped result.

If it keeps happening, your keyword is probably too broad. Keyword matches are partial, so “ESSO” would also match “MONTESSORI”. Make the keyword more specific on the Mapping Setup tab.

My bank shows the same store in different ways. What do I do?

Bank descriptions aren’t always consistent. For example, you might see “WALMART” one month and “WAL-MART” the next. Simply add two separate keyword rules pointing to the same category, and both will map correctly. Keywords aren’t case sensitive, and you can add up to 1,000 rules.

Why aren’t my accounts filling in automatically?

Keyword rules fill in the Transaction Type, Category and Sub-Category only. The From and To Accounts don’t auto-populate from the Mapping Setup tab, so select them manually for each transaction.

Protected cells and backups

Editing protected cells and keeping a clean copy of your template.

Good to know
Can I unprotect the workbook to make changes?

In Excel

Go to Review, then select Unprotect Sheet. This removes protection from that tab only, so you can make edits like changing colours, formatting or layout. When you’re done, go back to Review and select Protect Sheet to turn it back on.

Note: Please be careful when editing, since deleting or changing formulas can affect how the workbook works. Protection is set for each tab, so unprotecting one tab won’t affect the others.

In Google Sheets

You can temporarily bypass protection. When you try to edit a protected cell, a Heads up warning appears. Tick Don’t show this again for 5 minutes and click OK. You can then edit protected cells for 5 minutes, and protection resumes automatically afterward.

Removing protection permanently (not recommended):

  1. Go to Data, then Protect sheets and ranges. A panel opens on the right.
  2. Click the protected sheet name.
  3. Click the trash can icon and confirm.

Note: This removes protection completely. It won’t come back on its own and would need to be set up again manually.

What happens if I edit a protected cell? How do I start fresh?

Protected cells prevent accidental changes to formulas. If you see a protection message, click Cancel and only enter data in the input cells.

If formulas are accidentally changed and the workbook stops working as expected, it’s best to start with a fresh copy.

  • Keep a clean backup: save a blank copy of the template to your own drive as soon as you download it, and leave it untouched.
  • Work in a copy: when you’re ready to start, open the blank file and use Save As to create your working copy. That way, you always have a clean version if you need to start over.

Since this is a multi-year template, you can keep using the same file year after year. You only need a new copy if you want a completely fresh start or you’re setting up a different business.

Troubleshooting

Quick fixes if something isn’t opening or adding up.

Quick fixes
Why won’t my Excel file open?

Most of the time, it’s because the folder hasn’t been unzipped yet. Files opened from inside a ZIP folder may not open or save properly.

  1. On Windows: right-click the downloaded ZIP folder and choose Extract All.
  2. On a Mac: double-click the ZIP folder to unzip it.
  3. Open the template from the new, unzipped folder.
  4. If Excel shows a yellow Protected View bar at the top, click Enable Editing.
How do I use the Google Sheets version?
  1. Sign in to Google. If you have a Gmail address, you already have a Google account. If not, create one for free at accounts.google.com.
  2. Open your download and click the Google Sheets link.
  3. Click Make a copy. This saves your own copy to your Google Drive.
  4. That’s it! Your copy saves automatically. Find it any time at sheets.google.com or in your Google Drive.

Always work in your own copy. Please don’t request edit access to the original link.

Can I use the template on a Mac?

Yes. The Excel version works on both Mac and PC, and Google Sheets works on any device with a web browser. The template isn’t compatible with Apple Numbers, since it doesn’t support all of the formulas.

My dashboards are blank or the numbers look wrong.
  • Check the warning counter at the top of the Transaction Log. It must show zero before your dashboards are accurate.
  • Check the Chart of Accounts warning. If a main category is missing a sub-category, those transactions won’t show on your dashboards.
  • Check the month and year selected at the top of the dashboard.
  • Check your dates in the Transaction Log are valid and on or after your Start Year.
My account won’t reconcile. What should I check?
  • Every transaction has the correct From or To Account.
  • Your opening balances on the Inputs tab are correct (bank accounts positive, credit cards and loans negative).
  • Credit card payments are recorded as a Transfer, not an expense.
  • Statement dates are entered in order, with only one statement per account per month.

Still need help?

If you can’t find the answer you’re looking for, feel free to reach out.

CONTACT ME