customize a income statement

SOLVED

Is there a way to create a customized income statement to have the accounts running across the page rather and down?  I have a client where I just had to create a new sage file and built all the accounts from scratch to match their required set up.  It's basically 5 headings and within each heading they have the same type of accounts for both revenue and expense.  They want to have the income statement show the 5 headings across the top of the page (from left to right) and all the accounts within each listed below them.  Can this be done in Sage or can it only be done in excel?

Parents
  • 0

    swissmrs59 said:
    Can this be done in Sage or can it only be done in excel?

    Yes.  It may be possible to modify the Excel report that comes with Sage 50, this one creates two columns only:

    But a lot of the VBA code would have to be rewritten.   it's probably much easier to go with a custom 'third-party' reporting tool such as XLGL.

    swissmrs59 said:
    same type of accounts for both revenue and expense.

    You're more familiar with the client's requirements, wouldn't Projects or Departments match their report requirements?

  • 0 in reply to RandyW

    I could probably set up departments, but would that allow me to have them display side by side?  If not, then using departments probably won't make a difference.

  • 0 in reply to swissmrs59

    swissmrs59 said:
    would that allow me to have them display side by side?

    Yes, there is a pre-made Departmental Income Statement in the Report Centre, for the Universal construction sample company.

    I only have the Quantum Edition, but hopefully this would be in all of the Editions of Sage 50 that have Departments. 

    I did a quick, not too good screenshot, it may have cut off the engineering column on the right, and with no revenue or expenses for two of the Departments so it really doesn't show off the capability of Departments so well.. 

Reply
  • 0 in reply to swissmrs59

    swissmrs59 said:
    would that allow me to have them display side by side?

    Yes, there is a pre-made Departmental Income Statement in the Report Centre, for the Universal construction sample company.

    I only have the Quantum Edition, but hopefully this would be in all of the Editions of Sage 50 that have Departments. 

    I did a quick, not too good screenshot, it may have cut off the engineering column on the right, and with no revenue or expenses for two of the Departments so it really doesn't show off the capability of Departments so well.. 

Children
  • 0 in reply to RandyW

    Thanks Randy.   I know departments are only available from premium up.  I will try setting up the categories as departments and try this report.

  • 0 in reply to swissmrs59

    So I set up departments, one for each category and printed out a departmental income statement. (see below).  What the client would like is just 1 set of actual accounts listed going horizontally.   All the categories have same set of accounts.   Right now I have each set of accounts with their own heading, then the accounts as group accounts and a total at the end.    Would the report they want work if I just create 1 revenue heading and make all the accounts as sub accounts instead?    

  • 0 in reply to swissmrs59

    Hi,

    swissmrs59 said:
    What the client would like is just 1 set of actual accounts listed going horizontally.

    It looks like there may be 4 extra sets of base accounts - five sets of accounts x a set of five departments -  i.e. there are effectively 25 accounts for Federal Funding, 25 accounts for Provincial funding, etc for a total of 250 individual accounts, when you need 50?  Can you not just delete 4 sets of accounts?

    If you used 1 account for each of Federal Funding, Provincial Funding, etc. X 5 departments the software would keep a 'grid' of 50 accounts that can be displayed in a table - 1 for each combination of 10 accounts x 5 departments.

    If they absolutely insist on continuing with an old numbering scheme that worked in their old program or in their paper system, you could develop a naming scheme that would work with an Excel Pivot Table, or do a load of custom VBA programming to make that happen in one step, or develop a custom report for that one client in XLGL. 

    The best solution depends on whether they need to run this report hundreds, dozens, or a only a few times annually.

  • 0 in reply to RandyW

    There are only a total of 130 accounts.  50 under revenue and 80 under expenses.  Since I created the file from scratch I set up my own numbering based on what they wanted.  There are the 5 'departments'  Under each department there are 10 accounts for revenue and 15 for expenses.  But the account names under each department are the same.  Eg there are 5 federal funding accounts (1 under each department).  The account numbers don't match their old format which is why I created a brand new data base to effectively start over since it was easier than trying to change the existing accounts on the old file.   I tried setting up all the accounts under each heading (which are the departments) as sub accounts but it does not make any difference on the departmental income statement as those 5 sets of 10 accounts under revenue are still all listed down the page.  I know what he is asking is to have just the 10 accounts listed but have the columns running across under each department.   I can't get that to work unless I used only 10 revenue accounts and 15 expense accounts in total.   But if I do that I don't know how to allocate an expense to the same numbered account but belong to a specific department since the payments module needs an account # but it doesn't let you specify a department as the account # has already been assigned to a department.    I have a feeling the only way I'll be able to give them what they want is to export to an excel spreadsheet and manually move the figures.   Should not be too onerous a job if I can set up a macro do it each time.  I'll just have to make sure I have each account display on the income statement even if the balance is zero so I'll always have the same number of lines under each department's list of accounts.

  • +1 in reply to swissmrs59
    verified answer

    swissmrs59 said:
    But if I do that I don't know how to allocate an expense to the same numbered account but belong to a specific department

    I'm not sure what you mean, when I open the sample file, go to the payments screen, and select a payee I can choose an account and expand the + for departments :

    swissmrs59 said:
    I can't get that to work unless I used only 10 revenue accounts and 15 expense accounts in total.

    swissmrs59 said:
    but it doesn't let you specify a department as the account # has already been assigned to a department.

    Under Setup | Settings | General | Departments, each of the 10 revenue and 15 expense accounts has to be able to access all the departments, then you should be able to post the amounts by department.

    It should work, however because I'm at a distance I could easily be missing what you mean about a key part of the picture.

  • 0 in reply to RandyW

    that's it!  Not being familiar with using departments I didn't know you could assign an account to more than 1 department.   I made a copy of my file to test and did as you suggested above.  This should make it much easier since I only need to deal with a total of 25 accounts.    Thanks very much for your help!