DEV09:

1. DEV09: Interactive Spreadsheets Formats and Working Designs Date/Time: Wednesday, December 6 from10:00 to 10:30 am Session Leader: Bill Palmer, R W Palmer Consulting 14th InternationalXBRL Conference

2. Interactive Spreadsheets Formats and Working Designs 14th InternationalXBRL Conference

3. Barriers and Solutions 14th InternationalXBRL Conference

4. XBRL files difficulties using common Excel XML import • schema and linkbases files • Default loading of XML works using File-Open • Read only - saved changes not original XML • XML Data features of Excel 2003 fail for schemas • XSD substitutionGroups not allowed ! • instance files • Default loading of XML using File-Open fails ! • XBRL tags mapped as columns - max 255 ! • Applies XSLT 1.0 from <?xsl ?> embedded processing instructions on load. 14th InternationalXBRL Conference

5. Loading xml esp. XBRL instances 14th InternationalXBRL Conference

6. ...XSLT can column-ize and clean up Transform elements with a @contextRef as generic element with an attribute holding element names. Element names will then all be in the new attribute column. 14th InternationalXBRL Conference

7. Taxonomy processing ? • DTS “discovery” • extension logic and use=“prohibit” 14th InternationalXBRL Conference

8. …do DTS before Excel load ...do extension logic by “lookup” A simple <files><file></file> ...</files> is sufficient for all XSLT 1.0 extension processing. A cell formula may look or a matching arc that is flagged as “prohibited” 14th InternationalXBRL Conference

9. Spreadsheet showing tree view of linkbase is too complex 14th InternationalXBRL Conference

10. …do not use tree ...use list with links 14th InternationalXBRL Conference

11. … xlink:from=“GrossProfit” ... … xlink:from=“GrossProfit” ... … xlink:to=“Revenue” weight=“+1” ... … xlink:to=“CostOfSales” weight=“-1” ... Same as linkbase … work with a set of arcs for a particular FROM summation in a particular ROLE 14th InternationalXBRL Conference

12. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? 14th InternationalXBRL Conference

13. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Arc? summation item weight role 14th InternationalXBRL Conference

14. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation item weight Arc? summation item weight role 14th InternationalXBRL Conference

15. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation item weight role summation weight item 14th InternationalXBRL Conference

16. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? role summation weight item A larger example in Excel - including arcs for the px_CostOfSales summation. 14th InternationalXBRL Conference

17. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? • This example is greatly simplified by ignoring other attributes • such as use, order, priority, xlink:arcrole, xlink:href etc. • We will mark use=“prohibited” arcs with a “P” so that extension processing formulas can be used. The amount • columns will need to be blanked out for prohibited arcs and the arcs that they match. 14th InternationalXBRL Conference

18. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Calculated Control More rows added by text formulas. 14th InternationalXBRL Conference

19. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Note use of calculation number 10003 on prohibited extension arc. This allows unique VLOOKUP without listing long combined FROM and ROLE names. “P” as first character means “prohibited”. Previous “W” arc also via “lookup”. 14th InternationalXBRL Conference

20. Simplest summation spreadsheet concept? Map each xlink arc to a single Excel row? Another format example spreadsheet. Note numeric column headings … “r1c1” mode allows identical standard formulas on any row. 14th InternationalXBRL Conference

21. Summation section is arcs above a calculated total, a control total, the difference. MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference

22. Sheet is “summation” section above “leaf” values for lookup MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference

23. MANY “instance” concepts (at top to be found first by lookups) SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference

24. SINGLE “summation” concept (assigned to a “role” and annotated calculation “arcs”) SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference

25. SINGLE “summation” concept (assigned to a “role” and annotated with calculation “arcs”) MANY “leaf” concepts 14th InternationalXBRL Conference

26. MANY “leaf” concepts 14th InternationalXBRL Conference

27. 14th InternationalXBRL Conference

28. Full featured example in PDF with annotations The spreadsheet is created by a non-xslt processor that collects all arcs for the entire taxonomy and is able to discern which are LEAF nodes and to document where they are used. The program writes all formulas and need not understand “row” addresses by using the “r1c1” mode. A single program step in Excel activates the formulas. 14th InternationalXBRL Conference

29. 14th InternationalXBRL Conference See file xmsft-20050228_calSPREADSHEET.pdf