Any advice for historical data using spreadsheets?
I'm working on building spreadsheets for our reporting processes. I am curious what is the preferred or best practice for historical data?
Previously in workbook we did Index Match formulas to view the historical data and pull in prior periods based on the date. With spreadsheets we are syncing in the entire trial balance and creating our statements/footnotes to vlookup match formulas to get the appropriate account for each line item. This works great for current information but for historical periods has it been easier to store prior reported data in a spreadsheet worksheet and pull the data from there or to look up from the trial balance the prior periods.
It doesn't seem to cause much issues until we get to rolling from Q4 to Q1. We have to redo all the rounding for the I/S and the balance sheet if we pull from the trial balance.
Anyone have a fully built out spreadsheet they are using for their 10Q/10K reporting and have some best practices?
Previously in workbook we did Index Match formulas to view the historical data and pull in prior periods based on the date. With spreadsheets we are syncing in the entire trial balance and creating our statements/footnotes to vlookup match formulas to get the appropriate account for each line item. This works great for current information but for historical periods has it been easier to store prior reported data in a spreadsheet worksheet and pull the data from there or to look up from the trial balance the prior periods.
It doesn't seem to cause much issues until we get to rolling from Q4 to Q1. We have to redo all the rounding for the I/S and the balance sheet if we pull from the trial balance.
Anyone have a fully built out spreadsheet they are using for their 10Q/10K reporting and have some best practices?
1
-
In terms of GL data, we store rounded numbers in our system of record (HFM), so we resync current and prior period data (into spreadsheets) on each new project. For supplemental info in the footnotes, we do something that may be similar to what you described in your workbooks. We keep the historical data in columns to the right (generally in data collection templates) and the prior period numbers index/match based on the period. That being said, we don't do this for a lot of the balance sheet tables since they only have to be updated once annually and it's an easy copy/paste between adjacent columns or rows. We mostly wanted to get out of having to go pull same period prior year financials to populate the income disclosures every quarter. 0
Thank you so much Ben. Does the system round the numbers for you and squeeze the quarters? Or are you pulling down the full balance rounding and booking entries? Or something complete different :)
Also have you had any issues with this approach for restatements (i.e. discontinued operations or standard adoption)?0
I'd call it a semi-automatic process. A reporting analyst has to open a grid and choose which segment/FS line to force the rounding up or down. The system takes care of plugging the whole dollar amounts needed to get to the rounded totals. The rounding adjustments are stored in a separate data type so that users can see either view. I've heard of more robust rounding setups in HFM where you define all your rounding decisions in a set of rules, but the approach we're using is pretty quick and serving its purpose.
You are right that a restatement could cause an issue with historical data. It hasn't yet for us (in 3 years) because the only restatements we've had involved collapsing F/S lines together. But a discontinued op would cause us to have to reopen a prior period and resubmit the rounding form. At the end of the day, it has to happen somewhere, and i like keeping rounding in the source system so all consolidated GL reports match the presentation in the financials.0サインインしてコメントを残してください。
コメント
3件のコメント