Using Excel for Productivity Analysis, Page 2

You must prepare a weekly report for your administration that provides the following data elements. Use the formulas indicated to perform the calculations and follow the format on page 3, adding these columns to the report.

1. ELEMENT: Percentage of work received that was completed

COLUMN HEADING: % Completed

CALCULATION: Work Completed/Received x 100

2. ELEMENT: Actual work units completed per hour

COLUMN HEADING: Actual Units/Hour

CALCULATION: Work Completed/Hrs worked

3. ELEMENT: Variance of units completed compared to the work standard

COLUMN HEADING: Variance

CALCULATION: Actual Units/Hr Standard

4. ELEMENT: Percentage that the variance represents compared to the work standard

COLUMN HEADING: Variance %

CALCULATION: Variance/Standard x 100

5. ELEMENT: Given the work completed and the standard, calculate the hours you would expect the activity to take

COLUMN HEADING: Expected Wk Hrs

CALCULATION: Work Completed/Standard

6. ELEMENT: Calculate how productive your staff is, both actual hours and expected hours worked

CALCULATIONS: (1) Productivity Percentage: Actual = the actual hours/hours paid; (2)

Productivity Percentage Expected = expected hours/hours paid

Place both answers in one row, below the appropriate column

Using Excel for Productivity Analysis, Page 3

Report title: Productivity Report

September 4 10, 2009

Activity	Received	Completed	Std Units/Hour	Hours Worked
Chart Assembly	160	150	7	21.0
Chart Analysis	160	135	5	33.0
Coding	160	152	5	46.0
Abstracting	160	165	9	13.0
Scanning	1200	1175	30	42.0
Chart Retrieval	985	935	24	33.0
Release of Info	125	140	13	13.0

Totals (calculate total hrs worked

and total expected hours answer

to be placed at bottom of each appropriate column)

Timecard hrs paid 209.5

Productivity % (calculate based on both total

hrs worked and total expected hours

answer to be placed on this line below appropriate column)