From Trial Balance to Template
An Excel integration allows you to import your Actuals Data from any accounting system that can export Trial Balance data to Excel. It is also the best way to control how your data comes into Castaway if you need to split out balances from your TB reports for the purposes of your reporting.
An Excel template will allow you to bring in actuals data on a regular basis, retaining mapping to elements in Castaway via the account code.
First we need to understand how Castaway Excel templates are structured, to align your data with the template requirements.
Adding an Excel Integration
- Go to Forecast > External Links
- Click the Excel icon under Add a new data source
- Click Upload Excel File to upload a completed Template (Click Download Template for a simple template)
- Select your file and click upload
- You are now ready for Mapping
If you re-upload your Excel template with a different name you will need to Rename your Excel Integration File
- Under Connect to your source, click Refresh Data
- Select Upload Excel File and choose your file
- You will be notified the name differs to the current template, and given the option to Add New Integration or to Update Integration Name
- Selecting Update Integration Name will retain the mapping of the original file
Rules of an Excel Template
There are a few basic rules the Excel actuals integration template must follow to be readable by Castaway.
Basic Rules
- Your actuals data page MUST be the first tab
- Columns A, B and C MUST be Type, Account and Name
- Formulas are allowed on the data page
- Addition tabs are allowed
Column Headers
As in the example above, the column headers and data must appear as, and follow this order:
- Type - Income, Expense, Asset, Liability, Equity, Driver, Ignore
- Account - Mapping is connected to this, ensure no duplicates
- Name
- Months (MMM-YY format)
Other Rules
- Use 'Ignore' as the Type to tell the system to disregard a row
- 100 character maximum for account names
- No misspellings of Types are allowed
Advanced and Automated Templates
For more complicated, the Castaway Support Team can advise you with advanced import templates to automatically read your trial balance.
Reconciliations
If your trial balance structure does not facilitate the structure or reporting requirements in Castaway you may need to split out your data before it gets imported.
We recommend you get in touch with the Castaway Support Team so we can best advise how to proceed.
You can contact Castaway Support via email: support@castawayforecasting.com