The parent company compiles and consolidates the financial statements of its subsidiaries to create consolidated financial statements. This includes notes.
The notes have about 100 tables.
An example of a table is as follows, and each table has a different header name and number of header columns.
code name
beginning
increasing
decreasing
end of
assets
current assets
cash
100
30
-10
120
Accounts receivable
300
90
-110
280
subTotal
400
120
-120
400
Non-current assets
Longterm Accounts receivable
2000
1000
-800
2200
other Accounts receivable
2000
1000
-800
2200
subTotal
4000
2000
-1600
4400
Total assets
4400
2120
-1720
4800
Which of the following two methods would be better when designing an app to compile these 100 tables?
Method 1: Create each table for each note table.
Method 2: Create only one table. Add all columns to that table.
※ The person in charge of each subsidiary registers the value at the table type view (Enable QuickEdit (beta) is eabled) or registers the values ​​in bulk as a CSV file for all note tables.
I initially tried to create 100 tables, but it took too much work, so I am now proceeding with creating one table. Is this a good method?
Based on your description, it seems that using a single table schema (Method 2) is a good decision in terms of efficiency and maintenance.
Less structural complexity: Having 100 individual tables would make app maintenance extremely complicated. With a single schema, everything is kept in one place.
Ease of import/export: Since the responsible personnel of each subsidiary enter data through QuickEdit or CSV upload, a unified structure facilitates these processes without the need to separate data into different tables.
Scalability and flexibility: A single schema allows you to handle future changes in the note tables without having to modify 100 different structures.
Simplified automation: The logic for calculations, filters, and reports can be applied uniformly in a single table, without the need to repeat configurations for each note.
The question is, will you have security filters? When information is collected in a single table without security filters, synchronization slows down (depending on the number of records). It’s true that maintenance would be minimal since you’re only dealing with one table (which is the option I would choose), but if you risk losing storage capacity by having only one table, then the other option is to generate a separate table for each note and store them in a folder.
Honestly, when I hear “100 tables,” it sounds titanic, but I don’t know your full context.
Not only the parent company, but all subsidiaries prepare their own financial statements. The financial statements are the following five:
Balance sheet
Income statement
Statement of changes in equity
Statement of cash flows
Notes.
Notes are detailed information about the four statements, namely the balance sheet, income statement, statement of changes in equity, and statement of cash flows, and there are numerous (approximately 100) tables.
The parent company must additionally prepare consolidated financial statements, and the process begins by combining its own financial statements with the financial statements of all subsidiaries. Here, we will not explain the process of combining the balance sheet, income statement, statement of changes in equity, and statement of cash flows, but rather the process of combining the notes.
To simplify the explanation, let’s assume that all subsidiaries’ notes have 100 tables, and each table has 3 columns including the name column and 5 rows including the header. Then I need a table for notes collation that has 201 columns including the name column and 401 rows including the header for each subsidiary. This table will grow by 400 rows * count of subsidiaries for each period.
As the table grows, I am wondering if this approach is appropriate. There is a lot of unused space in this table. For example, there are 4 rows that have values ​​in one column and the remaining 196 rows are blank.
In that case, yes, there would be a lot of empty space, the single table would become huge and unmanageable and there could also be data redundancy. I don’t think that’s the best alternative. I was thinking about the possibility of restructuring everything. Creating a master table of notes where the notes’ IDs and the branch to which they correspond are attached. Then a table with data from said notes.
I understand your alternative. However, I believe that the table type view (Enable QuickEdit (beta) is eabled) will make the work of the subsidiary managers more convenient. Your suggestion seems to make it impossible.
Each note has its own topic. For example, financial assets, tangible assets, intangible assets, leases, sales, cost of sales, etc. Each note has its own unique topic, so each note has a different column name.
BTW, when I think about it again, many notes have common column names. In particular, in the case of notes that have one column with an amount value, they use a column with the same name, “amount.”
In the case of columns with names that can be used in common, it seems that removing duplication can reduce the number of columns.
I feel like we could offer more help, but you haven’t provided enough detail to give me a clear picture of what you’re dealing with and what you want to accomplish.
I’m also wondering if AppSheet is a good choice for your project.
Parent companies with subsidiaries need to prepare additional consolidated financial statements. To prepare consolidated financial statements, (group1) ERP or (group2) EXCEL are used. Of course, there are already many ERPs for preparing consolidated financial statements on the market, but they cost a lot of money to use and maintain. And the work by EXCEL has low stability in preparing financial statements.
My goal is to improve the process of preparing consolidated financial statements using (group2) EXCEL to using AppSheet.
My consolidated financial statements project can be divided into the following five sections:
Balance sheet
Income statement
Statement of changes in equity
Statement of cash flows
Notes.
I have completed the basic tasks for 1-4 and am now working on the Notes section.
The Appsheet TABLEs used in the notes section are as follows:
Table with a list of notes:
P4_NOTENUMBER_UA
Tables with codes for each row of notes:
P4_NOTECODEBIG_UA → parent
P4_NOTECODEMIDDLE_UA → son
P4_NOTECODEACCOUNT_UA → grandson
Table for registering notes:
P5_NOTEINPUT_UAD
Table for aggregating registered notes:
P6_NOTE_UAD
That’s what I wanted, thanks! Unfortunately, your needs are definitely not well-suited to a simple solution; separate tables appear to be what you need.
But let’s revisit your comment, “work by EXCEL has low stability in preparing financial statements”. Please explain.
What I mean by “work by EXCEL has low stability in preparing financial statements” is the same reason why we use “AppSheet” instead of “spreadsheet”.
In order to prepare consolidated financial statements, we compile the financial statements of each subsidiary, and if the currency is different, we convert them. We combine these compiled financial statements, and we complete the consolidated financial statements by adjusting the consolidation, such as eliminating internal transactions.
Here are some examples of disadvantages of EXCEL. Separate companies have their own code of account (“COA”) for preparing their financial statements. We map the COA of the consolidated entity and the COA of the separate company to prepare consolidated financial statements. The COA of the consolidated entity or the separate company is subject to continuous changes, and if we manage this with EXCEL, there is a high possibility of human error. This is what I mean by “work by EXCEL has low stability”.
Thank you for that! It does sound like you’re going to need many different tables to accommodate the disparate needs of the many subsidiaries. I’m sorry I don’t have anything better to offer.
