Ddesign a spreadsheet to calculate and report Productivity Statistics for your department for the week of March 20-24, 2023.

The Situation:

Your department staff completes a weekly work report on which they record:

1. Work Volume Received

2. Work Volume Completed

3. Hours Worked on the Activity

You have the following additional information:

1. Work standards that each activity should require per hour

2. Total hours paid for the department for the week (Timecard hours paid)

This information is provided for you on the next page.

a weekly report for your administrator that provides the following data elements. Use the formulas indicated to perform the calculations, and follow the format on the next page, adding these columns to the report:

1. ELEMENT: Percentage of work received that was completed

COLUMN HEADING: % Completed

CALCULATION: Work completed/received

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

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 ( 2 calculations)

CALCULATION: Productivity Percentage: Actual = the actual hours/hours paid;

Productivity Percentage: Expected = expected hours/hours paid

Add a row called "Productivity Percentage" and place these two productivity percentage answers below the appropriate column, "actual" or "expected" as shown on the attached data page.

Report title: Productivity Report

March 20 - 24, 2023

Std Units/ Hours

Activity Received Completed Hour Worked

Chart Assembly 150 145 6 20.0

Chart Analysis 150 130 4 32.0

Coding 150 148 4 45.0

Abstracting 150 155 10 12.0

Report Filing 1300 1275 30 45.0

Chart Retrieval 975 925 25 33.0

Release of Info 125 140 14 13.0

Totals (you must calculate Total hrs worked

and total expected work hours - answer

to be placed at bottom of appropriate

column)

Timecard hrs paid 209.5

Productivity % (you must calculate for total

hrs worked and total expected work hours

answer to be placed on this line below appropriate

column)

Assignment:

1. Using Excel or other spreadsheet software, design a spreadsheet report including all items listed above (both given and calculated) using the following specifications:

a. Include a report title and column headings as listed above.

b. Include your name and the date on the top right side of the report (must be entered as part of the report).

c. Use the software to perform the calculations above through inserting formulas.

d. All percentages and numbers must be carried to one decimal place.

e. Adjust the column widths so headings and activity titles fit.

f. Display the spreadsheet using landscape format. If necessary, adjust to fit on one page in the "Page Layout" tab, "Scale to Fit".

g. Using MS Excel, build 3 graphs to best show the following data (BE CAREFUL: Choose the graph type that is appropriate for displaying your data!):

1. Work received compared to work completed

2. Variance percentage of all activities

3. Actual compared to expected work hours

Be sure to include a legend/labels as appropriate.

h. a filter for the Variance % Column that includes only positive values; save this as a separate worksheet

i. a filter for the Variance % Column that includes only negative values; save this as a separate worksheet

j. Display the spreadsheet formulas - select "Show Formulas" on the "Formulas" tab and save as a separate worksheet

k. After preparing your report, analyze the data:

• What conclusions could you draw for your administrator?
• Where are the problem areas in the department?
• Which are the productive areas? Look at the graphs and filters for help. Be careful in your interpretation - remember, working more hours and/or completing more work than received in a given week is not necessarily being more productive!
• What are your recommendations after reviewing this data?
• Using Word, a brief paragraph of your data analysis and submit it with the assignment. Be sure to add your name on all work products submitted.

Products to submit for grading:

1. Excel workbook/worksheets to include:
   • Spreadsheet with data - full
   • Spreadsheet with data - filtered for positive variance %
   • Spreadsheet with data - filtered for negative variance %
   • Spreadsheet with cell formulas
   • Three graphs that were generated

1. Word Document:
   • Description of analysis of data(item "k" above)

Answer rating: 100% (QA)

Here is the completed spreadsheet and analysis for the Productivity Report Productivity Report March 20 24 2023 Activity Std UnitsHour Received Comple... View the full answer