Data Loading and Tagging

Tagging Income Statement and Balance Sheet accounts maps the data from your accounting system into RealTime CEO and provides the software with the information it needs to perform calculations on the dashboard and other screens.

All accounts that match one of the RTC tags (other than ‘Other’) must be tagged, whether via individual account or sub-total.  If the only tag that matches an account is Other, it does not need a tag of Other.  However, tagging it as Other means it will display on the Monthly Data screen so you can see it.  Not tagging it means it is summed with all other non-tagged accounts and shown as one figure in the ‘Untagged…’ line (there is an Untagged line in each section – see What are the Untagged lines on Monthly Data?).

At RealTime CEO we prefer that all accounts that can be tagged are tagged* whether via individual account or sub-total, even though the software does not require it.  When all accounts are tagged the Untagged lines in each section should be zero and it is a quick check that you haven’t missed something.

While you should be checking all untagged accounts when loading data each month (untagged accounts are white) and reviewing whether a tag is needed, we find this process is easier and faster if all accounts that can be tagged are tagged.

If you have been using RTC for a while and not all of your accounts are tagged and you would like them to be, please contact support and we will help you load a month of data and tag everything from that point Email Support

* Note that not all accounts in the Miscellaneous Revenue/Expense section of the Income Statement can be tagged because there is no header tag, and there is no Other tag.

* Note that some sub-totals are never tagged (e.g. Gross Profit). Also, some Income Statements have more than one profit line and we only ever tag one of them.  See How to load and check Monthly Data for information on double-tagging.

RealTime CEO requires a Balance Sheet and Income Statement to be loaded once the accounts are finished each month.  These files must be in a CSV (comma-separated values) format. Here are the requirements for the layout and content of the CSV files.

CSV file requirements:

• 2 Columns only: Column A with the account descriptions (and/or account numbers) and Column B with the account values.
• Cell A1: must contain words to describe the file – e.g. Income Statement or Balance Sheet
• Cell B1: must contain the month/year of the file – e.g. Jan 21
• No duplicate accounts: The file must not contain any duplicate account descriptions because the software uses a tagging mechanism on the account description to identify the account.  Note that an account description without a value (e.g. a heading) and the same account description with a value, is not considered a duplicate.
  See What are Duplicate Accounts in CSV files and how to fix them? and How to load and check Monthly Data
• Income Statement Totals: the Income Statement must contain the following Totals (they do not need to match these descriptions): Revenue, Direct Costs, Indirect Costs, Net Profit (if your Income Statement has Distributions, then Net Profit should be before Tax and Distributions).
• Balance Sheet Totals: the Balance Sheet must contain the following Totals (they do not need to match these descriptions): Assets, Liabilities, and Equity.
• Value Signs: The values must be positive if they are their true account sign.  Most (but not all) accounting systems output reports showing Revenues and Expenses as positives, and Assets, Liabilities, and Equity as positives.  The only negatives are usually when there is a negative expense in an expense account etc.  There is one exception to this rule:
  • Miscellaneous Income Statement accounts tagged as Non-Operational need to be as follows:
    • Expenses should be negatives if they are their true account sign.
    • Revenues should be positive if they are their true account sign.
  • This is because the Miscellaneous Non Operational tag can be applied to both revenues and expenses and therefore we need to distinguish.  Every other Income Statement tag only has one application – it’s either a revenue or an expense.
  • The Miscellaneous Non Operational tag may apply to some accounts in the Other Income/Other Expense section of your Income Statement.

See How to load and check Monthly Data for more information on getting the data into the correct format for loading into RTC, using some Excel tips.

Download example files:

Example Income Statement CSV

Example Balance Sheet CSV

The tags create a mapping routine from your accounting system to RealTime CEO. They determine whether accounts are operational or not, and in which calculations they are used on the Dashboard and other screens.  The following is an explanation of the tags and how they should be applied.

General Rules:

• Tags are attached to the account name (this can include an account number) and therefore you cannot have duplicate account names in your CSV files. See How to load and check Monthly Data and CSV file format for loading data
• The initial tagging routine is set up during the Onboarding Wizard and these tags remain for future data loads.
• If an account name changes in your accounting system after data has already been loaded into RTC, the newly named account will not have a tag.  The old account will still be tagged and remains on the Monthly Data screen, but when you load another month’s files, a tag will need to be applied to the new account.  You can then Merge the old and new accounts on the Monthly Data screen to add them together.  See How to Merge accounts
• Some accounts are tagged via a sub-total and some via each detailed account.  An example of tagging a sub-total rather than each individual account is the Total Bank.  If a sub-total is tagged, all the accounts within that sub-total are included and any new accounts within that sub-total do not need tagging.
• Not all accounts have to be tagged although we recommend tagging everything whether via sub-totals or detailed accounts. There are some exceptions to this: The Other Income/Expense section cannot all be tagged because there is no Header tag and no Other tag.  Gross Profit is never tagged.
• Tags only show data from the time they are tagged – i.e. they do not change prior months.

Header Tags

These identify the total of each section and are only used once per section. You will find a header tag in the following sections: Revenue, Direct Costs, Indirect Costs, Profit, Assets, Liabilities, and Equity.

Sub-Tags

Sub-tags define the accounts (either detailed or sub-totals) in the section above the Header tags. These are necessary as RTC requires certain information to perform the Dashboard and other calculations.

Optional Tags

These are voluntary options for additional detail you may want to include in your data, but we suggest you use them, therefore tagging everything where possible (whether via sub-totals or detailed accounts).

Income Statement Tags  

Balance Sheet Tags

An Income Statement and Balance Sheet need to be loaded into RealTime CEO each month when the accounts are finished for the month.  Data is imported from CSV files after exporting it from your accounting system.

After loading the onboarding data, you will need to load the remaining historical data sequentially, up to the current month using the steps below.  Once all your data is up to date, use the same instructions to load data each month thereafter.

Prepare your CSV files:

• Download your Income Statement and Balance Sheet from your General Ledger to Excel. Note that Quickbooks allows an export straight to CSV and the output is in a good format to begin getting it ready for RTC.
• Open the files in Excel and select File > Save as/Save a Copy and choose ‘CSV Comma Delimited’ format. Save the files to a folder using a common naming convention – e.g. “03-IS-Mar19” “03-BS-Mar19”.
• Your CSV file must have 2 columns only – column A for account descriptions and column B for values. If your account descriptions or values are spread over multiple columns, see the instructions below for merging them.
• In Cell A1 enter the words “Income Statement” or “Balance Sheet” (the software cannot read the CSV if cell A1 is empty).
• In Cell B1 put the date of the data (e.g. Mar 19).
• For more information see CSV file format for loading data

How to check for duplicate accounts:

When loading data in RealTime CEO, the CSV files cannot be loaded if any duplicate account names are found in the files.  This is because the account names are tagged and a tag will not work if the same account exists more than once in a file.

There is a feature in Excel that allows you to check for duplicate account names by highlighting the column with account names, then clicking Conditional Formatting > Highlight Cell Rules > Duplicate Values > OK.  Any duplicates will be shown in pink.  Please note that duplicates on headings (without values in column B) are not duplicates in RealTime CEO and do not need to be fixed.

To fix duplicates, you can either manually change one of the account names in the CSV file, or change it permanently in your accounting system.

How to merge account descriptions or values into one column, if needed:

If your file has the account descriptions or account values spread over multiple columns, use the Merge Across feature in Excel to move them all into the left-most column: e.g. highlight the columns to be merged, then click ‘Merge Across’ (under Merge and Center).  When the data is all in the left column, delete the empty columns.  Note that this only keeps the left-most column of data, so if there is data in more than one column on the same row, this feature should not be used.

How to join account numbers and account names into one cell:

Sometimes there are so many duplicate account names in an Income Statement that it is easier to distinguish them using both the account number and account name.  If your Income Statement contains the account numbers in one column and the account names in another, you can use the Excel feature CONCATENATE to join the two values together.  Here are the instructions.

Use the concatenation formula to join the account number with the account name:

1. Insert a new column after the account name.
2. Click in the cell on the first account no/name line (in the new column), and select the Formulas menu, then Insert Function button, then find CONCATENATE and double-click on it.
3. In Text1 click the account no cell.
4. In Text2 click the space bar to create a space.
5. In Text3 click the account name cell.
6. Click OK.
7. Drag the formula down to the end of the spreadsheet.
8. Highlight the column with the concatenate formula.
9. Select Copy.
10. Select Paste in the same column and choose Paste Values.
11. Delete the columns with account numbers and account names.

Import the CSV files into the RealTime CEO software:

Note: When accessing RealTime CEO, we recommend using the browser Google Chrome.  Our software is written in Google apps so Google Chrome is the best match.  Safari and Microsoft Edge are okay.  We do not support Microsoft Internet Explorer as it is old technology with some limitations.

1. Login to your RealTime CEO account:  Go to realtimeceo.com and select Login, then enter the username and password you set on registration.
2. Select Enter Data from the left-hand menu.
3. Click Add under Add New Month.
4. Click Import.
5. Click the Select File button to find the CSV files for that month (Income Statement and Balance Sheet).
6. Click Next.
7. The Tag Columns – Income Statement screen is checking that the columns are tagged (colored).  You do not need to scroll down for this step – just check that the date is correct (so you know you are uploading the correct month/year). Click Next.
8. The Tag Columns – Balance Sheet screen is checking that the columns are tagged (colored).  You do not need to scroll down for this step – just check that the date is correct (so you know you are uploading the correct month/year). Click Next.
9. The next step is important – this is to check the tagging of rows.  Slow down at this stage and make sure that the tags previously applied have come through. Check both the Income Statement and Balance Sheet.  If you have any new accounts added to your chart of accounts, assess if they need tags and if so add them.  To do this:
   1. Click on the relevant section tab, then select the tag you want to add.  It is active when it has a white box around the tag.
   2. Then, with the tag active, click on the line in the chart of accounts to add the tag.
   3. If you make a mistake you can remove the tag by clicking on the line again.
   4. Make sure not to double-tag (applying a sub-tag or optional tag to both a line and the sub-total containing the line).
      Tagging Notes and Tips:
      • Double tagging – Header tags – you cannot tag more than one account as a header tag.  A warning symbol and message will appear if a header tag is not assigned or is assigned to more than one account.
      • Double tagging – Sub-tags and Optional tags – you cannot tag both a sub-total line and an account within the sub-total. Note: The software will not warn you about this.
      • Untagged balances appear in a residual bucket called ‘Untagged Operational… ‘ in the appropriate section on the Monthly Data dashboard.  The Untagged balance in the Miscellaneous section is called Net Miscellaneous Revenue/Expense.
      • Tags only show data from the time they are tagged – i.e. they do not change prior months.
      • Accounts can be untagged by clicking on them again.
      • The order of tagging determines the order the accounts/balances display on the Monthly Data screen.
10. Once you have checked the Income Statement and Balance Sheet, select Next and it gives you a progress check view of your Income Statement. If you need to check or correct any tags, use the Back button.
11. Click Next to see a progress check view of your Balance Sheet.  If you need to check or correct any tags, use the Back button.
12. Click Next to go to the Normalization of Owners Salaries screen.  Use this step to adjust if the owners are paid a different amount from the commercial amount the role warrants. To enter an adjustment for owners salaries, do the following:
    1. Click Add an Adjustment.
    2. Where it says ‘Start typing here to filter lines’ enter part of the wages account name that you want to adjust (wherever the owners’ salaries are posted, or would be posted if they were entered). The accounts tagged as Wages will appear. Click the one you want to adjust.
    3. In the ‘Amount paid to owners that is included in costs’, enter the gross amount paid as an expense in the month you are loading.
    4. In the ‘Commercial wage that the role warrants’, enter the market rate/commercial monthly amount. The system will then calculate an adjustment.
    5. Click Add Adjustment.
    6. To adjust a second account, repeat the above steps.
    7. To edit the amount, you need to delete the adjustment just created, and re-do it.
13. Click Next/Skip to go to the Non Business Adjustments screen.  Use this step to remove any amounts charged in the Income Statement that do not relate to the business, or add any amount that relate to the business that were not in the Income Statement.  To enter a non-business adjustment, do the following:
    1. Click Add a Non Business Adjustment.
    2. Where it says ‘Start typing here to filter lines’ enter part of the account to be adjusted.  Account names matching your entry will be displayed.  Click on the one you want to adjust.
    3. The screen then shows you the monthly amount for the account selected in the Value field.  Enter the Adjustment amount. Check that the Adjusted Value is what you are expecting.
    4. Click Add Adjustment.
    5. To adjust another account, repeat the above steps.
    6. To edit the amount, you need to delete the adjustment just created, and re-do it.
14. Click Finish to go to the Monthly data screen where you have another chance to check that the data looks correct. In Monthly Data, check the data has loaded correctly using this checklist before loading another month of data.

Note that if you think there is a data loading issue you can reload the most recent month following these instructions: How to reload Data

Monthly Data Load Checklist

There are various reasons why you may need to reload data in RealTime CEO:

• The wrong month’s files were loaded.
• There is an untagged account that you want to tag.
• There were adjustments made to the accounts in a prior period after the data was loaded into RTC.

To reload the most recent month:

You can reload the most recent month of data loaded into RTC as many times as needed.  The process deletes what is there and reloads it so you will need to re-do any Normalization of Owners Salary and Non-Business Adjustments as part of the process also.

1. Select Enter Data from the left-hand menu.
2. Click Reload under Reload Most Recent Month.
3. Click Import.
4. Click the Select File button to find the CSV files for that month (Income Statement and Balance Sheet).
5. Follow the normal data loading instructions from here.  See How to load and check Monthly Data

To reload data older than the most recent month:

Email Support and we will clear the data back to the date you request.  Data should then be re-exported from your accounting system and reloaded sequentially from that month onwards using the normal data loading instructions (link above).

To reload more than one month of data, please Email Support and we will clear the data back to the date you request.  Data should then be re-exported from your accounting system and reloaded sequentially from that month onwards using the normal data loading instructions.  See How to load and check Monthly Data

This is our general recommendation of the tagging treatment of these accounts.

Costs in Excess of Billings (Asset)

Generally on the Asset side it is tagged as Other – it is viewed as operational because if the project or work (that the costs relate to) were not to go ahead, the goods would not be returned and a refund would not be given – i.e. you would ‘wear’ those costs.

Billings in Excess of Costs (Liability)

On the Liability side, if a client has prepaid and the project or work does not go ahead, you would normally have to refund that money. It is normally tagged as Deferred Revenue (but Related Party Loans is treated the same) which is a non-operational liability for that reason.

There is no need to remove intercompany asset and liability lines. If they are both tagged as Loans to/from Related Parties, they will offset each other and neither will be included in the operational investment calculation.

When data is loaded into RealTime CEO each month, the software checks for duplicate accounts in the CSV files – that is, the same account name more than once in a file.  RTC uses a tagging mechanism on the account description to identify the account and the file will not be imported if there are duplicates.

On importing CSV files into RTC, the system will report if duplicates are found and displays the rows within the CSV files the duplicates are found on.  There are 2 ways to fix this:

1. Manually fix the duplications in the CSV file by editing the account name but you will need to do this every future month.
2. Edit the duplicate account names in your General Ledger so you don’t need to manually edit every future CSV file.

After the CSV is fixed, you will need to reselect the file for uploading.

Note that an account description without a value (e.g. a heading) and the same account description with a value, is not considered a duplicate.

When loading monthly data, the Next button may appear greyed-out if cells A1 and/or B1 in the csv files are empty.  Check that A1 contains either ‘Income Statement’ or ‘Balance Sheet’ and B1 contains the date.  The format or exact wording in these cells does not matter, they just cannot be blank.

In RealTime CEO, we ‘normalize’ the owners salaries if the owners are paid a different amount (as an expense in the Income Statement) to the commercial/market rate amount for the role. This represents the true operational profit for the business.  If the owner of the business is being paid a commercial salary that reflects the role, no adjustments are needed.

Here are some examples of when we would normalize an owners salary:

• The owner is paid only via a distribution.  There is no expense in the Income Statement.
• The owner is paid as an expense in the Income Statement, and the amount is over or under a commercial amount.
• The owner is paid as an expense in the Income Statement but does not work in the business.
• The owner is paid partly as an expense in the Income Statement and partly as a distribution.  The expensed component is over or under a commercial amount.

Salary adjustments can be made when loading monthly data or after loading monthly data.

Entering NOS DURING data loading:

1. When loading a month of data into RTC, you are prompted with a Normalization of Owners’ Salary screen.
2. Click Add an Adjustment.
3. Where it says ‘Start typing here to filter lines’ enter part of the wages account name that you want to adjust (wherever the owners salaries are posted, or would be posted if they were entered). The accounts tagged as Wages will appear. Click the one you want to adjust.
4. In the ‘Amount paid to owners that is included in costs’, enter the gross amount paid as an expense in the month you are loading.
5. In the ‘Commercial wage that the role warrants’, enter the market rate/commercial monthly amount. The system will then calculate an adjustment.
6. Click Add Adjustment.
7. To adjust a second account, repeat the above steps.
8. To edit the amount, you need to delete the adjustment just created, and re-do it.

Entering NOS AFTER data loading:

1. From the menu, select the Monthly Data screen.
2. Choose the month/year you want to adjust.
3. Click the Edit button (top-right).
4. Click on the 3 dots next to the Wages line to be adjusted. Select Reallocate.
5. In the appropriate month(s), enter the adjustment amount in the Reallocation Amount field. NOTE that this is the adjustment amount, not the paid and commercial. You can enter multiple months’ adjustments at a time if needed. For an expense account, a negative amount increases the expense, a positive amount decreases the expense. Check the Remaining Value line to see if your adjustment is correct.
6. In the Select a Line field, type ‘NOS (Indirect)’ or ‘NOS (Direct)’ depending on which section the wages account is. Select the Non Operational tag if the line is new and has not been tagged.
7. Click Apply.

Explanation of NOS Adjustments in the NOS line:

The adjusted amounts can be seen on the Monthly Data screen, in the Income Statement – Direct or Indirect Costs section in a line called NOS (Direct/Indirect).  The amounts shown here are the reverse of the adjustment to the Wages line. So if the NOS (Direct/Indirect) amount is negative, it means the Wages were increased; if the NOS (Direct/Indirect) amount is positive, the Wages were decreased.  Because this line is tagged Non Operational, it is excluded from the Dashboard calculations.

The Non Business Adjustments (NBA) component of data loading allows you to adjust out any costs or revenue in the accounts that do not relate to the business. They also allow you to include costs or revenue that are business related but not already in the accounts.

Non Business Adjustments can be made when loading monthly data or after loading monthly data.

Entering an NBA DURING the monthly data load:

1. When loading a month of data into RTC, you are prompted with a Non Business Adjustment screen.
2. Click Add a Non Business Adjustment.
3. In the ‘Select the line to adjust‘ field, enter part of the account name to be adjusted and select from the list.
4. The software displays the value for the account for the month being loaded. Enter the Adjustment amount. Check that the Adjusted Value is what you are expecting.
5. Click Add Adjustment.
6. To adjust a second account, repeat the above steps.
7. To edit the amount, you need to delete the adjustment just created, and re-do it.

Entering an NBA AFTER the monthly data load:

Note: please Email Support if you need to adjust Balance Sheet values in RTC.  Only use these instructions for Income Statement adjustments.

1. From the menu, select the Monthly Data screen.
2. Choose the month/year you want to start from.
3. Click the Edit button (top-right).
4. Click on the 3 dots next to the line to be adjusted. Select Reallocate.
5. In each month to be adjusted, enter the amount. e.g. For an expense, enter a negative amount to increase it or a positive amount to decrease it.
6. In the ‘Select a Line’ field, type ‘NBA (Revenue)’ if it’s a revenue account being adjusted, ‘NBA (Direct)’ if it’s a direct cost, ‘NBA (Indirect)’ if it’s an Indirect Cost, or ‘NBA (Misc)’ if it’s a Miscellaneous account (purple). Tag the NBA line as NON-OPERATIONAL.
7. Click Apply.

This adjusts the line you chose and puts the opposite side to a Non Operational line (NBA….) which gets excluded from dashboard calculations.

Make a note of any adjustments you make because if data is reloaded, they will need to be re-entered.

To delete prior months’ data, please Email Support and we will clear the data back to the date you request.  Data should then be re-exported from your accounting system and reloaded sequentially from that month onwards using the normal data loading instructions.  See How to load and check Monthly Data