The variables sheet
Global variables related to the document such as your base currency and company name.
Basic usage
You can have any variable you want here, and reference it directly.
The base currency
You should define your base currency here, and define it as a named range because we use that information often in other sheets. To see how this works, go to the starterkit spreadsheet, and select Data > Named ranges, then look for the named range “homeCurrency”. This means that wherever we want in the document, we can reference “homeCurrency” rather than, say, “variables!B12”, which is harder to grasp. When choosing or changing your home currency, make sure the forex sheet has columns to convert all currencies to and from your home currency.
The start and end date of your document
In the “summary” tab you’ll be able to specify start and end dates for reporting. Here we are defining dates within which all opeartions need to be contained, for example the start and end date of a fiscal year or quarter.
Your company short name
All organization information is contained in the “partners” sheet, and all partners, including your own company are referenced by a company short name or id. You can define yours here.
Other optional information
I like to put my tax rates here, and then define a named range to access them, for example in Canada the GST is 5%, so have a gst variable of .05, and then a named range “gst” so that, in other cells, I can type, for example, “=ROUND(X9*gst,2)” to get the GST on X9.
The sheets in detail
- instructions sheet: Any instructions such as what to do monthly, which source to import exchange rates from, etc., can go here.
- sandbox sheet: Has no incidence on your reporting, can be used to test ideas, functions.
- current page: variables sheet: Global variables related to the document such as your base currency.
- verification sheet: Consolidations verification inforation from all other sheets, and makes a master verification cell available to all sheets. If it is zero, checks pass; if it is not zero, there are errors.
- chart sheet: The chart of accounts and how much money is in each account. A good overview of your assets, liabilities, expenses and income.
- facets sheet: Allows grouping of accounts in the chart of accounts.
- journal sheet: The journal of operations which is generated automatically from the imported data (sheets starting with "imp") via the intermediate sheet intJournal. Do not modify this directly.
- summary sheet: Running balance sheet, and any other high-level reporting, for the desired period. This sheet also lets you select a period and currency for all your reporting.
- acctStatement sheet: View operations related to a single account in the desired period, in the desired currency.
- log sheet: Supports the good practice of logging important work done on the document, for example monthly importing of data from your bank.
- partners sheet: All your partners.
- ACMEBANK sheet: Most organizations will import data from various sources; this is an example import short.
- PAYMENTS sheet: Example data import sheet allowing you to enter payments.
- MANUAL sheet: For "exceptional" operations which do not fall into any of your data entry sheets.
- LINE sheet: For "exceptional" operations which do not fall into any of your data entry sheets.
- INVOICES sheet: Display and print invoices.
- Invoice sheet: View and print an invoice.
- intJournal sheet: An intermediate sheet which colladates data from "imp" sheets and prepares them for the automated journal.
- forex sheet: Define your available currencies, and import daily rates.