Sage Integrating Reports Add in Tab Excel , Drillable Reports , previous years comparatives and other useful tips

Anne Marie Sowell 

Problem: She needs a report to list Bank reconciled entries in date order.  So she can through the report line by line.

to find out what went wrong .  

There is a report called a Retrospective Bank Reconciliation (Detailed ) or Retrospective Bank Reconciliation, and this can be found in version 10 onwards .

  1    The report also puts it on the Db and Cr side the same as it would appear on a bank statement.

  2    The report show the date of reconciliation and each transaction and each transaction matched line by line as it was    matched .

Anne Marie Mention that when exporting to excel  it does not format well.  Sage Intergrated Reporting is much better then using the export button .  It also has a number of reports but this is how you would add one .  Anne Marie I have checked as does export with your debit and Credit correct as appears in your books , it is very easy to switch columns if you need to . 

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

Using the Excel Add in Tab you can print off any report from sage and it is exactly the same way you use a sage report except you are using excel.  There are 35 standard reports, and you can add or design any reports that you wish to use and it is very easy to work with, it is very different to the export button used when in the preview button.  

It is then very easy to do want you want to do within the sheet.  If you do find it difficult , sometimes  it can be easier if you copy the info on to another sheet, I use another sheet to layout the P&L the way I want by linking the info from sheet to sheet 

How to add a report to excel Sage Integrated Reporting .

1 ) Pick your report that you would like to use , Press edit report and make changes or leave as it is.

2) Save As to your Program Data folder, then to the following folders .  Sage  . Accounts . 2011 or 2010 or 2009 folder depends which version you are using.  COMPANY.OOO.  REPORTS. Excel  and then save name of report.

This report will now appear in the excel integrated reports option for you.  My next reply is how to set up a company in Sage Integrating reports and you can set up as many companies as you like. 

- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

Firstly Close down your Sage 

1) Open Excel in 2007 or 2010 Version this will only work with one spreadsheet open at a time. The Add In Tab will not show if you have another spreadsheet open 

2) Go to the ADD IN TAB 

3) Pick Select Company and you will be asked for your Logon Name and Password  which is for your sage accounts .

4) In this menu at the bottom Press the Grey Area Look for another Company   then pick Program Data.    Sage . Accounts.  COMPANY.001 or COMPANY.002 extra . ACCDATA . SET.DTA   .

This will bring you to the Welcome to Sage Accounts Window and you will see your company .  Press OK

USING THE SAGE INTEGRATED REPORTS ONCE SET UP 

1) In the ADD IN Ribbon you will now see the company you have set up and you also follow the same procedure to set up as many companies as you like. 

2) Press the Drop down button in between a light grey box and the word insert.  All your reports you saved and the 35 other reports will be there.

3) Highlight the report you want and then press insert beside the drop down box were you picked your report .

4) You will now see that a window from sage comes up  just like as if you were picking it through sage .  

Your report will now appear in excel spreadsheet .   And you can now play with it as much as you like.

5) Save your sheet, and close.

Please note you can do as many reports as you like on various sheets .  For example you could have all the Various Reports your clients wants each month on one spreadsheet,  when you open your sheet your computer may a security Warning DATA CONNECTIONS HAVE BEEN DISABLED . If you just confirm you would like to Enable this content if you want to to.

The Great use of this is it will update data that has been entered since you ran this report. 

1) GO to ADD IN Tab and Press the Refresh Button , you will receive a message and if you want to refresh the date press NO and NO again .  And you will receive a message your report has been refreshed .

I use this for all my clients as they all have their own requests and it is really easy to use.

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

To Change your comparatives after you have closed down and received further Journals from an accountant .

1) Go into Nominal Ledger , highlight Nominal Code , Details Tab , 

                          and then 

Go to the Column where you have each year end and you can highlight each month and change the figures to want you want them to be .  Use a - minus if you want it to be a credit Balance.   

When you print off any comparative or prior reports these will now be changed.

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

Reports 

There is a very good Drillable Report which I like to use, and it can be found in the FInancials Module Reports under 

Management Anaylysis Reports , Management Anaysis Reports - Standard Budgeting  and the Report is Called 

       Profit and Loss Monthly Breakdown  .

The rest of these reports in this section are also Drillable .  There is also an Aged Debtors Drillable and Aged Creditors Drillable Reports.

Info in Customers and Suppliers 

Within each Supplier or customer you drill to the Sales Invoice , you can also see which invoices and  credit notes are allocated to which Purchase Payments  or Lodgement or Bacs . And Also which Nominal Code it went to.  Only see outstanding Invoices , Purchase Payment and Sales Receipts, you even see Payments on account only.  

Entering Customer Receipts and Supplier Payments 

You can now in Version 11 put the invoices in date order , pull up only invoices in a date range and specific date. Only Invoices that are due.  I am not sure About V10 I think you could do this also.

If you want to look at last years information it is quite easy to restore a backup. 

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

This is open to discussion , and I feel it should be a option ,

The reason they don, t is because Sage is extremely sensitive with dates for the Vat Returns and especially CIS returns.  Locking the figures would cause people to enter late invoices and adjustments with the wrong dates .

The incorrect dates could lead to invoices been chased to late and paying Suppliers earlier.

I also think that it could lead to misleading P&L for each month.  With my clients we always said them the previous P&L with the Currrent P&L for their Financial Meetings to take this into account.  Also if the figures where locked then it would be difficult to use the Comparative Reports

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

What can I say.  I like the idea of 70s and punk,  but even thats 30 years old.  I would be more for the 20;s as in 2000, great decade for music .  Any ideas we could send it to sage. -

- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

-I think this depends on your clients as I said I do think you should have the option .

I personally do not like to see wrong dates and I very it very irritating especially when you are trying to do creditors recs. `most of my clients like to see their monthly trend and therefore like to see the items in the month their in so they can compare prior year and month on month. 

I would always tell my clients why their accounts have changed and can clearly show them the items that have changed them in a report.  If you have a good relationship with your client, they will trust what you produce especially if you working with them month on month.

HMRC and Vat When you you do your Vat Return it asks you do you want to include unreconciled items prior to the Vat Period , this covers entries that are late and our then  put in to the Summary and Detailed Report ,  that is why they get a bill of health.  

- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

I meant to say Version 2010 and 2011 .  I have Version 2007 I will have a quick look for you to see if I can find them , they could be new reports . The Excel add In Tab is in Excel 2007 and Excel 2010 

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

Hi 

The reason I have  mention these reports is because some of the threads mention that Sage is not capable of doing certain things .  

I have just spoken to Sage and they have said  that the Profit and Loss Monthly breakdown Drillable report  and the Bank Retro report was not available according to Sage on any Version of Sage earlier then 2010 no matter what level.  (I'll stand corrected if I am wrong ) .  If they are please let me know as I would find that useful .

Witch Queen is right is also depends what level you have of Sage,  for example Sage Instant will not have most of these.

-- Kind Regards Sarah@ Douglas Accountancy & Bookkeeping Services, Glasgow

Hi Bryony 

i am not sure if it is available  on Sage Instant , perhaps some of the other  Sage users could help with this one.  Unfortunately I do not have a copy of Sage Instant to check for you. 

Kind regards Sarah Douglas Douglas Accountancy and Bookkeeping Services Glasgow