PROFESSIONAL SERVICES USE CASE: Microsoft Excel-based user interface (UI-Apps) in action
The Situation
A large interactive marketing and technology services agency with global operations selected Oracle E-Business Suite Projects Applications to streamline their project-based business. After using Oracle Project Costing and Oracle Project Billing to automate project financials in their business operations, they decided to implement Oracle Project Management to provide a robust project management tool for use by their Project Managers and Oracle Project Resource Management to automate business operations for resource management. Project Partners LLC was selected as the Implementation Partner for this work.
Why UI-Apps
Based upon this professional services firm’s business requirements, as Oracle Project Management was being evaluated for deployment to Project Managers, it became clear that users wanted the ability to work while in the field, not connected to the Internet. Project Partners proposed using Project Partners User Interface (UI-Apps) to provide an alternate user interface, and the Project Managers quickly warmed up to UI-Apps, which provide a Microsoft Excel-based interface with two-way interactivity to Oracle Applications and the ability to work in a disconnected mode.
The following functions were identified to be provided by UI-Apps for the firm’s Project Managers:
- Price New
- Projects Define
- Tasks Define Budget
- Define Staffing Plan
- Complete Project Forecasts
- Maintain Staffing Plan
Project Partners consultants worked closely with end users to develop two UI-Apps worksheets which together fulfill all of the functions above.
Template for Pricing Model
The worksheet below fulfills the functions of Pricing New Projects and Defining Tasks.
The worksheet above allows Project Managers to select a template and download task, labor and nonlabor resources, and associated cost and bill rate information to build a pricing model before the project is created in Oracle.
Budget Entry
The same worksheet also fulfills the Budget Entry functions of Defining a Budget and Defining a Staffing Plan.
The Project Manager selects tasks, Project Roles, and Expenditure Types to include on their projects from drop-down lists. After selecting tasks and resources, the Project Manager enters the quantities for labor and expenses. Bill rates can be overridden. During the entry of the quantity information, the margin amount and percent are automatically calculated based on the downloaded cost and bill rates. For fixed-price projects, after the budget is uploaded, the opportunity value entered at project creation replaces the calculated Fee amount.
After creating the budget, the Project Manager selects the associated project from the Project Name drop-down list and either uploads the budget for creating a working version or submission to the workflow for review and approval (Baseline).
UI-Apps perform the following actions using Oracle’s public APIs for Oracle Project Management (PJT):
- Create Tasks with all required attributes, i.e. Transaction Controls, Service Type (if the template is used for new projects)
- Setup and define the budget
- Submit the budget for approval
- Setup Staffing plan
- Project Requirements for Roles with no specific person identified
- Submit requirements to resource managers for staffing
- Attach a copy of the spreadsheet to the project
If the budget needs to be updated, the project manager can simply change the worksheets stored on their local desktop or laptop and resubmit the data, which would update and submit the current working version of the budget to establish a new baseline. Alternatively, if the PM has not retained the original worksheet, they can start a new Excel file and simply download the project and budget information once again and make changes as needed.
When the budget has been baselined, a forecast is automatically created from the approved cost budget and downloaded to the worksheet.
Forecast Entry
The second worksheet used by this professional services firm is for Forecasts. It fulfills the functions of:
- Entering Estimates to Complete
- Submitting Forecasts, and
- Terminating assigned resources or assigning new resources
Each Forecasting Period, the Project Manager downloads the latest information for their projects along with actual cost information (in disabled – grayed out – columns) from Oracle into the Forecast Entry spreadsheet.
As shown above, fulfilled project roles will show the person assigned, employees in the employee section, and contract personnel in the contingent worker section.
Project Managers then update the Estimate To Complete (ETC) columns. Existing resources are terminated by not entering any additional hours for them, and new rows are added for new resources. This activity can be performed in connected or disconnected modes. The Project Manager then submits the forecast. UI-Apps will take the submitted information and perform the following functions:
- Update the forecast and submit it for approval
- Update all existing assignments with new hours
- Terminate assignments with no future hours
- Create new resource requirements
- Submit updated and new requirements for approval/staffing
In Conclusion
UI-Apps Microsoft Excel worksheets will be the primary interface used by Project Managers for interacting with Oracle Project Management and Resource Management. The firm’s management and IT staff are very confident of system acceptance from the Project Managers primarily because of UI-Apps’ capability to provide their PMs with an interface that they are already very comfortable using and which allows them to work remotely from client sites and on the road without worrying about network connectivity. Watch this video series to learn more.
