Tier 4 Microsoft Excel template – Trouble shooting

Posted on 28 June 2016

By now many of you are getting stuck in to putting together your Performance Report for the year ended 31 March 2016. It’s been great to see how many people are using the tools on our website. We’re pleased to see that charities are using the Tier 4 excel template designed by the External Reporting Board(external link) (XRB). If you use this template correctly, you can be assured that you’re completing your performance report in accordance with the new reporting standards. We understand, however, that not everyone is familiar with the inner workings of Excel, and so we have provided some extra assistance to help you:

The Excel template is on our website, and if you are new to Excel, we have made a useful tutorial video  which will take you through the basics of the template. We highly recommend you look at it before you get started - it only takes 20 minutes! If you decide after watching the tutorial that you don’t want to use Excel after all, we have also created Microsoft Word and PDF versions  of the template which you can download and fill out. It just means you’ll have to do some manual adding up. These versions are on our website with the Excel versions.

I’ve been having a look at the returns coming in, and have compiled below a short list of the areas that people are having trouble with, with some suggested solutions: 

I keep getting a “FALSE” message in my Statement of Receipts and Payments

There are usually a couple of potential reasons for this:

• The false message occurs because it is comparing the “Bank accounts and cash at the end of the financial year” (Cell I57) to the “Total bank accounts and cash at the end of the financial year” (Cell I66). If these aren’t an exact match, the sheet will return a “False” message. The sheet asks you to enter in whole numbers only. If you’ve entered numbers with cents, the rounding can cause the two numbers to be unequal. Try entering all numbers rounded to the nearest dollar, and if you are still out by a couple of dollars, just adjust one of the numbers by a dollar or so until it balances.
• The other common reason for the “False” message is that you may not have a figure in the row called “Bank accounts and cash at the beginning of the financial year” (Cell I56). This cell is pulling the number through from the “Actual Last Year” column. Because you don’t need to provide comparative figures for the first year of reporting under the new standards, it’s likely that you won’t have entered anything in this column. All you need to do is to enter the closing balance of all your bank accounts as at 31 March 2015 into cell M56. This will then flow through to the current year column. 

Neither of the options for the GST accounting policy suit my organisation

Some charities are registered for GST but want to report on a GST inclusive basis. This has not been given as one of the options in the template, because most entities that are registered for GST will report on GST exclusive basis. It’s fine to report on this basis as long as you state what you are doing. Go to the lists tab in the template, and under accounting policies, just over-type the last GST statement to reflect what you are actually doing e.g. “X Charity is registered for GST and amounts recorded in the Performance Report are inclusive of GST”.

I’ve used the template but I don’t like the way it looks – what am I allowed to do to it?

Your Performance Report is available on our website for the public to see, so it’s worth spending some time on the presentation of the report as it’s an opportunity to market your organisation to readers. Here are some tips for making your report look nicer: 

• “Hide” any columns or rows that you haven’t used. For example, in the first year you are not required to provide comparatives so you could hide the “Actual Last Year” column and just use it next year. Likewise, if you don’t want to include your budget in the optional budget column, or if some of the minimum categories don’t apply to you, just hide them.
• Check the way your page breaks are set up. Go to “View” and then “Page Break Preview” to see the breaks. You can drag these up and down to format the pages. Hint: this may alter the page numbering at the bottom of the page so you might need to manually adjust this.
• Don’t forget to add page numbers on the Contents Page – this makes the report easier to read and adds a nice finishing touch.
• If you save the document as a PDF when you have finished, you won’t be able to see all the cell lines or have to flick through tabs to read the whole report. It also has the added benefit of protecting your work so that when you upload it to the Charities Register with your annual return, no one else can download and change what you’ve prepared.  You could also perhaps add an image to your front cover that reflects the work your charity is doing. 

The template prepared by XRB is entirely optional and you are not required to use it, however, it’s a really useful tool to make sure you are presenting all the information you are required to. Remember, it will be the most difficult the first time you do it, and after that it will be a piece of cake! We are here to help, so make use of the resources on our website or give us a call on 0508 CHARITIES.

Click here to be notified of future blog posts.(external link)