Got it Pass TeamExcel 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.
<