Abstract

This application is a simple example of a revenue report that uses my preferred programming elements.

Input:

Output:

How to identify your most important clients and employees at a glance (see green background):

• With the clients GHI GmbH & Co KG and DEF AG, you generate more than 50% of your total revenue.
• Your employees Emp2 and Emp1 generate more than 50% of your total revenue.

• The goal was a simple and transparent reporting process: Which employees had which revenue shares for which clients, and who created which revenue report, when, and with what result.
• The scripting dictionaries oStaff, oStaffSum, oClient, oClientSum, and oSum allow for easy collection, storage, and output of all numerical and non-numerical data using non-numeric keys.
• The output data is provided both in the worksheet Output and in the subdirectory Reports, and is additionally zipped into an annual archive by the subroutine sbZip.
• The function RoundToSum ensures error-free rounding of employee shares.
• The enumeration input_columns allows for understandable addressing and easy future modification of the input columns.
• The LoggerFactory module and Logger class module show who created which revenue report, when, and with what result. See the Workflow worksheet and the Logs subdirectory.
• The SystemState class module sets the desired environment parameters for the application.
• The application addresses the worksheets directly using VBA internal code names (wsI, wsO, wsW).
• Invalid inputs and inconsistent output totals are highlighted in yellow and logged.
• The module (external link!) LibFileTools ensures this application also runs under SharePoint / OneDrive.
• The module (external link!) TimeZone provides conversions between time zones – the prerequisite is a properly installed MS Outlook.
• The modules (external links!) modPerf and modTimer, along with the class module clsPerf, enable the measurement of the runtime of subroutines and functions – for this, the precompiler constant MEASURE_RUNTIME = True must be set.
• If another application can provide the input data (e.g., revenues per client, employee time tracking multiplied by their seniority as weights) as a file, this application should be extended with an InputFiles subdirectory containing yearly subfolders (analogous to the existing output structure).
• Detailed documentation of this application is easily possible using the BPM system Adonis.

Download

Please read my Disclaimer.

Employee_Revenue_Shares.xlsm [310 KB Excel file, open and use at your own risk]