How to use the Tier 4 Excel template
This tutorial will help you use the Tier 4 performance report template in Excel. You can watch the tutorial as a series of short videos or read the written version below. It will take 20 minutes to watch the whole tutorial.
Watch the tutorial(external link)
Watch the tutorial(external link)>
Contents
Welcome
1.1 Outline
1.2 Download the template
1.3 Save the template
A quick tour
2.1 Worksheet, ribbon & sheets
2.2 Name a year end
2.3 Asterisks and guidance notes
2.4 Edit cells
2.5 New paragraph within a cell
How to…
3.1 Unprotect a sheet
3.2 Insert, hide, or delete rows and columns
3.3 Formulas
3.4 Drop down lists
3.5 Editing the accounting policies
3.6 Print
3.7 Save as a PDF
3.8 Getting ready for the next year
Welcome
1.1 Outline
If you are new or not very familiar with Excel, you don’t need to worry. You don’t have to be an expert to use the Tier 4 Excel template.
In this tutorial we start with 'A quick tour' to introduce you to the template and give you a few helpful tips to get started. Then in the ‘How to…’ section, we cover the things you’ll need to know to use the key functions of the Excel template.
This tutorial is also available in a series of short videos.
Watch the tutorial(external link)
1.2 Download the template
You can find the Performance Report templates on the Charities Services website.
There are two versions of the Excel template:
- XLSX file - Download if you use the 2007 or a newer version of Excel.
- XLS file - Download if you use a version of Excel from earlier than 2007.
The Guidance Notes are designed to be used alongside the performance report template. Together, the template and the guidance notes will help charities complete a performance report that meets the Tier 4 Standard. The Guidance Notes tell you what information goes in the performance report template and provides references back to the Standard.
The Standard is the legal document which sets out the minimum content and quality of the performance report.
Go to the 'Standard, template and guidance notes' page to download>
1.3 Saving the template
When you first download and open the template, you might need to click Enable editing so that you can save and edit the template. It’s a good idea to save the template file with your charity's name and the financial year end date, and save it in a place that is easy to find later.
- Go to File, and select Save as.
- Choose where you want to save, such as in your Documents. You may want to create a folder to save all your performance reports in.
- In File name, type in your Charity’s name, and the date of the financial year end. For example, ‘Blue River Trust 31 March 2016’.
- When you’re done, select Save.
Remember to save regularly while you complete the performance report template. You don’t want to lose any of the work that you have done.
A quick tour
2.1 Worksheet, cell, ribbon & sheets
This is the template in Excel. This is the 2010 version of Excel. Your version of Excel might look a little bit different if it’s older or newer, but it should be pretty similar.
- The main area here is called the worksheet, which is the performance report template.
- When you click on the worksheet it will highlight a cell.
- You’ll find all the tools you need to edit and format the template at the top, which we call the ribbon.
- At the bottom of the window are tabs, which in Excel we call sheets. The different sections of the performance report are on different sheets. You can click on these to go to that part of the performance report, which makes it easy to move between sections.
When you first open the file you should be on the sheet named ‘Header (start here)’.
2.2 Name and year end
The Header (start here) sheet has some smart features built into it. When you enter your charity's name and financial year end date it will automatically appear throughout the performance report.
2.3 Asterisks and guidance notes
You’ll notice the red asterisks (*) throughout the template. The sections marked with a red asterisk must be reported if it’s relevant to your charity. If there isn’t an asterisk, then it’s optional.
When you click on a cell to type, a little box will appear. This will help you know what you need to put in the cell. For more information on what to report in each section, you will need to refer to the Guidance Notes. The column on the left has a reference to help you find the relevant section in the Guidance Notes. The Guidance Notes also contain references back to the Standard. You can download the Standard and Guidance Notes from the Charities Services website.
2.4 Edit cells
Click once on the cell and start typing. This will overwrite the text that is already in the cell.
Double click to edit the existing text.
2.5 New paragraph within a cell
To create a new line or paragraph within a cell, do the following:
Windows computer
On your keyboard, hold down the Alt key and then press the Enter key
Mac computer
Depending on the version of Excel you are using, the combination of keys will differ.
On your keyboard, either:
Hold down the Control and Command keys and then press the Enter (or Return) key
- OR -
Hold down the Control and Option keys and then press the Enter (or Return) key
- OR -
Hold down the Option key and then press the Enter (or Return) key
If you use just the Enter (or Return) key, you move to another cell.
How to...
3.1 Unprotect sheet
If I try to edit or delete some cells, a message will pop up to say the cell is protected. This template contains a number of formulas and other features, which are designed to make it easy to complete. Each sheet has been password protected to avoid accidentally changing these features.
You can remove the protection, but we advise you take care with any changes you make if you do so.
To unprotect the sheet:
- Go to the Review ribbon and select Unprotect Sheet.
- Enter the password xrb and click OK.
The passwords are case sensitive, so be sure to use lower case.
You will need to repeat this for each sheet that you want to unprotect. Once you have finished making your changes, you might want to put the protection back on.
To protect the sheet:
- Go back to the Review ribbon and select Protect.
- You can choose to add a password, or simply click OK.
The formula and features on this sheet will again be protected from accidental changes. If you do choose to enter a new password, be careful, you don’t want to forget it!
You should only unprotect the worksheets that you need to as the protection will save you from making accidental changes which could be difficult to fix.
3.2 Add, hide or delete rows and columns
At times you may need to insert extra rows, and delete or hide rows and columns that you don’t use.
These can all be done using the same method.
- Select a row or column. To do this, left-click on a column’s letter or a row’s number.
- While you hold the mouse down, you can drag to select more columns or rows.
- With the row or column selected, Right-click to view the shortcut menu.
- In the shortcut menu, click to choose to Insert, Delete, or Hide.
- You can also find these formatting options in the Home ribbon in the Cells section. Hide can be found under Format.
Some of the options may not be available and you will have to unprotect the sheet to access them.
We recommend that you hide rather than delete. The advantage with hiding, is that should you need them in future years, the columns or rows are still there and easy to add back in.
For example I may wish to hide the Budget column in the Statement of Receipts and Payments:
- First Unprotect the sheet. Go to Review, click Unprotect, and enter the password xrb.
- Left-click to select the Budget column.
- Right-click to view the options and click Hide.
- The budget column is now hidden.
You will know rows or columns are hidden as the row numbers or column letters will no longer be consecutive.
To unhide the Budget column:
- Left-click and drag the mouse to select the columns on either side of the hidden Budget column.
- Right-click to view the shortcut menu and select Unhide. You should be able to see all the hidden rows now.
3.3 Formulas
On some sheets, you will notice that some cells are grey. These have formulas built in that will automatically calculate things for you.
If you click on one of these cells, you will be able to view the formula in the Formula Bar, which is located just under the ribbon.
Here are some examples of formula on the Statement of Receipts and Payments:
1. Total Operation Receipts
I28 =SUM(I19:I28)
This formula will calculate the sum of the numbers in cells I19 down to I28, to show the total operating receipts.
2. Operating Surplus or (Deficit)
I44 =I29-I42
Calculates the number in I29 and subtracts the number in I42, to show whether there was a surplus or a deficit. If there is a deficit the number will be red and in brackets.
3. Balance check
I67 =IF((I57-I66)=0,"")
Checks that cell I57 (Bank Accounts and Cash at the End of the Financial Year) equals I66 (Total Bank Accounts and Cash at the End of the Financial Year).
If these numbers aren’t exactly the same, FALSE will display, letting you know that it doesn’t balance. You will need to check all the numbers that you entered to see why. You may have forgotten to include some receipts or payments. If the numbers are just a little bit out, this could be caused by rounding to whole dollars. If rounding has caused this problem, you can slightly adjust the ‘Other operating payments’ category so that the numbers balance.
The sheet is protected, so you can’t accidentally change the formulas. If you choose to unprotect the sheet, you will need to be careful that you don’t accidentally change the formulas.
3.4 Drop down lists
When you click on some cells, you might notice that an arrow appears to the right. This tells us that there is a list attached to this cell. Click the cell a second time to view the list. You may need to scroll up or down to view the whole list.
You can use the list suggestions in the template, but feel free to change and add to the list to make it relevant to your charity.
To edit this list, go to the Lists sheet. If you can’t see the sheet, use the arrows in the bottom left corner of the Excel window to scroll through and find sheets.
The Lists sheet contains all the drop down lists from throughout the template and is where they can be edited. There are lists for:
- Bank accounts and cash
- Resources
- Commitments
- Receipts notes, and
- Payments notes.
On the lists sheet, you will also find the accounting policies.
3.5 Edit the accounting policies
In the sheet Note1 Policies statements have been prepared for you. You just need to choose the GST statement that applies to your charity and hide or delete the other.
You shouldn’t need to edit these statements, but on the rare occasion you should need to, there is a trick to it. If you double click on the cell to edit the text, you notice the text disappears and there is only a formula. This is because it’s pulling in your charity’s name and information from the ‘lists’ sheet. You will need to go to the ‘lists’ sheet to edit.
- Go to Lists sheet.
- Scroll down to the bottom to find the statements.
- Double click the cell to edit the text.
3.6 Print
You can print by:
- Selecting File in the ribbon, and Print.
- Check that you have selected your Printer.
- To print just the sheet you are working on, in the Settings select Print active sheets.
- If you want to print the whole performance report, select Print Entire Workbook.
- When you are happy with the Settings, select Print.
You can scroll through the pages to preview what will be printed. If you only want to print some of the pages, you can specify which ones.
It should print fine, but if there are problems, you might need to change the Settings. For example, if all the information doesn’t fit on to a page, you can change the Scaling. You might want to choose Fit sheet on one page or Fit all columns on one page.
3.7 Save as a PDF
Once you have finished the performance report you may want to save it as a PDF. A PDF makes it easy to email or upload the performance report so you can share it with other people, funders or Charities Services. The PDF format means that others can’t make any further changes to the document.
- Go to File, and select Save as.
- Check where you want to save the file.
- Change the Save as type to PDF.
- Click Options.
- Under Page range, you can include All the pages or just select the pages you want to include by typing in the Page(s).
- Select Entire workbook and click Okay.
- Select Open file after publishing so you can view the PDF straight away.
- Now click Save.
It will take a few moments to publish, and then the PDF will open.
3.8 Getting ready for the next year
When the performance report is complete, you can get a file ready for next year.
- Go to File, and select Save as.
- Save the file in a location you can easily find later, such as a performance report folder
- Change the file name to next year. For example change 2016 to 2017.
- Then click Save.
Once the file is saved with the new file name, you can start making changes to get ready for next year.
Start on the sheet, Header (START HERE). Change the balance date, For the year ended, to the date for next year. For example change 2016, to 2017.
You don’t need to do anything with the Entity Info sheet. This will be reviewed and updated where needed next year.
In the rest of the sections, copy and paste This year values to Last year.
- The on the keyboard, press Ctrl and c (or right-click and select Copy).
- Select the top cell you want to paste to. On the keyboard, press Ctrl and v (or right-click and select Paste).
- You will need to Special paste if you copy a formula. When you go to paste, Right-click on the top cell, and select the Values option.
- Once the values have been copied correctly to Last year, you can delete This year values. Click and drag to select the cells, and press Delete on the keyboard.
Don’t copy and paste the formula in the grey cells, they don’t need to be changed. Changing these could create problems. If the sheet is protected you won’t be able to accidentally do this, so it’s a good idea to keep the sheet protected.