Projects:Time Sheet Review/Functional Specification
Time Sheet Review - Functional Specifications
Overview
Purpose
Recording and reporting of time sheets is not optimized in Openbravo ERP. This project aims to address this functional issue.
Scope
This project will
- review the current functionality,
- define and prioritize the areas of improvement,
- and develop enhancements
for the business processes of
- time sheet recording,
- time sheet approval,
- time sheet billing,
- and time sheet reporting.
Current functionality
Time sheet recording
- User story
- Today, John has being devoting 8 hours to the consultancy project he is participating on. His manager has told him to record every single hour that he spends on this project since this will allow him to calculate the profitability of the project. John enters a time sheet line, selects the concept (product), records a quantity of 8 and selects the project.
- John has also being told that for this project and the consultancy hours concept, the customer has to be re-billed so he sets this time sheet line as reinvoiceable.
- Business process
- John records a new expense sheet or opens an existing and not processed one (Project & Service Management || Transactions || Expense Sheet || Header).
- He creates a new line (Project & Service Management || Transactions || Expense Sheet || Header >> Lines) and:
- ticks Time Sheet check-box,
- ticks Reinvoicing check-box,
- selects Consultancy product (UOM is set to Hours automatically),
- sets 8 as Quantity,
- leaves Invoice Price blank (the customer will be re-invoiced based on his price list),
- sets today as the Expense Date,
- adds some Comments,
- selects the Project (Business Partner is automatically selected).
Time sheet approval
- User story
- Tim, John's manager, reviews each of John's time sheet lines.
- If they are correct, he approves the time sheet header.
- If not, he makes the necessary changes and then approves the time sheet header.
- Business process
- Tim filters John's time (expense) sheets (Project & Service Management || Transactions || Expense Sheet || Header).
- For each of them, he has to review the hours spent, the project, the re-invoicing check-box and the business partner (Project & Service Management || Transactions || Expense Sheet || Header >> Lines).
- When every data is OK, he comes back to the header and clicks on Process Expenses button.
Time sheet billing
- User story
- Sarah, the accountant, is in charge of billing the re-invoiceable time sheet lines to project's customers.
- This implies:
- Having a look at the processed time sheet lines to be re-invoiced to the customer.
- Launch the process to create the sales orders for this customer with the corresponding time sheet lines.
- Business process
- Sarah filters processed time sheet lines associated to a certain customer (Project & Service Management || Transactions || Invoiceable Expenses).
- She can review the time sheet lines (Project & Service Management || Transactions || Invoiceable Expenses || Customer >> Lines) and, if something is wrong and she has permissions, un-process the time sheet, correct the time sheet lines and process the time sheet again (Project & Service Management || Transactions || Expense Sheet || Header >> Lines).
- When everything is correct, she creates the sales order(s) from the time sheet lines selecting the previous customer (Project & Service Management || Transactions || Create Sales Orders from Expenses).
- She finally reviews the sale order(s) created (Sales Management || Transactions || Sales Order || Header) that will lead to invoices which, once completed, will be reflected in the project profitability report.
Time sheet reporting
- User story
- Project Profitability
- Tim wants to report the profitability of the project where John has being involved on.
- The profitability (real gross margin -RGM-) will be given by the remainder of:
- The amount that the customer has being invoiced for the services that he has received (real revenue -RR-).
- Minus the cost of the project in terms of human resources, that is, the hours that each employee devoted to provide those services times the amount that each employee is paid for each hour of labour (real cost -RC-).
- RGM=RR-RC or RGM%=(RR-RC)*100/RR
- If RGM is negative (RGM<0), the project was NOT profitable: the company lost money with it.
- If RGM is positive (RGM>0), the project was profitable: the company earned money with it.
- If RGM is zero (RGM=0), the project was balanced between revenues and cost but the company did not earned money.
- Time Report
- Tim wants to see how many hours his employees have being devoting to the project so far. This will tell him, knowing the status of the project and the average hours devoted at this stage, if this project is on track or if it is overpassing the average cost in man hours.
- He displays a time report, filtering by the project, and he gets the data that he was looking for.
- Project Profitability
- Business process
- Project Profitability
- Tim goes to the Project Profitability Report (Project & Service Management || Analysis Tools || Project Profitability).
- He selects the corresponding project and clicks on HTML Format button.
- Services Real Revenue shows him the amount that has already being invoiced to the customer for the services that he has received.
- Services Real Cost shows him the cost in man hours burned so far devoted to provide services to the customer of the project.
- Services Real Margin % shows the % of profit in terms of services provided for this project.
- Real Gross Margin shows him the profit of the project including the collateral expenses (taxis, hotels, servers, etc...).
- Real Gross Margin % shows him the profit of the project in % including the collateral expenses (taxis, hotels, servers, etc...).
- Time Report
- Tim goes to the Expense Report (Project & Service Management || Analysis Tools || Expense Report).
- He filters by the project and clicks on HTML Format button.
- The report shows him:
- The total hours (with their cost) devoted in providing services to the customer of the project.
- The employee hours (with their cost) devoted in providing services to the customer of the project.
- Project Profitability
Areas of improvement
Areas of improvement of time sheet recording, approval, billing and reporting are listed and prioritized at Time Sheet Review - Areas of improvement spreadsheet. They are sorted descendantly by Requirement score column: higher functional impact with lower difficulty.
Any feedback is welcome at the project forum.
Functional Requirements
User roles & profiles
The following three roles are involved in this process :
- Employee
- Responsible for creating time sheet everyday with proper project description. Needs to submit the form for approval for monthly/weekly/daily basis.
- Manager
- Responsible for reviewing the time sheet, he/she has the authority to approve/disapprove the time sheet. If the time sheet line can be reinvoiceable, should send/process the document to finance team.
- Finance Manager
- Responsible to make sales order from time sheet lines if all details are properly entered.
Business Process Definition
This can be classified based on four mandatory process involved in time sheet.
Time Sheet Recording
- Every employee is responsible for entering time sheet. Employee is working against some task. The main purpose of this process is to track all employee's work against project/task. So in this process he is asking to enter his details, project details and task details. In task details he needs to enter hours spent for task, date and some other details for creating sales order (if needed).
- These records permit to estimate, based on the salary category cost of employees, the cost of these tasks. Employees can submit their time sheet for monthly/weekly/daily basis. Once time sheet has been entered, employees can also have the rights to modify it till it gets approved/submitted. Once an employee has submitted his time sheet, the manager will be alerted to approve/disapprove.
Time Sheet Approval
- The manager's responsibility is to approve/disapprove time sheets, once it got submitted by team. He can modify it for further process. Employee’s work will be re-invoiced in some projects to the customer of the project.
- So he should analyse and modify the time sheets according to the needs or what are all the tasks which need to be re-invoiced. These changes will be notified to the employee and finance team, once it got approved.
Time Sheet Billing
- After approval process, finance team has to verify all details in time sheet. If time sheet has to be re-invoiced (client paying to company for employees work), they have to create sales order against time sheet. They can also edit time sheet.
- If they find any pending time sheet, they can send a reminder to employee (time sheet entry) or manager (for approval process) before running the re-invoicing process.
Time Sheet Reporting
- Reports are used to view all employees’ time sheet details, project details against time sheet and sales order details against time sheet. Pending time sheets can also be viewed.
- Projects (projects and time sheets are internally linked together) and profitability report gives the comparison between projects and revenue, also provides the detailed graphical view for top level management.
User Stories
Time Sheet Recording
Story - 1
- John is working in enhancements of banking domain softwares for different clients. His manager is asking him to enter his work in time sheet, to track his activities for different clients. Depending upon the task priorities, he is working currently. So at the end of the day he is creating time sheet with proper date, hours spent for each task, project and describing his exact work as description. Then he is submitting the time sheet for his manager's approval.
Story - 2
- Jessy is working in service kind of project that belongs to her company. She is creating time sheet with proper details for every day. At the end the week, she is submitting her time sheet for manager's approval.
Time Sheet Approval
Story - 1
- Tim who is manager for all employees (for example), receives e-mail for approving the time sheet. He is reviewing the all details that was submitted by John, and now he is ready to approve. Before that the tasks which are doing by John, is for other clients. So his work can be re-invoceable in the way of sales orders. Now Tim is approving his time sheet and the finance team will be alerted by e-mail for further process.
Story - 2
- Tim is reviewing Jessy's time sheet and approving her time sheet for one week. Since she is working on local projects, her work can't be re-invoiceable.
Time Sheet Billing
- Kevin who is in-charge of finance team, reviewing John's time sheet for further process. He is very much satisfied in John's time sheet details like hours spent, work for particular task. So he is creating sales orders against the time sheet.
Time Sheet Reporting
- Tim wants to know about all pending time sheets for week/month. He opens the report and identifying the pending time sheet for approval and pending from employee's side processing. He is sending reminder to all employees who are all not submitted their time sheet yet.
- Tim wants to know about profits for all projects under his control. From the report he gets the details of project, revenue and comparison details. He came to know that his projects are running in good profit. So he sends these details to his top level management in a graphical format.
Functional requirements based on business processes
The following are the basic requirements for time sheet and ordered by its process and priorities.
Time Sheet Recording
Requirement 1: Delivery Organization 1
Num | Requirement | Importance | Status |
1.1 | Add a new field called 'Delivery Organization' to Service Project tab, Multiphase Project tab, Project Phase tab and Project Task tab. This delivery organization will be the one that provides the service.
| Must have |
Requirement 2: Delivery Organization 2
Num | Requirement | Importance | Status |
2.1 |
In the expense (time) sheet lines, project drop-down list values will be filled with:
| Must have | |
2.2 |
In the expense (time) sheet lines, phase drop-down list values will be filled with:
| Must have | |
2.3 | In the expense (time) sheet lines, task drop-down list values will be filled with:
| Must have | |
2.4 | When saving an expense (sheet) line, we must check that we have chosen a project stage which delivery organization is equal to the expense line organization. This means that:
| Must have |
Requirement 3: Employee resources to projects, phases and tasks
Num | Requirement | Importance | Status |
3.1 |
Create a HHRR tab at a project level, at a phase level and at a task level so employees can be allocated at different levels.
Managers can able to allocate employees in all levels in Project. | Must have | |
3.2 |
Create mechanisms in Expense Sheet window in order that employees can only assign hours and expenses to the projects, project phases or project tasks they are working on. | Must have | |
3.3 | Create a report that lists:
| Have Next | |
3.4 |
Create a process window to quickly assign more than one employee to a certain project or project phase or project task. Feature request 0004504: Associate human resources to projects https://issues.openbravo.com/view.php?id=4504 | Have Next |
Requirement 4: Which products should you be able to choose on a time sheet
Num | Requirement | Importance | Status |
4.1 |
A new tab called "Expense and Time Products" should be added as a son of "Multiphase Project" and "Service Project" tabs with a drop-down field showing all the products with product type = Service or Expense Type (for expense lines). This tab also should include the client,organization and project fields as read only. | Must have | |
4.2 |
In the time sheet line:
| Must have |
Requirement 5: Which products specified in a time sheet could be reinvoiceable
Num | Requirement | Importance | Status |
5.1 |
A new tab called "Expense and Time Products" should be added as a son of "Multiphase Project" and "Service Project" tabs with a drop-down field showing all the products with product type = Services or Expense Type (for expense lines) and a check box called Reinvoicing. This tab also should include the client,organization and project fields as read only. | Must have | |
5.2 |
In the time sheet line:
| Must have |
Requirement 6: Restrictions on hours recorded
Num | Requirement | Importance | Status |
6.1 |
A new tab in Organization window(In Master Data Setup) has to be added, to set max. hours to be entered in Time Sheet. A tab to setup the maximum hours per time slot for each organization. The fields would be:
| Must have | |
6.2 |
When recording data in this tab, there should be a check for a valid conversion between hours and the selected time UOM, otherwise display an error message. When inserting or updating a new time sheet line, it has to be checked if the maximum hours per time UOM has been surpassed or not:
| Must have |
Requirement 7: Which products should you be able to choose on a time sheet defined in project type
Num | Requirement | Importance | Status |
7.1 |
A new tab called 'Expense & Time Sheet' has to be created in Project Type,this should have the following fields,
The process 'Set Project Type' while creating new multiphase project, the products in Project Type has to be defaulted in 'Multiphase Project'(as Project Phase/Project Task).So there is no need to set products, manually in Project levels.This is extension of Req:4. | Must have |
Requirement 8: Rapid data entry 2: Calendar matrix like
Num | Requirement | Importance | Status |
8.1 |
Time sheet entry in a calendar matrix way will be a manual window with the following structure (first number is the line and second number the position in the line). Defaults -
| Must have | |
8.2 |
Defaults -
| Must have | |
8.3 |
Defaults -
| Must have | |
8.4 |
Time Sheet Lines -
| Must have | |
8.5 |
Other Fields in Lines :
| Must have | |
8.6 |
Line Field Details:
| Must have | |
8.7 |
Other Details :
| Must have | |
8.8 |
Process :
| Must have |
Requirement 9: Rapid data entry 1: Simplified time sheet window
Num | Requirement | Importance | Status |
9.1 |
Simplified time sheet window - Header tab
Lines tab
| Have Next |
Requirement 10: Rapid data entry 3: Google calendar like
Num | Requirement | Importance | Status |
10.1 |
A new Time sheet entry, Google calendar widget that should display a calendar in a day, week or month view. When clicking on a time range (in Day or Week view) or in a day (in Month view) a pop-up window will display asking for the following information:
| Have Next | |
10.2 |
Process Details
| Have Next | |
10.3 |
This would be build in a manual window. Two new columns should be added to S_TIMEEXPENSE_LINE table in order to save Starting Time and Ending Time fields values. Nice to have: different projects would have different colors. If a time sheet line is processed, the color of the time range will become darker. See mock-up example at http://spreadsheets.google.com/ccc?key=0AohY6-U9-FFVdFlrRWpSS2FTUkRCU1ZfMWdTaXg4eXc Google calendar API http://code.google.com/apis/calendar/ | Have Next |
Requirement 11: Copy Lines from Expense Sheet
Num | Requirement | Importance | Status |
11.1 |
Process, "Copy from expense Sheet Lines" should have a pop-up with the following structure: Filters,
| Have Next | |
11.2 |
A grid which values will change automatically based on the filters with the following columns,
Feature request 0004521: Create Lines from Expenses https://issues.openbravo.com/view.php?id=4521 | Have Next |
Requirement 12: Rapid data entry 0: Re-order current field
Num | Requirement | Importance | Status |
12.1 |
A first review for a rapid data entry will consist in a change in the distribution of fields. This is the proposal (first number indicates the line and second number the position in the line):
| Have Next | |
12.2 |
In Project
| Have Next | |
12.3 |
In Details
| Have Next |
Requirement 13: Rapid data entry 5: Import from Excel
Num | Requirement | Importance | Status |
13.1 |
A Data Loader is required for Time sheet process(Import data from Excel Sheet to Openbrvo) to optimize time.
| Have Next | |
13.2 |
Reference - On-going Two projects
| Have Next |
Requirement 14: Copy from Expense Sheet
Num | Requirement | Importance | Status |
14.1 |
A button called Copy from Expense Sheet should be added to Expense Sheet header. This button will only display the following artifacts:
The copy process would copy exactly the same information of the time (expense) sheet selected into the lines of the new time (expense) sheet. | Have Next |
Requirement 15: Rapid data entry 4: Integration with 3rd party time sheet reporting tool
Num | Requirement | Importance | Status |
15.1 |
Create a generic module in order to easily integrate time sheet process (at least time sheet hours recording) with different 3rd party tools.
| Have Next |
Requirement 16: New Tabs
Num | Requirement | Importance | Status |
16.1 |
Add two tabs under Lines Tab:
| Have Next |
Time Sheet Approval
Requirement 1: Expenses approval system
Num | Requirement | Importance | Status |
1.1 |
An approval system for expenses would be useful in order that the responsible of a project supervises expenses that employees participating in it register in expense sheets. 1) Third status for process/unprocess:
| Nice to have | |
1.2 |
2) Approved check-box:
Filter for this window:
This way, he would easily track and approve/disapprove the expense lines of its projects using a 'Approve/Disapprove expense line' button or changing the Approved check-box value (this check-box would be the only non-read-only field). This is the better approach. | Nice to have | |
1.3 |
In both cases:
Feature request 0004524: Expenses approval system https://issues.openbravo.com/view.php?id=4524 | Nice to have |
Requirement 2: Process/Unprocess Expense Sheet lines individually
Num | Requirement | Importance | Status |
2.1 |
Expense sheet lines inside the same expense sheet header can be associated to order/invoice lines in different order/invoice headers. This is a great feature but the current mechanism to Process/Unprocess expenses has some problems. Process/Unprocess should only reflect a change from a draft/confirmed status for expense sheets. Nowadays, there is a button in Expense Sheet header to Process/Unprocess all the expense sheet lines. If one made a mistake in a processed expense sheet line and want to fix it, one has to unprocess all the lines in the same expense sheet header. This is something that it is not always possible since some lines could have been already associated to an order/invoice line.
Feature request 0004526: Process/Unprocess Expense Sheet lines individually https://issues.openbravo.com/view.php?id=4526 | Have Next |
Time Sheet Billing
Requirement 1: Delivery Organization 3
Num | Requirement | Importance | Status |
1.1 | Modify the processes that create documents (orders, invoices) from projects in order that the documents are created with the delivery organization value.
"Create Sales Order from Project Phase" in "Project Phase" tab: Orders created from phases and tasks have to respect phases and tasks delivery organization. If a task is delivered by organization A and another one by organization B, when clicking on "Create Sales Order from Project Phase" button two different orders have to be created. | Must have | |
1.2 | Modify the processes that create documents (orders, invoices) from projects in order that the documents are created with the delivery organization value.
"Create Sales Order from Project" in "Service Project" tab: Orders created from service projects have to be created with the delivery organization. | Must have | |
1.3 | Modify the processes that create documents (orders, invoices) from projects in order that the documents are created with the delivery organization value.
"Create Sales Orders from Expenses": if the project selector is left blank in the expense sheet line, the organization for the order created is the expense sheet line one. Otherwise, the organization for the order created is the delivery organization of the project selected. | Must have | |
1.4 | Modify the processes that create documents (orders, invoices) from projects in order that the documents are created with the delivery organization value.
"Create AP Expense Invoices": if the project selector is left blank in the expense sheet line, the organization for the invoice created is the expense sheet line one. Otherwise, the organization for the invoice created is the delivery organization of the project selected. | Must have |
Requirement 2: Project filter in billing processes
Num | Requirement | Importance | Status |
2.1 | While creating reimbursable expenses for employees (purchase invoices) or reinvoicing expenses for customers (sales orders), it would be useful that these processes differentiate projects since one employee or customer can be participating in more than one project.
References
| Must have |
Requirement 3: Grouping products when Create Sales Order from Expenses
Num | Requirement | Importance | Status |
3.1 |
Add a check-box called 'Group products in a single line' in 'Create Sales Orders from Expenses' in order to group products in time sheet.
Feature request 0010774: Grouping products when Create Sales Order from Expenses https://issues.openbravo.com/view.php?id=10774 | Have Next |
Time Sheet Reporting
Requirement 1: Delivery Organization 4
Num | Requirement | Importance | Status |
1.1 |
Modify the existing project reports - A new field called 'Delivery Organization' has been introduced in project settings. This change should reflect in all expense reports.So the organization selected in each of these reports will filter by project delivery organization value, not by project organization value.The existing reports are,
| Must have |
Requirement 2:
Num | Requirement | Importance | Status |
2.1 |
1) Pending Time Sheet Report - A new report should be added to view pending time sheets.
These will be generated and send to the employees/Managers in a weekly/Monthly basis. 2) Alert E-Mails - This E-mail will be triggered automatically when going some process.
| Have Next |
User Interface Mockups
Time Sheet Recording
Requirement 1: Delivery Organization 1
'Delivery Organization' is a new field to identify the target organization for expense sheets.This should be implemented in Service Project,Multiphase Project,Project Phase and Project Task. Field can be be implemented as its in the following mock-ups.
Service Project:
Multiphase Project:
Project Phase:
Project Task:
Requirement 3: Employee resources to projects, phases and tasks
Create a HHRR tab at a project level, at a phase level and at a task level so employees can be allocated at different levels.
Multiphase Project:
Project Phase:
Project Task:
Requirement 4: Which products should you be able to choose on a time sheet
A new tab has been introduced in projects(Multiphase Projects/Services) window with multi select product field, to select product(Type = Service/Expense) in expense sheet.This field should allow the products which has the 'Project Type' of Service/Expense.
Multiphase Project:
Service Project:
Requirement 5: Which products specified in a time sheet could be reinvoiceable
'Re-Invoicing' field needs to be added in Project windows(Multiphase Project,Service Project) to view this value in Expense Sheet lines.
Multiphase Project:
Service Project:
Requirement 6: Restrictions on hours recorded
A new tab under Organization(In Master Data Setup) has to be added, to set max. hours to be entered in Time Sheet.
Requirement 7: Which products should you be able to choose on a time sheet defined in project type
- A new tab in Project Type has to be created, this will work out in the process of 'Set Project Type' on creating new multiphase project.The products in Project Type has to be defaulted in 'Multiphase Project'(as Project Phase/Project Task)
Requirement 8: Rapid data entry 2: Calendar matrix like
Time sheet entry in a calendar matrix way will be a manual window with the following structure,if values are selected in header level.
Time sheet entry in a calendar matrix way will be a manual window with the following structure,if values are not selected in header level.
Time sheet approval
Muck-ups are given for requirements that have Importance like 'Must Have'.
Time Sheet Billing
Requirement 2: Project filter in billing processes
Add Project in filters like in the below mock-up:
Time sheet reporting
Muck-ups are given for requirements that have Importance like 'Must Have'.
Technical Requirements
Open Discussion Items
Closed Discussion Items
GRO
RRO: thank you very much for your contribution Galder. Please find my answers below.
Time reporting
This project forums on the forge do not work.
RRO: Can you participate now? http://forge.openbravo.com/plugins/espforum/browse.php?group_id=381&forumid=886644
Time sheet reporting should be faster and easier on one hand, and more validations in the other one. I mean:
Faster and easier: Creating a header and lines per day is too laborious. Taking into account time sheets are registered every week or every month (usually), having a kind of a spreadsheet would be much easier.
More validations: It does not make sense having enabled all the projects of the system. I mean, I should just be able to report to my assigned projects and generic projects. And not to any project in the company. Also, it could be interesting having default values for some fields.
RRO: This will be addressed via a delivery organization and human resources defined for each project. See areas of improvement TSREC001, TSREC002 and TSREC014.
A solution maybe a manual form with a month dropdown, where once a month is selected we get the days of the month horizontally. Vertically, we will get our assigned projects and the department's generic projects. This is already done by some partners.
RRO: Added to TSREC008 (Calendar matrix). See also TSREC009 (Google calendar).
Expense reporting
Expense report should be more automatic and flexible. I mean:
Automatic: Nowadays, expense report is not multycurrency. The user should be able to introduce the amount on a original currency and the applied ratio. The application should calculate the amount in Euros.
RRO: It is multi-currency since Openbravo ERP 2.40, see issue 0004546. Enter a bill amount in USD (Invoice Price field). If the conversion rate is correctly defined, Expense Amount will be quantity x invoice price (in USD) and Converted Amount will be in EUR (the entity base currency). And the customer will be billed in his price list currency.
Flexible: It can happend you are reinvoicing some expenses but you do not want to pay them to your employee (probably you pay them in advance).
RRO: This only applies to expense sheet lines, not to time sheet lines. You can always Create the AP Expense Invoice and void it.
Also it is a pain reinvoicing this expenses to a final customer. Right now, the relation most be 1 yo 1 between expenses and invoicelines. Sometimes I may have many expenses but I only want to print one line on the invoice. For example, think I have 10 people on the project and they all have taxi expenses. If I want to reinvoice them to my customer, I need to include 10 invoice lines. This is a mess. Additionaly, the reinvoicing process groups by description and is also a pain.
What do you think about?
RRO: I think that it is OK that taxi expenses are in different order lines, each with its description on what was the service used for and each with its bill price (if you mix them in a single line you do not distinguish how much you paid for each service). But I also agree with you that, in some cases, you may want to mix all this in a single order line. There should be a check-box in the Create Sales Orders from Expenses process that allows you to group the same products in single lines. Added to TSBIL003.