By Gill Myers Advanced Diploma Study tips: Advanced aspects of spreadsheets 30 Jan 2020 The fifth article of our series on some of the trickiest areas at Advanced level. Study Tips: Advanced Level series Advanced aspects 1: Management accounting Advanced aspects 2: Financial Accounting Advanced aspects 3: Appropriation Accounts and effective communication Advanced aspects 4: Short-term decision making Advanced aspects 5: Spreadsheets Advanced aspects 6: Time management 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. Lewis is a member of Gorgeous Threads & Co’s finance team, and works on both the organisation’s management and financial accounts. Heidi has recently joined the company and is shadowing Lewis as part of her induction. The monthly budget meeting was held yesterday, and as a result Lewis has been asked to investigate last month’s sales as they fell short of the budgeted target, despite the marketing team running a number of campaigns. The Sales Manager has asked to see which campaign was the most successful, and if any particular day of the week generated more sales than others, as he has heard that Sunday evening is popular for on-line sales. Investigating the sales Gorgeous Threads’ has a huge amount of data that is used for a range of purposes, including stock control, marketing, sales and accounts. As Heidi is working with Lewis at the moment, he has imported the relevant data into a spreadsheet so it is more manageable. Click here Advanced Synoptic Series – Part 5 to download the spreadsheet and replicate Lewis’s work. Despite only copying the required information there is still a lot of data which is not easy to make sense of in its raw form. Lewis therefore tells Heidi they are going to use pivot tables and charts to summarise and analyse the information, and present it in a much clearer format. Using pivot tables and charts Heidi hasn’t produced pivot tables before so Lewis gives her the following instructions: Highlight all the raw data to be included in the pivot table including column headings Click on the ‘Insert’ tab and then ‘PivotTable’ button Check the ‘Table/Range’ data (which should already be there as it was highlighted at the start) Select whether you want to insert the table into a new or existing worksheet Click OK The pivot table will appear as a blank proforma Select the appropriate fields from the ‘Pivot Table Field List’ on the right-hand side Heidi successfully follows the instructions up to producing the proforma, but now is unsure which fields to choose. Lewis says they need to think about what they are trying to show. In the first place they want to discover how many sales were made during each campaign. Therefore, Heidi needs to select ‘sales quantity’ and ‘campaign’: From the table they can see that 1,978 sales were made in total and of them, 617 correspond to when the first social media campaign was run. However, Lewis says the table can be enhanced in lots of ways to make the data even clearer. For example, he uses the drop down box in ‘Row labels’ to custom sort: Drop down the menu and click ‘More Sort Options’ Select ‘Descending (Z to A) by:’ Then click on the drop list to choose ‘Sum of sales quantity’ Click OK Then he selects the Social Media 1 cells and uses ‘Fill Colour’ from the font tools on the ‘Home’ tab to highlight them in green: The Sales Manager had also asked on which day of the week most sales are made. Therefore Heidi creates another pivot table showing ‘sales quantity’ and ‘day’: Presenting the data in a chart As it’s logical to leave the days in chronological order, Lewis suggests a chart might be the best presentation for this data. Heidi follows his instructions: Click on a cell within the pivot table Select the ‘Insert’ tab and then the ‘PivotChart’ button Select an appropriate chart Click OK Heidi is really pleased with the chart as the visual presentation shows a general build in sales as the week progresses, with a dip on Wednesdays and highest sales on Sundays as the Sales Manager suspected. Lewis reminds her to change the title so it is meaningful and shows her that it is possible to enhance pivot charts in the same way as pivot tables, for example, using the ‘shape fill’ button in the ‘Format’ tab to change the colour of the bars: The Sales Manager’s questions have been answered but Lewis wonders if there’s a link between two. In other words, were for example, the highest sales in the month made on Sundays during the first social media campaign and if so are they significantly more than other days? Lewis creates another pivot table and this time selects ‘Sales quantity’, ‘Day’ and ‘Campaign’ from the field list: It’s in a vertical format, doesn’t all fit on his screen and isn’t really showing him what he wants. Therefore, in the ‘PivotTable Fields’, he moves the ‘Days’ out of the Rows section and into the Columns section: Finally, he uses conditional formatting (discussed in Part 1) to highlight the top three daily sales: He can see that they were generated during Social Media 1 but Sundays don’t stand out particularly as the same number of sales (148) were made on Saturdays in the period. Interestingly though Heidi observes that Wednesday sales during the campaign were significantly better than the rest of the month. Using a number filter Lewis suggests they finish by looking at the top 10% to see what the average sales were and how Sundays and the campaigns affected the figures. To do that he says they will use a number filter. They return to the raw data and start by applying a filter: Highlight the headers Clicking on ‘Filter’ from the drop down menu of the ‘Sort & Filter’ button in the ‘Editing’ section of the ‘Home’ tab Now they have the drop down arrows for all the columns. In order to get the top 10% of sales they: Click on the drop down menu for ‘Sales Quantity’ Hover over ‘Number Filters’ so that another menu appears Select ‘Top10…’ from this second menu Change ‘Items’ to ‘Percent’ Click OK The filter shows that of the eight entries that comprise the top 10% only one was a Sunday but three were generated within the Social Media 1 campaign. Using subtotal Lewis wants to find the average sales quantity of the top 10% too but knows he can’t use a normal average formula as the data has been filtered. Therefore he uses Subtotal because it will produce an average just of the eight entries. To do it he: Clicks the cell in which he wants the average Clicks the fx box, in the ‘Formulas’ tab, and searches for ‘subtotal’ to bring up the function argument box In the first box he types 1 as this is the number that specifies average Clicks in the second box and then selects the cells to be included from the worksheet Ignores Ref2 and clicks OK Lewis is now ready to give their work to the Sales Manager which shows that in January: The most sales were made during the first social media campaign The highest sales were generated on Sundays The three highest daily sales were all achieved during the first social media campaign. The top 10% of daily sales is comprised of eight entries, of which one is a Sunday and three were generated within the Social Media 1 campaign. A daily average of 48 sales were made in the top 10% days. In summary Lewis and Heidi have used a number of advanced spreadsheet skills and techniques. Further help with excel tips can be found by clicking on the link. The final part in this series will challenge Lewis in a new way as he tries to work out how to manage his time in order to complete a project. Read the next article in the series now: Advanced aspects of time management. Browse the full range of AAT study support resources here Gill Myers is a self-employed accounts consultant. She has taught AAT qualifications since 2005 and written numerous articles and e-learning resources.