WP_AR_Data

This is an extract of current and historical WIP, A/R, Worked Value, and Billings used to calculate Budgeted Billing and Collections by year, period, office, and department to support the Cash Cycle module in the Planning Application.  Used as the core source for loading the WIP AR fact table and the Xchange WIP AR cube in Planning.

The data values that are required to support the Cash Cycle module are:

• Local_Ending_WIP and  Local_Ending_AR for the last period of the current year.

• Local_Billable_Dollars and Local_Billings for all periods of the current year.

There are a number of non-required data values (not listed above) in this load file used to support the Forecasting module and values that are used to report Actuals and can be used for custom reporting that are optional.

For single currency implementation, only the Local values should be included.  

All source data is based on Working attorney basis.

Idx Field Type Maximum Length Required
1 Year Numeric 4 Yes
2 Period Numeric 2 Yes
3 Office_Code Alphanumeric 20 Yes
4 PG_Dept_Code Alphanumeric 20 Yes
5 Currency_Code Alphanumeric 20 Yes
6 Local_Ending_WIP Money   Yes
7 Local_Ending_AR Money   Yes
8 Local_Billable_Dollars Money   Yes
9 Local_Billings Money   Yes
10 Local_Rate_Realization Numeric 19, 4 No
11 Local_Net_Worked_Value Money   No
12 Local_Months_of_WIP Numeric 19, 4 No
13 Local_WIP_Release_Value Money   No
14 Local_Billing_Realization Numeric 19, 4 No
15 Local_WIP_Write_Ups_Offs Money   No
16 Local_Months_of_AR Numeric 19, 4 No
17 Local_AR_Release_Value Money   No
18 Local_Collection_Realization Numeric 19, 4 No
19 Local_AR_Write_Ups_Offs Money   No
20 Local_Collections Money   No
21 Local_Contingent_Matters_Worked_Value Money   No
22 Base_Ending_WIP Money   No
23 Base_Ending_AR Money   No
24 Base_Billable_Dollars Money   No
25 Base_Billings Money   No
26 Base_Rate_Realization Numeric 19, 4 No
27 Base_Net_Worked_Value Money   No
28 Base_Months_of_WIP Numeric 19, 4 No
29 Base_WIP_Release_Value Money   No
30 Base_Billing_Realization Numeric 19, 4 No
31 Base_WIP_Write_Ups_Offs Money   No
32 Base_Months_of_AR Numeric 19, 4 No
33 Base_AR_Release_Value Money   No
34 Base_Collection_Realization Numeric 19, 4 No
35 Base_AR_Write_Ups_Offs Money   No
36 Base_Collections Money   No
37 Base_Contingent_Matters_Worked_Value Money   No

 

Summary

WIP_AR_Data

Current and Historical WIP, A/R, Worked Value, and Billings used to calculate Budgeted Billing and Collections by year, period, office, and department.

Columns

 

Year

Year associated to the data values. Required.

Period

Two digit fiscal period number for the period associated to the data values. Required.

Office_Code

Office code associated to the data values; must match entry in OfficeRequired.

PG_Dept_Code

Practice Group / Department code associated to the data values; must match entry in PG_DeptRequired.

Currency_Code

Currency code for the Local_/nnn/ values; must match entry in Currency file; this is meant to validate the local amounts. Required.

Local Values

 

Local_Ending_WIP

Ending Work in Progress (unbilled time) value excluding disbursements in the local currency of the office specified byCurrency_CodeRequired.

Local_Ending_AR

Ending Accounts Receivable value excluding disbursements in the local currency of the office specified byCurrency_CodeRequired.

Local_Billable_Dollars

Worked value amount at standard rates in the local currency of the office specified by Currency_CodeRequired.

Local_Billings

Billed value in the local currency of the office specified by Currency_CodeRequired.

Local_Rate_Realization

Rate Realization percentage of the local worked value in the local currency of the office specified by Currency_Code.  Rate Realization = Net Worked Value / Billable Dollars. Optional.

Local_Net_Worked_Value

Net Worked Value at agreed rates in the local currency of the office specified by the Currency_Code.  Note: If using agreed rates for Billable Dollars, Net Worked Values should be equal to Billable Dollars. Optional.

Local_Months_of_WIP

Value in terms of the number of months for Work in Progress (unbilled time) remaining in inventory at end of the period in the local currency of the office specified by Currency_CodeOptional.

Local_WIP_Release_Value

Value relieved from Work in Progress (unbilled time) in the local currency of the office specified by theCurrency_CodeOptional.

Local_Billing_Realization

Realization percentage of the local worked value in the local currency of the office specified by Currency_CodeOptional.

Local_WIP_Write_Ups_Offs

Difference between Billings and WIP Release Value in the currency of the office specified by Currency_CodeOptional.

Local_Months_of_AR

Value in terms of the number of months for Accounts Receivable remaining in inventory at end of the period in the currency of the office specified by Currency_CodeOptional.

Local_AR_Release_Value

Accounts Receivable Value relieved from inventory in the local currency of the office specified by the currency code. Optional.

Local_Collection_Realization

Realization percentage of the local worked value in the local currency of the office specified by Currency_Code.  Collection Realization = AR Release Value / Collections . Optional.

Local_AR_Write_Ups_Offs

Difference between Collections and AR Release Value in the currency of the office specified by Currency_CodeOptional.

Local_Collections

Fees Received (Collected) in the currency of the office specified by Currency_CodeOptional.

Local_Contingent_Matters_Worked_Value

Worked value for Contingent Matters.  Reserved for future use. Can be used in Cash cycle for a contingency adjustment. Optional.

Base Values

 

Base_Ending_WIP

Ending Work in Progress (unbilled time) value excluding disbursements in the base (or reporting) currency. Optional.

Base_Ending_AR

Ending Accounts Receivable value excluding disbursements in the base (or reporting) currency. Optional.

Base_Billable_Dollars

Worked value amount at standard rates in the base (or reporting) currency. Optional.

Base_Billings

Billed value in the base (or reporting) currency. Optional.

Base_Rate_Realization

Rate Realization percentage of the local worked value in the base (or reporting) currency.  Rate Realization = Net Worked Value / Billable Dollars. Optional.

Base_Net_Worked_Value

Net Worked Value at agreed rates in the base (or reporting) currency.  Note: If using agreed rates for Billable Dollars, Net Worked Values should be equal to Billable Dollars. Optional.

Base_Months_of_WIP

Value in terms of the number of months for Work in Progress (unbilled time) remaining in inventory at end of the period in the base (or reporting) currency. Optional.

Base_WIP_Release_Value

Value relieved from Work in Progress (unbilled time) in the base (or reporting) currency. Optional.

Base_Billing_Realization

Realization percentage of the local worked value in base (or reporting) currency. Optional.

Base_WIP_Write_Ups_Offs

Difference between Billings and WIP Release Value in the base (or reporting) currency. Optional.

Base_Months_of_AR

Value in terms of the number of months for Accounts Receivable remaining in inventory at end of the period in the base (or reporting) currency. Optional.

Base_AR_Release_Value

Accounts Receivable Value relieved from inventory in the base (or reporting) currency. Optional.

Base_Collection_Realization

Realization percentage of the local worked value in the lbase (or reporting) currency.  Collection Realization = AR Release Value / Collections . Optional.

Base_AR_Write_Ups_Offs

Difference between Collections and AR Release Value in the currency of the base (or reporting) currency. Optional.

Base_Collections

Fees Received (Collected) in the base (or reporting) currency. Optional.

Base_Contingent_Matters_Worked_Value

Worked value for Contingent Matters.  Reserved for future use. Can be used in Cash cycle for a contingency adjustment. Optional.

Sample CSV Load File

This load file is a small sample of a WIP AR Data  load.

Filename: WIP_AR_Data.csv

2008,11,01,030,USD,1450230.00,153120.00,768900.00,801250.00,.96,769200.00,1.43,755425.00,.93,13475.00,1.29,689457.00,.94,13760.00,675697.00,35769.00,,,,,,,,,,,,,,,,

2008,11,01,034,USD,1560230.00,176120.00,725900.00,791250.00,.95,734200.00,1.43,732425.00,.94,12475.00,1.27,665457.00,.95,12760.00,694697.00,41769.00,,,,,,,,,,,,,,,,

2008,11,01,050,USD,1413230.00,143220.00,734500.00,765250.00,.94,791200.00,1.32,698425.00,.95,14565.00,1.35,654657.00,.92,21760.00,645697.00,21469.00,,,,,,,,,,,,,,,,

2008,11,01,070,USD,1324230.00,143520.00,756700.00,798250.00,.95,793400.00,1.31,734525.00,.92,13475.00,1.34,645657.00,.93,12360.00,674567.00,24359.00,,,,,,,,,,,,,,,,

Columns

Year

Year numeric(

4

) NOT NULL

Year associated to the data values. Required.

 

 

Field Load File Format
Type Numeric
Maximum Length 4
Required Yes

Period

Period numeric(

2

) NOT NULL

Two digit fiscal period number for the period associated to the data values. Required.

 

 

Field Load File Format
Type Numeric
Maximum Length 2
Required Yes

Office_Code

Office_Code varchar(

20

) NOT NULL

Office code associated to the data values; must match entry in OfficeRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

PG_Dept_Code

PG_Dept_Code varchar(

20

) NOT NULL

Practice Group / Department code associated to the data values; must match entry in PG_DeptRequired.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

Currency_Code

Currency_Code varchar(

20

) NOT NULL

Currency code for the Local_/nnn/ values; must match entry in Currency file; this is meant to validate the local amounts. Required.

 

 

Field Load File Format
Type Alphanumeric
Maximum Length 20
Required Yes

Local Values

Local_Ending_WIP

Local_Ending_WIP money NOT NULL

Ending Work in Progress (unbilled time) value excluding disbursements in the local currency of the office specified by Currency_CodeRequired.

 

 

Field Load File Format
Type Money
Required Yes

Local_Ending_AR

Local_Ending_AR money NOT NULL

Ending Accounts Receivable value excluding disbursements in the local currency of the office specified by Currency_CodeRequired.

 

 

Field Load File Format
Type Money
Required Yes

Local_Billable_Dollars

Local_Billable_Dollars money NOT NULL

Worked value amount at standard rates in the local currency of the office specified by Currency_CodeRequired.

 

 

Field Load File Format
Type Money
Required Yes

Local_Billings

Local_Billings money NOT NULL

Billed value in the local currency of the office specified by Currency_CodeRequired.

 

 

Field Load File Format
Type Money
Required Yes

Local_Rate_Realization

Local_Rate_Realization numeric(

19,

4

) NULL

Rate Realization percentage of the local worked value in the local currency of the office specified by Currency_Code.  Rate Realization = Net Worked Value / Billable Dollars. Optional.

 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Local_Net_Worked_Value

Local_Net_Worked_Value money NULL

Net Worked Value at agreed rates in the local currency of the office specified by the Currency_Code.  Note: If using agreed rates for Billable Dollars, Net Worked Values should be equal to Billable Dollars. Optional.

 

 

Field Load File Format
Type Money
Required No

Local_Months_of_WIP

Local_Months_of_WIP numeric(

19,

4

) NULL

Value in terms of the number of months for Work in Progress (unbilled time) remaining in inventory at end of the period in the local currency of the office specified by Currency_CodeOptional.

 

For example if WIP is 45 days, value would be 1.5.

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Local_WIP_Release_Value

Local_WIP_Release_Value money NULL

Value relieved from Work in Progress (unbilled time) in the local currency of the office specified by the Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Local_Billing_Realization

Local_Billing_Realization numeric(

19,

4

) NULL

Realization percentage of the local worked value in the local currency of the office specified by Currency_CodeOptional.

 

Billing Realization = Net Worked Value / Billable Dollar.s

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Local_WIP_Write_Ups_Offs

Local_WIP_Write_Ups_Offs money NULL

Difference between Billings and WIP Release Value in the currency of the office specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Local_Months_of_AR

Local_Months_of_AR numeric(

19,

4

) NULL

Value in terms of the number of months for Accounts Receivable remaining in inventory at end of the period in the currency of the office specified by Currency_CodeOptional.

 

For example if Accounts Receivable is 45 days, value would be 1.5.

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Local_AR_Release_Value

Local_AR_Release_Value money NULL

Accounts Receivable Value relieved from inventory in the local currency of the office specified by the currency code. Optional.

 

 

Field Load File Format
Type Money
Required No

Local_Collection_Realization

Local_Collection_Realization numeric(

19,

4

) NULL

Realization percentage of the local worked value in the local currency of the office specified by Currency_Code.  Collection Realization = AR Release Value / Collections . Optional.

 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Local_AR_Write_Ups_Offs

Local_AR_Write_Ups_Offs money NULL

Difference between Collections and AR Release Value in the currency of the office specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Local_Collections

Local_Collections money NULL

Fees Received (Collected) in the currency of the office specified by Currency_CodeOptional.

 

 

Field Load File Format
Type Money
Required No

Local_Contingent_Matters_Worked_Value

Local_Contingent_Matters_Worked_Value money NULL

Worked value for Contingent Matters.  Reserved for future use.  Can be used in Cash cycle for a contingency adjustment. Optional.

 

 

Field Load File Format
Type Money
Required No

Base Values

Base_Ending_WIP

Base_Ending_WIP money NULL

Ending Work in Progress (unbilled time) value excluding disbursements in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Ending_AR

Base_Ending_AR money NULL

Ending Accounts Receivable value excluding disbursements in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Billable_Dollars

Base_Billable_Dollars money NULL

Worked value amount at standard rates in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Billings

Base_Billings money NULL

Billed value in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Rate_Realization

Base_Rate_Realization numeric(

19,

4

) NULL

Rate Realization percentage of the local worked value in the base (or reporting) currency.  Rate Realization = Net Worked Value / Billable Dollars. Optional.

 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Base_Net_Worked_Value

Base_Net_Worked_Value money NULL

Net Worked Value at agreed rates in the base (or reporting) currency.  Note: If using agreed rates for Billable Dollars, Net Worked Values should be equal to Billable Dollars. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Months_of_WIP

Base_Months_of_WIP numeric(

19,

4

) NULL

Value in terms of the number of months for Work in Progress (unbilled time) remaining in inventory at end of the period in the base (or reporting) currency. Optional.

 

For example if WIP is 45 days, value would be 1.5.

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Base_WIP_Release_Value

Base_WIP_Release_Value money NULL

Value relieved from Work in Progress (unbilled time) in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Billing_Realization

Base_Billing_Realization numeric(

19,

4

) NULL

Realization percentage of the local worked value in base (or reporting) currency. Optional.

 

Billing Realization = Net Worked Value / Billable Dollar.s

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Base_WIP_Write_Ups_Offs

Base_WIP_Write_Ups_Offs money NULL

Difference between Billings and WIP Release Value in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Months_of_AR

Base_Months_of_AR numeric(

19,

4

) NULL

Value in terms of the number of months for Accounts Receivable remaining in inventory at end of the period in the base (or reporting) currency. Optional.

 

For example if Accounts Receivable is 45 days, value would be 1.5.

 
 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Base_AR_Release_Value

Base_AR_Release_Value money NULL

Accounts Receivable Value relieved from inventory in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Collection_Realization

Base_Collection_Realization numeric(

19,

4

) NULL

Realization percentage of the local worked value in the lbase (or reporting) currency.  Collection Realization = AR Release Value / Collections . Optional.

 

 

Field Load File Format
Type Numeric
Maximum Length 19, 4
Required No

Base_AR_Write_Ups_Offs

Base_AR_Write_Ups_Offs money NULL

Difference between Collections and AR Release Value in the currency of the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Collections

Base_Collections money NULL

Fees Received (Collected) in the base (or reporting) currency. Optional.

 

 

Field Load File Format
Type Money
Required No

Base_Contingent_Matters_Worked_Value

Base_Contingent_Matters_Worked_Value money NULL

Worked value for Contingent Matters.  Reserved for future use. Can be used in Cash cycle for a contingency adjustment. Optional.

 

 

Field Load File Format
Type Money
Required No
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk