Office Workload

Purpose

The Office Workload spreadsheet provides a number of resource related reports together in one spreadsheet.

The spreadsheet can be used to:

▪Identify where actual hours booked differ from planned resources

▪Provide a view of staff commitments and overall availability

▪Provide a monthly view of predicted profitability for one or all projects

The spreadsheet provides the view for a team leader who can then analyse their staff's workloads. Staff included in the report are staff whose line manager matches the staff input in the selections. Projects included are projects whose project leader matches the staff entered in the selections.

Additional Information

The spreadsheet is divided into a 4 sheets:

Staff Resource Plans

The Staff spreadsheet provides a view of staff resource allocations for a team showing the monthly (or weekly) allocations to projects and tasks. If the spreadsheet is generated for a historic period it will include a comparison of actuals against resource plan. The staff included are those whose line manager was entered on the spreadsheet selections page.

▪The number of resource-able days in the month for the member of staff is shown in gray at the top of the spreadsheet.

▪The projects and tasks resourced to are shown down the left hand side

▪The planned allocations and actual hours booked are shown in the right hand side.

▪The task scheduled dates are shown as blue bars in the calendar

Project Resource Plans

The Projects sheet shows the same information but from the project, rather than the staff point of view. The projects included are the projects whose project leader matches the staff ID entered on the spreadsheet selection page.

▪The Projects sheet include projects whose project leader matches the staff input on the selection page.

▪All staff or grades resourced to the project are shown down the left hand side.

▪The comparison with actuals is optional.

▪The total resource cost is shown in blue as a total for the project.

▪The target cost is shown as a total for the project.

oThe target cost is derived either from target schedules if used (task target method = Schedule) or as a straight line from the task target over the task duration.

oFor users who do not schedule targets, an option of printing the fees is provided instead. This gives a view of planned resource costs against anticipated fee income.

▪Totals are shown for the period selected, as work may be planned against a different time-scale to fee income.

Additional Sheets

Resource Availability (Workload and Availability Summary)

The Resource Availability may be used with or without the resource plan and provides a graphical view of workload for a resource group by averaging out target hours across the task durations.

The report is currently available against a weekly calendar and shows all tasks allocated to a resource group within the calendar span. The task durations are shown as blue bars. Underneath each bar is the remaining target (after deducting either % complete or actual costs) spread evenly from the last costing date to the end of the task. This if a task had a target cost of 100 hours and was 40% complete with 3 weeks remaining, the report would show an average weekly spend of 20 hours per week. (60 hours remaining equally divided by 3 weeks)

Where a project has a resource plan, then instead of taking the average spend equally divided, the actual hours planned through the resource plan are used.

At the bottom of the report, all hours are totalled up and converted into staff days using the standard hours per week for full time staff (e.g. 37.5 hours).

As an option you may then add a percentage for lost time or non-plannable time (eg reactive maintenance jobs or advisory jobs). The percentage is calculated as a percentage of staff available. For example, of there are 8 staff in the group and the estimate of lost time is 10%, then this would equate to 0.8 of a man-week.

The totals are then plotted on a graph against the number of staff in the group.

Period Detail

The Period Detail sheet shows the same information, but for all projects being undertaken in the month, against the team’s availability.

The Period Detail sheet shows, for each month included in the date range:

▪The staff making up the team for the line manager input in the selections page and their availability

▪The projects for which the project leader matches the staff input in the selections page

▪The resource allocations from the team

▪Resource allocations from other staff outside the team and borrowed to the project

▪Resource allocations from grades where individuals have not yet been identified

▪Total resource cost for each project and for the month

▪Total target cost (or fee income as an option)

▪Balance of target (or fee) less resourced costs

Operation

Make your selections and click the Generate Spreadsheet button.

The Office Workload will then load in an embedded Excel spreadsheet.

You can edit and save the embedded spreadsheet but to take full advantage of Excel's functionality it is advised that you Save the embedded spreadsheet and re-open it in Excel.

When saving you have two options;-