The Challenge

To produce a Human Resources “Open Positions Report” ( OPR ) which would give overall control resulting in an up-to-date, accurate status of the staffing positions. Document ownership was to be maintained by the HR department but with certain data fields to be updateable and/or amendable by the manager responsible.

Weekly review meeting held by Human Resources was to use the report as the main driver and needed the compilation to be based on sector and manager.

Managers were travelling on a regular basis and required access to view and maintain from anywhere in the world.

The Solution

Excel workbook split by sectors and manager containing all data fields required with a totals analysis. Comprehensive use of protection, formulae, formatting and server held data. As information was entered, cells would automatically show information applicable to the record being added in the form of whether the position was Open, Filled, Under Offer, Prospect and automatically update totals on the sector and overview analysis.

Fields : Position, Date Requested, Sector, Project Reg No, Staff/Agency, Location, Required Date, Demob Date, Manager, Recruiter, Filled, Start Date, Under Offer, Date, Prospect and Comments.

A reconciliation check was built in to highlight any missing or incorrectly entered data and Microsoft Share-point used to give anytime / any where maintenance and visibility to managers.

End user training given to multiple users.

The Outcome

A password protected Excel model owned by the Human Resources department which could have certain data added/amended by denoted managers who have assigned rights. Model uploaded onto Share-Point to give visibility and maintenance by managers from any PC with internet access.

Although model was built and implemented for the European region, the model was taken on by all regions giving an integrated global coverage of all staff positions.

Example of model below :