Rapid Research Tracker.zip

Introduction

The tool was designed to calculate the utilization of each analyst on daily, weekly and monthly basis.

Instructions

Please find below the explanation for each tab used in this excel macro

Frontend:

When you want to see your Total Time Utilization and Total Billing for a given period, just fill in Start / End Date for ABC Tracker and Start / End Date for Time Utilization Tracker, and click on the button – Update. You will get the results in ‘Time Utilization’ tab and ‘ABC’ tab. Please do not click on other buttons.

Tracker:

Details such as Charge code, requestor and description will be populated automatically by the help of macro.

The ABC will take inputs from this sheet and the Billable Actual sheet to calculate the cost and total hours spent on a request.

Billable_Actual:

This is the tab where analyst input data on actual and billable hours on a particular request. 

Details such as Charge code, requestor, description and date will be populated automatically by the help of macro.

Please do NOT delete or add any rows or columns(hidden or otherwise) as it will disrupt the functioning of the macro

Put your hours under the column for your respective center.

We have two columns – ‘Actual’ and ‘Billable’ – because, at times the effective time spent on the request might be less than the actual time spent.

For including data in this tab, please note the below format:

Request Charge Code Requestor Description Date Actual Billable
NOD157 Requestor 1 Profiling of playground equipment players in Portugal 6/1/2011 ABC(5)
XYZ(4)
ABC(5)
XYZ(3.75)

Others:

In this tab we will include all information regarding the ‘Non-Billable Hours’ spent on a particular day. For including data in this tab, please note the below format:

Date Others
40695 ABC(5)(Meeting:1)
40695 XYZ(5)(Townhall:1, Training:2)

Pipeline:

This sheet has to be updated (manually) daily using our pipeline tracker, which has data on the number of requests processed in a day, timelines, allocations and requestor details. 

Time Utilization:

The sheet will automatically display every Analyst’s Total Time, Billable Hours and time spent on Other Activities.

ABC Billing Tracker:

The sheet will automatically update The Total Hours and Total Cost for each request

Requestor Details:

This tab has data on the requestor location and FMNO.

We will have to update it (manually) as and when we receive FMNO’s from requestors.

Functionality

The tool was designed to calculate the utilization of each analyst on daily, weekly and monthly basis. The tool also helps in creating the monthly invoicing/billing tracker. The tool is able to extract data that has been incorporated by the analysts and consolidate the hours spent on a particular request. Thus avoiding data redundancy and rework.

Copyrights 2018, www.expertsupdates.com