I am looking to streamline the preparation of the annual Stat accounts & notes to the financials.
Currently, we export the TB and colour-code the GL’s into groups.
I am looking to introduce mapping so that the notes to the financials are mapped to particular GL codes and the financial statements link back to the notes to the financials to ensure that the numbers agree.
For the line items, which are not accompanied by Note #, I am looking for them to pick up the appropriate value directly from the TB.
In regards to Accounts Payable & Accounts Receivable balances, when they are the inverse of their nature state, I want them treated as their opposite (i.e. If the AR balances is in credit, I want it treated as AP & vice versa).
Fortunately, we have the prior year mapping approach & in order to reduce restating the prior year comparatives, I want to maintain consistency across years.
Example of data:
<tbody>
</tbody>
My initial thoughts are to have something like Name Ranges, so that all GL accounts within a certain number range (only based in the #s before the 1st decimal place) are grouped as one line item, that way a sum-if could be applied, so that when new GL codes are created, they will automatically be picked up in the mapping. I have not effectively used name ranges, so I may be barking up the wrong tree in terms of Excel function for the desired result.
Regarding the AR/AP allocation, I am thinking of have the financial note/financial statement column description for these accounts to be based on a formula to be driven by the signage of the account, something like an IFS statement (so that AR Negative balances are treated as AP, vice versa). I am open to suggestions.
I have tried the SUMIF applied to the “Financial Note Line Item”, but this is not always giving me the desired result, nor do I think this is necessarily the most efficient approach.
Further, given that rounding issues can occur with stat accounts, I am looking for the ideal Rounding formula to ensure that all numbers round and flow between the notes, P&L, Balance Sheet, Statement in Changes of Equity & Cash Flow Statement. I'm currently thinking that =ROUND(CellRef,0) is the best approach, I could be wrong.
As I wish to use this for consolidated reporting as well as individual company reports, it would be great to be able to use something akin to SUBTOTAL, so that I can choose the company codes I wish to use for a particular set of accounts.
Additionally, regarding the text field in the financial notes, some of these refer to note #s; however, the text is in brackets. I want to be able to pick up the note number via cell referencing but keep the other text (including the brackets). I was thinking &TEXT, but this does not seem to work with brackets within text).
Furthermore, when it comes to ensuring that the Excel Table formats are using the exact same format as the word template (so that when figures are copied from excel back into word, the formatting is the same as MS WORD, how is that best achieved)? Especially if the word template format changes in future years (as I do not want to be having to re-engineer the excel workbook beyond minor formatting changes).
Another issue that exists is that it doesnÂ’t seem possible to have MS word reference a particular note number via a reference approach (out current approach is to manually type the Note Number in the reference, rather than have the reference # change when the note number changes (IÂ’m referring to where the note number is within a text paragraph rather than as a header).
I am open to alternative suggestions provided it is more efficient and is reusable for multiple entities across multiple years and ensures consistency in approach from the prior year (therefore avoiding the need or restatement of the prior year comparative).
Currently, we export the TB and colour-code the GL’s into groups.
I am looking to introduce mapping so that the notes to the financials are mapped to particular GL codes and the financial statements link back to the notes to the financials to ensure that the numbers agree.
For the line items, which are not accompanied by Note #, I am looking for them to pick up the appropriate value directly from the TB.
In regards to Accounts Payable & Accounts Receivable balances, when they are the inverse of their nature state, I want them treated as their opposite (i.e. If the AR balances is in credit, I want it treated as AP & vice versa).
Fortunately, we have the prior year mapping approach & in order to reduce restating the prior year comparatives, I want to maintain consistency across years.
Example of data:
| GL Code | Closing Balance | P&L/Balance sheet | Asset, Liability, Equity, Rev, Exp | Current/Non-current | Financial Statement Line Item | Financial Note Line Item |
| Acct#.#.Company# | Debit +, Credit - | |||||
| xxxx.xx.xx* | ||||||
<tbody>
</tbody>
My initial thoughts are to have something like Name Ranges, so that all GL accounts within a certain number range (only based in the #s before the 1st decimal place) are grouped as one line item, that way a sum-if could be applied, so that when new GL codes are created, they will automatically be picked up in the mapping. I have not effectively used name ranges, so I may be barking up the wrong tree in terms of Excel function for the desired result.
Regarding the AR/AP allocation, I am thinking of have the financial note/financial statement column description for these accounts to be based on a formula to be driven by the signage of the account, something like an IFS statement (so that AR Negative balances are treated as AP, vice versa). I am open to suggestions.
I have tried the SUMIF applied to the “Financial Note Line Item”, but this is not always giving me the desired result, nor do I think this is necessarily the most efficient approach.
Further, given that rounding issues can occur with stat accounts, I am looking for the ideal Rounding formula to ensure that all numbers round and flow between the notes, P&L, Balance Sheet, Statement in Changes of Equity & Cash Flow Statement. I'm currently thinking that =ROUND(CellRef,0) is the best approach, I could be wrong.
As I wish to use this for consolidated reporting as well as individual company reports, it would be great to be able to use something akin to SUBTOTAL, so that I can choose the company codes I wish to use for a particular set of accounts.
Additionally, regarding the text field in the financial notes, some of these refer to note #s; however, the text is in brackets. I want to be able to pick up the note number via cell referencing but keep the other text (including the brackets). I was thinking &TEXT, but this does not seem to work with brackets within text).
Furthermore, when it comes to ensuring that the Excel Table formats are using the exact same format as the word template (so that when figures are copied from excel back into word, the formatting is the same as MS WORD, how is that best achieved)? Especially if the word template format changes in future years (as I do not want to be having to re-engineer the excel workbook beyond minor formatting changes).
Another issue that exists is that it doesnÂ’t seem possible to have MS word reference a particular note number via a reference approach (out current approach is to manually type the Note Number in the reference, rather than have the reference # change when the note number changes (IÂ’m referring to where the note number is within a text paragraph rather than as a header).
I am open to alternative suggestions provided it is more efficient and is reusable for multiple entities across multiple years and ensures consistency in approach from the prior year (therefore avoiding the need or restatement of the prior year comparative).
