Multi-Year Bookkeeping System Auto Mapping: Setup Guide

Help  /  Multi-Year Bookkeeping System

SETUP GUIDE

Multi-Year Bookkeeping System

WITH AUTO-MAPPING

Thank you for purchasing the Multi-Year Bookkeeping System! This is a complete bookkeeping system, set up the way an accountant keeps books. Work through this guide at your own pace, one step at a time, and keep it handy as a reference as you go.

Step-by-step guide Excel and Google Sheets Video walkthrough

WATCH FIRST

Prefer to watch? Start here.

This video walks through the Auto-Mapping version, including how to set up your keyword rules and paste in your bank transactions.

WATCH ON YOUTUBE

Before you start

First time using the template?

Start here! This is a full bookkeeping system, so follow the steps below in order. Helper notes and smart prompts are built in to guide you as you go.

Not sure where to begin? Watch the video above first, then come back and work through the steps. Have a quick question? Check the template FAQ.

Protected cells

Your template has locked cells that protect the formulas and links between tabs. Keep the protection on so nothing breaks by accident. You only type in the white cells.

EXCEL

Locked cells can’t be selected or changed. Click a white cell to keep going.

  • Turn protection offReview → Unprotect Sheet
  • Turn it back onReview → Protect Sheet → OK
  • Excel for the webReview → Manage Protection, then switch it off or on
GOOGLE SHEETS

Because you own your copy, Sheets shows a warning instead of blocking you. If it pops up, click Cancel.

  • Turn protection offData → Protect sheets and ranges → click the sheet → trash icon
  • Turn it back onData → Protect sheets and ranges → Add a sheet or range
  • Each tab is separateProtection is set tab by tab, so only remove it where you need to
Before you unprotect: save a copy of your file first, only change what you need, then turn protection back on. Formula cells can’t be undone once they’re overwritten and saved.

Legend

Your template has three kinds of tabs. White cells are always where you type.

INPUT TABS

Where you enter your information.

  • SetupCompany details, fiscal year, accounts and categories. Do this first.
  • BudgetYour monthly net income targets, year by year.
  • Mapping SetupYour keyword rules that fill in the Type, Category and Sub-Category for you.
  • Transaction LogWhere you’ll spend most of your time. One row per transaction.
  • Balance SheetUpdated at month end: enter your balance sheet account names and amounts.
REVIEW TABS

Automatic and protected. Some you just read; on others you pick the month or year to view.

  • Chart of AccountsYour categories at a glance, built from the Setup tab. Nothing to select.
  • Profit & LossPick the year to see monthly income, expenses and cash flow.
  • DashboardsPick the month and year for the Monthly, Annual and 5-Year views.
  • Sales Tax ReportingSales tax collected and paid, ready for filing.
HYBRID TABS

Mostly automatic, with a few white cells where you enter dates, balances or choices.

  • Accounts ReconciliationEnter your statement dates and balances to confirm each account matches.
  • Accounts CustomChoose any date range to check your account balances.
  • Transaction HistorySelect a category or account to see its transactions.

See a fully populated example

See how everything flows together in a completed version. The button opens the Google Sheets example. Using Excel? The example file is in your download folder.

Bank CSV import file

Skip the typing. Paste your bank or credit card transactions into the CSV import file and it lines them up for your Transaction Log. Instructions are inside the file.

The file has two import tabs. Use North America for month first dates (MM/DD/YYYY) or International for day first dates (DD/MM/YYYY). If a date shows a warning, try the other tab.

Using Excel? The CSV import file is in your download folder.

Template FAQ

Have a question or something not working as expected? The template FAQ has quick answers, plus a troubleshooting section for common fixes.

Let’s get started

Jump to a section

Tap a section to jump straight to it. Click any step to collapse or expand it.

Setup tab Your company details, accounts, opening balances and categories.

Step 1⚙️ Setup

Enter your Company Name (or your name if you’re using it for personal budgeting), your Start Year (the year you begin using the template), select the statement name from the drop down, and set your currency by replacing the dollar symbol with your own.

Optional: select the starting month of your fiscal year, or leave it as January if you follow the calendar year.

Step 2🏦 Set up your accounts

Add up to 10 accounts where money comes in or goes out, like bank accounts, credit cards or loans, along with their opening balances. These are used later for your account reconciliations.

  • Your opening balance should reflect the balance at the time you first begin using your template. For example, if you selected January as your starting month and 2025 as your start year in Step 1, enter the opening balances as of January 1, 2025.
  • Bank accounts: enter as positive amounts.
  • Credit cards and loans: enter as negative amounts.

Step 3📁 Set up your main categories

Create up to 15 main income and 15 main expense categories. These are your high level reporting buckets that show up on your Profit & Loss and dashboards. Keeping the number of main categories limited is intentional. Too many main categories reduces clarity and makes financial analysis harder. These should remain broad and strategic. For example, General & Administrative or Salaries & Payroll.

Step 4📂 Add your sub-categories

Under each main category, add the detail. For example, General & Administrative might include Professional Fees, Legal Fees, Accounting Fees and Office Expenses. They all roll up into the main category on your reports.

  • Up to 75 income and 75 expense subcategories
  • To maintain performance and ensure the template runs smoothly, each main category is limited to 25 subcategories per main category.

DuplicatesIf a category or sub-category is highlighted on the Setup tab, it’s a duplicate. Rename or remove it so your reports stay accurate.

Why it works: This structure mirrors how professional accounting systems are designed. It gives you detailed tracking at the transaction level while keeping dashboards and reports clean, readable, and decision focused.

Optional👀 Hide notes

Helper notes are built in throughout the workbook. To hide them for a cleaner view or for printing, tick the checkbox (Google Sheets) or type YES (Excel). Clear it any time to bring them back.

TipOnce you’ve entered transactions, don’t rename your categories or subcategories, since that breaks the links between tabs. If you don’t need a category, simply leave it blank. You can always add new categories as you need them.

Chart of Accounts A clean view of how your categories roll up.
No manual input required

The Chart of Accounts tab is automatically generated based on the categories you define in the Setup tab. This tab does not require any manual entry. As you add or update income and expense categories in the Setup tab, they will automatically appear here and remain organized by Main Category and Sub-Category.

Error checkCheck the warning at the top of the tab. If any main category is missing a subcategory, a red warning appears and those transactions won’t show on your dashboards. Clear the warning before you move on.

Budget Set your net income goal for each month.

Enter your budgeted net income (or savings) by month. Your dashboards use it to track your progress.

  • Budget up to 10 years, starting with the Start Year on your Setup tab
  • No need to fill in every year now. Add each year’s budget when it begins.
  • Enter a positive number for a profit goal and a negative number for a planned loss
  • Totals calculate on their own and flow into your dashboards
Auto-Mapping version Mapping Setup Set your keyword rules once and let your transactions sort themselves.

Take a few minutes to set this up before you start your Transaction Log. It’s optional, but if you paste in transactions from your bank, it saves a lot of time every month.

How it works

Bank descriptions are messy, like “STARBUCKS 12345 TORONTO ON” or “NETFLIX.COM 1234567890”. On this tab you create keywords the template looks for. When you paste your bank transactions into the Transaction Log, it scans each description, finds a match, and fills in the Transaction Type, Category and Subcategory for you.

Set it up

  1. Enter a keyword in the Description Keyword column. Pick a word that shows up consistently in your bank descriptions, like Starbucks or Netflix.
  2. Choose the Transaction Type, Category and Subcategory that keyword should map to. Your categories from the Setup tab appear in the drop downs.

From then on, any transaction with that keyword is categorized automatically every time you paste in your bank data.

Good to know

  • Partial matches work. The keyword match is partial, so if your bank description says “STARBUCKS 12345 TORONTO” and your keyword is “Starbucks” it will still match. You do not need to enter the full description. However make sure your keyword is specific enough to avoid unintended matches. For example entering “ESSO” could also match “MONTESSORI” since the letters appear within the word. When in doubt use a more specific phrase.
  • Banks aren’t always consistent. If you see “WALMART” one month and “WAL-MART” the next, add both as rules pointing to the same category.
  • Not case sensitive, so capitals don’t matter.
  • Up to 1,000 rules. The more you set up now, the less you pick by hand each month.
  • Optional. Prefer to enter everything yourself? Skip this tab and use the Override columns in the Transaction Log.
Transaction Log This is where you record every transaction. Do this daily or weekly as you go.

Every report in the template is built from this tab, so this is where you’ll spend most of your time.

Step 1🗓️ Input date of transaction

If a date is not entered correctly, a pop-up will appear. Ensure a valid date is entered.

Step 2✏️ Enter the Description

Enter a description for the transaction. This is for your own recordkeeping and can be as brief or detailed as you like. You can also use the description directly from your bank statement. If you are importing from your bank CSV, this comes directly from your bank statement and is used to match against your Mapping Setup keyword rules.

Step 3💰 Enter the transaction amount (and optional sales tax)

Enter all amounts as positive values, except for the following cases:

  • Returns and Discounts → negative values
  • Expense Recoveries (e.g., income tax recovery) → negative values

💡 Save timeSteps 1 through 3 can be completed all at once using the CSV import method. See CSV Import at the bottom of this step.

Sales tax Optional

The Transaction Amount should always be the total amount received or paid, including any sales tax. If you enter a sales tax rate, the template automatically extracts the tax and calculates the net amount for you. It works backwards from the total, so 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, and the template calculates $13 as the tax and $100 as the net amount.

Don’t collect or remit sales tax? Simply leave the Sales Tax Rate column blank and the full amount flows directly into your Profit & Loss.

Step 4🏷️ Pick your Override Transaction Type

This is the most important field. Income and Expense transactions impact your Profit & Loss. All other transaction types affect cash/accounts only.

  • If you are using the Mapping Setup tab: The Auto Transaction Type column is already populated from your bank description. Review it and move on. If it’s blank or incorrect, use the Override Transaction Type column.
  • If you are not using the Mapping Setup tab: Select the Transaction Type directly in the Override Transaction Type column.
Income

Money coming into your business that impacts your Profit & Loss

  • Select a Category and Sub-Category
  • Select a To Account (where the money was deposited)
  • Leave From Account blank
Expense

Money leaving your business that impacts your Profit & Loss

  • Select a Category and Sub-Category
  • Select a From Account (where it was paid from)
  • Leave To Account blank
Transfer

Moving money between your own accounts, like a credit card payment (see the FAQ)

  • Select both a From Account and a To Account
  • Leave Category and Sub-Category blank
Owner’s Contribution and Other Deposit

Cash you put into the business (does not impact Profit & Loss)

  • Select a To Account only
  • Leave everything else blank
Owner’s Withdrawal and Other Withdrawal

Money you take out of the business (does not impact Profit & Loss)

  • Select a From Account only
  • Leave everything else blank

Don’t worry!You don’t need to memorize this. As you enter each transaction, the Smart Prompts column in your Transaction Log will guide you and show a clear message if something needs to be fixed.

Step 5📂 Select your Category and Sub-Category

  • Using the Mapping Setup tab? The Auto Category and Auto Sub-Category columns are already populated. Review them and use the Override columns if they’re blank or incorrect.
  • The drop-down selections populate from the categories and sub-categories you created in Steps 3 and 4 of the Setup tab.
  • Required for all Income and Expense transactions. These selections drive your dashboards and Profit & Loss.
  • Not required for transfers, deposits or withdrawals.

Step 6🏦 Select your Accounts

  • Select the “To” and/or “From” Account based on the Transaction Type. This is only required if you are tracking cash flow and using the Accounts Reconciliation or Accounts Custom tab.
  • Accounts aren’t limited to bank accounts and credit cards. You can also set up accrual accounts and tag transactions to them (see the FAQ for how). If a transaction doesn’t relate to any account, just select “No Account Assigned”.
  • Accounts do not auto-populate from the Mapping Setup tab and must be selected manually.

Step 7⚠️ Check your warning counter before reviewing dashboards

The warning counter at the top of the Transaction Log must show zero before your dashboards will be accurate. If you see a warning, the row will tell you exactly what to fix. Just follow the message. Common fixes:

  • Missing Transaction Type → add it
  • Wrong Category and/or Sub-Category for the Transaction Type → re-select from the drop-down (useful if you accidentally change a category name)
  • Transfer with same From and To Account → they must be different accounts

💡 CSV Import: Copy and Paste from your Bank Statement

You can save significant time by copying transactions directly from your bank statement CSV download instead of entering them one by one. See the separate “CSV Import File” included in your downloads, which includes detailed instructions.

The file has two import tabs. Use North America for month first dates (MM/DD/YYYY) or International for day first dates (DD/MM/YYYY). If a date shows a warning, try the other tab.

  1. Download your bank statement as a CSV from your online banking portal, and open the CSV and arrange your columns to match the Transaction Log order: Date, Description and Amount.
  2. Use Paste Special (not regular paste) to paste values only. This preserves the existing formatting and formulas in the template. You can paste directly into the Transaction Log, or use the CSV Import File for assistance.
  3. Once pasted, go through each row and select the Transaction Type, Category, Sub-Category and Accounts from the dropdowns, OR use the Mapping Setup tab to set up keyword rules that automatically populate your Transaction Type, Category and Sub-Category based on the description.
Profit & Loss Your profit by month, in a summary and a detailed view.
No manual input required

Your Profit & Loss pulls everything from the Transaction Log, using the categories from your Setup tab. Blue cells calculate on their own. Use it to spot trends, compare months and see how profitable you are.

  • Summary Profit & Loss: main categories only, for a clean, high level view
  • Detailed Profit & Loss: every subcategory under its main category, sorted alphabetically, so you can see what’s behind each line

Both views stay in sync automatically.

Monthly, Annual and 5 Year Dashboards Select a month or enter a year and your charts fill in.

Monthly Dashboard

Select a month and enter the year to automatically populate the Monthly Dashboard for the selected period. The dashboard provides a high level view of key metrics, including income, expenses, net income, income allocation, expense distribution, and progress toward your savings goal.

  • Automatic prior year comparison: prior year results populate automatically, so you can quickly review variances and identify seasonal trends.
  • 15 month trailing chart: when a month is selected, income, expenses and net income for the prior 15 months are displayed to help visualize performance trends over time.
    • For example, selecting May 2025 populates May 2024 comparatives and displays a trailing period from March 2024 through May 2025.

Annual Dashboard

Enter the year to automatically populate the Annual Dashboard for the selected year. This dashboard provides similar charts and key metrics as the Monthly Dashboard but presents results by month across the full year, along with full year totals.

Use this dashboard to review annual performance, compare monthly trends, and evaluate overall results for the selected year.

5 Year Dashboard

Enter the first year in the 5 year range to automatically populate the 5 Year Dashboard. The following four years will auto populate, displaying totals and visual charts for the full five year period.

This dashboard provides a high level view of long term performance, including revenue, expenses, net income, and key trends over multiple years. Use this view to assess growth, consistency, and overall financial direction.

Transaction History Drill into the transactions behind any category.
No manual input required (other than category selection)

The Transaction History tab provides a consolidated, read only view of all transactions entered in the Transaction Log. All data flows in automatically.

  • Two sides: income transaction history on the left, expense transaction history on the right
  • Filter by main category to view all related activity
    • To drill down further, select a sub-category to narrow your results
    • Optional: filter by year by entering the year directly

This drill down functionality mirrors how professional accounting systems allow users to explore general ledger activity. It helps you understand your transactions, investigate variances, and analyze activity at both a high level and a detailed level.

Account Reconciliation Two ways to review your account balances. Use one or both.

This template includes two ways to review your account balances. Both tabs allow you to monitor all cash activity across your accounts, including bank and credit card statements. All transactions flow automatically from the Transaction Log based on the account selected.

ImportantFor this to function properly, each transaction must include the correct From Account or To Account.

Review each option to decide which is more suitable for you, and hide the other tab. Or feel free to use both!

Option 1

Accounts Custom tab

A flexible way to monitor cash activity across your accounts using custom date ranges. Great for checking balances for any period you choose. It does not perform statement reconciliation.

Custom View

  • Input the “From” and “To” date.
  • The opening balance reflects all activity before the “From” date, plus the opening balance entered in the Setup tab.
  • It pulls all transactions in the Transaction Log within the selected date range and calculates the ending balance for each account.
Option 2

Accounts Reconciliation tab

Reconcile your accounts directly to your bank or credit card statements, similar to traditional accounting software. The template determines the statement period and compares the balance from your Transaction Log to the balance on your statement.

Accounts Reconciliation View

  1. Select the Account: scroll to the far right of the tab and choose the account from the drop-down menu. The list comes from your account setup in the Setup tab.
  2. Enter the Statement Start & End Date exactly as shown on your bank or credit card statement.
  3. Enter the Statement Balance: the ending balance from your statement.
  4. Let the template do the rest: it pulls every transaction from your Transaction Log within the statement period, calculates the ending balance and compares it to the statement balance you entered.
  • If the difference falls within the tolerable range ($), the account displays “Reconciled.” You can set this range at the top of the tab.
  • If it displays “Unreconciled,” review your Transaction Log to ensure all transactions have been entered correctly.
Both options include
  • Current View: the opening balance reflects the first day of the template start date defined in the Setup tab, and pulls all transactions from the beginning of the year up to and including today’s date to calculate the ending balance for each account.
  • Summary Charts: key metrics are summarized at the top of the tab for a quick overview, and two charts on the left show total cash flow activity and ending balances across all accounts.

First monthThe statement start date defaults to the template start date defined in the Setup tab. For example, if the template begins in January 2026 and your first statement ends on January 25, 2026, transactions will be pulled from January 1 to January 25, 2026. Any activity before the template start date should be reflected in the opening balances entered in the Setup tab.

ImportantOnly one statement date per account per month should be entered. Duplicate months will trigger an error warning, and statement dates should be entered in chronological order.

Sales Tax Reporting Sales tax collected and paid, ready for filing.
No manual input required

If you use the sales tax rate column in your Transaction Log, enter a year here to see the sales tax you collected and paid, by period. Use it to support your sales tax filings.

Balance Sheet Enter your month end balances once your books are done.

At the end of each month, once all your transactions are in, enter the closing balances for your bank accounts, credit cards, loans, sales tax payable and other assets and liabilities. The account names are a starting point, so rename them to fit your business.

At the end of your fiscal year

Since this is a multi-year template, the Balance Sheet doesn’t roll forward on its own.

  1. Save or export a copy of your completed Balance Sheet for your records.
  2. Clear the balances in the template.
  3. Start the new year with fresh opening balances.

That’s it. Close your month with confidence and be ready for year end.

Disclaimer: This template is provided for small business budgeting and reporting purposes only and does not constitute professional financial, accounting, or tax advice. Always review your numbers carefully and consult a qualified professional before making financial or tax decisions.

© 2026 InsightSheets. For personal and business use only. Not for resale or redistribution.