For this illustration, the company's need is to improve the reporting and analysis of Operating Expenses, by generating more comprehensive monthly spending reports that compare current year actuals to the Plan, and to last year spending. These reports will be used to analyze the results, more easily identifying the organization(s) and expense group(s) that are contributing to the more significant variances, and reaching out to the organizations for an explanation and understanding behind these.
To assist with the analysis, the reports need to allow for exploring lower levels of detail. The details that are available via extract files are as follows:
There are two reporting structure files, one for Organization and the other for Account.
For the Organization structure, the child / parent hierarchy is Department Code > Department (Code - Name) > Function > Organization.
For the Account structure, it is Account Code > Account (Code - Name) > Expense Category > Expense Group.
Actuals are recorded in the company's General Ledger (GL) via Journal Entries and Accounts Payable subsystem entries, and the related extracts identify for each amount the Department Code, Account Code, Accounting Date, and either the Journal Entry ID or Supplier Name.
The GL also records amounts from the Employee Expenses System (i.e. Travel, Meals, Entertainment), but not at the Employee level. The company wishes to include Employee level actual expenses in the reports, therefore more detailed data from the Employee Expenses System is required via an Extract. Each record in the extracts identifies the Department Code, Account Code, Accounting Date, and the Employee Name.
Planning is performed at the Account level, and at the Function level rather than the Department level. An extract from the Planning System is available, but the format differs significantly from the GL Extracts. Each record identifies the Function, Account (Code - Name), and 12 amounts, one for each month from 01Jan to 12Dec.
Downloadable Extract files
You may download any or all of these files. Each file relates to the explanations and images that follow.
Downloadable Reports file
Explanations and partial images follow the Extract files explanations/partial images
For this example the Organization Structure is driven by Department Codes. Each Code has a Department (Code and Name) associated with it.
One or several Departments maps and summarize to a Function, and one or several Functions maps and summarize to an Organization.
The Account Structure is driven by Account Codes. Each Code has an Account (Code and Name) associated with it.
One or several Accounts maps and summarizes to an Expense Category, and one or several Expense Categories maps and summarizes to an Expense Group.
For this example, there are two General Ledger extracts, one for Current Year (2023) activity and one for Last Year (2022) activity.
The Current Year extract contains data only up to the latest accounting month-end (for this example, up to June 2023). This extract would need to be re-created as part of each month-end cycle, to pick up the latest month's data.
The Last Year extract contains data for all months of 2022 and does not need to be recreated each month.
The extracts only have Department and Account Codes. There is no other Organization and Account structure information. They have a ‘Journal Entry/Supplier’ field that is used in the reports.
For this example, the company wishes to include reporting and analysis of Travel, Meals, and Entertainment expenses incurred by each employee. This information is collected in the Employee Expenses system which at month-end submits a Journal Entry to the General Ledger, but only at the Department and Account level, not at the Employee level.
These extracts provide the Employee level information required for the reporting. Since the General Ledger data already includes the expense amounts, it is necessary to ensure that when including the Employee level details the amounts are not duplicated, so the transforms process needs to 'reverse' the General Ledger amounts.
As with the General Ledger extracts, the extract for the Current Year needs to be generated at each month-end while the Last Year extract already contains all 2022 monthly data.
This extract comes from the Planning system and contains Plan information for each month of the Current Year. Only one extract is required, once the year's Planning process has been completed.
In this scenario, Planning is not done at the Department level, but rather one level up at the Function level. The extract therefore contains data for every combination of Function and Account that have Plan amounts for one or more months.
The transforms process must take into account the fact that each record has twelve columns (months) of amounts.
The Reports file contains seven sample MS Excel Pivot Table based reports. Each report can be filtered by and drilled down to various dimensions and attributes.
Any report can be revised using the normal Excel Pivot Table features.
New reports can be added relatively quickly, by copying an existing report or tab, and then renaming and revising the copy.
Note the series of 'slicers' at the top of each report. These are used to filter to one or more values for the dimension/attribute identified by each slicer Header. The filter then applies not only to the report below the slicers, but to all or most reports within this particular sample Reports file.
Which report(s) a Slicer is connected to and filters is controlled by the Reports file administrator. This flexibility can be very useful, as explained for the 'Travel, meals, entertainment' Report below.
For these Sample reports, the Month Slicer/Dimension is filtered to 01Jan to 06Jun. This causes the Quarter Slicer to show that Qtr 1 and Qtr 2 information is being reported, even though this Slicer has not been filtered.
Each Report image and explanation below identifies the Report in the Reports file, by the tab name.
This report provides results for 01Jan to 06Jun (or YTD June as identified in the 'Reporting period' caption) by Organization, initially drilled down to the Function level only.
Several other dimensions/attributes are included.
The report can be filtered to any combination of values identified in the Slicers, e.g. filter to a particular Expense Group, one or more Expense Categories, etc.
In this partial image of the 'by Organization' report, the user has drilled down the 'Sales - Region 3' Function (clicked on the + sign on the left) to the Expense Group, then drilled down the 'Travel, meals, entertainment' Expense Group to the Expense Category, drilled down 'Meals & entertainment' to the Account, drilled down '13500 - Employee meals' to the Month, drilled down '06Jun' to the Department, and finally drilled down 'S301 - Sales Region 3 Dept 1' to the Journal Entry / Supplier.
Note the yellow highlighted fields and amounts in yellow. The amount with Journal Entry / Supplier value 'Emp Exp System' of 1,735 comes from the General Ledger extract. The amount with Journal Entry / Supplier value 'Emp Exp System Offset' of (1,735) was generated by the Data Transforms process as part of incorporating Employee level amounts from the Employee Expenses System extract, so that the total of these amounts does not cause a duplication. The Employee level amounts from the Employee Expenses System are included in the report, and these total 1,735.
Note that, since the planning occurs at the Function level rather than the Department level, there are no Plan numbers in the report for the Department level, and the B/(W) Plan column shows the opposite sign of the Actual amounts.
The same thing occurs for the Journal Entry / Supplier level.
This report provides a breakdown of the YTD June results by Expense Group, initially drilled down to the Expense Category level only.
Similar to the by Organization Report, several other dimensions/attributes are included.
Note that if the user used the Slicers to filter to different values while viewing the by Organization Report, these same filters automatically apply to the by Expense Group Report.
This report is similar to the 'by Organization' Report, but sorts every dimension/attribute in ascending order of Actual Current Year B/(W) Plan amounts, i.e. Variance to Plan.
In this partial image, 'Marketing' is the first Organization listed in report, since the amount in column Actual Current Year B/(W) Plan is the lowest of all organizations, i.e. the worst variance to Plan, at (1,010,587). This is followed by Organization 'Corporate' at (534,683).
Within Organization 'Marketing', Function 'Marketing - Retail' shows up first since it has the worse variance to Plan. The ‘Marketing - Wholesale Function’ is listed next and has an Actual Current Year amount Better than Plan.
This approach applies to all other dimensions/attributes, except for the Month dimension, as shown in the drill down of the 'Engineering' Organization.
Note that if the user filters the report, e.g. for a particular Expense Group, the report re-sorts automatically based on the Variances to Plan for that Expense Group.
This report is similar to the 'by B(W) Plan - Organization' Report, except the primary dimensions are Expense Group and Expense Category.
In this partial image, Expense Group 'Miscellaneous' is the first Expense Group listed in report, since the amount in column Actual Current Year B/(W) Plan is the lowest of all expense groups, i.e. the worst variance to Plan, at (383,773). This is followed by Expense Group 'Equipment, supplies, maintenance' at (109,423).
This report differs from the 'by B(W) Plan - Expense Group' report primarily in two ways:
- Some of the Slicers are disconnected from every other report;
- The sorting uses a different column/metric.
In this report, the 'Expense Group', 'Expense Category', and 'Account' Slicers at the top, filter only for this report. Filtering on any of the other Slicers continues to affect all reports. Conversely, if the 'Expense Group', 'Expense Category' and 'Account' Slicers in any other report are filtered, it will NOT affect this report.
This Slicer connect/disconnect capability gives the Reports file administrator a high degree of flexibility when including multiple types of reports in the same file.
For this report, all dimensions/attributes are sorted in descending order of 'Actual Current Year' amount, instead of ascending order of the 'B/(W) Plan' amount, to be able to see where the highest spending has occurred.
This is an example of a drill down of the 'Travel, meals, entertainment' report for 'Sales - Region 3'. All dimensions/attributes, with the exception of the 'Month' dimension, are sorted in descending order of 'Actual Current Year'. At the 'Journal Entry / Supplier' level, note the highest spending is for the 'Emp Exp System' Entry for 728, with the lowest spending being the 'Emp Exp System Offset' amount of (728) generated by the Employee Expense System extract Transforms process.
This report uses the 'Journal Entry / Supplier' attribute to help analyze the actual amounts purchased from suppliers, and which organizations spent the most from each supplier.
Since the 'Journal Entry / Supplier' attribute identifies journal entries, suppliers and employees, it is necessary to filter this field to only suppliers. Note that there is no Slicer for this, so the filtering in this case is performed in the field Header, and uses a dynamic filter to ensure any new suppliers in future periods are included.
The report is sorted in descending order of 'Actual Current Year' amounts for this attribute as well as all other dimensions/attributes, except for 'Month'.
Since there is no Plan information at the 'Journal Entry / Supplier' level, all amount columns referring to Plan are excluded. There is only comparison to Last Year amounts.
This is an example of a drill down of the report, only to the Function level rather than all the way to the Department level.
As shown, the Actual Current Year amounts are sorted in descending order for the Organization and Function levels.
This report is a monthly trend of 'Actual Current Year B/(W) Plan' amounts across the page, with the 'Grand Total' column in this case showing the YTD June amount.
Note that this version of the report is not sorted in Ascending order of variance.
As with any other report, it can be drilled down all the way to the 'Journal Entry / Supplier' level. However, since there is no Plan information at the 'Department' and 'Journal Entry / Supplier' level, the amounts at these levels are the opposite sign of Actual amounts, i.e. Actual Current Year B/(W) than 0.
Powered by GoDaddy