Budget Rollup

Client Information

Large semiconductor manufacturer.

Opportunity (problem statement)

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.

Objectives

  • Obtain complete and consistent information from each department
  • Reduce processing time
  • Eliminate errors
  • No more copy and paste

Solution

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.

Results

  • Most departments now provide complete information - in a consistent format - the first time
  • The process time has reduced from a full day each quarter to about 15 minutes
  • The process has eliminated errors in the rollup
  • Client does not ever need to manipulate the data manually