- 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
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
This will display a pop-up menu asking if you want to group by rows or columns
Select Columns, and click OK
Your spreadsheet will now look like this:
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:
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.
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:
Take the ‘Auto Outline’ option and all the total rows will be grouped
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
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’
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:
You can use the ‘Hide Detail’ buttons to collapse the hierarchy and only show the totals, but with the detail readily available.
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.
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
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
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.
Select the range of tabular data and then click on the ‘Forecast Sheet’ icon. A pop-up menu will appear to create your forecast
Select the forecast end date as ’31-Dec-2020’ and click on ‘Create’
This will create a new worksheet with the computed forecast.
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.
#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