home
news
study zone
career advice
pq awards
free mag
advertise
contact
nq mag
PQ magazine is for part qualified accountants.
Read the latest web issue here – if you like what you see sign up today
Study Zone
Let’s get technical: spreadsheets
Nick Craggs just loves spreadsheets – and here he explains how you should use them to pass your exam
July 2017
The March 2017 advanced level synoptic window was the first window when a large number of students sat. This exam involves some element of human marking, so the results take six weeks. So it was not until May that it was discovered a lot of students did not do as well as they would have liked, and it was the spreadsheet section that was the area students struggled with. In fact, AAT has since added 15 extra minutes to this section to help students. Problems, however, seem to have persisted (just read this issue of PQ).
I love spreadsheets and think a long, convoluted, formula that includes as many functions as possible is a thing of beauty. In fact, if you asked me to choose my favourite spreadsheet function I don’t think I could. It would be like asking me to choose between my children.
Before I get into functions, the first thing I would like to stress to students sitting this exam is to be very diligent with your formatting. So if the question says all monetary values must be to two decimal places, with a £ sign and a comma to separate thousands, don’t think that is just to make it look nice. If it isn’t exactly in the prescribed format asked for in the question you will drop valuable marks.
After stressing that, I want to move onto the more exciting part of this exam; the functions. I do not have space to go through every assessable function, but I want to go through some of the more common and, in my opinion useful, functions.
The exam will be based on various scenarios and you need to be able to use simple functions to achieve certain accounting tasks; these could include add (the function you type is +), subtract (-), multiply (*) and divide (/). An example of the type of question you may get is a flexed budget.
Why don’t you have a go at flexing the above simple budget, using * to flex the figures from the original budget to 12,000 units, note overheads are fixed. You could also use + and – to automatically calculate the profit:
£
Sales (based on 10,000 units) 240,000
Cost of sales (110,000)
Gross profit 130,000
Overheads (50,000)
Net profit 80,000
The next function I want to mention is the IF statement. There are three sections to an IF statement: the test, the action if the test is met, the action if the test isn’t met. Let’s have a look at an example:
=IF(B2>500,B2*1.2,”no commission”)
The IF tells the program that you want to use an IF statement. Then in brackets you have three sections separated by commas. The first section, B2>500, is the test. So the program is looking to see if the value in cell B2 is greater than 500.
The next section is what you want the program to do if the test is met – in this case it will multiply the value in cell B2 by 1.2.
The third section is what the program will do if the test isn’t met. In this case, due to the inverted commas, it will return the “no commission” as a text value.
Why don’t you have a go at an example? Enter the following figures into an individual cell in your spreadsheet program:
257
280
303
312
220
In the cell to the right of these enter an IF statement. This will calculate 20% of these figures if the figure is above 300, and returns “no tax due” if the figure is below 300.
As I have mentioned, formatting is really important. A key tool is conditional formatting. This changes the formatting of a cell if a test is met. It is pretty straightforward too – go to home, then conditional formatting, and highlight cell rules. Here you can see the functions available; try highlighting the numbers above red if they are above 266
Another function I really like, and it is used extensively in AAT’s Sample assessments, is Goal Seek. When you know the result you want from a formula, but not one of the variables, this will work backwards through the formula to find the missing variable.
A common use for this when you know how much money you want to borrow, you know how long you want to pay it off, and you know how you can afford per month. You could use the PMT (not assessed by AAT) function to calculate the maximum rate of interest your potential loan can have.
Let’s look at a simple example:
In a cell in a new worksheet enter the figure 456 into cell A1. Then in cell B2 enter the following formula = A1+B1. We want Excel to enter into B1 the number that will make our formula output 1,000. So we go to the Data Tab, then What-If Analysis, then Goal seek.
We can then enter our values. So the set cell is the cell we have the formula in, which is B2. Set the value to 1,000, and we want to do this by changing cell B1. Press ok and it tells us that cell B1 should be 544.
My last piece of advice for the exam, is save your work frequently.
You can check the answers at www.firstintuition.co.uk/category/aat/
• Nick Craggs is distance learning director at First Intuition
[«all Studies]
Subscribe to RSS