by rwalliker » Wed Jan 03, 2018 5:12 pm
This is where you upload a spreadsheet to bring in monthly TB balances (actuals from your Accounting System).
The “Choose File” button allows you to upload a file from your computer.
1 TB.xls(x) – Normal year to date TB balances in a single column Or 2 NL.xls(x) – Brought forward balances and monthly TB movements in columns named BF,CYFCT_1,CYFCT_2 etc Example files are available for download. They require named ranges TB or NL respectively
You select the uploaded file and Click on “Import TB” or “Import NL”
Both files have the same first 5 columns: ACCT,ACCTName,CC,TYPE,ACCTGP0
CC - Must match that of your company in 4CastPro eg 001FCT TYPE - Each account needs to be assigned to a Statutory Accounts type eg INC,COS,OVH (see list above) ACCTGP0 - This is your Management Accounts layout grouping Eg Main Sales, Other Sales etc.
The same files can be used to initially sync the COA with your underlying Accounting system (Click on “Initial Sync TB” or “Initial Sync NL”) Using Excel’s Query feature you can set-up a refreshable query against your Accounting Systems database (eg using the Sage odbc driver). This would allow you to bring data in each month by refreshing the query and uploading the latest file. This may not be possible if using a cloud accounting system. Alternatively there is a "Quick TB Import" button. This requires a simple xls TB with columns headed ACCT(Account code) and BAL(Account balance) on row1 of Sheet1. No named range is necessary but the sheet must have the default name of Sheet1. Missing Accounts are reported allowing you to add them using the COA facility and rerun import.
The Import TB option allows you to import brought forward balances into Last Year Period 12 if the period is set to 0 (ie no actuals for current year)
Initial Sync (TB or NL) The TB.xls(x) or NL.xls(x) files can be used to initially sync your COA with your underlying accounting systems. Orphaned accounts are deleted(unless “Don't delete orphans” is ticked) and new accounts are brought in. If this routine is run a second time any changes to Account Names, Types, or Account Groups (ACCTGP0-4) will be updated within 4CastPro. Tickbox options (which apply to both Initial Syncs (TB and NL): “Bring in Account Group Table” tickbox - this allows you to import at the same time the Account Group Table from your ACCTGP0 column. It will overwrite any changes you have made within 4CastPro itself. “Bring in Sections” tickbox - brings in sections if a column called SECT exists in TB or NL “Don't delete orphans” tickbox - just adds new accounts Export Acct Gp Tbl/Re-Import Acct Gp Tbl – These buttons allow you to export and re-import any changes to your Account Group Table which defines the layout and order of the main P&L and Balance Sheet views. Export NL – This allows you to export the entire COA so you can use it to create another NL.xlsx or TB.xlsx file. Delete Accounts – Allows you to delete all accounts on a selected Company