Large semiconductor manufacturer.
Each calendar quarter, Client must collect travel plans from several departments. The plans must include a variety of details about the trip and relate each trip to specific internal programs. Then Client must organize and summarize each department's plans as well as create some higher-level summaries across departments.
The departments provide their plans in spreadsheets, emails, or messenger - each one in a different format and containing some subset of the required information.
Client must expend considerable effort to obtain complete information and to consolidate everything into consistent, useable structures.
Clear Data Incorporated created an Excel/VBA application and separate department templates (Excel workbooks) to streamline the process. Each department uses the same template (one for each department) to enter their travel plans. The template limits some entries to specific choices and requires all of the mandatory information. It highlights any missing data.
The rollup application automatically processes all of the department templates, checking each for completeness and correctness and placing all of the data into appropriate tables. The user does not have to open any of the templates manually.
The system keeps the summaries (including the higher-level) current by using formulas.