Load Trial Balance Data Into a Module and then Converting Into Financials
I have loaded trial balance data into a module (called 'Trial Balance'). In that module it has the 'account string' as the row, the time period as the pages and then the line items going across the top (see attached Exhibit 1). The middle 5 digits of the account string represent the office location (profit center). The right 6 digits represent the natural account. The line item 'Consolidated Mapping' is brought in via a Lookup formula from the 'Natual Account' list (see Exhibit 2 and Exhibit 3 on attached). The next step that I need to conquer is going from the 'Trial Balance' module and converting that information into an income statement (which can be shown by location, the 'middle' segment and the 'consolidated mapping' line items). So I have set up a 2nd module called 'Income Statement - Long Form' (see Exhibit 4 on attached). Office Location (profit center), which is the 'middle' 5 digits on the 'Trial Balance' modules, is the pages. The line items are not from a list (see Exhibit 5 for blueprint view). I am struggling to write a lookup or sum function that will bring the trial balance module data in. Thoughts?
Best Answer
-
Hi Josh
For you example it's better not to create the "Consolidated P&L lineitem" as a list
Use Lineitem Subset functionality for your case
So you could create new linitem subset Consolidated P&L lineitem using the module Income statement - Long form as a source
And you could easily get values from "Trial Balance" using SUM formula for lineitems that you want to get
Of course you need to replace all references from "Consolidated P&L lineitem" list to "Consolidated P&L lineitem" lineitem subset0
