Custom Trial Balance showing Debit/Credit of a given Period Range.
I’m trying to make a yearly Trial Balance with starting and ending balance. What I was trying to accomplish was:
Account | Balance as of 1st Period | Period 1 Debit | Period 1 Credit | Period 2 Debit | Period 2 Credit | ... | Balance as of last Period
What I end up was:
Account | Balance as of 1st Period | Period 1 - 12 Debit 1 | Period 1 - 12 Credit | Balance as of last Period
I couldn’t use the Amount (Debit/Credit) column because it defaulted to total, instead of letting me choose the view. So I inserted them separately. I’m using an Alternate Period Range Type : Relative to report date, Alternate period range : This Fiscal Year and Range Segment : Accounting Period
Hopefully anyone has tried to do the same in a better way, any help is appreciated.
Unfortunately, I think there aren’t any great answers here. Your options are:
- Live with what you created. If I’m understanding correctly, it’s got all the right data and it’s dynamic, it’s just that the columns aren’t ordered the way you want. You could maybe do some excel wizardry to reorder your columns for you if it’s too much of a pain to do it manually.
- Manually create each column in the report with ‘Alternate Period Range Type: Relative to today’s date’ and ‘Alternate Period Range: custom’. This will get you the ordering you desire, but unfortunately, this means that the period for each column is hard-coded and not dynamic, since the dynamic date range options won’t allow you to select each period in a fiscal year individually. So every year you’re going to have to remember to come in and update the report to for all 24 columns in the middle.
- Do this as a saved search: You could get the best of both worlds here, but the formulas are going to be a PITA, since you’ll have to manually separate out the BS and IS accounts and calculate appropriately. And there’s probably going to be a couple of gotchas I’ve forgotten about, which you’ll discover when reconciling. If you’re not already very familiar with formulas/SQL expressions, I think this will be very challenging to do.
So yeah, no silver bullet, unless NetSuite does some more work on SuiteAnalytics Workbook, but at least one of those should be workable, depending on your use case. Hope that helps!