• Got it Pass Team

Excel for Accountants: Grouping, Outline and Trend Forecasting

Updated: Sep 30, 2020

Accountants need to manage a lot of data on everyday. One example is they usually group data and display them in different rows or columns in MS Excel. Outline consists of various features which can help you.


For management accountants, performing trend analysis is done often especially in projecting performance in future. Forecasting in Excel is excellent for you in projecting future trends.


We explain these tools below which can help you to improve your work efficiency.



Grouping


Spreadsheets can get extremely complicated and there are times when certain columns or rows, which may be involved in a stage of the calculation, need to be hidden for the sake of simplicity, but can easily be shown if necessary.


For example, there may be a set of tabular data on a spreadsheet


Image 1: A set of tabular data on a spreadsheet

The first name and last name of the customer may not be important enough to be shown initially. These would be required to be hidden, but easily displayed if required by the user.


To group the data, select the first name and last name data, and then click on ‘Group’ in the ‘Data’ tab of the ribbon


Image 2: ‘Group’ in the ‘Data’ tab of the ribbon

This will display a pop-up menu asking if you want to group by rows or columns


Image 3: Pop-up menu under Group

Select Columns, and click OK


Your spreadsheet will now look like this:


Image 4: Group by columns

To hide the first and last name, click on the ‘Hide Detail’ button (minus sign indicated by arrow in screenshot above)

Your spreadsheet will now look like this:


Image 5: Hide Detail for grouping under columns

Columns B and C are now hidden, but can be easily be unhidden with a single click on the ‘Unhide Detail’ button (plus sign indicated by arrow in above screenshot)


The process of grouping can also be used on rows in the case of a block of rows which are needed to be hidden until required


There is also an option called ‘Auto Outline’. This is found on the drop down for ‘Group’ on the ‘Data’ tab on the ribbon.


Image 6: Auto Outline button

If you have tabular data which has totals or sub totals within it, so that it looks like a continuous range of data with many totals, you can use this function to automatically group for the detail of each total. Note that the totals must have a formula e.g. SUM otherwise you will get an error message ‘Cannot create outline’


Your data should look like this:


Image 7: Auto Outline example

Take the ‘Auto Outline’ option and all the total rows will be grouped


Image 8: Hide Detail in Auto Outline example

You can use the ‘Hide Detail’ buttons to collapse the detail of each total


The ‘Clear Outline’ option on the ‘Ungroup’ drop down will clear all the groupings on the spreadsheet


Image 9: Clear Outline button



Sub Totaling


This function is on the ‘Outline’ group of the ‘Data’ tab on the ribbon. You need to select your range of tabular data first.


It is a useful way of grouping by a specified column and providing a sub total for that group. The tabular data needs to be sorted into order of the column that you wish to group and sub total by. In this case the grouped column will be ‘Order No’


Image 10: Subtotal example

You can choose the column that you want to be the group column and which function you want to use for each subtotal, e.g. Sum, Count


You select the column to be totaled – there can be multiple columns here.


Click OK and your data will look like this:


Image 11: Subtotal example result

You can use the ‘Hide Detail’ buttons to collapse the hierarchy and only show the totals, but with the detail readily available.


Image 12: Hide Detail in Subtotal example

The 1,2,3 boxes at the top left will show the levels of detail when clicked. 1 will only show the ‘Grand Total ‘whereas 3 will show everything



Forecast


What-If Analysis

This function is found on the ‘Forecast’ group on the ‘Data’ tab of the ribbon


It allows the user to set a target value for specific cell e.g. Profit / Loss based on altering a value in a cell that is part of the formula for that target cell.


For example, you could have a value for sales and values for fixed and variable costs. Assume that the variable costs are always 20% of sales. Using a simple formula, you can find the value of the profit.


Image 13: Variable costs are 20% of sales

Supposing that your target for profit is 5000 and you wish to find out what sales target is needed to achieve this, you can use ‘Goal Seek’ on the ‘What-If Analysis’ drop down


Image 14: 'Goal Seek' in 'What-If Analysis' button

Image 15: Set profit at 5000 and find how much sales and variable costs have to be

B4 is the profit cell and we want to set this to a value of 5000 by changing the sales value at cell B1


Click OK and the sales value will be instantly worked out


Image 16: Result page after 'Goal Seek' completed

Click OK to keep the data, or click ‘Cancel’ to go back to the original data. As you can see, sales would need to be 6250 to achieve a profit of 5000


‘Scenario Manager’ allows you to set up a library of ‘What-If’ scenarios based on your data


Forecast Sheet

This function will allow a forecast to be created based on a timeline of values. The function is found on the ‘Forecast’ group of the ‘Data’ tab on the ribbon


As an example, you have a timeline of data showing sales data for each month end for the year 2020. However, current data only goes up to the month of April, and you wish to see a forecast up to the year end.


Image 17: Forecast Sheet example

Select the range of tabular data and then click on the ‘Forecast Sheet’ icon. A pop-up menu will appear to create your forecast


Image 18: Create Forecast Worksheet

Select the forecast end date as ’31-Dec-2020’ and click on ‘Create’


This will create a new worksheet with the computed forecast.


Image 19: A new sheet created for the forecasted values

Your original data is still intact on your previous worksheet.


A forecast column has been created which shows values for each future month end based on the data entered for the first four months. As the sales data entered shows an upward trend, the forecast shows a general upward trend to the end of the year.


A chart is also provided which can be edited using the chart design tools menu.


Returning to the pop-up menu for the forecast, you can use the ‘Options’ button at the bottom of the window to enhance the forecast.


This will provide a further range of parameters for your forecast


You can select the forecast start date as well as the end date


The confidence interval (default is 95%) can be changed. For example, the confidence interval is the values surrounding each forecast value, in which 95% of future values are expected to go down, based on the forecast (with normal distribution).


Seasonality is a number for the length (number of points) of the seasonal pattern and is automatically detected. For example, in an annual sales cycle, with each point representing a month, the seasonality is 12. You can edit this by choosing ‘Set Manually’ and then picking a number.


To handle missing points, Excel uses interpolation. This means that a missing value will be completed as the weighted average of its neighbouring values as long as fewer than 30% of the values are missing. To treat the missing values as zeros instead, click zeros in the dropdown list.


Image 20: Use the ‘Options’ button at the bottom of the window to enhance the forecast


#excelforecast #excelforecastfunction #exceloutline #excelgroupdata #exceltrendfunction



If you find this article is helpful and you want to help others too, just share it in any social media (such as Facebook, LinkedIn).



Got it Pass Team is happy to work with Richard in presenting this article.

About Richard Shepherd-

Richard is an expert in MS Excel and Access with extensive experience of developing MI reporting systems, databases and dashboards using VBA or RAD for major companies such as Santander, Nationwide Building Society and Zurich Insurance. He is also the author of Access 2010 VBA Macro Programming and Excel VBA Macro Programming. He was graduated in University of the Thames Valley, United Kingdom.


Note: Some of the links in this page contain affiliate links. I will earn a small commission for any purchases you make, though there is no extra cost to you.

The products or services I recommend only I use them myself or after my investigation and truly believe they are useful to you. Don’t forget there are many other options out there – these are simply my preferred choices.

162 views

Phone Number: +852 9667 4853

©2020 by Got it Pass.