Xero: A unique combination of education and employability

Posted by & filed under Learning.

This content is brought to you by Xero.

Unlike any other accounting software provider or platform, Xero now offers a mix of education opportunities that benefit budding accountants and bookkeepers, education providers and, ultimately, employers.

Getting ahead with Xero Certification, for FREE

Xero Advisor Certification is the core way for accountants and bookkeepers to demonstrate their knowledge and understanding of using Xero in practice. Originally, this was only available to those already working and using Xero – but that’s all changed. Advisor Certification has opened up opportunities for anyone with ambitions to work in this new world of automated accounting.

Taking around six hours to complete, the course consists of a complete pack of self-paced e-learning modules, getting to grips with cloud fundamentals and an introduction to the Xero platform. Completing this certification doesn’t just show a commitment to working in the industry, it saves employers hours in training new joiners on unfamiliar software – it’s good all round.

This is all fantastic for digital skills and employability; with Xero Certification in very high demand we have seen hundreds of students gain employment in both practice and industry since launch.

As 83% of accountants in practice believe understanding technology is now as important to their as understanding accountancy, tech know-how is now a higher priority than ever before.*

Changing the face of education

Earlier this year we unveiled Xero Learn – a brand new way for training providers to introduce cloud accounting to their students and deliver a digital skills agenda. This unprecedented shift creates a more flexible way of teaching and learning as both tutor and student have access to a full, live version of Xero wherever there’s WiFi so, they can use it on any device, almost anywhere, any time – and it’s all backed up in real-time.

Students learn using real life business simulations, and tutors have greater control and creativity than ever before. They’re able to create, and view progress on, in-Xero exercises and manage their students’ access to Xero. So, it’s not just a chance for their students to get a broader education but for tutors to add another string to their bow.

We’re very excited to see the first wave of students successfully passing the ‘Using Accounting Software’ module of their AAT qualification – using just Xero, a groundbreaking achievement.

Everyone’s a winner

No matter where you are on your accountancy and bookkeeping journey – just starting out, sharing your knowledge or growing your practice – you can benefit from these initiatives.

If you’re learning the ropes and making yourself stand out to potential employers, you have the opportunity to shine – demonstrating a thirst for knowledge and an understanding that the world of accountancy is evolving. All you need to do is to visit Xero Advisor Certification and create a Xero login, choosing AAT as the organisation and you are ready to start your learning journey.

If you’re bringing on this new generation of accountants and bookkeepers, Xero can provide all the tools and support you need to successfully train them in cloud accounting, gaining genuine experience, not just hypothetical scenarios. And as an AAT Training Provider you are now not only able to teach AAT UACS using Xero but you are also able to offer Xero Advisor Certification to your students.

A practical understanding of cloud accounting

Zoe Robinson, Director of Learning for Kaplan UK says “Whilst knowledge of accounting concepts will always be important, the Xero Certification course gives our students a practical understanding of the impact cloud accounting technologies has on the profession – and helps them better apply their knowledge to add value to their clients”.

If you are an approved AAT training provider and would like to know more about Xero Learn and about Xero Certifications and Badges, please get in touch.

If you’re bringing new blood into your practice or into your company, you can look out for candidates with accounting software qualifications or Xero Certifications through recognised training providers – you’ll be hiring the cream of the crop.

Where else can you find that combination of qualification, added value and employability?

*Xero research with Censuswide 2016

This content is brought to you by Xero.

Excel tips: Save time with named ranges

Posted by & filed under Excel tips.

Named ranges are one of the MOST under utilised functions in Excel, and that’s a huge shame as it can save time, reduce the risk of error and make formula’s easier to enter, read and understand.

Part of the reason they are under utilised is that on their own, they don’t seem to do very much, but if use them alongside formulae, pivot tables or data validation, that’s when you start to see the magic happen.

What is a named range?

A named range is simply a ‘name’ that is given to a cell, or a range of cells. This ‘name’ can then be used in place of that cell, or range of cells.

So, in this instance, the formula is looking at the value in cell F1 for the VAT rate:

We could give the cell F1 a named range (VAT_Rate), and then the formula can refer to that instead:

This saves the user having to work out what cell F1 contains, as they can see directly from the formula that the ‘VAT_Rate’ is being used.

How to create a named range?

Creating a named range is super simple, select the cell (or range of cells), click into the ‘name box’ and type in the name, followed by enter:

There are a couple of rules to observe when choosing as name:

DO

  • Make names as long or short as required
  • Use mix of upper & lower case and numbers
  • Use underscore ( _ )

DON’T

  • Start named range with a number
  • Use characters, such as space or dashes
  • Use a name that is the same as a cell reference i.e. B2 etc

This is the error message you’ll see if a name is invalid:

This is not terribly clear as to what the issue is, or how to resolve it, and also does not make it clear that the named range has NOT been created.

How to find an existing named range?

To the right of the name box, you will see a small arrow:

Click on the arrow and a list of ALL named ranges in the file (across ALL sheets) will appear:

If you select a named range from this list, the cursor will move to the cell or cells in the range. This is perfect if you forget where a named range is, or if someone else has created them.

Key tip: This is also a great tool to use before ‘sort’ to highlight data, or to select data to print.

How to amend a named range?

From the ribbon: formulas, select ‘name Manager’:

This screen will appear, containing a list of ALL named ranges in the whole file:

By selecting the named range, the cell reference will appear here, and can be manually edited as required. Once this has been edited, click on the tick to save the changes, then close.

Of course if you insert or delete rows or columns within the range, then the named range will automatically update to accommodate this.

How to use a named range within a formula?

The ‘F3’ button on the keyboard will bring up a list of ‘named ranges’ within the file, and they can be selected. This saves the user needing to remember the exact names and spellings.

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Where are named ranges best used?

In the above examples, they have been used to represent an individual cell within a VAT calculation, but they also have many other uses:

1. Vlookup formula

Here we have a list of suppliers and the associated expense type they each relate to. This list has a named range called: Supplier_Type (this named range includes the cells D3:E11):

In the next sheet we can include a Vlookup formula to find the associated expense type for the selected supplier, by connecting to the named range:

2. Data validation

This list of expense types have been given the named range: Expense_Type:

This named range can then be used in the data validation to provide the source of data to be used in the pick list:

3. Pivot tables

Pivot tables can also be linked to named ranges, as opposed to the cell references. This is extremely useful if you have more than one pivot table, as it means you don’t need to manually amend the range for each pivot table, as it will automatically update as the named range is amended.

This is not only less risky (in case you forget to amend the range of a pivot table), but also quicker as it saves a manual job.

4. Other formula

Named ranges can be used within any formula, and as you can see from the above examples, they make formulae far easier to read, as they contain words (hopefully meaningful) instead of cell references. As a result they tend to make formulae appear neater, shorter and certainly less daunting.

I am pretty obsessed with named ranges and use them extensively, and I hope I may have convinced you to try them out too.

Read more Excel tips

Browse the full range of AAT study support resources here

AAT – CIMA- the path to management accounting

Posted by & filed under Career-boost.

If you are interested in the way businesses work and you want to become more deeply involved in strategy, planning and profitability, you could consider becoming a management accountant.

Many management accountants are chartered with CIMA, the Chartered Institute of Management Accountants.

Management accountants analyse information to help companies create a strategy and improve profitability and business performance. Anyone can study for the CIMA qualification, whether they are new to finance and business or an experienced professional.

If you already have an AAT qualification you will be exempt from some of the exams as your studies will have covered some of the topics. This means you can qualify more quickly.

Why become chartered with CIMA?

CIMA trains management accountants in skills that they can use to combine financial and non-financial data to paint a complete picture of the business. It is a globally recognised qualification and opens the door to a job in thousands of companies around the world, from multinationals to the newest start-ups in the public and private sectors.

According to CIMA, the qualification is designed to bring together management accounting, financial accounting and business, so you’ll have the practical skills employers need. That way, you’ll be qualified to work in a wide range of roles from finance to consultancy, IT to senior management positions.

It makes you very attractive to a potential employer, as you can work and study at the same time and employers like to see that you are developing your professional skills. CIMA qualified accountants can also command higher salaries and greater job opportunities.

What does the role involve?

Management accountants work within companies or as a consultant to help organisations take strategic decisions about the finances and direction of the business. They also need excellent people and leadership skills and the ability to communicate information about accounts and analysis to both financial and non-financial staff. At the top level, they talk to the board about the future strategy of the company.

While financial accounting focuses on preparing reports and financial information, management accountants are required to look at revenue, cash flow and liabilities and make forecasts and strategic recommendations. If you are fascinated by how companies work, what makes a successful business and why organisations fail, you will enjoy management accounting.

Don’t forget: If you go on to study for chartered accountancy as a full or fellow member, you can apply for a reduced annual subscription and receive a discount on your AAT membership fees while you study. As an AAT full or fellow member, you’ll also receive generous exemptions.

Key takeaways

  • Being AAT qualified is a fast-track route to beginning your studies to become a CIMA chartered accountant.
  • If you are interested in business strategy and planning you will enjoy the skills that the CIMA course offers.
  • By studying for the qualification you improve your career and pay prospects.
  • AAT qualifications will exempt you from some of the CIMA exams.
  • You can begin to study at any stage of your accountancy career.

Case study: Fiona Bevan

“I qualified with CIMA in 1993 because I was working at Siemens and the CIMA was very highly regarded there. If you are interested in working in industry and with businesses it is a very good qualification. It helps you analyse how well a company is doing in terms of profitability and my role was to help the Siemens business managers run their sections more effectively.

“The AAT qualification gives you a lot of flexibility and then you can go on and choose which path you wish to specialise in. I chose CIMA because I was working in industry and it has been brilliant to have a professional qualification which I have used all my working life. I took a career break to have children and with my CIMA qualification was able to go back and get a job later on.

I work with companies that are from £500,000 to £10 million turnover and help them to understand their own business at a deeper level. We work together on forecasting, budgeting and planning which is all CIMA related work. It is great to be able to add value for clients.”

Advantages of dual AAT-CIMA membership

If you go on to study towards chartered accountancy, there are important advantages to maintaining your AAT full or fellow member status.

  • While studying CIMA, you can use your internationally recognised designatory letters, which recognise MAATs as accountants.
  • You can apply for a reduced rate subscription on your AAT membership while you study.
  • Retain access to exclusive user-friendly AAT technical resources and support services.
  • If you hold MAAT status, dual membership also means you can continue to work towards gaining your FMAAT status, the mark of senior technical knowledge and skill.

In summary

There are real advantages in remaining with AAT or sustaining dual membership when you are studying for your CIMA qualification, including exemptions from examinations.

Further reading:

Study tips: Linear regression part 1 – High low technique

Posted by & filed under Professional Diploma, Students.

This series looks at linear regression for the Professional Diploma in Accounting qualification from AAT. We will start off with the high low technique, and follow up with a focus on regression in part 2.

Linear regression series

Within this article, we’ll be looking at linear regression within a time series analysis. We will look at the trend analysis part of time series analysis, specifically focusing on data that varies in a linear manner, and use the high low technique to make forecasts.

Time series analysis is a statistical technique used to:

  • examine how data has changed over a given time period
  • identify the underlying trend
  • and then predict future figures

based on the assumption that the data will behave in the future as it did in the past.

Businesses often use time series analysis to predict both future revenues and costs. The process involves calculating moving averages and the average change over the period, adjusting for seasonal variation and forecasting.

Combining cost behaviour with linear regression

I have written about the high low technique in previous articles and have also stated that at professional level, we are expected to adjust the basic technique to accommodate more complex situations. So far we have incorporated stepped fixed costs and bulk discounts. This article is slightly different and combines our knowledge and understanding of cost behaviour with that of linear regression.

Let’s imagine we are the management accountant for a company that manufactures reusable bamboo products. and the sales for coffee cups for the last six months have been:

It’s not hard to see that sales are increasing month on month by 500 units, and if we plot the figures on a graph we can see that they produce a straight line:

It’s logical to assume that the data will continue to behave in the future as it did in the past, so we can forecast that in January sales are likely to be 6,760 cups, increasing to 7,260 in February.

This is a very simple, and to be honest, unrealistic example. However, without getting too confused by mathematical theories, linear regression techniques are based on the assumption that the relationship between two variables can be represented by a straight line and can be calculated by the equation: y = a + bx.

We know February’s sales are likely to be 7,260 cups, but now let’s prove it. 

Proving the forecast

In the equation, y = a + bx:

  • ‘y’ represents the forecast, so is the answer we are calculating
  • ‘a’ is the first figure we have in the data set, i.e. 3,760 units. It’s the fixed point or element that the rest of the data changes in relation to
  • ‘b’ is the amount by which the data increases each month i.e. 500 units. It is the variable amount per unit that will change in proportion to the number of time periods
  • ‘x’ is the forecast time period, in this case February, which is seven months after the start of the period.

What we end up with is:

3,760 units + (500 units x 7 months) = 7,260 units

So how does the high low technique fit in? Well, normally it’s used to separate a total semi-variable cost into its variable and fixed elements. In effect, we are using our understanding of cost behaviour to find missing figures.

Free Excel webinar

Learn how to present effectively in Excel from expert Deborah Ashby. To view the recorded webinar please register your details below

View webinar

Using the high low technique

If you look at the calculation above again, it could be written as:

Fixed element + (variable cost per unit x number of periods) = total semi-variable cost

It is important to note that you need to allow for context when applying the equation. For example, ‘y’ is a forecast quantity in our scenario but it can be the forecast value of either revenue or total cost. Likewise, ‘x’ can denote a number of time periods or a number of units.

Let’s say the company’s delivery costs vary in a linear manner and can be expressed using the regression equation where ‘x’ equals the number of units delivered. From past data, we know that the cost of delivering 3,800 and 6,300 units is £18,680 and £27,680 respectively.

The information tells us that ‘x’ is the quantity and gives us two sets of figures from which we can use the high low technique to calculate ‘a’ and ‘b’ because we know that ‘a’ is the fixed element and ‘b’ the variable cost per unit. The details we know are:

The variable cost per unit or ‘b’ is calculated as the difference between the two costs divided by the difference between the two quantities: £9,000 ÷ 2,500 units = £3.60 per unit delivered. The total variable elements are then calculated by multiplying each quantity by the variable cost per unit, in other words ‘b’:

The fixed elements or ‘a’ are calculated by deducting the total variable elements from the total costs:

Now if we want to forecast the cost of delivering 7,260 units in February, we can apply the equation:

In summary

I hope you’ll agree that once you apply the high low technique to the linear regression formula, you can see that really we are still using the technique to make forecasts, and that this application is no more complicated than the adjustments we have made for stepped fixed costs or bulk discounts.

Watch our study support video on MDCL for more

Part two of our series has more of a regression focus, where we will apply our understanding of linear regression to a more complicated scenario, with incomplete information that does not vary in a linear manner.

Read more on the high-low technique here;

Browse the full range of AAT study support resources here

How can you boost your chances of getting the role you really want?

Posted by & filed under Career-boost.

You’ve got AAT qualifications. And you’ve plenty of relevant work experience. So what can you to do boost your chances of getting the role you really want?

It’s time to examine what transferable skills you have – and which ones employers really want.

What are transferable skills?

These are the skills which aren’t job specific, but are ones that are desired by employers for many jobs and industries such as leadership or communication skills. You might have picked up transferable skills at home or through other activities or experiences as well as through work.

Key takeaway: Transferable skills can be picked up at work and outside.

Why are they useful?

Transferable skills are useful when you apply for a new position which you might not be an obvious fit for or one in a new industry. Lee Owen, Director at Hays Accountancy & Finance says: “They enable you to move smoothly across different roles and sectors allowing you to develop a breadth of experience throughout the career.

Today’s professionals can expect to have a number of different careers as the retirement age grows higher, so having this flexibility to explore different career paths will hold you in good stead throughout your working life”.

Key takeaway: Transferable skills will allow you to move between industries, sectors and jobs easier

What they don’t replace

Transferable skills don’t replace qualifications and relevant experience. Technical computer skills will also be essential for many such roles. Bradley Glen, Operating Director of recruitment consultants Page Personnel said: “You will find that some skills are specific to the company concerned or the role. However, other skills can be used in every role and such transferable skills will develop throughout your career”.

Key takeaway: All the transferable skills in the world won’t replace good qualifications and work experience.

Accountancy and bookkeeping special skills

What are the transferable skills employers want for bookkeepers and accountants? Owen says: “A natural aptitude for numeracy and accuracy is crucial particularly for those working closely with business. Also modern day accountants with a strong appreciation for IT and business intelligence tools are in demand in order to present financial information quickly and in an easy to use format”.

What are your transferable skills?

“Identify what your transferable skills are and how you can improve them” says Glen. “Doing so will improve your chances of climbing the career ladder”.  Think about what your current job entails. For example, do you oversee other colleagues?

If so, you have training skills. Or maybe you’re in charge of forward planning. You’re an organiser: a useful skill regardless of the industry sector you want to work in. The ability to work as part of a team is a desired transferable skill for many roles

Communication

“Strong communication, effective organisation and a willingness to learn are transferable skills accountancy candidates ought to be equipped with” says Lee Owen, Director at Hays Accountancy & Finance.  And you might have gleaned communication skills from outside work activities: if you run a club then you’re probably a great communicator.

Prove it

There’s no point in just saying in an interview or application that you have such and such transferable skill. You need to back it up with real examples. For example, point to when your negotiating skills worked in moving a project forward.  

Born leader

Key takeaway: You need evidence of your transferable skills: just saying you have such and such a skill is not enough.

Leadership skills will be desired for many roles. Glen says: “An effective leader takes responsibility and also knows which tasks to delegate. You don’t have to be in a senior position to have leadership responsibilities – volunteer to manage projects and others even early in your career and that will impress interviewers”.  

What’s your problem?

Think about the difficult situations you’ve faced in your current work: how did you solve it? Employers will want to know about problem solving skills. Again, make sure you have the evidence (rehearse in advance if you’re being interviewed) and point to it. 

Reading between the lines

How do you decode job specifications to find what transferable skills are desired? Some are obvious – a “flexible outlook” means you should demonstrate times in your current or past jobs where you’ve gone beyond the job description or normal office hours. Owen adds:

“Look out for phrases like ‘organised’, ‘attentive to detail’ and ‘able to work in a team’. This is where you can draw on your transferable and soft skills to demonstrate that you are a strong candidate for the role”.

Be relevant

Make sure that when you apply for a job you refer first to the transferable skills relevant for that post. And, says Owen, “Rather than just listing your transferable skills, explain how you built each skill and how you use it in your current role or study”.

Key takeaway: Read the job spec carefully: it will contain lots of clues as to what transferable skills the employer is looking for. Emphasise the most relevant skills for the job description.

In summary

You have transferable skills: just find what they are. Analyse what you do in a typical workday and you’ll detect some. Don’t forget skills gleaned from outside work activities.

Do remember that just saying you have a skill isn’t enough: think of practical examples where you’ve demonstrated such a skill. Don’t forget qualifications and work experience: they are the most important things to would-be employers. 

Further reading:

Essential maths skills for success in your accounting qualification

Posted by & filed under Study tips.

I was teaching a class recently at First Intuition and I asked my students to calculate the total cost of 10 units, with a purchase price of £5 each.

I was surprised to see half of the class pick up their calculators!

As budding accountants who will be spending their careers recording, analysing and communicating financial information, they need to be comfortable with figures, numbers and basic calculations. It’s time to go back to basics.

Here are some of the essential skills you’ll need to be successful in your assessments and careers.

Calculating averages

When businesses are dealing with large volumes of production involving large amounts of resource and large costs, it’s useful to be able to break these down into averages. This allows us to consider things on a smaller scale.

For example, if we produced 100,000 units using 200,000 kg of material then the average amount of material per unit would be 200,000 kg/100,000 units = 2 kg per unit. This can be useful for:

  • Helping to plan resource requirements. If we’re going to produce 2,000 units next week then we need to purchase around 4,000 kg of materials.
  • Identifying unit production costs. If materials costs £5 per kg then the total material cost per unit would be £10.
  • Setting prices. Once we have worked out the average costs for making a unit we can add a margin to set a profitable price.

The main thing to be careful about when calculating averages is that you divide the two figures ‘the right way around’. A common mistake is that students divide them ‘the wrong way around’ i.e. they work out 100,000/200,000 = 0.5 kg per unit rather than the correct 2 kg per unit.

Have a go at these examples:

  • Gohere Railways provide meals for their passengers. They made a total of 1,000,000 meals using a total quantity of 250,000 kg of ingredients. What is the average usage of materials per meal?
  • Runup Ltd spent £210,000 on 700,000 kg of materials. What is the average cost per kg of their purchases?
  • Walkdown Ltd made 200,000 units in 40,000 labour hours. What was the average time taken to make each unit?
  • Strollaway plc paid staff £428,750 for 35,000 hours of work. What was the average wage rate per hour?

Percentage changes

Percentages are a useful way to look at how figures are changing, perhaps over time.

If I tell you that the price of a car has changed from £13,500 in year 1 to £14,850 in year 2, then we can see that the price has risen between the two years, but it’s not immediately clear by how much.

Using percentages (‘per cent’ means ‘parts of a hundred’) can make this much clearer.

To work out the increase as a percentage, we can first work out the increase in pounds. £14,850 – £13,500 = £1,350.

We normally work out percentage changes based on the starting figure, so in this case we would work it out as £1,350/£13,500 x 100 = 10%.

Another way to work this out is to calculate £14,850/ £13,500, which gives 1.1. This is ‘1 + the percentage increase as a decimal’ or ‘1 + 0.1’ or ‘1 + 10%’. As ever, make sure you divide the two figures ‘the right way around’ with the starting figure at the bottom.

Have a go at these examples:

  • A cost has increased from £80 to £100. What is the percentage increase in the cost?
  • A cost had fallen from £150 to £120. What is the percentage decrease in the cost?
  • A price has risen from £18 to £20. What is the percentage increase in the price?
  • The interest rate on our bank loan is increased from 10% to 13%. What is the percentage increase in the interest rate?

Using margin and mark up information

The relationship between costs and prices is a critical one in any business as it determines the profit (or loss) that is made.

  • Margin is short for ‘margin on sales’ so the profit element is shown as a percentage of the sales value
  • Mark-up is short for ‘mark-up on cost’ so the profit element is shown as a percentage of the cost

Here are a few examples:

  • If the sales price is set at £190 and the company has a target profit margin of 40% what is the maximum target cost that they can afford to incur?
  • If a company has a target profit margin of 25% and spends £150 producing a unit what must the selling price be set at?
  • If the sales price is set at £190 and the company uses a mark-up of 40% what is the production cost of the product?
  • If a company spends £150 producing a unit and sets the price to give a mark-up of 25% what will the selling price be?

Read more on some basic accounting skills here;

To access your eLearning tools click the image below and login

e-learning-banner-1_aat-comment_728x90px

Study tips: Advanced aspects of appropriation accounts and effective communication

Posted by & filed under Advanced Diploma, Students, Study tips.

The third article of our series on some of the trickiest areas at Advanced level.

Study Tips: Advanced Level series

We’re working through a business scenario to illustrate how the AAT Advanced Diploma in Accounting can be applied, in practice, to the typical day-to-day tasks of a part-qualified Accounting Technician.

In part two Lewis began producing Gorgeous Threads & Co’s final accounts.  He corrected the extended trial balance (ETB), which had been started by a colleague, and calculated the profit for the year figure. Now he needs to produce an appropriation account to show how that net profit is shared between the organisation’s two partners, in accordance with their partnership agreement.

Producing an appropriation account

As with Lewis’s previous tasks, he knows that in order to accurately prepare an appropriation account, he’ll have to think through the manual accounting theory, and use his knowledge to help him decide which spreadsheet functions, formula and formatting to use to achieve the right results.

Gorgeous Threads have precise instructions for preparing financial information using spreadsheets, which they expect the accounts team to follow. This includes using separate worksheets for subcalculations and details, proformas for consistency, writing formulae, linking sheets and using relative and absolute cell referenceing.

This is what Lewis finds in the spreadsheet:

Click Advanced Synoptic Series – Part 3 Spreadsheet to download the file and replicate Lewis’s tasks.

Lewis starts by pulling through the net profit from the ETB:

  1. C2 =
  2. Click on ETB tab and cell I31 (which is the net profit in the credit column of the SFP)
  3. Return

Next he calculates Chris’s salary:

  1. B4 =
  2. Click on Workings tab and cell B17
  3. Type *12 (to calculate the annual figure)
  4. Return
  5. As Mel’s salary is directly under Chris’s in both worksheets, Lewis can use relative cell referencing                (discussed in part 1) to drag the formula from B4 to B5

Interest on capital is linked as well but this time to both the ETB and Workings worksheets.  Chris’s will be:

  1. B7 =
  2. Click on ETB tab and cell I8 (which is Chris’s capital balance in the credit column of the SFP – be mindful not to link to the TB columns in case there are any adjustments)
  3. Type *
  4. Click on Workings tab and cell B20
  5. Return

As interest on capital is 3% for both partners its cell reference can be made absolute* in formulae, enabling Lewis to drag the formula from B7 to B8 to calculate Mel’s interest:

  1. Click back on B7
  2. In the formula bar, place the curser between B and 20 then press F4 on your keyboard. This is the shortcut to insert $ signs both before the letter and in-between the letter and number thereby absoluting the cell with that reference.
  3. Return
  4. Drag B7 to B8

Please note, calculating interest on capital has been included here as a spreadsheet skill rather than an accounting one.

Calculating the subtotals

Next Lewis needs to calculate all the subtotals:

Lewis is going to use the subtotals to calculate the how much profit is left for distrubution. However, he needs to think about the accounting theory first:

  • Partnership agreements set out rules for sharing profits and losses
  • Partners claim both salaries and interest on capital so they are deducted from the profit before what’s left is shared in the agreed ratios
  • Partners are charged interest on drawings so it is added back to the profit before it is shared

Therefore, he write the following formula:

Lewis calculates that the residual available for distribution is actually a loss of £1,810. Again he thinks about the theory before he continues:

  • The terms of the agreement must be followed even if the has made a loss or that as a result of the agreement, a profit is turned into a loss
  • Losses are shared between partners in the same way as profits

He has applied the first point and now shares the loss by linking to the profit sharing ratios on the Working sheet and absoluting the loss figure in C13 on the Appropriation Account worksheet:

He also calculated the final subtotal and checked that it matched the -£1,810, reformatted columns B and C to remove the inconsistencies and amended the headings.

Explaining the results

Lewis is sure his work is correct but is unsettled by the loss. He therefore talks to his manager who asks him to write short but clear notes which firstly explain appropriation accounts, then analyse Gorgeous Thread’s appropriation account and finally identify the result.

This is what he writes:

In summary

Lewis’s manager is impressed with his notes, commenting that they are comprehensive but concise and will effectively communicate the points to Chris and Mel as neither of them have financial backgrounds.

As Lewis has shown an aptitude for writing his manager has asked him to prepare some further explanations, for a new apprentice who will be joining the team, on the interrelationship between financial and management accounting, which we will look at in part four.

* This means that when the formula is dragged from one cell to another, the cell with the absolute referencing will stay the same, in other words it’s the opposite of relative cell referencing.

Read the next article in the series: Advanced aspects of short-term decision making.

 

Browse the full range of AAT study support resources here

Study tips : Valuation of raw materials – part 2

Posted by & filed under Foundation Certificate, Students.

In this three part series we’re going to review valuation of raw materials for the AAT Foundation Certificate. 

Study Tips: Valuation of raw materials series

In part one of this series on inventory valuation methods, we reviewed why and how raw materials are processed and controlled as part of the manufacturing process.  

We also looked at the characteristics of the three common methods available. Having covered the theory, we’re now going to put it into practice and calculate the cost of issues and the value of closing inventory using FIFO, LIFO and AVCO.

Let’s return to the inventory record for raw material SSt14, which you may remember are sheets of stainless steel, and assume the records show that the 5,000 in stores were all bought at the same time:

Now let’s update the record with details from a purchase order:

At this point it doesn’t matter which inventory valuation method is being used as the details of receipts are entered in the same way for all three methods.

Note that the cost per unit is shown to three decimal places.   Also note that if you needed to, you could work out any of the three figures as long as you know two of them.   In other words, the total cost is the quantity multiplied by the cost per unit, but that can be reversed to calculate the cost per unit, by dividing the total cost by the quantity or to calculate the quantity, by dividing the total cost by the cost per unit.

Now we’ve updated the record for a receipt let’s look at how to deal with an issue of 500 sheets to production.  

FIFO

Under FIFO we value the issue based on the price paid for the oldest inventory.  In this case that’s the 5,000 sheets that are valued at £112,500. So, we start by calculating the cost per unit (£112,500 ÷ 5,000 = £22.50) then multiply the cost by unit by the quantity to calculate the total cost of the issue:

The balance of inventory remaining is valued at the most recent price paid. It is updated by deducting the quantity of the issue from the latest balance (10,000 – 500 = 9,500) and the value of the issue from the latest total cost (£231,275 – £11,250 = £220,025).  

These figures can be double checked as they should equal the quantity and value left from each purchase:

LIFO

When we’re using LIFO though, we value issues to production at the most recent price paid.  

This time it’s the 5,000 sheets that were purchased on 7th April.  Therefore we can enter the quantity and cost per unit then multiply them to calculate the total cost of the issue:

The balance of inventory remaining is valued at the price paid for the oldest inventory but is updated by the same method as FIFO.  

Again the figures can be double checked as they should equal the quantity and value left from each purchase, only this time we are working in reverse date order or from the bottom up:

AVCO

Finally, if we work the issue again using AVCO, we value issues to production at the average cost of all the inventory held at the time of the issue.  

That means calculating the average cost per unit as at 7th April (£231,275 ÷ 10,000 = £23.128) rounded to three decimal places** and multiplying by the quantity to calculate the value of the issue on 10th April:

The balance of inventory remaining is an average figure but should always be calculated by deducting the quantity and value of the issue from the previous balance as per FIFO and LIFO.  

This is because if the cost per unit has been rounded discrepancies can occur. Our case is a good example because the balance of 9,500 would be valued at £219,716 if the cost per unit of the issue (£23.128) was used in the calculation and that would be incorrect.

Changing your valuation method

In part one, we said that organisations can choose either of the three methods for cost accounting purposes. Let’s say our toaster manufacturer uses AVCO currently but is thinking about changing to one of the others.

Here’s the inventory record for some plastic trim which is another raw material it uses.  The cost per metre is shown to three decimal places but the total cost rounded to whole numbers:

In order to work out what the cost of the issue and the closing inventory balance would be if FIFO or LIFO had be used instead, we need to apply our knowledge and understanding of all three inventory valuation methods. Firstly, we know that the quantities must remain the same as the original AVCO record.

We also know, due to the assumptions each methods makes about the order issues are made in, that each method will give a different result.  If prices have increased, as they usually do, the FIFO issue value is going to be lowest of the three and the LIFO issue the highest.

This is useful to know as it enables us to check that our figures fit our expectations. Using the same theory, it is therefore logical that we should expect the lowest closing value from LIFO and the highest from FIFO.

So what are they really?

Using FIFO the cost of the issue would be £725 and the closing inventory value £186:


Using LIFO the cost of the issue would be £733 and the closing inventory value £178:

In the final part of this series we’ll have a look at how the valuation of raw materials fits into the bigger picture of cost accounting and is used to calculate the total costs of products and their cost per unit.

* Note that the rounding of 4,500 units @ £23.755 per unit has led to a £1 discrepancy between the record, which is correct, and the check, which is acceptable as we know the reason for the difference.

** The need to round figures is common with AVCO so care should be taken to comply with any policies over the number of decimal places required.

Read part 3 of this series next.

Browse the full range of AAT study support resources here





The MTD ‘grace’ period is over – so what now?

Posted by & filed under Making Tax Digital.

The ‘soft landing’ introduction of MTD for VAT comes to an end this year, so what happens next?

For the great majority of businesses the soft-landing for MTDfV will end after 31March this year. A relatively small number with more complex affairs will have an extra 6 months, up until 30 September 2020.

After this date, businesses required to account for VAT must have a digital link between the data in their record keeping systems and the software that submits the VAT Return.

HMRC’s goal is end-to-end dynamic data, removing the possibility of human error that exists in cut and paste.

Digital links

The end of the grace period does not mean that businesses need to move all of their financial records out of spreadsheets and into cloud accounting software.

HMRC recognises spreadsheets as a component of ‘functional compatible software’, which is necessary to file returns through MTD. But a spreadsheet isn’t enough on its own.

A business will require a ‘digital link’ in order to receive the return. HMRC classifies a digital link as an exchange of data between two systems within a business, or the transfer of data to an agent, such as accountant. HMRC has listed some common examples of what they classify as digital links:

  • emailing a spreadsheet containing digital records so the information can be imported into another software product,
  • transferring a set of digital records onto a portable device (such as a pen drive, memory stick, flash drive) and physically giving it to someone who imports that data into their software,
  • XML, CSV import and export, and download and upload of files,
  • automated data transfer,
  • API transfer.

Grace period extensions

Businesses with complex legacy systems can apply for an extension of the grace period to get the right software in place.

Likewise, where a business has acquired another company, it can also apply for an extension.

These applications will be reviewed by HMRC and if the business qualifies, additional time will be granted as a specific direction.

HMRC has stipulated that cost alone is not enough to win an extension. It is also expected that businesses would have applied for an extension at the earliest possible date, with a complete map of their existing VAT systems, a plan and timetable for compliance, and an outline of controls to avoid errors in manually transferred data.

Read more about MTD

Can you rely on HMRC’s improved CEST tool for IR35?

Posted by & filed under IR35, Tax.

The new IR35 regulations will come into effect in the private sector in April this year. HMRC promised to improve its online CEST tool to ease the pain. Does it work?

The HMRC Check Employment Status for Tax (CEST) tool was a great idea on paper. It should have given employers a simple way to review their contracts to determine whether they sat inside or outside IR35. Nice and simple.

That’s not quite how it worked in reality.

IR35: are you ready?

Preparing for IR35 in the private sector is a practical half-day AAT course in February to help accountants prepare for IR35 in the private sector.

Book now

Legal controversies over CEST results

CEST has developed a reputation for being deeply flawed. Determinations made by the tool have already been defeated in tribunals – Mr Elbourn v the Met Office and Qualserve Consulting is one such case.

Worse still is that HMRC has labelled the tool as ‘irrelevent’ in another case, RALC Consulting v HMRC. The Revenue is pushing to dismiss a CEST assessment, which determined that IR35 should not apply, as evidence.

“They’ve made some big howlers along the way, and I just don’t know if they’re going to be able to resuscitate the reputation of CEST or not,” says James Poyser, CEO of contractor-focused accounting practice InniAccounts. “If the contract comes out as being outside IR35 [in CEST], then fine. But you’ve got no confidence that HMRC won’t come knocking anyway.”

A lot of contractors are not accepting IR35 determinations from CEST, Poyser explains, because the tool’s reputation is now so tarnished. Third party tools are available, which have proved to give more reliable results than CEST does.

InniAccounts have set up a secondary website, offpayroll.org.uk, as a resource for contractors to share their experiences with various employers. It has highlighted the common issues arising as employers try to re-assess their contracts, and which tools available are the most reliable. It’s clear that CEST has caused confusion and consternation, and that contractors don’t trust it. “Recruiters and clients who are using CEST are going to find that its reputation makes it quite difficult to attract contractors and consultants who are genuinely working outside of IR35.” 

Revised CEST tool

Against this backdrop of unhappiness, HMRC produced an updated and supposedly improved version of CEST at the end of last year.

So how does it fare?

Mutuality of Obligation

The biggest problem with CEST is its algorithm – specifically, what it ignores. HMRC has not been forthcoming in sharing information on how the CEST tool actually works, but it has been upfront about one thing: it does not take Mutuality of Obligation (MOO) into account in its assessments.

In a nutshell, MOO, refers to the obligation of the employer to provide work and the employee to accept it as part of the employment contract. HMRC has assumed that it is present in all contractor engagements, which is why it isn’t taken into account within the CEST algorithm. In reality, MOO isn’t always present in contracts, and it has been a significant factor in numerous tribunal cases.

“Here is something that been shown time and time again to be a clear indicator of working outside IR35, and it’s been completely neglected by CEST, which is why it isn’t fit for purpose,” says Poyser. “HMRC has not managed to overcome a contractor’s defence at tribunal because of MOO.”

By ignoring case law completely, HMRC has left a gaping hole in CEST’s calculations. The best rated tools and services out on the market – Qdos Contractor, IR35 Shield and Kingsbridge Insurance are all based on case law in addition to the regulations. “It’s not a flaw with the technology-based approach, it’s a flaw with HMRC’s product,” says Poyser. “It’s not comprehensive enough to answer what is quite a tricky question.”

Who uses the tool?

The other big issue with CEST is more of a people problem. Untrained, ill-informed line managers are using the CEST tool without really understanding IR35 or even the contracts being issued to their contractors, and getting erroneous results.

Often, they’re testing one contract and applying it to all contractors and consultants, rather than taking the time to properly assess each contract.

“Contractors are pulling their hair out because the line manager is pushing these buttons in CEST without actually understanding the contractual relationship between the client and the contractor and the working practices. It’s a complex thing, so to put it in the hands of untrained line managers and expect them to deliver a fair outcome is madness.”

Under IR35, a client must take “reasonable care” in assessing a contract. They then have to issue the contractor with a Status Determination Statement (SDS), which the contractor can challenge. “Any contractor worth their salt that’s given an SDS that’s come from CEST, would go to one of the third party assessors, get them to assess the situation, and use that to challenge the client’s determination.”

Accountants advising employers

Accountants advising organisations that hire contractors want to avoid getting too bogged down in the ins and outs of determining what sits under IR35 and what doesn’t – it’s a legal issue, not an accounting issue.

The best approach is to partner with a third-party specialist in IR35 determinations. Qdos and Kingsbridge both have affiliate programmes that accountants can join.

“They can help accountants to educate their clients and their services aren’t that costly. You don’t necessarily have to deliver the solution – you can mediate and introduce your clients to the right people.”

Accountants advising contractors

Accountants should be educating their contractor and consulting clients about IR35 and encouraging them to be proactive with their clients in asking for new IR35 determinations. One of Poyser’s clients, an engineering contractor, spoke to his clients about it, only to find that they were completely oblivious to the changes. With Poyser’s client’s help, that client has now adjusted its practices and is known as an IR35-friendly employer.

“He pushed forward the case that if they got it right, they would have the pick of the very best contractors and consultants – they love firms that can make fair IR35 assessments. Particularly if you’re working in a small firm, it’s a big opportunity for accountants’ clients to really shine by helping their end clients to get this right.”

Alternative tools for assessing status

Qdos Contractor: Qdos has a free IR35 calculator on its site to help determine whether a contract sits within IR35 or not, among other resources.

Kingsbridge Contractor Insurance: Kingsbridge has an IR35 hub with information for contractors, recruiters and employers.

Further reading