Case Studies


Here's a peek into some of the projects Barbara has worked on in the areas of staffing, facilities operations, finance, and data conversion.


Schedule Management System training custom software
Challenge
The client originally proposed an MS Excel-based form to replace a paper form already in use to process and track requests for paid time off (PTO) and unpaid time off (UPTO). However, upon further discussion, it became clear that this solution would not meet the client's long-term goals for the data. In particular,
  1. this form would be used for hundreds of employees,
  2. the client hoped to integrate these PTO/UPTO requests with some way to also manage and review employee schedules, and
  3. administrators and others wanted a way to, at a glance, see who was working that day or would be working on any given day in the future, in any given department, to maintain proper staffing levels.
Solution
Given the number of users and the future plans for this data, a web-based application was designed that allowed supervisors to manage employee schedules, and allowed users to submit PTO and UPTO requests for themselves and, as permitted, other employees (e.g., subordinates who had clocked in late).

The design of the web form for PTO requests was based on the original paper form in order to promote the employees' acceptance of the electronic form and enhance the employees' overall transition experience and acceptance of the system.

A dynamic calendar feature was designed for "reporting", allowing users to generate a schedule calendar for select departments or specific employees for a given calendar year. This calendar report could be exported to MS Excel for inclusion in other reports or for further analysis.

To promote short response times between request submission and review, supervisors were automatically notified by email whenever a new request was submitted to them, and employees were notified by email once the request was reviewed.

The client was given administrator access over the system in the form of one administrator account, which allowed the client to manage their own users, company holidays (automatic time off for all employees), and other information without having to pay for or work with another party to complete these tasks.

A web server and a database server were configured and installed at the client's location to house this application and provide the client with full control over the final application and their data.

Finally, online training was developed in the form of a FAQ, glossary, and interactive tutorial modules to address both the "big picture" questions of how the system worked and the day-to-day minutiae of how to fill out a specific screen.
Business Benefits
The calendar feature meant management could quickly review and ensure staffing levels throughout the year. The calendar feature also aided supervisors in their review of PTO requests by visually contextualizing those requests in the employee schedules and other employees' PTO.

The supplied online tutorials meant that the client would not have to pay for training for new employees or for refresher training for existing employees, but could instead direct employees to the relevant online training modules to be worked through at the employee's own pace.
Technologies
ASP.NET C#
MS SQL Server
MS Excel
Dedicated web server and database server
Purchase Requisition System custom software
Challenge
An MS Excel-based form for submitting and reviewing purchase requisitions lacked scalability, version control, and ease of querying and tracking. Further, requisitions were being "lost in the mail", some supplies were being double-ordered while other supply stocks were not being replenished in a timely fashion, and it was hard to track where in the approval chain the requisition was sitting for review and processing. Any solution needed a means of
  1. quickly and easily tracking requests,
  2. querying those requisitions that had been approved for entry into Infor's Lawson financial software for final processing and invoicing, and
  3. recording the relevant tracking information provided by Lawson for easy cross-referencing.
Finally, as Lawson did not allow for an automated sync between itself and other software packages, the new solution would have to support a manual sync of data between Lawson and itself.
Solution
A web-based solution was created behind the client's intranet firewall. The solution supported
  • vendor management,
  • automatic calculation of quantities for individual line items and sums of line items to prevent user errors,
  • file attachments to be considered along with the requisition (e.g., project proposals from vendors), and
  • a change log that included the date of review and status (e.g., approved or denied) by each of the required reviewers.

To accommodate the manual transition of data to Lawson, a separate user account was created with view access specific to only those requisitions that had been approved for or already entered into Lawson.

The system automatically generated black and white PDFs with appropriate margins, headers, and footers for "pretty printing".

Historical MS Excel data was "cleaned" and imported into the database.
Business Benefits
Staff productivity was increased in several ways:
  1. data entry accuracy was improved,
  2. users were able to more quickly track requisitions throughout the approval process,
  3. there was a more timely turnaround of requisitions into purchase orders, and
  4. historical activity was more easily available and searchable.
Technologies
ASP.NET C#
MS SQL Server
MS Excel
Dedicated web server and database server
Signage Cart custom software
Challenge
A signage group serving multiple physical locations was having issues with unsatisfied customers caused by customers receiving signs that looked different from what they thought they had ordered or selecting signs that weren't available for their location.

Submitted signage orders would be entered into an existing work order database that the signage group was already using to house and track their signage orders.
Solution
A front-layer was introduced to the website that forced users to select their location before they could order signage. A web-based interface was designed to provide users with a what-you-see-is-what-you-get order experience through the creation of a writable sign template to customize the selected signage, complete with the appropriate font families, sizes, and character limits, so users would see exactly what they would be getting. Cookies were used to temporarily store customized signage to the cart, instead of sessions, in order to work within the client's web farm environment and to bypass the need for a "temp" space in the work order database. Once the order was submitted, the signage customer could track the signage order using the unique work order number retrieved from the database and displayed to the user upon order submission.
Business Benefits
Signage customers were now restricted to selecting only from those signs available for their locations, alleviating the problem of receiving orders for signage not available to the customers at that location. Signage customers were more satisfied with the results of signage orders, as the writable sign template had given them more reasonable expectations for the appearance of the finalized signage.

The signage group itself experienced improved efficiency as
  1. the staff no longer had to verify the approved signage for each location, and
  2. the writable template meant that the signage group no longer had to follow up with customers for final approval on orders.
  3. the signage backlog was reduced from over 1,400 to only 200 at any given time
Technologies
Classic ASP
Macromedia (Adobe) Flash
MS SQL Server
Full Time Equivalent (FTE) Forecasting data services custom software
Challenge
The client was looking for a way to utilize work order data from an existing data warehouse to calculate how many full time equivalents (FTEs) might be required by trade (e.g., plumbers, carpenters, electricians) for the upcoming year(s). The solution would also need a way to incorporate projections for preventive maintenance work orders required for regulatory compliance and best practices to maximize facility and equipment life.
Solution
Given that only one employee would be generating these reports, a desktop application was created that allowed the user to select
  1. a date range for which historical work orders to reference for analysis,
  2. which preventive maintenance procedures to consider for analysis, including those that had not been performed previously,
  3. which assets to consider, and
  4. the number of annual manpower hours expected per FTE.

Forecasts could be "drilled down", starting from an MS Excel summary table separated by trade to an MS Excel "data dump" worksheet with auto filter for those wishing to review the actual work order data used to compile that projection. Results could also be automatically ported to a MS PowerPoint presentation for a graphical summary of the analysis following the client's own template specifications. All forecast reports were automatically formatted for "pretty printing" to ensure all forecasts would be printed with appropriate margins, headers, and footers, and, optionally, in black and white to save money on color toner.
Business Benefits
According to the client, this application "proved invaluable in budget preparation" by providing a robust justification for staffing guidelines.

Forecasts were utilized to establish a business model: staffing guidelines for the type of trade and number of employees required/desired in each trade in order to maximize the available resource investment to meet the needs of the business; as employees retired or were promoted, vacant positions could be filled with the right trade based on the business model.

According to the client, they were further able to use a "deep dive" analysis of the data provided by the forecasts to define shift work schedules and balance work loads throughout the year.
Technologies
MS Excel with macros
MS PowerPoint template
MS SQL Server
Budget Model data services custom software
Challenge
A finance group wanted to deploy an MS Excel workbook to collect budget proposals from all departments for the upcoming fiscal year. Each workbook would need to be customized for the specified department, that is, that department's prior year budget data, including vendors and accounts, and the upcoming year's budget allocation, would need to be imported. Then, after the department filled in their requested line items, the workbook would be returned to the finance group who would then augment that proposal with the confidential salary information. Finally, completed department workbook data would be consolidated and summarized.
Solution
Two primary MS Excel "template" workbooks were created: (1) a department template, and (2) a reporting template.

Each department template could be manually configured through a series of clicks: by selecting the department and then clicking a button to automatically pull the relevant department data into the workbook from an MS Access database and a series of CSV files in a shared network directory.

Each department could then add up to 100 line items in each predefined account category, one category per worksheet (e.g., grants, training expenses). This data was summarized on the first worksheet in the workbook, which also housed the summary information from the prior year's budget and upcoming year's allocation for comparison.

The reporting template allowed the finance group to select the department data to be consolidated, generate a single summary worksheet at the click of a button.
Business Benefits
These templates improved the overall efficiency of the budget process.

The department template format encouraged users to think more carefully about each line item by vendor and whether the expenditures needed to be repeated and, if so, whether they were a reasonable amount.

The standard model allowed for faster analysis and adjustments to meet budget targets; the finance group no longer needed to manually compile results from department-specific budget proposal formats. The reporting template allowed the finance group to quickly see the full impact of changes made at the department level to the entire organization.

According to the client, department level budgets could now be finalized in two or three passes, as opposed to more lengthy negotiations between the finance group and each department, and seven or eight passes at higher levels of the organization.
Technologies
MS Excel with macros
MS Access
CSV files
Computerized Maintenance Management System (CMMS) Training Modules training custom software
Challenge
The client was using the CMMS of another vendor, but that vendor's training videos were only available on YouTube. Unfortunately, the client's IT policies blocked employees from accessing streaming video, like YouTube video, from company computers, and the vendor was not willing to post their videos on another site or in another format.

Further discussion revealed that the client
  1. envisioned new and existing employees working at their own pace through the CMMS training materials, without the need for formal or group instruction, and
  2. wanted some sort of performance test result that employees could print out for their supervisors upon completion of training.
Solution
A full set of interactive training modules was designed. Each of these modules provided
  1. a learning objective,
  2. a training video,
  3. a simulation space in which users could, with real-time feedback, practice entering sample data based on real life scenarios, and
  4. a quiz in which users were both asked basic questions (e.g., true/false, multiple choice) about the material in the module and asked to perform simple tasks on simulation screens.
Employees could repeat the module as many times as necessary, and print out the results of the quiz.

Modules were designed based on three forms of familiarity with the CMMS:
  • the vendor's user guide,
  • utilization of an administrator-level CMMS account, and
  • discussions with the client on how they wanted to use the CMMS for daily operations, including company guidelines for how to enter data, which sometimes differed from the more general guidelines provided by the vendor in the user guide.

To bypass the problems with the IT policies regarding streaming video, the training videos were provided in a non-streaming format, meaning each user had to wait for the entire video to download before watching the video. While this was potentially more time consuming, each employee on every company computer now had access to the CMMS training materials.
Business Benefits
An online, asynchronous training space meant that the client did not have to organize formal training sessions for their employees, even with employee turnover and the need for CMMS refresher training.
Technologies
Adobe Photoshop
Adobe Premiere
Audacity
CaptureWizPro by PixelMetrics
Overtime Usage Analysis data services custom software
Challenge
The client wanted some means of aiding a member of staff in recording how overtime was being used and by whom. Once collected, that data needed to be available for analysis by another member of staff.
Solution
Given the client's comfort level with Excel, the small scope of this project in terms of numbers of employees being tracked, and the small number of people in charge of data entry and analysis (i.e., two), a desktop application was designed using MS Excel.

One workbook was utilized to manage overtime categories, employees tracked, employee overtime pay rates, and to record employee overtime charges as they arose.

A second workbook could be used to query, analyze, and generate reports on the recorded overtime charges. Reports included summaries by overtime category, person, department, date range, and day of the week. Generated reports, complete with graphs, could be saved to MS Excel or automatically ported to MS PowerPoint using the client's own PowerPoint template guidelines.

Both workbooks were stored in a shared network space to facilitate coordination between the two employees.
Business Benefits
Ongoing analysis of overtime allowed the client to see how overtime was being used and which areas of expertise were being employed. These analyses were used to justify additional staffing types (i.e., expertise) and levels. These analyses were also used to justify changes in work schedules to minimize overtime cost yet provide the required support services at the right times and on the right days.
Technologies
MS Excel with macros
MS PowerPoint template
Excel Project File Consolidation data services custom-software
Challenge
For ten years, the client had been tracking projects using one MS Excel workbook per project, but was now faced with the task of having to consolidate the project cover worksheet data for reporting and import into ProjectoTM by Wizard Software Solutions.
Solution
Given the client's comfort level with MS Excel, another MS Excel workbook was created that contained a "Control Panel" that accepted the year and parent directory of the target files. Clicking the "consolidate" button traversed the relevant directories and collected the desired summary data onto a new worksheet created at run time. In this way, not only was each year's data consolidated onto a single worksheet, but all of these consolidated worksheets were automatically collected into the same workbook for easy reference and dissemination of results.
Business Benefits
According to the client, it took only 3 minutes to consolidate each year's data, saving countless manpower hours either completing this project manually or trying to learn how to write MS Excel macros themselves.

This entire project only took 2 hours from initial contact to delivery!
Technologies
MS Excel with macros