Select the column with the accounts title and Copy&Paste (Paste Values) to replace the FSMACCOUNTTITLE formula with text.
Insert two columns to the right of the column with the accounts title
Select the column with the accounts title
Click the ‘Data’ tag and click the ‘Text to Columns’ button
Select ‘Fixed width’
Click ‘Next’
Create a break line after the prefix
Click ‘Next’
Specify the ‘Destination’
Click ‘Finish’
(For the particularity of this sample, where the result for the accounts without an account number is ‘#NAME?”, we simply Copy&Paste(Paste Values) for each case)
Delete the column with the original accounts title and the column with the prefixes
Consolidated Adjusted Trial Balances Side By Side
How to build a trial balance working paper showing the consolidated adjusted trial balances side by side.
The FSMACCOUNTBALANCE formula
In the Trial Balance working paper created in an FSM consolidated workbook,
in which the Parent Company accounts (‘A’) and the Subsidiary accounts (‘B’) are combined,
the ‘Adjusted balance' of an account is the result of the FSMACCOUNTBALANCE("<account index>,<item sense>,<beginning period index>,<ending period index>") formula
In a financial statement built with ExcelFSM
clicking the ‘Details’ button for a selected cell, toggles the display of the Item's accounts. The balance of an account is also the result of the FSMACCOUNTBALANCE("<account index>,<item sense>,<beginning period index>,<ending period index>") formula.
Create one item per company
In a new worksheet, create an item grouping all the accounts of the Parent Company (‘A’)
and, in another column, create an item grouping all the accounts of the Subsidiary Company (‘B’)
Select the columns with items balances and click the ‘Details’ button to toggle the display of the items accounts
(to get the accounts with the account numbers, click on the arrow next to the Details and select ‘With Account Numbers’)
Split the account number from the account title
Select the column with the accounts
Click the ‘Data’ tag and click the ‘Text to Columns’ button
Select ‘Delimited’
Click ‘Next’
Check '_Other' and specify the '-'
Click ‘Next’
Specify the ‘Destination’
Click ‘Finish’
Fix the accounts with account's title which included a ‘-‘ (Copy&Paste (Paste Values))
Cut&Paste the two columns (account number, account title) before first column
Delete the third column
Merge the accounts rows of the Parent Company and the Subsidiary
Select all cells (click corner between 1 and A)
Click the ‘Data’ tag and click the ‘Sort’ button
Select the column to sort by
Click OK
Cut&Paste on the corresponding Parent Company account's row, the FSMACCOUNTBALANCE cell of the Subsidiary
Select all cells (click corner between 1 and A)
Click the ‘Data’ tag and click the ‘Sort’ button
Select the columns with the account balances to sort by (Use ‘Add Level’)
Delete the rows without an account's balance
Select all cells (click corner between 1 and A)
Click the ‘Data’ tag and click the ‘Sort’ button
Select the column with the account numbers to sort by
and complete the presentation.
Note
Double clicking on an account balance displays the Account Balance Detail
