CR Update Formula Tutorial April 12, 2007 Herm Fischer

1. 1 CR Update Formula Tutorial April 12, 2007 Herm Fischer

2. 2 Overview Why, who needs formulas for what? Historical perspectives Process flow of formulas Formula Linkbase Contents Formula & assertion processing Features by use case example Functions Standardized, registry End-user Roadmap http://herm.ws/XBRL/files/docs/FormulaTutorial.ppt

3. 3 Why formulas Calculation Linkbase is Enough Roll-ups validates item sums Addition within equal context Period, entity, dimension are equal Calculation Linkbase Can�t Do Cross context Movement (start + changes = end) Dimensions Business rules

4. 4 Historical Perspectives 2002 - FDIC contract motivates solutions Conceptual prototype based on XSL Excel-like, implicit matching (now in use) � - Rule base Requirements IWD 2003 - XPath syntax chosen � - Functions spec drafted. 2004 - Formula drafts, some implementations 2006 - Alternative approach with XQuery 2007 Jan- Issued Formula PWD Feb, Mar - Survey of users, feedback June, Dec, Feb � PWD updates after PWD feedbacks Mar 2008 � Candidate Release, implementation

5. 5 Original motivationEffective exchange of data Information producer Validate data types and values Consistency and quality checks Information consumer (ditto) Completeness and value-based checks Augment received data with calculated values Augment received data with annotations

6. 6 XBRL without formulas Calculation linkbase Well behaved roll-ups of compatible numerical values (Proposed) Aggregation of dimensional values Calculation linkbase can�t do Cross-context (ending bal = starting bal + changes) Cross-unit (dollars = price per share X nbr shares) Dimensional aggregation (all = red wine + white wine) Missing items checks Threshold and limits (sum amounts � �5)

7. 7 Rules vs. Formulas At first it was just �formulas� or just �rules� Sort of like a personal choice of favorite term Now: Rule: a principle or regulation Asserts fact item consistency, existence, and value check Formula: an expression to produce something Produces fact item Into an output instance document For consistency checking of corresponding input

8. 8 Formulas & Assertions Formula Results in an fact item For an output instance document For consistency checking of corresponding input Assertion Consistency check computed item to source item Existence check for source item Value check based on source items

9. 9 Process flow of formula linkbase

10. 10 Input instance processing Instance provides producer�s taxonomy, facts, and producer�s formulas Consumer may have their own formulas, their own resulting items taxonomy Formula processor evaulates formulas and assertions that match to input fact items Formula processor may produce output instance with result fact items User�s application may report based on assertion test results

11. 11 Formula linkbase contents

12. 12 Four processing models

13. 13 Examples of each model

14. 14 Consistency assertion processing

15. 15 Formula processing

16. 16 Value assertion processing

17. 17 Existence assertion processing

18. 18 Boolean formula test of assets Test that �assets = liabilities + equity� Input instance has assets, liabilities, and equity Output boolean test result, true or false

19. 19 Assets sum test, flow Processing flow

20. 20 Assets sum test, instances Instance document input has values to test Output is the boolean result of testing the sum

21. 21 Assets sum test, formula

22. 22 Assets sum test, formula resources

23. 23 Assets sum test, formula - compiled

24. 24 Assets sum test, formula execution

25. 25 Assets sum test, formula input

26. 26 Consistency check of assets Prior example tested assets = equity + liabilities 3 inputs (assets, equity, liabilities) 1 output (boolean test) Now consistency assertion checks computed sum 2 formula terms (equity, liabilities) 1 formula output (monetary assets) Consistency assert computed assets match input assets

27. 27 Compute assets, taxonomy Taxonomy has monetary concepts for assets, liabilities, equity Here output is the assets concept (vs. boolean) Last example:

28. 28 Consistency check assets Instance document input has values to add Consistency check computed sum to input fact

29. 29 Compute assets

30. 30 Consistency assertion of assets

31. 31 Formula linkbase resources Parameter Function declaration Variable sets ? {Formulas, Assertions} Variables Filters Precondition Generic label Generic reference

32. 32 Parameter resources Provide input parameters to formula processor From external source (invocation of processor) Or from XPath expression in Parameter May be required (or optional) Treated as a variable in variable-sets (Parameter resources can have arcs to labels & references)

33. 33 Function declaration resources Declare custom functions for XPath expressions May be user-supplied May be implemented by formula processor Function name Function input parameters Order, data type Function output data type (Function declarations can have arcs to labels & references)

34. 34 Variable-set resources Produce evaluations of related variables Fact variables of individual or sequences of items General variables Parameters Used to implement Assertions Formulas Arcs to variables, preconditions, group filters Arcs from variable sets

35. 35 Formula is a variable-set resource Value expression Filtering model (implicit, dimensional) Aspects of result in output instance Sources for aspects from input facts Concept specification Context & Unit (where needed) OCC common to scenario and segment Fact variable reference (copy from fact variable) Augment or replace (e.g., period, measure, OCC) Decimals and precision

36. 36 Assertion is a variable-set resource Consistency assertion Computed fact items compared to input fact items v-equal, percent, or absolute difference tolerance Existence assertion Specifies cardinality of variable-set evaluations Value assertion Specifies expected value test

37. 37 Variable resources Fact variables Bind to input instance fact items Filters constrain fact bindings Iterate through input facts or bind as sequence Can bind if missing, with fallback value General variables An XPath expression evaluation Iterate through sequence or bind as sequence Parameters From external environment or XPath expression Ordered by inter-dependency Named by arc

38. 38 Filter resources Specify a facet to use as fact predicate Have arc from fact variables and variable-sets Specify if covering the aspect for implicit filtering Available filter facets Concept, Dimension, Entity, Period, Tuple, Unit, Value, and General (XPath expression) Implicit filtering Automates matching non-covered filtered facets Boolean filters Permit and-or combinations of filters

39. 39 Precondition resource Provides an XPath expression to test Runs after parameters and variables are bound Arc-connected from formula resource(s)

40. 40 Generic label resources Annotates descriptive text on any Resource (e.g., any in formula LB) Element in xsd (e.g., concept, arc type, etc.) Arc connected (from resource or element) Unconstrained contents

41. 41 Generic reference resources Provides link:part�s to any Resource (e.g., any in formula LB) Element in xsd (e.g., concept, arc type, etc.) Arc connected (from resource or element)

42. 42 Fact variable aspect filters Concept filter Dimension filter Entity filter General filter Period filter Tuple filter Unit filter Value filter

43. 43 Concept filter Filter by name of concept Filter by facet of concept Balance Data type Substitution group Period type Custom attribute

44. 44 Concept name filter Sum test uses a concept filter on each variable <cf:conceptName xlink:type="resource" xlink:label="filter_assets�> <cf:name strict=�true�> <cf:qname>c:assets</cf:qname> </cf:name> </cf:conceptName>

45. 45 Period filter Match completely another variable�s period Match date date/time of: periodStart periodEnd periodInstant forever instantDuration (to a duration factVariable) Matching by an XPath expression

46. 46 PeriodStart filterMovement example Ending balance = beginning balance + changes Inputs have cross-context matching Starting balance�s date matches duration�s start Ending balance�s date comes from duration�s end

47. 47 Use case, movement example

48. 48 Period filter instantDuration matches beginningBalance�s instant date to change�s duration start date <pf:instantDuration variable="v:changes" boundary="start" />

49. 49 Aspects Location (tuple nesting) (both tuple & item) Concept (namespace & name) ( � ) Entity identifier (item only) Period ( � ) Segment (complete, nonXDT, or dimension) ( � ) Scenario ( � ) ( � ) Unit (numeric items only)

50. 50 Implicit filter, changes

51. 51 Implicit filter, beginning balance

52. 52 Implicit scenario & segment issues Dimensional implicit filters Dimension contents are matched with d-equals Remaining contents matched with s-equals Non-dimensional implicit filters Contents matched with s-equals

53. 53 Result concept taken from input fact Result sourcetakes conceptof a boundvariable forresult fact

54. 54 Movement example Try it, examine it, execute it online: Directory �\xml\examples\0004 Movement

55. 55 Use case, stock flow Ending stock = starting stock + inflow � outflow Starting context (instant at start date) Flow/change context (duration from start to end) Ending context (instant at end date) Inputs have cross-context period matching Starting stock�s instant date matches inflow & outflow duration starts Both inflow & outflow need periodStart matching Output result (ending stock) gets inflow/outflow duration ending date Formula specifies period for ending stock

56. 56 Use case, stock flow

57. 57 Stock Flow example Try it, examine it, execute it online: Directory �\xml\examples\0003 Stock Flow

58. 58 Tuple filter ParentTuple Concept name of the fact�s tuple parent AncestorTuple Concept name of a fact�s ancestor TupleSibling Another variable�s fact is a sibling of this fact TupleLocation Another variable�s fact is a given relation to this fact E.g., $a is �../eg:tupleB/*� related to this fact

59. 59 GL-based tuple examples (tuples in, non-tuples out) Trial balance in-balance checks For each entry details section check if in-balance Output string result item (for this example) Trial balance ending balances report For each account add changes to balance brought fwd Aggregate account across entry detail sections Output string result item (acct name & ending balance) (Research into tuple generation is discussed later)

60. 60 GL trial balance check <xbrli:xbrl> <gl-cor:accountingEntries> <gl-cor:entryHeader> <gl-cor:qualifierEntry �>balance-brought-forward</ �> <gl-cor:entryDetail> <gl-cor:amount �>242678.26</ �> <gl-cor:debitCreditCode �">D</ �> </gl-cor:entryDetail> � </ gl-cor:entryHeader> <gl-cor:entryHeader> <gl-cor:qualifierEntry �>standard</ �> <gl-cor:entryDetail> <gl-cor:amount �>242678.26</ �> <gl-cor:debitCreditCode �">D</ �> </gl-cor:entryDetail> � </ gl-cor:entryHeader> </ gl-cor:accountingEntries> </ xbrli:xbrl>

61. 61 General filter Just an XPath 2 expression Need to test if sibling of amount is credit code <gl-cor:entryDetail> <gl-cor:amount �>242678.26</ �> <gl-cor:debitCreditCode �">D</ �> </gl-cor:entryDetail> test=�../gl-cor:debitCreditCode eq �D��

62. 62 Trial balance checks

63. 63 Value filter Value matches an XPath 2 expression Not Nil test Nil test Precision expression

64. 64 GL ending balance computation <xbrli:xbrl> <gl-cor:accountingEntries> <gl-cor:entryHeader> <gl-cor:qualifierEntry �>balance-brought-forward</ �> <gl-cor:entryDetail> <gl-cor:account> <gl-cor:accountMainID �>5100</ �> <gl-cor:accountMainDescription �>Supplies</ �> <gl-cor:accountType �>account</ �> </gl-cor:account> <gl-cor:amount �>242678.26</ �> <gl-cor:debitCreditCode �">D</ �> <gl-cor:xbrlInfo> <gl-cor:xbrlInclude �>beginning_balance</�> </gl-cor:xbrlInfo> </gl-cor:entryDetail> � </ gl-cor:entryHeader> <gl-cor:entryHeader> <gl-cor:qualifierEntry �>standard</ �> <gl-cor:entryDetail> � </gl-cor:entryDetail> � </ gl-cor:entryHeader> </ gl-cor:accountingEntries> </ xbrli:xbrl>

65. 65 GL ending balance computation

66. 66 GL ending balance test Added 2 more variables For each account Get amount for ending-balance entry with �C� (credit) code Get amount for ending-balance entry with �D� (debit) code Change equation to test Sum (credits which are not ending-balance - debits which are not ending-balance + credit which is ending-balance � debit which is not ending-balance) < 1.00

67. 67 GL example code Try it, examine it, execute it online: Directory �examples\0007 GL Examples Instance-trial-balance-check.xml Instance-test-ending-balance.xml Instance-compute-ending-balance.xml

68. 68 Dimension filter Explicit Dimension filter Fact has QNamed dimension (in context or default) Constrains to QNamed member(s), or their children or descendants Typed Dimension filter Typed dimension is specified by QName XPath 2 expression tests dimension value

69. 69 Dimension filter, implicit interactions Primary items with multiple dimensions Dimensions explicity filtered Remaining dimensions implict matched Remainder of segment/scenario implicit matched Example has 2 dimensions (product, region) Aggregation check by product or region requires implicit matching of other dimension

70. 70 Dimension aggregation Test aggregation of each member Products & region dimensions have member hierarchy

71. 71 Pharmaceutical example

72. 72 Two dimensions to check Will require checking of aggregation for Product dimension Region dimension Formula shown for product dimension(region is same except for dimension name) Demonstrates mixed explicit/implicit dimension filtering

73. 73 Product dimension check

74. 74 Product dimension check Try it, examine it, execute it online: Directory �examples\0005 Pharmaceutical

75. 75 COREP Use case 18: Weighted average of member children Weighted average of its dimensional children by another primary item

76. 76 Weighted average of member children Excel formulas: Make PD controlling fact, get PD and EV of dimensional children General variable for PDxEV member matching

77. 77 Weighted average of member children

78. 78 Weighted average of member children Try it, examine it, execute it online: Directory �\xml\examples\0006 Weighted Avg

79. 79 PWD 2 Filter Additions Boolean filter Allows and/or filter expressions Match filter Specifies specific aspects to match Relative filter Specify matching all non-covered aspects

80. 80 PWD 2 Validation & Assertions Validation supplements formula item production Existence assertion Filtering constructs find existing fact item Value assertion Value-assertion expression is true Fact Consistency assertion Produced fact matches corresponding fact(s), in value

81. 81 Existence assertion Examples Instance has fact values for total assets Facts contexts have entity identifiers of specified scheme No facts have period end after cut-off date What it tests Cardinality of variable-set Expression processes the cardinality

82. 82 Value assertion Example Capital adequacy ratio must exceed 8% Interest cover ratio must exceed 2.5 Cash balance must be positive What it tests Value expression of variable(s) Expression processes the variables and parameters

83. 83 Consistency assertion examples Examples Balance sheet is in balance Total assets = total liabilities + total equity Movement pattern consistency Starting balance - ending balance = flowsduring the intervening period What it tests Produced fact matches corresponding input fact There is no expression (as this is a match only)

84. 84 Conformance tests Conformance test cases in review Formula static analysis and processing About 550 tests Generic link and functions About 40 tests Functions registry test cases About 150 tests

85. 85 Functions Built-in functions Functions defined in function registry Previously functions 1.0�� December 7, 2006 http://www.xbrl.org/Specification/XF-PWD-2006-12-07.htm User-defined functions Signatures provided in linkbase resources Code provided to formula processor somehow Maybe XQuery Maybe Java or other coding language

86. 86 Function registry Function definition specification Formal definition of name, arguments, output Function test case Variations to test function operation Each variation has input taxonomy and instance files function call XPath expression XPath expression testing output data type and values

87. 87 Function registry examples

88. 88 Functions 1.0 examples Precision xfi:precision( item ) xfi:decimals( item ) Accessors xfi:context( item ) xfi:unit( item ) xfi:period( context ) xfi:period-start, :period-end, :period-instant( period )

89. 89 Functions 1.0 examples Test example functions xfi:s-equal( node ) xfi:u-equal( item ) xfi:v-equal( item ) xfi:c-equal( item ) xfi:p-equal, :cu-, :pc- , :pcu- ( item )

90. 90 Roadmap Candidate recommendation status Formula conformance suite (PWD) Function registry suite (PWD) Implement formula processors (2 required) Full Recommendation Future extensions Tuple output Formula chaining

91. 91 Changes since June 2007 PWD Variable-set introduced Formula is a variable-set Output aspect syntax improvements Assertions are new variable-sets Consistence assertion (formula compared to input fact) Existence assertion Value assertion Uniformity to filtering syntax (qnames, expr.) Implicit filtering model improved Boolean & group filtering

92. 92 Assets sum test, 2007 PWDs

93. 93 Assets sum test, back to 2004, 2002

94. 94 Research areas Tuple output Formula-formula arcs to specify tuple nesting and variable inheritance from outer to inner formulas Tuple arcs traversed prior to precondition test Formula chaining Formula-formula arcs chaining and variable inheritance Chaining arcs traversed after precondition test Multiple instances input Formulas bridging different versions of DTS

95. 95 Tuple nesting research

96. 96 Formula �chaining� research

97. 97 Developing a formula processor Objectives Formula & assertion development Development environment Formula & assertion execution (only?) Production performance Platform integration Rendering support (only?) Versioning support

98. 98 Developing a formula processor Architectural alternatives XML foundation XDT (validated typed nodes, not DOM) XSLT on an xdt data model XBRL infoset (or other object model) XPath2 foundation XPath2 processor product XSLT with XPath2 model Parser and processor from other sources Function support XQuery or other language(s)

99. 99 Developing a formula processor Development environment Editing, debugging Physical level editing Logical level development Project management Source code control, release control Collaborative working support Versioning support Maintenance facilities

100. 100 Developing a formula processor Production environment Platform Performance Database/Repository Interoperability Integrability Custom functions

101. 101 Developing a formula processor Instance processing considerations Assertion processing Instance generation Instance document prefiltering/staging/assembly Multi-instance processing

102. 102 Developing a formula processor Interoperation with XBRL modules Rendering Versioning Generic linkbase

103. 103 Planning for later features Formula chaining Tuple generation Messaging integration

104. 104 Herm Fischer herman.fischer@ubmatrix.com fischer@markv.com +1-818-995-7671 +1-818-404-4708 http://herm.ws/XBRL/files/docs/FormulaTutorial.ppt THANK YOU! Let us help you get your first formulas easily! Questions