Integrating Google Forms and Pivot Table

Knowledge Sharing Session 3 on Database

Review

• Google Forms – for collecting data• Pivot Table – for summarizing large amounts

of data

Basic Problem Solving Phases from Data Collection Perspective

Plan

• Identify and analyze the problem

• Clarify what question do we want to answer

• Design the IS accordingly

Collect

• Implement the data collection plan

Use

• Analyze the data

• Make decisions

• Report as needed

Sample Case: Daily Time Record

• Situation: The PMS Time Attendance Monitoring System (TAMS) has broken down. Temporarily, the HRDMS has procured a bundy clock that will print the times the employees log in and out every day onto a bundy card. The HRDMS then encodes the printed times to an Excel file for review and validation of overtime requests, among others.

Sample Case: Daily Time Record

• Analysis: While the bundy clock system has been implemented in other agencies, the system is prone to error during the encoding phase (i.e., HRDMS staff copies the printed times in and out from the bundy card to an Excel file). Further, there have been observed cases when some bundy cards, due to difference of sizes, are not “accepted” by the bundy clock; hence, the security guard has to write the time by hand, presenting data integrity question to the process.

Sample Case: Daily Time Record

• Strength– Easy to implement– Traditional (easy to

recognize)• Weakness– Inefficient

• Opportunity– Availability of wireless

internet connection and extra computers

• Threat– Immediate solution’s risk

of data integrity (during card printing in bundy card and encoding in computer)

S W

T

StrengthEasy to implementTraditional (easy to recognize)

OpportunitiesAvailability of wireless internet connection and extra computers

ThreatsImmediate solution’s risk of data integrity (during card printing in bundy card and encoding in computer)

O

WeaknessInefficient

ANALYSIS

There is an existing asset that can be used to address the weakness and the threat.

COMPUTER-BASED TIME ATTENDANCE LOGIN FORM USING GOOGLE FORMS AND PIVOT TABLE IN GOOGLE SHEETS

Alternative Solution

Drivers of the Alternative System

• Minimize human intervention in recording the time of logging in and out

• Utilize computer power and efficiency in reporting related attendance indicators

• Allow easier reporting of data • Allows scalability and possible integration with

existing HR-related databases/information systems

What questions or requirements do we want to address?

• Every day, what were the times that Employee A logged in and out?

• Of Employee A’s OT Request, how much can be recognized as actual and official OT?

• Can we get an easy visual check if an employee was late or had undertime?

• Can we formulate a logic that will calculate how much time will be deducted for lunch and 15-min breaks?

• Can we calculate the allowable OT payment given the above considerations?

The resultant form per employee should be like this:

Date AM In AM Out

PM In PM Out OT In OT Out Late Under-time

1

2

3

4

5

HR looks at an employee’s attendance with the following considerations

• Date• AM In• AM Out• PM In• PM Out• OT In• OT Out

• OT– OT Request– OT Allowable (no under-

time or late?)– How much OT allowable– How much OT Pay

• Undertime– How much leave to

deduct (if under-time)

LET’S GET TO WORK – DEVELOPING AN ALTERNATIVE TAMS

Basic Steps

1. Create the Google Forms2. Create the associated Google Sheets 3. Use Pivot Table to generate the individual

DTR4. Use Google Sheets functions to incorporate

the needed algorithms and formulae5. Test6. Revise as needed

DIFFERENTIATING DATA AND KNOWLEDGE

Integrating Knowledge Management to Data Management

Plan

Collect

AnalyzeDecide

Learn

QUESTIONS?

EGC WEBSITE

Please visit this website (You must be logged in via your Gmail to see the contents):

https://sites.google.com/site/egcpms