Using User Interface Applications™ to help Project Managers smoothly manage capital project budgets and forecasts across Oracle E-Business Suite applications.

The Situation

The fourth largest international Oil and Gas exploration and production company and Oracle’s largest EBS customer in the Oil and Gas industry was looking to consolidate and standardize across all their locations to one instance running Oracle E-Business Suite R12. With more than 10,000 employees and 20,000 contractors on four continents, global deployment of a streamlined, robust, and standardized project management solution was of paramount importance. The corporation’s operations in some countries were up and running using Oracle Project Costing and Oracle Project Management (PJT), and the operations in other countries awaiting deployment were in the process of sending their users to Oracle training. The general consensus was that Oracle E-Business Suite Projects was the right fit to meet their global needs, but users became immediately aware of the painful data entry involved in using the Oracle HTML screens.

This company’s project managers needed daily visibility and control of capital project costs and prompt recognition of excessive expenses. Pertinent capital projects information such as capital projects cost, percent spent, and expenses summarized by the organization, project classification, expenditure category, expenditure type, work type, and time are just a few of the requirements they needed to globally manage their capital projects.

Oracle Projects, with its wide range of capital project functionality and fixed assets integration, was the perfect fit to handle the complex needs of this capital-intensive company. Such vast functionality, however, comes with an extensive learning curve to master the robust functionality and an enormous increase in the time needed to perform simple everyday tasks. With over 20 Oracle Project Management screens needing to create a simple budget or a forecast, users were turning to their own budgeting and forecasting systems to get their project planning done. This was not the globally standardized, streamlined approach this company aimed for.

This firm’s project managers needed a more integrated, easy-to-use collaborative entry model for task maintenance, budgeting, task level progress collection, and estimate to complete forecasting that can be used from anywhere in the world, regardless of network connectivity. They wanted an easy way to manage complex projects.

Why UI-Apps

Based upon the needs of this capital-intensive company, UI-Apps quickly became the ideal easy-to-use, fully integrated, out-of-the-box solution for their project managers. UI-Apps allow their project managers to manage their large work breakdown structures (WBS), define project budgets, track capital costs, and perform weekly cash flow forecasting. Upon installation, UI-Apps worksheets were configured (by their IT staff requiring no special skills) with their company logo (specific to the organization using the sheet), their desired colors and layout, and to show only the data fields they needed to display for quick and efficient project planning. In the case of tracking construction-in-process (CIP) costs, the budget and forecasting worksheets were simplified for the project managers to display only the cost columns. This made for a clean, simple, easy-to-use worksheet.

“We have everything we need right here in one worksheet. It is nice not to have to navigate from screen to screen to screen to accomplish a simple project budget.”

Project Management User

Project Managers were able to quickly benefit from the Microsoft® Excel-based interface with two-way interactivity to Oracle Applications and welcomed the ability to work in a disconnected mode from anywhere – a gas pumping station, an oil loading dock, an oil field, or a refinery. Their most up-to-date project costs could be reviewed and maintained, regardless of where they work.

Using UI-Apps, their project managers can now perform all of the following functions from one Microsoft Excel workbook:

• Create, Define, and Maintain Work Breakdown Structures, including indent and outdent capabilities and task modifications once saved
• Create, Define, and Maintain Project Workplans and Schedules
• Create, Define, and Maintain Budgets
• Collect Progress
• Review Capital Project Costs/Actuals
• Complete Weekly Project Financial Forecasts

Capital Task & Budget Management

The ability to create a work breakdown structure using one familiar Excel worksheet or to download the WBS and maintain it if previously created in Oracle, combined with the ability to integrate with both the financial plan and work plan, was a tremendous timesaver for their project managers. This company manages thousands of projects with over 10,000 organizations and cost centers involved. The duration of their projects can span from as short as one year for a small station to upwards of ten years for a refinery, oil field, etc. The task structures for these projects can become quite complex and multi-leveled. The ease of the UI-Apps worksheets has made this task much more manageable.

Their budget management also became much more simplified. Without navigating through the many financial planning HTML screens in Oracle, the cost budgets are created or maintained right in UI-Apps. Project managers can plan their capital costs and expenditures using fully burdened cost rates from Oracle that are downloaded into their worksheets.

Using the Task and Budget worksheet above, project managers select their desired project and plan type from the list of projects they have access. Due to the large number of projects, operating units, and cost centers involved in managing the capital project expense for this large company, an enhanced project search filter was built to filter the large amounts of project data by project name, number, key member, operating unit or cost center. Once the project is downloaded, all budget lines, labor, and non-labor resources, along with their associated cost information and their associated list of values and drop downs, are also downloaded from Oracle directly into the worksheet for easy online and offline use.

Task management options in this worksheet allow project managers to add or select tasks, add associated task attributes and task information, maintain WBS by indenting and outdenting the task location in the structure, and add both labor and non-labor resources to their budget plan. After selecting tasks and resources, quantities are entered for labor and expenses. The associated cost and bill rates have already been downloaded with the project and can be overridden if necessary. Using the time-phased functionality provided by UI-Apps gives project managers the option of entering total plan amounts for each period individually, or they can use any of the various spread methods implemented to automatically spread across all periods or only specific periods within the task and resource dates of their project.

Due to the complex nature of this company’s capital budgeting model, several unique spread mechanisms were implemented in addition to those already pre-defined in UI-Apps. Additionally, due to the long duration of many of the projects at this company, the creative use of period profiles was implemented. UI-Apps worksheets can display data for up to 36 time periods, but by using the period profile already defined in Oracle Project Management, additional periods for longer projects are grouped into preceding and succeeding periods. For example, periods before the current planning date are displayed in a column called Preceeding Periods, current and near-term periods are displayed by month, out-term periods by quarter, and all other future periods are grouped into a Succeeding Periods column.

Project managers can easily adjust their view of the project’s periods by simply changing the current planning date, and the worksheet will automatically display this preferred view. This provides great flexibility to the project managers regarding how their project data is displayed. Once their changes have been made or their budget is complete, the project plan is either saved as a working version or submitted for baseline. UI-Apps automatically follow the workflow rules set forth by their business processes, as established in Oracle Applications.

Upon submission, UI-Apps perform the following actions using Oracle’s public APIs:

• Create tasks with all required attributes (i.e., transaction controls, task status, billable flags, chargeable flags, service type)
• Create and define the budget with resources, quantities, and amounts
• Update project work plan and publish the workplan
• Submit the budget for approval – working or baseline
• Attach a copy of the spreadsheet to the project

For future budget updates, project managers have the choice to use the worksheet stored on their local desktop or laptop, or alternatively, if they have not retained the original workbook, a new Excel file can be started, and the project, WBS, and budget information are downloaded once again to make changes as needed. Project managers can download the project budget or forecast for review or update. If the project has already been downloaded for the update, UI-Apps locks the worksheet and prevents updates to the worksheet while in use by other users. The project budget or forecast is always available to download for review. The project budget (and/or forecast) can also be attached to their project in Oracle as a project attachment.

Once the baseline version has been created, project managers can choose to have their forecast automatically generated from the approved cost budget and downloaded to the worksheet or generate the forecast from their prior forecasts.

Forecasting Capital Projects

Using the Time Phased Forecasting worksheet was a real benefit to project stakeholders in this company. The ability to perform weekly forecast updates provides an up-to-date view of project percent complete totals. With many contractors worldwide working on each project and lots of money being spent, it is very important for this company to be able to monitor actual costs extremely closely. Weekly forecasts help to manage their CIP cash flow, and they are better able to fund such expensive project endeavors. The result of the UI-Apps forecasting implementation and its integration into their project work plan provides greater visibility into their capital expenditures, thereby resulting in significant cost reductions and much more accurate project forecasts.

The Forecasting worksheet allows their project managers to:

• Review Capital Project Costs
• Enter Estimate-to-Complete and Forecast (EAC) Values
• Review Project Budget and Forecast Variances
• Submit New Financial Forecasts
• Attach Forecasts as Attachments to the Project in Oracle

For each forecasting period, project managers download the latest project information, including actual cost information (in read-only capability) from Oracle, into the forecasting worksheet. Estimate-to-Complete (ETC) amounts are calculated by default from the displayed budget amounts but are easily overridden if desired. ETC values default into the worksheet from different sources – approved budget, prior forecast, and the standard Oracle-generated ETC values. ETC amounts and work plan progress are updated by the user at the task and/or resource level for existing tasks or for new tasks and/or resources. These values are entered once and integrated with their work plan and project and task schedule dates. Percent complete and ETC amounts from the forecast and resource assignments are also integrated right into their work plan.

Due to the configurability of UI-Apps, several departments within the company have combined variations of the budgeting and forecasting worksheets into different workbooks to meet the demands of their differing business processes. UI-Apps worksheets also provide summary data for project managers to quickly view their budget and forecast variances. At any given time, they have access to the latest information regarding their projects and can make any necessary changes “on the go” and submit those changes back to Oracle once connected. The ease of UI-Apps keeps project managers focused on managing their projects rather than time-consuming, multi-page, multi-step data entry. Timely project information is crucial and is only kept up to date if it can be maintained quickly and efficiently.

Task and Resource Cost Summary Reporting

Monitoring costs and capital expenses for projects is key for this company. To keep a close eye on planned and actual costs, users can view their summary project costs using the Task and Resource Cost Summary Worksheet. This worksheet displays project inception-to-date costs broken down by tasks and provides a view of either budget amounts or actual amounts for up to 25 expenditure categories rolled up by WBS and totaled for each task. This worksheet is part of their workbook and is a quick and easy tool to view up-to-date amounts for their projects simply by running the report right on the worksheet.

In Conclusion

User Interface Applications is an application suite delivering easy-to-use, easily tailored Microsoft Excel worksheets pre-configured to work with Oracle Projects Applications. UI-Apps provide an out-of-the-box solution for Professional Services. UI-Apps workbooks provide a user-friendly front-end that meets industry business needs while seamlessly integrating with Oracle security and business logic. It also provides business users with an intuitive way to accomplish complex tasks using a familiar yet powerful tool: Microsoft Excel. Watch this video series to learn more.