How can expense reports be simplified and automated?
Efficiency question :
An expense report is not used only to request reimbursement. It should also apply controls, including verifying that an expense respects authorized thresholds, determining its accounting allocation and correctly calculating recoverable input taxes.
Good automation simplifies data entry for the employee while automatically preparing the information accounting needs. From the expenses entered, it is possible to determine accounting allocations, calculate taxes and produce the information required for reimbursement.
Key considerations:
The approval process must first be determined. In some organizations, the employee simply has the expense report approved by their immediate supervisor. In others, several hierarchical levels may be involved before the document is sent to accounting.
You must also determine when controls will be performed: during entry, at approval or by accounting before reimbursement is issued.
Challenges:
Rules can become more complex when an organization includes several companies or entities. Tax rules and methods for recovering taxes may vary depending on the entity to which the expense is allocated.
Rules must therefore be precise enough to ensure that taxes, expense categories and accounting allocations are handled correctly while keeping the process simple for the user.
Technology:
An expense report can be automated with relatively simple technologies. A standardized Excel form can allow the employee to enter expenses in a structured grid. Rules and Visual Basic for Applications (VBA) can then automatically determine accounting allocations, perform controls and prepare the reimbursement amount.
The result can be a table containing the required accounting entries or allocations and the payment to be issued, significantly reducing re-entry by accounting.
Developer's advice:
If your organization uses standardized Excel forms for expense reports, Power Query can then automatically retrieve and consolidate the different files. This provides an overall view of expense reports without having to open and combine each document manually.
The SLV Expert on-demand training portal offers courses on Power Query and Power Pivot, including Level 1 and more advanced Level 2 training. These courses teach you how to retrieve, transform, consolidate and analyze data from multiple sources.