INTEGRATIONS: Importing from Excel

The Integrations Add-on module makes it easy to extract data from any accounting ledger system to an excel template and use that data to build and maintain a Castaway forecast. You can:

• Build a new Castaway forecast from your excel chart of accounts (including mapping)
• Map the excel chart of accounts to an existing Castaway forecast
• Import Opening Balances
• Import Actuals data (for one month or multiple months)

This article guides you through the following steps:

1. Accessing the Integrations module
2. Create or Open a Mapping File
3. Select the excel file
4. Review the excel data screen
5. Map the excel accounts to Castaway
6. Import Values (Opening Balances)
7. Import Values (Actuals data)

1. Accessing the Excel Integrations module
You can access the Integrations module from Forecast > External Sources and click Excel logo

2. Create or Open a Mapping File
A Mapping File (*.pxim file extension) records how the excel chart of accounts maps across to the Castaway chart of accounts. Mapping Files can be used and updated every time you want to import data into Castaway from excel.

The Mapping File is created and accessed via the External Sources- Excel. Choose between creating a new Mapping File or opening an existing Mapping File.

We recommend saving the Mapping File in the same folder location as the Castaway file.

3. Select the Excel integration file

Once the Mapping File has been identified, you need to Select Excel File or Build Excel Template you want to import to Castaway.

If using an existing template, just click on Select Excel File and navigate and select and open the existing excel file and click on Load File data

If you don’t have an existing excel file, do the following to create one:

• Click on Build Excel Template and save it. the template will open up in excel
• Add the values based on the instructions sheet in the template and save the file
• In Castaway, click on Refresh/validate Excel data to update the saved changes in the screen
• Click Load File Data to start the excel integration process

Please note that the data entered need to be valid data to get a valid green tick, otherwise you won’t be able to load file data.

 

4. Review the Excel data screen
The excel data screen appears, showing the excel accounts list across 6 tabs:

Each tab shows the number of excel accounts in 3 different categories:

• (M)apped: accounts successfully mapped to Castaway
• (A)rchived: inactive or redundant accounts intentionally not mapped to Castaway
• (U)nmapped: accounts not yet archived or mapped to Castaway

5. Map the Excel accounts to Castaway
The Forecast Designer makes it easy to map the excel chart of accounts across to Castaway. The mappings are stored in the Mapping File, which is designed to be updated and re-used whenever you need to import data into the Castaway forecast.

Using drag & drop, you can map:

• one excel account to an existing Castaway account
• multiple excel accounts to an existing Castaway account
• one excel account to a new Castaway account
• multiple excel accounts to a new Castaway account
• multiple excel accounts to multiple new Castaway accounts

  Select the Excel account(s) as follows:

• select one account: single-click on the excel account
• select multiple accounts (adjacent): single-click on the first excel account, then press SHIFT and single-click on the last excel account
• select multiple accounts (non-adjacent):single-click on the first excel account, then press CTRL and single-click on the other excel accounts

Drag & drop the account(s) to the Castaway chart of accounts as follows:

• map to an existing Castaway account: drag & drop the Excel account(s) over the name of an existing Castaway account (eg Main Bank Account in the picture above)
• map to a single new Castaway account: drag & drop the Excel account(s) over the '+' element button. If mapping multiple Excel accounts, select 'Map to one element' from the popup
• map to multiple new Castaway accounts: drag & drop the Excel accounts over the '+' element button. Select 'Map to multiple elements' from the popup

You can also 'archive' Excel accounts to show they have been considered but do not need to be mapped to Castaway. To do this, click the button in the Link column until it shows a dark line ( ).

6. Import Values (Opening Balances)
Once the excel accounts have been mapped as required, you can import opening balance sheet and prior P&L data from the excel file. To do this:

1. click the Import Values button to launch the Select Import Value Months screen
2. select the Opening Balance month from the drop-down list and set the number of months data required in the Prior P&L (from 0-12)

3. click OK to save the mapping file, save any changes to the Castaway chart of accounts and import the selected opening data

7. Import Values (Actuals data)
Once you have enabled Actuals in your Castaway file, you can import actuals data from excel for one month or multiple months. To do this:

1. click the Import Values button to launch the Select Import Value Months screen
2. click the Actual Value radio button, then select the month(s)
3. click OK to save the mapping file, save any changes to the Castaway chart of accounts and import the selected actuals data