Import trial balance data from a spreadsheet
You can import trial balance data from a spreadsheet into Engagement Manager.
If you are working in a parent consolidated engagement, you can import balances for multiple child engagements at the same time using a single spreadsheet.
If you are working in a divisional engagement, you can import balances for multiple divisions at the same time using a single spreadsheet.
Follow these steps to import trial balance data from a spreadsheet into Engagement Manager.
- In Engagement Manager, open the engagement you want to import the trial balance data into.
- If you are working in aconsolidated structure, open the parent consolidated engagement. Engagement Manager looks down through every level of the hierarchy beneath it, so you can map balances for child engagements at any level from 1 import.
- If you are working in adivisional structure, set up your division numbers before you import balances. Open the engagement, then set up the division mask and division numbers on theSetup Divisionsscreen. You can add division numbers one at a time, or import them from a spreadsheet that has a division number column and a description column.
- Select theTrial Balancetab, thenData, thenImport.
- SelectBrowse for file, select the spreadsheet file with the trial balance data you want to import, thenOpen, andNext.
- Consolidated engagements:Organize your spreadsheet so that each child engagement's balances appear in a separate column. Engagement Manager will apply the account numbers, descriptions, and any account groupings or classification codes consistently across all child engagements included in the import. You can map balances for up to 20 engagements in a single spreadsheet.
- Divisional engagements:Organize your spreadsheet so that each division's balances appear in a separate column. Do not include the division number characters in the account numbers. Engagement Manager adds the division mask characters to the beginning of each account number during the import. Classification codes and subcodes in the spreadsheet are applied to each division's accounts at the same time.
- Use theSelect worksheetdropdown to select the worksheet that includes the data.
- Divisional engagements only:Select how your spreadsheet is organized.
- Divisions by column: Each division's balances are in a separate column, and the account numbers do not include the division mask characters. Engagement Manager adds the mask characters to the account numbers for you.
- All divisions in single column: The account numbers already include the division mask characters, and all balances are in a single column.
- Choose how you want to handle duplicate accounts from the following options:
- Display as errors: If duplicate account numbers exist, you'll be prompted to choose between renumbering the duplicate account number or excluding the duplicate account number from the imported file.
- Import first account: If duplicate account numbers exist, the 1st account is imported.
- Import last account: If duplicate account numbers exist, the last account is imported.
- Mark theExclude accounts with zero balancescheckbox if you don't want to include zero balance accounts in the import.
- SelectNext.
- Define data in each column of the trial balance.
- Map each spreadsheet column by specifying the column title (Type) for each column in the table.
- Select a column heading in the table, then choose a title from the dropdown. Available column titles include:
- Consolidated engagements:For each balance column, select the child engagement you want to map the balance to. The dropdown lists the lowest-level child engagements in the hierarchy, including those that are not directly attached to the parent you are working in. Parent consolidated engagements at any level do not appear in the dropdown. You can map the current period and prior period columns for each child engagement.
- Divisional engagements:For each balance column, select the division you want to map the balance to.
Available columns for mappingColumn titleAdditional selectionsNotesNot UsedN/AEngagement Manager won't import any data from this column.Account NumberN/AThis is a required column. Account numbers can contain up to 65 characters.In a divisional engagement that uses the divisions by column layout, do not include the division mask characters in this column. Engagement Manager adds them during the import.Account DescriptionN/AThis is a required column. If the account description for an existing account differs from the description in the spreadsheet, Engagement Manager updates the description using the information in the spreadsheet during the import. Account descriptions can contain up to 120 characters, including spaces.Account GroupingAccount grouping code or subcodeEngagement Manager adds new custom codes and subcodes to the Account Groupings screen.The selected engagement must include at least 1 account grouping for this column to be visible. If the Account Grouping column doesn't appear for the current engagement, you must add or import account groupings to the engagement.In a consolidated or divisional engagement, Engagement Manager applies the codes and subcodes from the spreadsheet to all child engagements or divisions in the import.Tax InformationTax Code, Tax Subcode, M-3 Tax Code, M-3 Tax Subcode, or Property CodeEngagement Manager adds new custom tax subcodes and M-3 subcodes to the client Tax Code setup. You can assign property code units to a column only if the tax code is already assigned to an existing column.When transferring data to a tax application, a property code can designate the input screen location for multiple-unit entities.For example, if you're transferring utilities expenses for a farm to the UltraTax CS Farm input screen and the tax code for the 1st unit's utilities is 632 and the property code is 1, you'd transfer the 2nd unit's utilities with a tax code of 632 and a property code of 2.Unadjusted BalanceCurrent Period only and Amount Type (DR/CR, DR, CR)In a consolidated engagement, also select the child engagement. In a divisional engagement, also select the division.Budget Balance and Adjusted Budget BalancePeriod end date or Year for the current year and prior years available and Amount Type (DR/CR, DR, CR)In a consolidated engagement, also select the child engagement. In a divisional engagement, also select the division.Adjusted Balance, Report Balance, Federal Tax Balance, State Tax Balance, Other Balance, and Proposed BalancePrior period and years available and Amount Type (DR/CR, DR, CR)In a consolidated engagement, also select the child engagement. In a divisional engagement, also select the division. - Select theOmitcheckboxes for any rows of data that you don't want to include in the import.
- SelectDone.
- Consolidated engagements:After the import finishes, selectData, thenConsolidatein the parent consolidated engagement. Engagement Manager pulls the balances from the lowest-level child engagements up through each level of the hierarchy. You don't need to import into each child engagement separately, or run a separate data consolidation for each level.
- Divisional engagements:The balances for all divisions are imported into the single divisional engagement. You don't need to run a data consolidation.
If you're importing data into a trial balance that already exists, Engagement Manager updates and appends the existing data during the import process.
Example:
Existing trial balance
- Account 101 - 10,000
- Account 102 - 20,000
Import spreadsheet
- Account 102 - 14,000
- Account 103 - 5,000
End result
- Account 101 - 10,000
- Account 102 - 14,000
- Account 103 - 5,000
Sample spreadsheet
Download and save this sample trial balance spreadsheet. It includes Account Classification codes, subcodes, current-year unadjusted balance, and prior-year adjusted balance. You can modify the formatting, columns, and data to fit your needs.