What do I have to think of when using the data sheet in the balancing report? How do I filter to get the data I want?
Written by Cecilia Bäckström
Updated at April 2nd, 2024
Table of Contents
This article applies to the following countries
- All countries
In this article, you will learn how to use the data sheet in the Balancing report and how to filter in the Excel file to extract the information that you need, and also what to think of when sorting out the data.
Here you can learn how to download the balancing report.
The data sheet in the balancing report will show what has been posted on the salary codes/accounts within the period that you choose when generating the report. The period you choose is based on the booking dates and not the expense dates. You can also choose to generate the balancing report and include unsubmitted reports but this article will be based on that you only chose to generate the report with submitted reports.
You can extract a lot of different information from the balancing sheet. Following are the data you can filter on:
Data to filter on
User
User email
Employee ID
Report ID
Report Name
Status
Report Description
Booking date
Booking period
Approved
Sent
Report Cost center
Project
Verification number
Voucher date
Expense description
Category
Category type
Currency
Exchange rate
Expense amount
Distance
Tax amount
Tip
Accounting amount
Account/Salary Code
Account type
Account group
Report number
Recipient
Approver 1
Approver 2
Approver 3
Dimensions
First of all, open the generated Excel file. Go to the data sheet and start by making a filter for the cells in column 1:
Now when you have made the filter, you can start sorting out the data you want.
Please note that the filtering techniques may vary depending on what type of settings and integrations you have on your organization. The following information is only guidelines and examples of scenarios in our demo environment.
We will describe three types of scenarios below on what to think of when you sort out the specific data:
Extract all data of what has been reported on a specific account
In this scenario, we want to see how much has been posted on account 5820 within the period that we chose when we generated the report from Findity (in this case 2023-01-01 to 2023-11-14).
Since we want to see all users that have registered expenses on account 5820, we will only have to filter on the account in the data sheet:
If you send the same account to several recipients you also have to filter on one of the recipients to not get duplicate amounts.
Extract the data of how many expenses a user has reported within the period
It's quite tricky to extract data on how many expenses one user has made within the period. You have to take into consideration that you might send the same expense to all of your recipients and some to just one of them. This will make it harder to filter to get an exact amount and you manually have to count the expenses.
In this case below, its a bit more difficult to filter since we have both salary- and accounting recipients and some expenses are just sent to salary and some are sent to both salary and accounting. Then we have to keep in mind that we can filter on the salary recipient and not the accounting recipient. We also remove the TAX to just get the tax-free expenses and the taxable expenses:
In case you send all of the expenses to all of your recipients it's a bit easier to filter the data since you can filter on both/all your recipients and get the same amount of expenses. In this case below we filter on the accounting recipient, we check if there are any taxes to remove and we also have to filter on credit or debit, so we choose to filter on credit (payment type):
Extract the data of how many reports have been sent within the period
In this case we want to know how many reports have been submitted within the period. Watch the video to learn how to sort this out:
Please keep following in mind when you are filtering the data you want to extract:
- Debit/credit amounts creates several rows in the sheet
- VAT has a separate row
- If you have several integrations they are also creating several rows in the sheet
- Salary code and accounts create several rows in the sheet
- If you only have cost centers on your costs, you can filter on this to get a unique value
- You can sort on account group (normal amount/tax-free amount/tax amount/payment type)
- Per diem and mileage can have both a row for taxable and tax-free remuneration