I have to create a new report: Profit and Loss Management Report.
The first column needs to display the actual balance for the current month.
The second column needs to display the sum of the balances from start of the financial year to the end of the current month.
When I set up the column layout for this report, I have set column type as Balance To Date for the first column, it is OK.
But what about the second column, there is no option in the column type field on the Column Layout table that enables me to sum up the balance of several periods.
Is it possible to create another option for the column type field in the Column Layout table that enables this?
In fact, in the Column Type field, only the following options are available:
Formula
Net Change
Balance at Date
Beginning Balance
Year To Date
Rest of Fiscal Year
Entire Fiscal Year
My question is whether it is possible to create a new Column Type option that will enable me to retrieve figures of cumulative balances from the start of financial year to the current period for one column in my account schedule.
Say, I am running my account schedule for March 2006 and my financial year runs from January to December 2006. The first column will display balances at March 2006(Current {Period), whereas the second column will have to display cumulative balances from JAN 2006 to MAR 2006 in the same account schedule.
So you are trying; not to modify an Account schedule; but actually to modify the code that generates account schedules so that you can then create new accounts schedules with this feature?
The Year To Date will give me the net change in the account balances from the start of the fiscal year to the end of the period. The figures comes same as my first column, that is, the balance at the end of the current period.
What I need is the cumulative balances from January 2006 to March 2006. That is, the sum of the balances for JAN 2006, FEB 2006 and MAR 2006.
In the column layout look at the help files in the Column Date Formula and Column Period Formula, this will tell you what values date formula you need to enter to change this column to a fiscal period based on the date range you enter when running the report.
Something is not right, balance at date should be cummulative balance to date not balance at end of period (I’m checking 3.7), year to date is from start of fiscal year and net change is the movement.
Can you check what your fiscal year is set as? Also are the results expected with the other date periods.
I don’t have a Balance at Date except on Chart of Accounts. Balance produces the same value as the Balance at Date on the chart of Accounts. If you have closed the previous fiscal year balance and Year to Date will be the same (if closing entries excluded)
Which Version are you running? Have you had this area modified? I take it all the other column types exist. In any case you should just need Net Change and Year to Date, and if these are not working and column type has been changed, I would contact your NSC to see what they have done.
I wonder if in fact this is because of the differnce between Balance Sheet and Profit Loss accounts. P/L accounts require an end of year closing for them to really make sense. Its quite possible that this is causing you problems.
Not sure I can help much more, but one of the real experts may. It works fine in 3.7 and would be surprised if changed in 4. Can only suggest you re-check the Accounting Calander setup, that year end processes are complete, you are using the correct column layout, or set up new column layout with a column for all the options and post the results, and compare with chart of accounts (by Dimension)