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

Based on the last couple of threads from the Sage discussion group , I also thought this may be helpful to some users in both excel and the sage user group . I do appreciate some of this may not be relevent to excel users , but the beginning is to use excel for integrated reports.  I have try to cover some of the items that have been mention in previous threads.   Some of the things I have suggested  need Excel 2007 onwards.  Sage Versions are 2010 and 2011 , and I will have a look to see if I can find them in a earlier Version 

I have done them by a Comment Re each topic so sorry if it appears I have loads of comments .  I did it that way so it was easier to follow. The Areas i have Covered are Sage Integrated Reports , Bank Rec's , Drillable Reports , Locking Month End (Discussion ).  Entering  Comparative Figures after Shutdown.  Suggestions for the music to be forwarded to Sage .  I hope some of this is useful.   If anyone needs further info just ask in the comment and I will try to help.  Kind Regards Sarah.  

Please note there is new Update available from yesterday for 2011 UPDATE 1 if you are missing this report .  When you upgraded. 

Comments

Bank Reports

sarah douglas | | Permalink

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

How to Save a Report to Use in Excel Sage Integrated Reporting

sarah douglas | | Permalink

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

Set up Company in Sage integrated Reports and How to use it in t

sarah douglas | | Permalink

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 Prior year Comparative figures , when accountants prov

sarah douglas | | Permalink

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

Drillable Reports for for your P&L Accounts , Aged Debtors and A

sarah douglas | | Permalink

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

Lock Items after Month End

sarah douglas | | Permalink

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

The Music ( Thats unforgivable )

sarah douglas | | Permalink

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

Locking figures after month end

taxrebel | | Permalink

The reality is that anyone that needs decent monthly management reporting has to use incorrect document dates in order to post transactions in the correct accounting period.  You cannot present monthly accounts where the old YTD figures keep changing.  Management lose any faith in reliability of the accounts.  The conflict arises because of Sage's failure to recognise that there is a difference between a document date and an accounting effective date.  What is needed is proper period control. 

In light of this functional weakness, and the fact that it drives widespread abuse of the VAT regs, I am always amazed that HMCE gives Line50 a clean bill of health.

 

 

 

Locking Figures and HMRC

sarah douglas | | Permalink

-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

Bank Repots

nordic | | Permalink

Do you mean Sage Line 50 Version 10 or Version 2010 as I have Version 14.01.21.241 and cannot find the reports you mention

Versions

sarah douglas | | Permalink

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

Witch-Queen's picture

Reports

Witch-Queen | | Permalink

The reports available in Sage are not based on which version you have (ie. 2010/2011 or whatever).

The reports available to you are based on which of the 4 types of that version you purchased.

So is you only purchased Sage Instant you will only have a few reports. Sage Accounts will have a few more, Accounts Plus has more again and Professional will have them all

If you only buy the cheaper versions, don't expect to get advanced reporting

-- Witch-Queen

Reports

sarah douglas | | Permalink

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

Reports

Optegra | | Permalink

In response to this post, anyone wanting detailed reports no matter what version of Sage 50 or Sage instant might want to take a look at Optegra's applications.

Email me for a free no obligation trial of MRP or Pivot.

All done simply by clicking 3 buttons in Excel - Could not be any easier

Jeremy@optegra-financial.co.uk

 

P&L monthly breakdown

Bryony | | Permalink

Does anyone know if this is supposed to be available in Sage Instant? I can scroll to get Management Analysis reports and the split to include Standard, but it does not provide a report in the screen on the right. It is a very useful report and I use it on Sage 50.

Sage Instant

sarah douglas | | Permalink

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 

 

Add comment
Log in or register to post comments