Excel 2016: our top 10 features Posted 08/02/2016 by Alan Gurney & filed under Excel tips. Excel 2016 was released earlier this year and we thought it’s only right to give you the lowdown on our favourite new features in Microsoft’s latest version of the popular spreadsheet programme. 1. Tell me what you want to do? The “tell me” box is one of the most obvious new arrivals to the Excel party and quite frankly it’s a shame it wasn’t around in the 2007 edition when the menu ribbon was initially reduced (remember how hard it was to find where Microsoft had hidden all of the tools?). The tell me box is essentially a search function that lists functions and operations matching your search terms. A really handy shortcut to get you where you want to go! On top of this there’s smart look-up which will use the internet to search relevant articles on your search term. 2. Forecasting Excel 2016 has seen the addition of ‘one-click’ statistical forecasting for data that has a time element associated with it. As long as you have a decent amount of data it will be able to forecast past your last data point into the future as well as showing levels of confidence at each end of the spectrum. What’s also great about the forecasting function is that, if Excel can see trends due to seasonality, it will factor these into the forecast. Pretty smart! Once you’ve made your forecast you’ll be able to display it as either a line or bar graph, but be aware that the data used to create your graph of choice will not link to the chart, meaning that if you make changes to the data these will not automatically be reflected in the chart. 3. Search field (PivotTables) PivotTables have been revitalised with a search bar which has made life much easier when dealing with large data sets with numerous different fields. Just search for the field you’re looking for and up it pops for you to select. 4. Date Grouping (PivotTables) The last versions of Excel PivotTables would always register dates individually meaning that your charts would have hundreds of fields along the date axis. Now dates are automatically grouped into Years, Quarters and Months. If you want to delve deeper you simply use the + option next to the date segment to expand the next level of grouping. New charts The people at Microsoft claim that Excel has not seen the addition of a new chart type since 1997. To make up for lost time, 2016 sees the unveiling of six new chart types, each with their own specialist uses, so here’s a run-down of our favourites: 5. Histograms and Pareto charts Histograms show you frequencies, for example how much of one product has sold against another. They’re not based on categories but on values which are allocated into different bins, though if you want to alter this to show categories you can do that too. Pareto charts go one step further by sorting these frequencies and adding a cumulative percentage line to give you a trend through the data. 6. Sunburst charts These show values by hierarchy. A good use for sunburst charts would be to analyse sales of a company and break it down by salesperson, customers and products purchased. The chart will plot the sales people at the highest level and size their sections by the sales they have made. The next level will be the customers each sales person has and the total amount they have spent with their respective sales person. The final level would show the products bought by each customer. Sunburst charts allow you to go to dozens of levels so you can really drill down into your data. 7. Waterfall charts Waterfall charts are great for showing movement from an opening position to a closing position and are therefore ideal for plotting financial data such as cash flow over a period of time. They show your opening balance and the progression through the various stages of trading you may have and then stop at your closing balance. Useful for business of all sizes. 8. 3D data map Perhaps the most impressive looking of all the new visualisations in Excel 2016 is the 3D data map that is now a fully integrated option. This chart is perfect for analysing data with a global span, for example a company sales report where the company operates with a global client base. Obviously you need locational data to use this type of chart and you can then add other fields to build your picture. The chart will give you a map of the world with bars or columns in the locations that have fields attached to them. What’s more, if you set this against a particular period of time, the chart will allow you to record a simulation video showing the change in values over that period. Great for presenting sales growth to the board or your team. 9. PowerPivot PowerPivot is still technically an add-in, and not new to Excel, but it’s a great tool to use to bring data into Excel. In fact it allows you to import much greater levels of data (we’re talking hundreds of millions of fields here). PowerPivot even has its own function language, Data Analysis Expression or DAX, and this is where the new features lie, so if you feel like you’ve exhausted all of Excel’s functions have a go with PowerPivots. 10. Get & Transform Another permanent fixture that started life as the Power Query add-in, Get & Transform helps you import data from various data sources. There are the standard options of importing from a Comma Separated Values (CSV) file or an Excel workbook but now you can also choose options such as Facebook, Salesforce and other programmes and packages. Once you’ve imported your data it will give you the chance to tidy the data into a useable format. You can also apply the Get & Transform tools to data already in Excel. So that’s our favourite new features for Excel 2016, there’s some pretty cool new additions and now we just need the chance to use them properly. Let us know if you agree or if there are any features that you think should’ve made the list.
Meet the experts – tax specialist Michael Steed Posted 08/01/2016 by Nick Huber & filed under Video and podcast. Michael Steed worked in oil exploration before switching to accountancy. The former president of the Association of Taxation Technicians talks about tax controversy, career switches and office romance. In business the phrase “burning platform” describes a company in crisis that needs to take urgent action. Michael Steed, a former president at the Association of Taxation Technicians, has experienced a real one. Before he worked in accountancy he worked in oil exploration. He worked on ships in the North Sea doing geo-physical surveys and on rigs. Jobs could take a couple of months. He worked on Piper Alpha shortly before the oil platform in the North Sea exploded in 1988, killing 167 men. Once, when Steed was drilling for oil in a Yorkshire moor the team hit a gas pocket deep in the ground. “It was quite unexpected,” he says. There wasn’t enough mud in the hole. It “blew out”, caught fire and destroyed the rig in about three hours. “So that was quite fun,” he says drily. He was unhurt. Career switch In the second half of the eighties when the oil price fell sharply Steed, who studied economics at university, switched career to accountancy. An acquaintance who worked in tax told him it was “quite intellectual”. Steed began his training with what was then Coopers & Lybrand (now PwC). He advised the oil industry. He travelled regularly, working in cities including London, Hong Kong, Dallas and Vancouver. “I worked up [oil] prospects, results from geo-physical surveys, and turned them into prospects, selling them to the board, basically [saying] ‘Do you want to spend £3m on drilling this well?’” Steed met his wife at Coopers. Business culture was more conservative then. “I got into terrible trouble [with the firm],” he says. “I was carpeted and up before a partners’ meeting.” It didn’t harm his career, though. Steed completed AAT, ATT and the CTA (chartered tax adviser) qualifications. In 1995, he was awarded a Chartered Institute of Taxation Fellowship in 1995 for a thesis on VAT groups. Taxing tasks “Taxation is very analytical,” he says. “It’s rather like looking at a Rubik’s cube. Part of a job of tax is to put all these little shards together to make a coherent story.” Since the financial crisis of 2008 tax (how much rich people and large companies are paying or avoiding) has become political and highly controversial. Governments are passing tougher laws against tax evasion and tax avoidance. “Tax has a moral element now that it never had when I started in the trade in the late eighties,” Steed says. “And of course, there is some very strong stuff coming in about anti avoidance. HMRC has been in the ascendant over the last five years.” What motivates him in work? “I don’t get up in the morning thinking about money. I get up thinking about doing another 15-hour day solving tax problems.” Career advice Steed is now a consultant for Kaplan, an education and training company. He works for Kaplan’s “leadership and professional development team”, advising clients on subjects including how Britain’s exit from the European Union may affect companies’ VAT bills. Most VAT and excise duties are directive driven [from the European Commission] and the member states have to stamp them into their own legal canons, Steed says. What’s his advice for a trainee accountant at the AAT? First, get good training − ideally with a large firm or business then move to a smaller one after you’ve gained experience. Most important, though, is to understand tax. Most clients have two main questions, he says: ‘much tax have I got to pay?’ and ‘how can I reduce it?’. Accountants tell Steed that their biggest challenge is coping with rapid changes in regulations, international accounting standards and tax law. Technology is also changing how accountants work. “Who’d have thought for example that 20 years ago we’d be storing all our information on a cloud?” HM Revenue & Custom is using technology to modernise the tax system, including replacing annual tax returns with digital tax accounts. Accountants will probably need to change how they work in response to the digitisation of tax. Financial paperwork for company year ends may have to be done much quicker. Tax advice may be given more often, rather than annually or quarterly. Downtime Steed is a busy man. How does he relax? “The short answer is I’ve long forgotten how to relax.” Leisure time includes working on his farm in Kent. “We’ve got cherries, plums, apples, Kentish cobnuts. We also have some sheep. I quite enjoy the hustle and bustle and going up to the farm, I suppose in a sense, it’s the real me.”
What tax will look like post referendum Posted 08/01/2016 by Brian Palmer & filed under News. My last published commentary before the 23rd of June ended: “If we vote ‘leave’ then it is highly likely that we would face a period of market turmoil …tax laws would remain in place until such time as the Government can plan for change. I am confident that the world will not stop spinning on its axis. We would just enter a new phase.” And a new phase is certainly what we have…. post referendum we have a new Prime Minister, Chancellor and a wholesale change of Ministers. The new Government is creating time to plan for an orderly change as we move gently away from the language of austerity. The following overview takes a look at what that change might bring: Autumn Statement While spectacularly over confident in my earlier accountingweb prediction that an emergency budget was sure to follow a leave vote, I am absolutely convinced that this year’s Autumn Statement will be a budget by any other name. Sovereignty For the time being Britain remains a full member of the EU and it is without doubt to our benefit that the UK remains in charge of the timing of our exit. The impact of which is that we have least a two-year window to negotiate the terms of our exit from the time that Article 50 is invoked. If the UK does not join either the European Economic Area or the European Free Trade Association it will have complete control over the setting of taxes within the framework of existing double tax treaties. This would mean that government would no longer be required to seek European approval in respect of state aid or tax incentives, such as enhanced capital allowances or follow EU directives. Multinationals I am currently getting a mixed message from Multinationals. Non-EU companies who have previously seen the UK a staging post, from which, to trade with the EU are threatening to relocate to mainland Europe (for example in financial services). While others, including GSK, have announced inward investment in recent works. It is too early to be sure over the government’s position regarding Base Erosion Profit Shifting, up until now it has been an early-adopter. Nevertheless, there has is speculation that post Brexit this might no longer be the case. Income tax, Capital Gains Tax, SDLT and Inheritance Tax The impact of Brexit on direct taxes is unlikely to be significant. Unless existing legislation has been adapted to avoid discriminating against EU nationals, falling foul of EU competition law and complying with the fundamental freedoms enshrined in EU law each is head-of-duty based on domestic legislation. Corporation tax As with the above direct taxes, Corporation tax (CT) is underpinned by domestic legislation, as a result there is not likely to be fundamental change. If George Osborne had remained Chancellor the headline rate of CT was set to drop as low as 15%. However, with the appointment of a new Chancellor and the mooted move away from austerity it is too early to predict if the Phillip Hammond will make good his predecessor’s commitment. VAT VAT might be based on European law, however, given that the UK collects in excess of 115bn relatively easily, it is unlikely that our government will sweep it away or implement radical changes to existing VAT legislation. The UK would, however, be free to extend the scope of zero rating and exemption without the fear of a referral to Court of Justice of the European Union (CJEU). In addition, there would be an end to the requirement to levy VAT at a minimum of 15%. The UK would be free to impose different rates of VAT on different types of goods and services and to widen the categories of goods and services benefiting from zero rating and exemption. At this stage it is unclear what would happen to any CJEU referrals that might be in the pipeline once UK leaves the EU. From the date of “exit the recently introduced Mini-one-stop-shop (MOSS) will cease to apply. Non-EU businesses currently registered in the UK for Moss-covered supplies made across the EU will need to register anew in another EU country, as will similar UK businesses. Unless, they wish to register for VAT in every EU country to which they sell. The UK will be free to introduce a new registration regime for Moss-covered services supplied to UK consumers from outside. The reports of my death… While undoubtedly there will be many changes to UK tax legislation in the wake of the outcome of the 23rd of June referendum vote. Ultimately in the tax context and particularly with VAT in mind I am left thinking of the Mark Twain quote “The reports of my death were greatly exaggerated.” After all, as Benjamin Franklin once wrote: “In this world nothing can be said to be certain, except death and taxes.”
Rio 2016 Olympic Games Posted 08/01/2016 by Mark Rowland & filed under News. Like the 2014 FIFA World Cup before it, will stupendously expensive white elephants be Rio 2016’s legacy? On the eve of the 2016 Olympics, protests have flared up again in Rio. When it comes to sporting events in Brazil, this is becoming a habit. The Olympic construction project has been plagued by delays, prompting the International Olympic Committee (IOC) to step in. “We have become very concerned,” said IOC vice-president John Coates after an inspection in 2014. The Olympics is now on track to be delivered, though still not without controversy – namely around polluted water. For both international commentators and the Brazilian public, it’s hard to shake the feeling of history repeating itself. Own goals On Thursday 20 June 2013, the streets of Brazil filled with more than a million protesters, spread across 100 cities. The Brazilian people were angry at everything: the state of public services, police brutality and the amount of taxpayers’ money that had been spent on the upcoming FIFA World Cup. Public feeling about the tournament had hit rock bottom. The cost of building the 12 stadiums required to hold the event had spiralled out of control. Promised infrastructure projects had been delayed. To make matters worse, unscrupulous landlords near the stadiums had raised their rents to the extent that people could no longer afford to live there, forcing them to live in makeshift slums nearby. “We were hoodwinked,” said Amir Somoggi, a finance and marketing consultant who works with some of Brazil’s biggest football clubs. “They said the World Cup would help Brazil take a huge leap forwards in terms of public transport, but all they did was the stadiums.” The 12 arenas ended up costing 8.44bn reals (£1.97bn), considerably more than the 5.6bn reals (£1.3bn) originally estimated. Most expensive was the redevelopment of the National Stadium in Brasília, which cost 1.44bn reals (£336m), nearly twice its original estimate, and three times the cost of some of the stadiums that were completely rebuilt. “The venues cost much more than they should have,” said Somoggi. “They went for pharaonic projects, and we got left with white elephants.” The final insult A rise in bus fares tipped the Brazilian public over the edge. The protests continued right up to the weeks before the tournament, when protesters tried to block the roads leading to the stadiums. “They are giving priority to soccer and forgetting about the Brazilian people,” said nurse Rita de Cassia, who was forced to leave her small apartment when her landlord doubled the rent. As the tournament itself got going, protests started to fizzle out, due to a combination of government concessions and the distraction of the event itself. “We are Brazilians, and football is the culture of our country,” said Rio resident Carla Vilardo. “Now is not the time to protest.” But the World Cup has left scars that may never heal. Rio’s mayor, Eduardo Paes, said of the lessons he’s learned: “Don’t ever in your life do a World Cup and an Olympic Games at the same time. I am not cut out to be a masochist.”
4 branding lessons to learn from Pantone Posted 08/01/2016 by Dale Rolfe & filed under Run your business. Once just a colour chart for graphic designers and interior decorators, Pantone has developed a cult following, splashed across mugs, iPhone cases and watches. The Pantone Universe extends to a pop-up cafe in Monaco with colour coded eclairs and a Pantone hotel in Brussels with coloured feature walls and furniture. So how did such a niche product become a global brand and what are the lessons you can apply to your own business? Be useful Pantone’s key success lies in its ability to solve a problem. All colours are created by a combination of the four CMYK (Cyan, Magenta, Yellow and Black) process colours. Pantone is a recipe book for colour, ensuring that when you want Scuba Blue, you always get Scuba Blue by specifying the exact CMYK combination. This consistency is essential for branding and production. Starbucks green is consistently the exact same shade by following the recipe for its patented, custom Pantone colour 3425C. While there are other colour charts out there that also provide standardised colour formulas, Pantone has established itself as the premier expert in colour. When thinking about your business, ask yourself, ‘What unique problem do I solve and how do I make my customers’ lives easier?’ Be shareable Pantone’s key audience – designers, decorators and artists, are a highly active digital community. By connecting with these influencers and taste makers, Pantone has inspired entire Instagram feeds dedicated to matching doughnuts, flowers and shoes to Pantone colour chips. The images are fun and quirky and are rapidly liked and shared, spreading a niche tool for a small community into the mainstream. Ask yourself, ‘What can I do to inspire others to talk about my business?’ Be emotional Colour is evocative. Pantone uses this to create an emotional connection with its customers. Each year Pantone announces its Color of the Year reflecting the trends, habits and current state of the world as it sees it. 2016’s colour is Rose Quartz and Serenity. Dual colours were picked this year to coincide with “societal movements toward gender equality and fluidity, the consumer’s increased comfort with using color as a form of expression, a generation that has less concern about being typecast or judged and an open exchange of digital information that has opened our eyes to different approaches to color usage.” Gimmick or not, the Color of the Year immediately impacts fashion and design, appearing quickly across clothing, makeup and furniture. Ask yourself, ‘How do I want my customers to feel so they become loyal and advocate for my business?’ Be relevant Pantone rides trends and taps into popular culture. In 2015 Pantone launched a new colour, Minion Yellow, based on the incredibly popular animated movies. A concept devised by Pharrell while creating music for the movie, Minion Yellow can now be bought for your living room walls, throw cushions or textbooks. Pantone’s ability to trend spot and to collaborate with influential designers, artists and musicians brings the brand to a mainstream audience. By also establishing itself as a trend forecaster with its institute of experts, Pantone is trusted as the leader in colour. Ask yourself, ‘How do I react to the trends and movements in my industry and represent this in my business?’ Good branding is all about putting the customer first. By asking yourself these four questions you can create a brand that sets yourself apart. Photo: From left, Minion Stuart , Minion Kevin, and Minion Bob. The characters are from the Universal Studios 2015 animated movie called Minions.
Networking… not a very British pastime Posted 07/29/2016 by Neil Johnson & filed under Career. “I guess it’s been a journey, as for most people,” says Laurie Bernard, CEO of The Business Services Partnership, a firm he set up in 1992 to provide business development and marketing counselling, alongside training and mentoring. The journey he’s talking about is the move from a corporate world with which he’d become disillusioned, to a path all of his own making. “I wanted to be in charge of my own destiny,” he says. “I realised I no longer wanted to be at the beck and call of somebody in some grey office somewhere in the world. For right or for wrong, I no longer wanted to be told what to do, so I took control of my own destiny and started my own business.” And there’s been no looking back. Laurie, who will be running a workshop on networking at a forthcoming AAT branch event later this year, is genuinely passionate about what he does, whether its inspiring people to take the plunge into setting up their own business, then growing that business, showing people how to harness social media marketing or training people to become elite networkers. “I get a thrill from seeing people succeed and stop making the mistakes I’ve seen others make, be it myself, my family, or other businesses, so it’s great to see people grow and succeed.” The many thousands of people who have crossed Laurie’s path include a dog walker who decided to go down a different route and set up the HungryHouse takeaway company, to people at a training session who went on to set up the Innocent smoothie brand. “It’s been a great reward to be able help people make something of their lives, I get a real buzz out of it, out of training people.” Networking… not a very British pastime One of Laurie’s key philosophies in business is treat other people how you want to be treated yourself. “I think that’s really important,” he says. “What we’re looking to achieve at the workshop is to show people how to make connections and maintain those relationships, physically how to work a room, how to develop networking techniques, how to use social media effectively, to build a profile on LinkedIn, and to avoid some of the pitfalls of using social media. “Hopefully people will come away feeling more confident about networking and understand how to use it to gain business, to widen their contacts and subsequently get more referral business by building a reputation as a person to be connected to.” But that’s easier said than done with us often reserved Brits. Generally speaking, we don’t feel comfortable blowing our own trumpet, we like to be seen and not heard. “But when you get into a networking environment it’s like being in a sales exhibition, people come to hear your story and if they like what you’ve got to offer, then at some point they might buy what you’ve got or, even better, recommend you to someone else,” says Laurie. “This is the power of networking, building trust with people whereby they’re happy to part with their money or recommend you to more people.” Unlike Americans, people in the UK don’t know how to work a room, says Laurie. “They don’t realise that they’ve not just gone to a networking event to have a cup of coffee and talk about themselves. You need to identify what you want to achieve from the meeting and then follow up afterwards. Send people follow up emails reiterating what you said, or maybe talk to them on a regular basis. It’s just a way of making a connection that’s warm as opposed to totally cold. “Whether you’re networking face-to-face or electronically, people buy people first, and people want to know what you can achieve for them, not what you can achieve for yourself,” says Laurie. “Networking is very much about being useful to somebody else and being perceived as an expert in your field, but you’ve got to work on it.” Laurie’s top three networking tips Ensure you are networking with the right people e.g. fish in the right pool. Where possible research who’ll be attending an event, or check to see whether there’s a theme relevant to particular industries. Preparation: plan and rehearse your ‘elevator pitch’, e.g. what are you going to say about the benefits of your business from the listener’s viewpoint? Follow-up after the event: a simple email saying it as a pleasure to meet, going over what you talked about, offering more detail, or providing links to websites, blogs or previous work. “I think it’s the bible that has a saying, give a man a meal and he eats for a day, but teach him how to fish and he’ll feed his family for a lifetime, and I guess that aligns with what inspires me to do what I do.”
My studies helped me beat anxiety Posted 07/28/2016 by Mark Rowland & filed under Career, Inspiring stories. I chose an accountancy course due to my love of numbers. I love the simplicity of an answer being either right or wrong. There are set methods and ways to solve problems. For me, numbers are safe. Words, not so much. I have really struggled with anxiety, which has prevented me from choosing certain paths. I was terrified of being noticed and felt deeply uncomfortable sharing my ideas or opinions. It was better to stay quiet than let myself become vulnerable, or risk upsetting anyone. I threw so many opportunities away when my anxiety was at its worst, leaving school halfway through my A Levels. I had a difficult few years when I found myself in situations I couldn’t handle. Eventually, I ended up in hospital. When I got out, I decided that I needed a fresh start and began a journal to track my experiences. At the same time, I began my AAT qualification, and instantly fell in love with the routine of studying. I was finally facing a ‘normal’ challenge. Despite taking a distance learning course, Home Learning College treated me as an individual and I never felt isolated; I enjoyed taking part in conversations on forums with my peers. I shared a common interest with them; I could chat and know that I was not being judged. I was learning how to just be me, to take control in healthy doses and to handle situations I’d always found difficult. My studies became a reassuring constant in my life, the one thing I could take full control of. I began to share my journal experiences, and found myself reflecting on the positives and negatives of each one. Determination to succeed Sometimes things happen that we can’t explain. I thought my AAT studies would involve a safe world of numbers and calculations, but, in fact, it has opened up a whole new world for me. I’ve been named a brand ambassador for Home Learning College, and been on a photo shoot. This year, I was also shortlisted for the AAT Distance Learner of the Year award. When I was younger, I was scared of the possibilities that words, both written and spoken, could open up. I was terrified of the idea that something I said or wrote could be taken in the wrong way by somebody, and that I would get hurt. Now my attitude has changed. The encouragement of others has pulled me out of isolation, and given me strength and encouragement. I want to use the qualification to go to university. After that, I want to work as an accountant. The AAT qualification has gradually built up my confidence and enabled me to settle back into society. It has given me renewed determination to succeed. A lot of things that frightened me last year don’t seem so bad anymore. The prospect of a new life and career has worked wonders for my health. I hope my blog will give hope to others with similar difficulties. It is your responsibility to put yourself out there, but you should also accept ‘leg-ups’ from others. The climb is hard, but the view is well worth the effort. Amy studied with Home Learning College. If you’ve experienced feelings similar to those described in the article, visit Mind or call 0300 123 3393 for free advice and support.
AAT Stories: the real faces of AAT Posted 07/27/2016 by Paul Coombes & filed under AAT news. There’s more to AAT than number crunching. Everyday different people embark on their journeys with AAT and they emerge on the other side with new skills, different experiences and a fresh outlook on life. This journey is anything but a linear affair. #AATStories on Instagram, will discover the real people and heroes of AAT and the accountancy profession. These stories shine a light on our students and members that have come a long way by being original, breaking with convention, and persisting despite the odds. They’re an inspiration and represent the ethos and spirit of AAT. So we would like to invite you all to join them on Instagram and be inspired by their stories and share your own experiences. This is a celebration of the people of AAT and their hard work. We are proud to have played a part – whether big or small in their path to success. Tune in this week to discover why Holly chose a vocational education over university and why this was the best decision she ever made, and how Claire overcame a serious illness and became her own boss. Keep an eye on our Instagram, Twitter and Facebook for more updates. If you would like to share your story please get in touch.
Get the most out of CPD by making it personal Posted 07/26/2016 by CIMA & filed under Career, CPD. Continuing professional development (CPD) is a constantly evolving area as finance functions respond to an increasing number of internal and external pressures. We spoke to Rob Collie, Director of Group Finance Delivery at Sky, a CIMA development quality partner organisation, about the value of consistently high standards of CPD. The home entertainment and communication market is moving forward apace, with constant challenges from new competitors, new technology and new media. “What sort of people do we need to embrace that change and respond?”, asks Rob Collie, Director of Group Finance Delivery at Sky. “One key thing is a ‘can-do’ attitude – taking ownership for performance and delivering results in the right way.” Collie says Sky’s ambition is to have the best finance team in Europe by supporting great win-win decisions and by making it “simple by design” for finance or users of finance to “do finance stuff”. “To do that we need to operate as one team, which means each individual and team collaborating and operating at the best of their abilities. CPD is essential in helping to achieve this,” says Collie. Sky has a matrix of skill and behaviour expectations at each level in the organisation. Business priorities feed into the matrix and this helps identify development gaps in individuals and teams. Collie says that a recent example of how this worked was “a growing recognition that we needed to get better at giving each other good feedback. So we designed a bespoke training course for Finance, which everyone is attending, including our most senior people. This will ensure we all speak the same language and can really help each other.” Sky measures the effectiveness of CPD closely. Managers are tasked with making sure personal development plans are of high quality and may be audited. Every six months, employees have a more formal discussion on their performance with their manager, and the managers come together to ensure consistency, completeness and fairness. It also surveys employees about these discussions and their general development. “That makes it more about the individual and helping them feedback about their managers,” says Collie. “Sky, like any business, is a group of individuals who have chosen to come together to work. Every day we need to ensure we get both the business results as well as what we each want personally. That is why at Sky, your ongoing development is a must-have for your personal and our business success.” Where should you start with CPD? CPD is more than just a box-ticking exercise. The key to valuable CPD is to make it personal. You should ask yourself: What are my development needs? and What are the needs of my business? Begin by going back to basics and identify where your development needs sit and segment your analysis into broad sections: technical knowledge conduct and ethics business development practice management personal development You should ask yourself the following questions for any CPD activity you take on: Why did you choose this activity? Does it fit in with your personal development plan? How does it add value to your knowledge and skill set? Can you put this theoretical learning into practice in your firm? Think about tailoring your personal development plan to meet your individual needs and goals. CPD at CIMA CIMA professional development is the continuing professional development scheme for CIMA members, supporting members with their career by helping them to identify their future learning needs. Unlike other schemes you may be familiar with, CIMA doesn’t prescribe specific activities or number of units or hours. Instead CIMA’s policy gives you the freedom to decide how you are going to develop, so that you can focus on activities that are relevant to your role and to your individual development needs. As business becomes increasingly complex, this approach allows CIMA members to remain up to date and relevant. Using the CGMA Competency Framework, CIMA members are asked to identify any gaps in their technical, business, personal and leadership skills, then select the most appropriate activities to address these gaps. Planned activities are recorded within the Competency and Learning website. Once each activity has been completed, members record what they learned, how they have applied that learning and any changes they would make next time. The plan is revisited regularly, and the CPD cycle is reviewed at least once every 12 months. Find out more about CPD requirements for CIMA members.
Trend analysis – Level 4 study tips Posted 07/25/2016 by Mathew Pickering & filed under Professional Diploma, Students, Study tips. The aim of this article is to cover: What is a trend? How do we calculate a trend? What is a seasonal variation? How do we use trend analysis to make forecasts? What is a trend? A trend is described as general movement of data over time. A trend can be positive or negative depending on the data in question. Once calculated, trends can be used as a means of analysing historic data so that forecasts can be made for the future. A classic example of trend analysis would be in the retail industry, where sales of goods could be analysed over time and the underlying trend could be calculated. In turn, the underlying trend can then be used to make forecasts for sales in the future. Once a forecast has been made then the business can be managed accordingly, for example inventory management, decisions about whether to continue selling certain products and whether the business needs any further investment to support growth. How is a trend calculated? In order to calculate the movement of data over time we can start by calculating the moving average. The moving average is a means of calculating averages of our actual data in small batches, this process ‘smooths out the peaks and troughs’ of the data over time. The following data is a series of data representing sales of a product in units over a 12 month period. It is clear here, that any trend or underlying pattern is not easy to spot. If we plot this data on a graph we can see that the data is ‘messy’. In order to smooth out this data we can calculate a moving average. To calculate a three point moving average we: Add up the data from January, February and March Divide by three List the answer next to the data from the ‘middle’ month (February) Repeat the process, this time exclude the first month (January) from the first calculation and include the next month (April) Repeat for all months By calculating the moving average the underlying pattern of the data will emerge. It is important that at this point it is recognised that there will not be a moving average for January or for December as we have insufficient data to calculate them. Important note: The same process can be followed for any moving average that is an odd number (three, five, seven etc. moving average). If dealing with a moving average for an even number (a four point moving average for example) then see the end of the article to see how to deal with this. Table with three point moving average The three point moving averages show a clear pattern, increasing steadily every month. We can apply the following formula to calculate the average increase in trend per month: Most recent trend less – Opening trend Number of trends less 1 Transferring our own data into this formula: This calculation tells us that the underlying trend is an increase of 90 units per month. Plotting the trend line on a graph Looking closely at the data, it is clear that although the trend line is linear, the actual data fluctuates around this line. In some months the sales are above the trend line, in others the sales are below it. The trend line cannot go beyond November based on the data given because we would require the actual data for November, December and January (added together and divided by three). The gap between the actual data and the trend line is known as the seasonal variation. Seasonal variation can be described as the difference between the trend of data and the actual figures for the period in question. A seasonal variation can be a numerical value (additive) or a percentage (multiplicative). The term ‘seasonal’ is applied to a time period, not necessarily a traditional season (summer, autumn etc.). For example sales may be a lot higher for a store around Christmas, but lower in January. Making forecasts As we have established previously, the graph cannot extend beyond November (trend line) and December (actual data) because we have insufficient data. We now need to be able to work with what we have, in order to make forecasts for the future months and years. The ability to make forecasts for the future is a fundamental aspect of management accounting as it enables a business to make realistic and informed decisions. It is a realistic scenario that a business owner would like a projection of future sales revenue and trend analysis would be a useful tool in answering this question. In order to make a forecast, we will have to take into account two factors: The trend The seasonal variation The key formula connecting our data is as follows: Actual Data or Forecast Data = Trend + Seasonal Variation This formula can be switched around: Trend = Actual Data or Forecast Data – Seasonal Variation Seasonal Variation = Actual Data or Forecast Data – Trend It will be this last formula that will be used to find the seasonal variation for our data. Now we have established the variations, it is clear that they are operating on a repeating cycle (-450 then -900 then +1350). These sorts of seasonal variations could occur in numerous industries. An example of an industry which may find it operates on a pattern similar to the one we have calculated is a pharmacy which despatches prescriptions of contact lenses on a quarterly basis. The pharmacy may find that customers rush in at the end of a quarter to collect their prescription but in the other 2 months they are not as busy. Now that we know the average trend movement (+90) per month and the seasonal variation cycle, we can make forecasts for future time periods. This can be accomplished by doing the following: Using the November three point moving average (trend) as a starting point Add 90 for every additional month required Add or subtract the relevant seasonal variation, taking into account the repetitive nature of the seasonal variations Forecast data = Last calculated trend + (average trend movement x number of periods after November*) + seasonal variation** *as a numerical value, December = 1, January = 2 etc. **the relevant seasonal variation depending on the month required Workings December = 4230 + (90 x 1) – 900 = 3420 January = 4230 + (90 x 2) + 1350 = 5760 February = 4230 + (90 x 3) – 450 = 4050 Table showing all forecast sales until June The graph below shows an extended version of the graph previously used. It demonstrates the forecast data beyond the limitations (time constraints) of the data we were given. Why is this useful? A management accountant could use this technique to make forecasts. This would then be communicated to either their own manager, or perhaps a client they are working for. If a trend is negative, it could be that this forecast is used to predict when sales for a product are going to drop below the break-even point and hence stop becoming profitable. Trend analysis is not only useful for sales figures but also other areas of different businesses such as production and purchases. Limitations When using trend analysis it is always important to bear in mind that the further into the future our forecasts are, the less reliable a forecast will become. Nobody can realistically predict the future and although the seasonal variations in this example are neat and tidy, what happens if there is a flood? Or suddenly the product we are selling becomes obsolete? Obviously the forecast would not be an accurate one in this situation. Further note – four point moving average Earlier in the article, the case study looked at a three point moving average. When looking at a four (or any even number) point average there is a little bit more work to do. The following is an example of what to do when faced with an even number moving average. Imagine for example you were analysing data based on four month periods: List the data in a vertical list, leaving a space between each row As with a three point moving average, systematically work your way through the data finding the average of the data in small batches. This time find the averages of the data in steps of four. The average should be listed in the row adjacent to the middle of the four numbers, so for the opening average we would list it between numbers two and three. Once you have calculated the averages, logically we cannot calculate any seasonal variations because the averages are not adjacent to a specific month. We therefore must work our way through and calculate the averages of our previous calculations, this time in pairs. This new average will be listed in between each pair, so the first one on the table shown will be listed adjacent to Q3 of 20X4. Once this column is filled with our averages, the same process as a three point moving average can be followed and forecasts can be made. Example: 1584 – 1824 = -240 1344 – 1776 = -432 Etc. The completed table which could be used for forecasting purposes can now be constructed. The average movement in trend is -48 per quarter (1296-1824)/(12-1) The seasonal variations cycle every 4 months Forecasts could be made using this information Forecast = 1296* – (48 x quarters after from Q2 20X7) + seasonal variation** *last calculated trend ** The relevant seasonal variation depending on the quarter required Forecast Sales for 20X8 are therefore as follows: Q1 = 1296 – (48 x 3) + 480 = 1632 Q2 = 1296 – (48 x 4) + 192 = 1296 Q3 = 1296 – (48 x 5) – 240 = 816 Q4 = 1296 – (48 x 6) – 432 = 576 Conclusion This article has shown how to calculate trends, analyse them and use them to make forecasts for the future. The skills used are important for Financial Performance, Budgeting and the optional Cash Management unit. The skills are also transferable to the workplace, where analysing patterns can be part of the role of a management accountant. Communicating findings to either the management of the business or clients will potentially be of paramount importance. To access your eLearning tools click the image below and login