Understanding the Financials Warehouse

This chapter provides an overview of the Financials Warehouse, the Financials Warehouse structure, and the Financials Warehouse components.

Financials Warehouse Overview

PeopleSoft Financials Warehouse is a comprehensive reporting and analysis platform. It includes a multidimensional warehouse (MDW) infrastructure for operational and near real-time reporting. It also contains packaged marts that provide a key multidimensional analysis and reporting environment. These marts help you understand the profitability trends and issues that your organization is facing.

With PeopleSoft Financials Warehouse, you can review data against organizational metrics and perform strategic analyses to identify and track the multiple dimensions that affect profitability, and identify key performance drivers, trends and opportunities, and so on. You are able to organize information into several easy-to-manipulate dimensions, including: Business Unit, Account, Time, Department, and Transaction Party, as well as take action on those issues that will most directly optimize business performance and influence profitability with measures and derived metrics such as debt-to-equity ratio, quick asset turnover, working capital-to-assets ratio, and receivables and payables turnover.

PeopleSoft delivers the PeopleSoft Financials Warehouse complete with extract, transform, and load (ETL) maps, data models, data marts, and a library of key profitability metrics. PeopleSoft Financials Warehouse combines the various dimensions, measures, and limited derived metrics into prepackaged financial-specific analysis and sample reports. PeopleSoft Financials Warehouse also includes the tools that enable modifying or adding to the predefined reporting content

Note. For details of dimensions, see the PDF file that is published on CD-ROM with your documentation.

Financials Warehouse Structure

PeopleSoft marts are subsets of PeopleSoft Financials Warehouse and are the foundation for the reporting applications. Marts are subject-specific relational schemas created for a particular group of users for multidimensional analysis and reporting. Each core functional area of the warehouse has predefined marts. PeopleSoft Financials Warehouse includes the General Ledger and Profitability Mart, Receivables Mart, Payables Mart, Enterprise Service Automation (ESA) Mart, Real Estate Mart, and Advanced Cost Accounting Mart.

Financials Warehouse marts and business processes

Each mart is associated with a business process that helps you to answer the questions you need to keep your organization robust and ahead of its competition. With each mart, we present the fact tables that will help you answer your critical business questions.

Note. Although we list only the actual fact name, all facts that represent dollar amounts have three facts in the fact table, one for each possible selected reporting currency. Populating the facts for alternate currencies is optional.

For additional details of facts, see the PDF file that is published on CD-ROM with your documentation.

This section discusses:

• Receivables Mart

• Payables Mart

• General Ledger and Profitability Mart

• Enterprise Service Automation (ESA) Mart

• Real Estate Mart

• Advanced Cost Accounting Mart

Receivables Mart

Accounts receivable are often the largest asset on a company’s balance sheet and are critical to its financial health. While extending credit to the customers helps boost sales, it also increases the cost of working capital and financial risk, especially in challenging economic times.

You use the Receivables Mart to analyze your company’s receivables. With it you can perform multidimensional analysis against your receivables activities and receivables information to see how much is due from customers, view accounts receivable transactional activities, analyze customer credit worthiness and risk, and conduct account receivable account level analysis.

Receivables Mart helps you identify areas within your organization contributing to higher days sales outstanding, increased credit risk, poor collection performance, or more bad debt write-offs. In essence, it helps you better understand your receivables asset and identify areas for improvement within your organization that will enable you to improve the quality of your accounts receivable and reduce the cost of working capital.

Order Fulfillment Business Process

Receivables Mart is related to PeopleSoft’s Order Fulfillment business process, which is also known as Order to Cash. The Order Fulfillment business process fulfills an organization's requirements for capturing, fulfilling, and settling goods sold. With the Order Fulfillment business process, you capture, confirm, and manage sales orders and contracts, deliver goods or services, and then invoice, collect, and resolve payment. The Order Fulfillment business process also helps you to manage returns and inventory, process customer payments, and maintain profitable customer relationships. The information captured within the Order Fulfillment business process helps you determine answers to questions such as “What are my cost per sales?”, “What is my ending receivables balance?” and “What is my bad debt compared to sales?”

Process Customer Payments is the business sub-process related to the Receivables Mart, where you receive and apply customer payments, maintain customer balances, manage past due accounts, and resolve payment discrepancies.

Receivables Delivered Fact Tables

With Receivables Mart PeopleSoft provides the following fact tables:

• AR Aging

• AR Transactions

• Days Sales Outstanding

• Credit Limit Analysis

• AP-AR Netting Analysis

• AR Account Line Analysis

AR Aging Fact Table

The AR Aging fact table describes aging information by aging category and customer. With this fact group, you can answer questions such as “What money is owed in this period?” and “What percentage is past due?”, as well as determine the aging schedule of customer payments.

• This is a destructively loaded fact table.

• The data is extracted from the AR transactions.

• Data is aggregated and aged to populate this fact table.

  This table describes the AR Aging fact table facts:

  Fact Name	Fact Description
  Balance Amount	Amount that is outstanding for that aging category. Use this fact to answer questions such as “What is the highest balance for a customer?”, “What is the highest past due for a customer?” and “What are the aging trends of my accounts receivable?”

AR Transactions Fact Table

The Transactions fact table presents the summary of transactional information at a transaction header level. This fact group helps you determine factors such as the AR Transactions Performance metrics, for example transaction amounts, discount amounts, and so on. With this fact group, you can answer questions such as “What are the total number of AR invoices and AR payments?”, “What is the total AR invoice amount and total AR payment amount by customer?” and “What is the current status of the AR transactions?”

• This is an incrementally loaded fact table.

• This table both inserts and updates existing records based on the underlying AR transactions.

  This table describes the AR Aging fact table facts:

  Fact Name	Fact Description
  Transaction Amount (For AR, this is the item amount)	Total transaction amount. With this fact, you can answer questions such as “What is the transaction activity for a customer for a specific period of time?” and “What is the invoice, adjustment and payment history for a customer?”
  Discount Eligible 1 (Different discounts may apply based on the date payment was received.)	Discount eligible as per payment terms. With this fact, you can answer questions such as “What is the discount eligibility for a customer?”
  Discount Eligible 2 (Different discounts may apply based on the date payment was received.)	Discount eligible as per payment terms. With this fact, you can answer questions such as “What is the discount eligibility for a customer?”
  Discount Taken Amount	Discount taken by the customer during payment. With this fact, you can answer questions such as “What is the discount usage for a customer?”
  Adjustment Amount	Adjustment transaction amount. With this fact, you can answer questions such as “What is the total adjustment amount for a customer?”
  Dispute Amount	Amount in dispute. With this fact, you can answer questions such as “What is the dispute amount for a specified customer in a given time period?”
  Earned Discount Amount	Discount earned by the customer. With this fact, you can answer questions such as “What is the earned discount amount for a specified customer in a given time period?”
  Amount Taken as Discount	Discount taken by the customer during payment. With this fact, you can answer questions such as “What is the amount deducted as discount by a customer when payment is made?”
  Unearned Discount Amount	Unearned discount by the customer during payment. With this fact, you can answer questions such as “What is the unearned discount amount for a specified customer in a given time period?”
  Balance Amount	Amount outstanding from the customer. With this fact, you can answer questions such as “What is the remaining balance amount for a particular AR invoice transaction for a specified customer?”
  Payment Amount	Payment amount towards this AR item. With this fact, you can answer questions such as “What is the payment amount made by a customer in a given time period?”
  Discount Amount	Total discount amount applied towards this AR item. With this fact, you can answer questions such as “What is the discount amount for that payment transaction for a specified customer in a given time period?”
  Discount Available Amount	Total discount available amount available towards this AR item. With this fact, you can answer questions such as “What is the available discount amount for that payment transaction as per the payment terms for a specified customer in a given time period?”
  Original Discount Amount	Original discount amount in the AR item. With this fact, you can answer questions such as “What is the original discount amount for that payment transaction as per the payment terms for a specified customer in a given time period?”

Days Sales Outstanding Fact Table

The Days Sales Outstanding (DSO) fact table contains information about DSO data for the accounting period selected. With this fact group, you can answer questions such as “What is the days of sales outstanding?” and “How has this changed over time?”

• This is a destructively loaded fact table.

• Data is extracted from the AR transactions.

• Data is aggregated as defined by the AR DSO process specification of the AR source system.

  This table describes the fact in the Days Sales Outstanding fact table:

  Fact Name	Fact Description
  DSO Amount	The days sales outstanding is an important financial indicator showing both the age, in terms of days, of a company's accounts receivable and the average time it takes to turn the receivables into cash. With this fact, you can answer questions such as “What is my DSO by customer?”

Credit Limit Analysis Fact Table

The Credit Limit Analysis fact table contains information about the credit worthiness and credit allocated to a customer based on the customer’s credit rating. This fact group helps you answer questions such as “What is my available credit and credit limit by customer?”

• This is a destructively loaded fact table.

• The data is extracted from the AR Aging Analysis fact table and the credit attributes of a customer.

• The currency conversion is based on the currency code for Credit Limit Amount, rather than the transaction currency.

  This table describes the facts in the Credit Limit Analysis fact table:

  Fact Name	Fact Description
  Customer Credit Percentage Over Limit	Percentage by which customers are allowed to exceed their credit limits before hold processing takes effect. With this fact, you can answer questions such as “What is the customer credit limit percentage over limit and how much has the customer actually exceeded this credit limit range?”
  Corporate Credit Percent Over Limit	Percentage by which corporate customers are allowed to exceed their credit limits before hold processing takes effect. With this fact, you can answer questions such as “What is the customer credit limit percentage over limit and how much has the customer actually exceeded this credit limit range?”
  Customer Credit Limit Range	Percentage to represent a comfort level that is relative to the customer’s credit limit. With this fact, you can answer questions such as “What is the customer credit limit range and does the pending AR outstanding credit fall within this range?”
  Corporate Credit Limit Range	Percentage that represents a comfort level that is relative to the corporate customer’s credit limit. With this fact, you can answer questions such as “What is the customer credit limit range and does the pending AR Outstanding Credit fall within this range?”
  Actual Customer Credit Limit Range	Actual customer credit limit range. With this fact, you can answer questions such as “What is the actual customer credit limit?” and “What is the outstanding AR amount and by what percentage is the AR outstanding above or below the credit limit?”
  Actual Corporate Credit Limit Range	Actual corporate credit limit range. With this fact, you can answer questions such as “What is the actual customer credit limit?” and “What is the outstanding AR amount and by what percentage is the AR outstanding above or below the credit limit?”

AR-AP Netting Analysis Fact Table

The AR-AP Netting Analysis fact table contains information about the AR amounts outstanding of a customer and the AP amount outstanding from the company to that customer (if that customer is also a supplier to the company). This provides a net total for the customer or supplier. With this fact group, you can answer questions such as “What is net balance for a customer or supplier?”

• This is a destructively loaded fact table.

• Data is extracted from the AR Aging fact table and the AP Aging fact table for that customer or supplier.

  This table describes the facts in the AR-AP Netting Analysis fact table:

  Fact Name	Fact Description
  AP Outstanding Amount	Amount that is outstanding to suppliers for that aging category. Use this fact to answer questions such as “What is the highest balance for a supplier?”, “What is the highest past due for a supplier?” and “What are the aging trends of my accounts payable?”
  AR Outstanding Amount	Amount that is outstanding and to be paid by the customer. Use this fact to answer questions such as “What is the outstanding balance for a customer with respect to the customer’s credit limit?”
  Available Credit Limit	Difference between the AP Outstanding Amount and AR Outstanding Amount. With this fact, you can answer questions such as “What is the AR-AP Netting Balance Amount for customers who are also suppliers?” and “What is the AP and AR Balance for a customer who is also a supplier?”

AR Account Line Analysis Fact Table

The AR Account Line Analysis fact table contains all AR accounting entries posted by the AR module.

This table describes the fact in the AR Account Line Analysis fact table:

Payables Mart

You use Payables Mart to report and analyze the performance of your suppliers and your payables department. Payables Mart includes information about suppliers, vouchers, match exceptions, and payments. This mart helps you to assess your supplier relationships by answering questions such as "Which suppliers have billing errors?" and "How long does it take us to pay suppliers?" With this information, you can ask suppliers for better service or negotiate with them for better terms. To measure performance of your payables department, this mart helps answer questions such as "What are my accounts payable aging balances?", “Does my organization take advantage of supplier discounts?” and "How many vouchers are entered per day?" You can analyze results across many dimensions, enabling you to compare performance of multiple payables departments or even individual employees. After you understand current performance levels, you can set targets and monitor results.

Payables Mart includes data needed to calculate standard metrics such as Days Payable Outstanding, Accounts Payable Turnover, and Sales to Accounts Payable Ratio. With this information, you can measure your organization against external benchmarks. You can baseline your own results and then work towards improving payables performance over time, as specified by your overall corporate strategy.

Procurement Business Process

Payables Mart is tied to PeopleSoft’s Procurement business process, which is also known as the Source to Settle business process. The Procurement business process fulfills an organization's requirements for sourcing, engaging, procuring, and settling payment for goods, services, or both. The Procurement business process enables you to determine profitability sourcing strategies, collaborate with suppliers, and drive efficient procurement and settlement for all goods and services.

Pay Supplier is the business sub-process related to Payables Mart, which aides you in areas such as creating and matching vouchers, resolving disputes with suppliers, and creating payment.

Payables Mart Delivered Facts

With the Payables Mart, PeopleSoft provides the following fact tables:

• Aging Analysis

• AP Transactions

• AP Account Line

• Voucher Match Exception

Aging Analysis Fact Table

The Aging Analysis fact table contains the aging information by aging category and supplier. With this fact group, you can answer questions such as “What money is owed in this period?” and “What percentage is past due?” and also determine the aging schedule for supplier payments.

• This is a destructively loaded fact table.

• Data is extracted from the AP Transactions.

• Data is aggregated and aged to populate this fact table.

  This table describes the facts in the Aging Analysis fact table:

  Fact Name	Fact Description
  Gross Amount	Amount that is outstanding to suppliers for that aging category. Use this fact to answer questions such as “What is the highest balance for a supplier?”, “What is the highest past due for a supplier?” and “What are the aging trends of my accounts payable?”
  Discount Amount	Amount that is available as discount as per the terms of the voucher. Use this fact to answer questions such as “What is the total discount amount available in AP Vouchers?”
  Prepaid Amount	Amount that was prepaid against this AP Voucher. Use this fact to answer questions such as “What is the total prepaid amount to a specified supplier?”
  Total Amount	The result of Gross Amount − Discount Amount − Prepaid Amount. Use this fact to answer questions such as “What is the total AP outstanding amount to be paid to a specified supplier?”

AP Transactions Fact Table

The AP Transactions fact table contains the summary of transactional information at a transaction header level. With this fact group, you can answer questions such as “What are the total number of AP vouchers and AP payments?”, “What is the total AP voucher amount and total AP payment amount by supplier?” and “What is the current status of the AP transactions?”

• This is an incrementally loaded fact table.

• This table both inserts and updates existing records based on the underlying AP Transactions.

  This table describes the facts in the AP Transactions fact table:

  Fact Name	Fact Description
  Transaction Amount (for AP, this is the payment amount).	Total transaction amount. With this fact, you can answer questions such as “What is the transaction activity for a supplier for a specific period of time?” and “What is the voucher, adjustment and payment history for a supplier?”
  Discount Eligible	Discount eligible as per payment terms. With this fact, you can answer questions such as “What is the discount eligible to be deducted if AP vouchers are to be paid as per due date?”
  Adjustment Amount	Adjustment amount to the original AP voucher amount. Use this fact to answer questions such as “What is the adjustment amount in the AP Transactions?” and “How efficient is the AP process?”
  Paid Amount	Amount paid to the supplier in the AP payment transaction. With this fact, you can answer questions such as “What is the total amount paid in AP payment transactions?” and “What is the AP amount paid to a specified supplier during a period of time?”
  Open Amount	Amount pending payment to the supplier for an AP voucher transaction. With this fact, you can answer questions such as “What is the total pending AP amount for a particular supplier?”
  Gross Paid Amount	Gross amount paid to the supplier in the AP payment transaction, including items such as tax. With this fact, you can answer questions such as “What is the AP gross paid amount to suppliers during a specified time period?”
  Discount on Paid Amount	Deducted discount amount applied to the paid amount in the AP payment transaction. Use this fact to answer questions such as “What is the total amount claimed as discount during a specified time period?” and “What is the amount saved as discount over time?”
  Discount Lost on Paid Amount	Amount that could have been deducted if the AP voucher were paid on time. Use this fact to answer questions such as “What is AP amount lost as discount over time?”
  Late Charge Amount	Amount identified as late charge to the supplier in the AP payment transaction. Use this fact to answer questions such as “What is the total late charge amount to a particular supplier over time?”
  Late Charge Denied Amount	Amount that is not considered as late charge in the AP payment transaction. Use this fact to answer questions such as “What is the late change denied amount to a particular supplier?”
  Late Charge Paid Amount	Amount paid as late charge to the supplier in the AP payment transaction. Use this fact to answer questions such as “What is the total late change paid amount to a particular supplier over time?”

AP Account Line Fact Table

The AP Account Line fact table contains all of the AP accounting entries posted by the AP module. With this fact group, you can answer questions such as “What are the posting details of the AP transactions to GL in a specified time period?”

• This is an incrementally loaded fact table.

• Because it is extremely granular, this table may contain millions records.

  This table describes the fact in the AP Account Line fact table:

  Fact Name	Fact Description
  Journal Amount	Journal amount in the accounting entry in base and transaction currency. With this fact, you can answer questions such as “What is the journal amount posted to the GL for the selected AP account by AP transactions?”

Voucher Match Exception Fact Table

The Voucher Match Exception fact table contains information about all failed transactions of the AP matching process. With this fact group, you can answer questions such as “What is the number of exceptions generated by the AP voucher matching process by supplier and AP operator ID?”

• This is a destructively loaded fact table.

• The data is extracted from the AP Matching Process Exception table and is aggregated by Matching Rule.

  This table describes the fact in the Voucher Match Exception fact table:

  Fact Name	Fact Description
  Match Exception Count	Total number of match exceptions occurrences during the AP processing. With this fact, you can answer questions such as “What is the count of voucher matching exceptions generated for a supplier by an AP operator ID?”

General Ledger and Profitability Mart

General Ledger and Profitability Mart helps you analyze things such as the condition of assets, liabilities, and equity of an organization, as well as the profit and loss position of an organization. This mart also enables your organization to compare results for a period across fiscal years. Additionally, it helps you determine which customers, products, and channels are most profitable and why. With it, you are able to rank customers based on revenue, gross margin, net income, or total expenses. You can identify changes in product mix, analyze profitability trends over time, and learn how each factor impacts costs, profit, and ultimately, the value of your organization. If you have purchased PeopleSoft Global Consolidations, you can access this information through the Profitability Mart.

Global Consolidations introduces consolidation by using flows that enable you to track and reconcile the gross variation of an account. Gross variation is the difference between the opening and closing balances of an account. For example, the gross variation of a fixed asset account can be distinguished by additions, disposals, deprecation, asset impairment, current translation, and reclasses. Data flow of specific accounts is required as part of regulatory reporting, such as Security and Exchange Commission, International Accounting Standards, and so on. This feature enables you to identify and document the change in account activity, meet certain regulatory requirements, and provide financial statement footnotes.

Note. The Global Consolidation subject area is primarily sourced from the Operational Warehouse Enriched (OWE) tables GC_CLED_MGT_F00 and GC_FLOW_MGT_F00, which are the final output tables of the Global Consolidation application. The Profitability subject area is sourced from the OWE table PF_LEDGER_F00.

Financial Control and Reporting Business Process

PeopleSoft Financial Control and Reporting business process fulfills an organization's requirements for recording, settling, consolidating, and reporting enterprise financial transactions. Using the Financial Control and Reporting business process, you capture and record financial transactions, transform financial information, allocate costs and adjust currencies, and close your books. You can use this information to report your results and to analyze your financial information, as well as to adjust your budgets, business events, and strategic plans. The Financial Control and Reporting business process helps you answer questions such as “How is my company performing against budget?”, “How is my company performing against previous years?”, What is my company’s cash flow position?” and “How strong is my company’s liquidity and solvency?”

Analyze Financial Information is the business sub-process tied to the General Ledger and Profitability Mart, which aids you in analysis of areas such as creating allocations, revaluing balances, performing eliminations, recording period end adjustments, and consolidating data.

General Ledger and Profitability Mart Delivered Fact Tables

With General Ledger and Profitability Mart PeopleSoft provides the following fact tables:

• Profitability

• GC Ledger

• Ledger

• Journal

• GL Beginning Balance

• GC Flows

Profitability Fact Table

The Profitability fact table contains ledger level facts sourced from PF_LEDGER_F00, which is created by various processes such as PeopleSoft Allocation Manager and PeopleSoft Activity Based Management. This fact table helps you answer questions such as “What is the activity level for specific business activities, business units or entities?”, “ What is the relative activity for the business activities, business units or entities across time, within or across fiscal years?”, “ What is the trend of spending for specific expense line items across specific organizational units?” and “ What is the revenue trend for specific products or services within a region or across organizational units?”

This table describes the facts in the Profitability fact table:

Note. The Net Period Amount is the amount provided on the ledger. The Beginning Period Amount and Ending Period Amount are provided to facilitate reporting. For example, assume the net amount for January is 10. This number is of limited use for reporting, as it must be considered in relation to the beginning amount. Assuming the beginning amount is 100, you add 10 (the net amount) to get 110 (the end amount). With these three amounts, you can create a wide variety of reports, although they all help to answer the same business questions. Also, be aware that the end amount of a period becomes the beginning amount for the next period.

GC Ledger Fact Table

The GC Ledger fact table contains the consolidated results from all of your sources. With this fact group, you can answer questions such as “What are my aggregate assets for all of my source ledgers?”

This table describes the facts in the GC Ledger fact table:

Ledger Fact Table

The Ledger fact table contains posted journals aggregated to the period level. Period beginning, ending, net, and three-month rolling ending average amounts are provided. This fact group helps you answer questions such as “ How much revenue was recognized this period?”

This table describes the facts in the Ledger fact table:

Journal Fact Table

The Journal fact table contains the lowest level of detail. All journals that are posted are available for analysis at the journal line level. The Journal fact table helps provide an audit trail for activity affecting the ledger.

This table describes the fact in the Journal fact table:

GL Beginning Balance Fact Table

The GL Beginning Balance fact table contains facts at the fiscal year level. Period beginning, ending, and net amounts are available. Requested budget, approved budget, and final budget amounts are also available. The GL Beginning Balance fact table helps answer questions such as “What is the variance between prior year actuals and current year budget proposals and final data?” You can use this information to provide financial facts for the budget preparation process.

This table describes the facts in the GL Beginning Balance fact table:

GC Flows Fact Table

The GC Flows fact table contains all the flows activities entered. With this fact group, you can answer questions such as “What is the breakdown of a fixed asset account by additions?”

This table describes the facts in the GC Flows fact table:

Analyzing projects and contracts is an increasingly important, yet difficult task in the growing services economy. Whether you are part of a professional services organization, an internal IT department, a research organization, or a capital project accounting department, Enterprise Service Automation (ESA) Mart helps you analyze project performance; contract profitability; resource utilization; and time and expense compliance.

ESA Mart includes data that you need to calculate standard metrics such as Proposal Pipeline, Project Profitability, Contract Revenue Recognized, Estimate to Complete, Resource Utilization, and Earned Value. With this information, you can measure your organization against external benchmarks. You can create a baseline for your own results, and then work towards improving project performance over time, as specified by your overall corporate strategy.

Project Management Business Process

ESA Mart is tied to PeopleSoft’s Project Management business process. The Project Management business process fulfills an organization's requirements for the delivery of professional or internal services, such as planning, prioritizing, and managing resources and projects. If customer focused, this business process includes managing contracts, as well as contract changes, billing, and revenue information. Both the customer and internally focused process includes capturing project costs, adjusting projects, and settling expenses, and so on.

The information captured within the Project Management business process helps you determine answers to questions such as “How is my project performing?”, “Is this contract profitable?”, “What is the organization’s resource utilization?” and “How much of the corporate expenses are compliant with policy?”

Create Project, Execute Project, and Assign Resources to Project are business sub-processes related to ESA Mart. These business sub-processes aid you in areas such as creating projects and tasks, defining budget and resource requirements, tracking projects, and adjusting transactions, resources, scope and schedules, as well as creating service orders and assigning resources.

ESA Delivered Fact Tables

ESA Mart is divided into the following subject areas and the following fact tables:

• Project

  • Project Transaction

  • Project Activity (Current and Periodic)

  • Project (Current and Periodic)

  • Project Deliverable

  • Project Issue

  • Project Change

• Proposal and Grant — Proposal and Grant Transaction Detail

• Resources

  • Assignment

  • Employee Forecast

  • Resource Rate

  • Service Order

• Expense

  • Time Report Daily Detail

  • Expense Distribution Transaction Detail

  • Expense Report Approval

• Contracts

  • Contract Transaction Detail

  • Contract Distribution Transaction Detail

  • Contract Revenue Recognition

  • Contract Amendment

  • Contract Forecast (Current and Periodic)

  • Contract Renewal

Project − Project Transaction Fact Table

The Project Transaction fact table maintains project financial transactions data. With the exception of programs (summary projects), at the most basic level, each project is composed of transactions. These transactions contain the quantity and amounts associated with each transaction and all of the identifying information that accompanied those costs.

With this fact group, you can answer questions such as “What is the total cost of the project?”, “What is the total revenue of the project?”, “What is the total cost of the activity?”, “What is the total revenue of the activity?”, “How many hours were billed against this project?” and “What is the budget versus actual for this project?”

This table describes the facts in the Project Transaction fact table.

Project − Project Activity (Current and Periodic) Fact Table

The Project Activity (Current and Periodic) fact table maintains information about a project activity (tasks) measures.

With this fact group, you can answer questions such as “What is the actual cost of work performed (ACWP) for this activity?”, “What is the budgeted cost of worked scheduled (BCWS) for this activity?” and “What is the budgeted cost of work performed (BCWP) for this activity?”

See PeopleSoft Setting Up Procurement Options PeopleBook: Using Voucher and Order Contracts

This table describes the facts in the Project Activity (Current and Periodic) fact table:

Project − Project (Current and Periodic) Fact Table

The Project (Current and Periodic) fact table maintains project measures.

With this fact group, you can answer questions such as “What is the actual cost of work performed for this project?”, “What is the budgeted cost of worked scheduled for this project?” and “What is the budgeted cost of work performed for this project?”

See PeopleSoft Setting Up Procurement Options PeopleBook: Using Voucher and Order Contracts

This table describes the facts in the Project (Current and Periodic) fact table:

Project − Project Deliverable Fact Table

The Project Deliverable fact table maintains project deliverable information.

With this fact group, you can answer questions such as “What is the average number of deliverables per project?”

This table describes the fact in the Project Deliverable fact table:

Project − Project Issue Fact Table

The Project Issue fact table maintains information about project issues. Project issues may be optionally related to a project activity.

With this fact group, you can answer questions such as “What is the average number of issues per project?”

This table describes the fact in the Project Issue fact table:

Project − Project Change Fact Table

The Project Change fact table maintains project change control information.

With this fact group, you can answer questions such as “What is the average number of changes per project?”

This table describes the fact in the Project Change fact table:

Proposal and Grant − Proposal and Grant Transaction Detail Fact Table

The Proposal and Grant Transaction Detail fact table captures the information relating to proposals and grants. For proposals it captures the different versions of project proposals created for business opportunities.

For grants it captures information relating to proposals, budgets, administrative, personnel, and submission information required by funding agencies.

With this fact group, you can answer questions such as “What is the average estimated margin percentage for all proposals?”, “What percentage of proposals were won?” and “What is the proposal pipeline?”

This table describes the facts in the Proposal and Grant Transaction Detail fact table:

Resources − Assignment Fact Table

The Assignment fact table represents information about assignments. Assignments pair a resource with a unit of work.

With this fact group, you can answer questions such as “What is the average length of an assignment?” and “What is the average billing rate for an assignment?”

This table describes the facts in the Assignment fact table:

Resources − Employee Forecast Fact Table

The Employee Forecast fact table presents information about employee forecasts.

With this fact group, you can answer questions such as “What is the revenue forecast for my organization in this period?” and “What is the forecast utilization for my organization in this period?”

This table describes the facts in the Employee Forecast fact table:

Resources − Resource Rate Fact Table

The Resource Rate fact table maintains resource actuals, employee, and job code rates. It enables analysis across different rates.

With this fact group, you can answer questions such as “What is a resource’s actual rate versus bill rate?”

This table describes the facts in the Resource Rate fact table:

Resources − Service Order Fact Table

The Service Order fact table maintains information about service orders (SOs). SOs submit, track, and fulfill requests for services from external or internal customers. A SO defines the required services for a project and specifies the criteria that the application uses to locate the appropriate resources.

With this fact group, you can answer questions such as “What is the average number of days requested on a service order?” and “What is the total number of resource requests in days?”

This table describes the facts in the Service Order fact table:

Expense − Time Report Daily Detail Fact Table

The Time Report Daily Detail fact table stores information relating to a person’s time report entry. It captures the related accounting information and associated hours. The details are captured at a day level.

With this fact group, you can answer questions such as “What is the average number of billable hours entered in a period?” and ”What is the resource utilization of my organization in a period?”

This table describes the fact in the Time Report Daily Detail fact table:

Expense − Expense Distribution Transaction Detail Fact Table

The Expense Distribution Transaction Detail fact table stores information relating to expense transactions and captures the related accounting information and associated amounts.

With this fact group, you can answer questions such as “What is the average cost of an expense type, such as a hotel stay?”, “What is the maximum amount spent on an expense type?”, “What is the average number of nights spent in a hotel?” and “What is the total number of passengers for an expense type in a period?”

This table describes the facts in the Expense Distribution Transaction Detail fact table:

Expense − Expense Report Approval Fact Table

The Expense Report Approval fact table contains information relating to an expense report’s approval and captures information relating to approvers, types, and approval dates.

With this fact group, you can answer questions such as “How long on average does it take for an expense report to be approved?”, “Which approvers are taking the most time to approve expense reports?” and “How many expense approvals are being done in a period?”

This table describes the fact in the Expense Report Approval fact table:

Contracts − Contract Transaction Detail Fact Table

The Contract Transaction Detail fact table stores information relating to a contract line and captures the contract terms and revenue metrics. The metrics are at the contract line level of detail.

With this fact group, you can answer questions such as “What is the average discount given on a contract line?”, “What is the largest or smallest discount given on a contract?” and “What is the total discount given on all contract lines?”

This table describes the facts in the Contract Transaction Detail fact table:

Contracts − Contract Distribution Transaction Detail Fact Table

The Contract Distribution Transaction Detail fact table stores information relating to accounting distributions of a contract line.

With this fact group, you can answer questions such as “What is the average amount allocated to a given combination of PeopleSoft General Ledger ChartFields?” and “What is the total amount allocated to a given combination of PeopleSoft General Ledger ChartFields?”

This table describes the fact in the Contract Distribution Transaction Detail fact table:

Contracts − Contract Revenue Recognition Fact Table

The Contract Revenue Recognition fact table contains revenue recognized and reversed accounting entries for a contract line.

With this fact group, you can answer questions such as “What is the total revenue amount recognized during a period?”

This table describes the facts in the Contract Revenue Recognition fact table:

Contracts − Contract Amendment Fact Table

The Contract Amendment fact table contains the change in the obligations and entitlements of a contract. The negotiated and discount amount changes are at the contract level.

With this fact group, you can answer questions such as “What is the total amount attributed to contract amendments in a period?” and “What is the average percent increase due to contract amendments?”

This table describes the facts in the Contract Amendment fact table:

Contracts − Contract Forecast (Current and Periodic) Fact Table

The Contract Forecast (Current and Periodic) fact table captures the current and trend of revenue backlog for both fixed-fee and rate-based contract lines and the probability percentage of the revenue forecast.

With this fact group, you can answer questions such as “What is the percentage change over time for a contract revenue forecast?” and “What is the current total contract revenue forecast for a period?”

This table describes the facts in the Contract Forecast (Current and Periodic) fact table:

Contracts − Contract Renewal Fact Table

The Contract Renewal fact table contains the contract renew amount and percentage increase over the different contracts for different renewal cycles.

With this fact group, you can answer questions such as “What is the average percentage increase in contract renewals?” and “What is the total amount attributed to contract renewals for a period?”

This table describes the facts in the Contract Renewal fact table:

Real Estate Mart

Real Estate Mart enables real estate managers to monitor the operating expenses and operations of their real estate portfolio, understand rental rates by property, and optimize occupancy costs, space usage, and rental rates. With Real Estate Mart you can track work orders by originators, order type, or tenant; understand property and billing space usage; and identify trends in leasing operations. The analytics included in Real Estate Mart will help you to track occupancy costs and compare to plan or other properties, understand and compare property book values, monitor rental income streams, including subleases, and uncover average rental rates by building, property type, or business unit.

Asset Lifecycle Management Business Process

Real Estate Mart is related to PeopleSoft’s Asset Lifecycle Management business process.

The Asset Lifecycle Management business process fulfills your real estate organization's requirements for property acquisition and disposal, portfolio management, commercial management, and building management. With the Asset Lifecycle Management business process, you plan capital asset requirements, acquire assets through buying, building or leasing, prepare and deploy assets, utilize and maintain assets, retire assets, and evaluate asset performance. The Asset Lifecycle Management business process also helps with real estate development project management; contract management; lease management, billing and payments; property management; and building and space management. With this business process, you can analyze the profitability of your space, as well as manage customer financial relationships. The lease management and asset maintenance business processes help you determine answers to questions such as “What are my sales per square foot?”, “What are my nonrecoverable costs per square foot?” and “What is my portfolio exposure for a specific tenant?”

Property Management is the business sub-process related to Real Estate Mart; this business sub-process includes the activities required to create and maintain leases, manage lease changes, and manage lease billing and payments. Creating and maintaining leases fulfills your organization's requirements for capturing leasing terms and conditions. Managing lease changes involves checking for lease expirations, verifying occupancy, and monitoring billing changes. Managing billing includes capturing sales, billing rent and other charges, collecting payments, handling escalations, expense participation, sales overage rent, and security deposits. Managing payments includes monitoring, verifying and approving your organization’s outbound lease payments. Managing billing and payments helps you capture and confirm rental revenue, sales history, recoveries, escalated rent and percentage rent. The information captured as the result of billing activities helps you determine answers to questions such as “What are my sales per square foot for my anchor tenants?”, “What is my monthly rental revenue?” and “What are my collected recoverable expenses?”

Real Estate Delivered Fact Tables

With Real Estate Mart, PeopleSoft provides the following fact tables:

• Area Master

• Billing

• Market Potential Renewal Rent

• Tenant Sales History

• Lease

Area Master Fact Table

The Area Master fact table contains all of the information about area of the property, building, floor, unit, and lease. With this fact group, you can answer questions such as “What is the total occupied square footage in my portfolio?” and “What is the total vacant square footage in a particular building?”

This table describes the fact in the Area Master fact table:

Billing Fact Table

The Billing fact table contains all of the real estate management billing information. With this fact group, you can answer questions such as “ What is the average annual rent per square foot on a specific property?”

This table describes the facts in the Billing fact table:

Market Potential Renewal Rent Fact Table

The Market Potential Renewal (MPR) Rent fact table contains information about market, potential and renewal rental rates. With this fact group, you can answer questions such as “What are my market rental rates for space used in retail, industrial or office?”

This table describes the fact in the MPR Rent fact table:

Tenant Sales History Fact Table

The Tenant Sales History fact table contains all of the sales reporting information posted to the Sales History file. With this fact group, you can answer questions such as “Which sales categories are growing?”

This table describes the fact in the Tenant Sales History fact table:

Lease Fact Table

The Lease fact table contains information on leases from the Real Estate Management application. With this fact group, you can answer questions such as “What percentage of my leases are rolling over?” and “What is the number of leases expiring in the next three months?”

This table describes the facts in the Lease fact table:

Advanced Cost Accounting Mart

Through Advanced Cost Accounting (ACA) Mart you can analyze profitability by cost objects and their related category codes. For example, you can view your profitability by customer (ship to or bill to), customer geographic region, product, or product line. Because Advanced Cost Accounting Mart includes the data in the ACA module of your application, it contains direct costs and indirect cost assignments that you have previously created.

With Advanced Cost Accounting Mart, you answer questions such as “Which customers are generating the most revenue and which are most profitable?”, “Which products yield the highest margins?” and “How is the contribution margin impacted by direct and indirect costs?” Advanced Cost Accounting Mart enables you to investigate these and other questions as they relate to the profitability of your products, customers, geographical regions, and business units.

Manage Performance and Profitability Business Process

PeopleSoft Manage Performance and Profitability business process assists organizations in actively managing the costs directly impacting product, customer, and channel profitability. Organizations focused on managing performance and profitability are able to deliver accurate cost information to improve visibility, strengthen accountability, and enable more informed decision-making, driving improved performance. The Advanced Cost Accounting module enables organizations to capture detailed cost information, generate detailed cost assignments, and analyze and evaluate profitability. The corresponding analytics module provides increased visibility down to the cost object level and enables comparison and trending information for channel, customer, and product profitability. The Manage Performance and Profitability business process helps you answer questions such as “Who are my most profitable customers?”, “What products provide the highest contribution margin?”, ”What channel consumes the most company resources?” and “What profitability trends exist across product lines, customers and channels?”

Advanced Cost Accounting Delivered Fact Tables

Advanced Cost Accounting Mart contains the following fact tables:

• Cost Analyzer Balance

• Driver Balance

Cost Analyzer Balance Fact Table

The Cost Analyzer Balance fact table provides information about the value of activity that has occurred within a specific period. This balance, which is stored at the cost object level, contains both direct costs and revenue, plus any assigned costs. With this fact group, you can answer questions such as “What is the revenue for this product for this period?” and “What are my overhead costs for this customer for this period?”

This table describes the fact in the Cost Analyzer Balance fact table:

Driver Balance Fact Table

The Driver Balance fact table provides information about activity by business unit, as well as cost object information. The Driver Balance fact table enables you to understand profitability by product or product line. With this fact group, you can answer questions such as “What are my sales, general and administrative expense costs by product?”, “What products are most profitable?” and “How do direct costs compare as a percent of product revenue?”

This table describes the fact in the Driver Balance fact table: