`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1007
`
`

`

`USOO5189608A
`Patent Number:
`Date of Patent:
`
`5,189,008
`* Feb. 23, 1993
`
`11
`45
`
`OTHER PUBLICATIONS
`"Using Spreadsheets to Monitor Your Portfolio",
`Thomas A. Meyers PC Magazine, Apr. 1986.
`Dow Jones Software, Product Brochure, Spreadsheet
`Analysis.
`Teddi Converse, Reflex. In Business, pp. 204-217 1988.
`Doyle, William J., “Using SuperCal: The Next Genera
`tion" pp. 179, 292-315.
`"Leading Edge: Nord Processing', pp. Appendix B.
`"Using 1-2-3", pp. 182-193.
`Primary Examiner-Gail O. Hayes
`Attorney, Agent, or Firm-Pennie & Edmonds
`57
`ABSTRACT
`An advanced financial reporting and analysis software
`package is described. The package collects, organizes,
`manages and consolidates financial data and provides
`user defined capabilities for creating financial and cor
`porate reports. Financial data is organized into four
`business classifications or dimensions: Schedule, Entity,
`Period and Type. Data is stored in the system in such a
`way that all data associated with a particular Schedule,
`Entity, Period and Type is identified by that particular
`SEPT value. To accommodate automatic data entry, a
`mapping means or template is provided that specifies
`for each different input spreadsheet the location of the
`first data cell in the spreadsheet and the size of the
`spreadsheet. Data is read from the data store by various
`report and spreadsheet generating functions which con
`vert data associated with particular SEPT values to
`desired output formats.
`9 Claims, 29 Drawing Sheets
`
`United States Patent (19)
`Lyons et al.
`
`(54) METHOD AND APPARATUS FOR STORING
`AND GENERATING FINANCIAL
`INFORMATION EMPLOYING USER
`SPECIFIED INPUT AND OUTPUT FORMATS
`Inventors: Richard J. Lyons, West Hempstead;
`Kevin F. Nolan, Middle Village, both
`of N.Y.; Wah C. Chu, Fairfield,
`Conn.
`
`(75
`
`*) Notice:
`
`73) Assignee:
`
`MRS Operations, Inc., Stamford,
`Conn.
`The portion of the term of this patent
`subsequent to Jan. 29, 2008 has been
`disclaimed.
`(21) Appl. No.: 480,701
`(22
`Filed:
`Feb. 15, 1990
`
`63
`
`Related U.S. Application Data
`Continuation of Ser. No. 55,823, Jun. 1, 1987, Pat. No.
`4,989,141.
`Int. Cl. .............................................. G06F 15/21
`51
`52 U.S. C. ..................................... 364/408; 364/401
`58) Field of Search ....................... 364/400, 401, 408;
`11/11; 395/148
`
`(56)
`
`References Cited
`U.S. PATENT DOCUMENTS
`4,321,672 3/1982 Braun et al. ......................... 364/408
`
`STATERROR
`REPORT
`
`
`
`

`

`U.S. Patent
`US. Patent
`
`wF
`
`fOlamS
`
`5,189,008
`5,189,608
`
`
`
`555.55128
`Walsis Hind?00
`
`
`€2.62m.zofimzzséz.
`«9.£255
`mDUD1,gang?
`.82>
`
`UUu
`
`:22:
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 2 of 29
`
`5,189,008
`
`FIG.2
`
`START
`
`DISPLAY
`FUNCTIONS
`
`SELECT
`FUNCTION
`
`NO
`
`
`
`YES
`
`DISPLAY
`SUBEUNCTION
`
`SELEC
`SUBFUNCTION
`
`
`
`NO
`
`
`
`VERIFY
`SUBFUNCTION
`
`YES
`
`
`
`
`
`DISPLAY
`SUB-SUBFUNCTION
`
`SELECT
`SUB-SUBFUNCTION
`
`NO
`
`
`
`
`
`VERIFYSUB
`SUBFUNCION
`
`
`
`YES
`
`

`

`U.S. Patent
`US. Patent
`
`Feb. 23, 1993
`
`:Sheet 3 of 29
`
`5,189,008
`5,189,608
`
`<m.GE
`
`ESE
`
`
`
`
`
`
`
`
`
`
`
`Emmi—z.
`
`mzjzo
`
`Em
`
`m2::02E225:—
`
`5828
`
`5.5”
`
`range:
`
`E0555
`
`.338
`
`:95
`
`momzm
`
`mgmmw:
`
`_
`
`Eaves.
`
`zoEzmg
`
`.mdmo
`
`zoEmuxm
`
`mm»
`
`mmEm
`
`5.3ESQ—=2.
`
`.mflzm
`
`2:38aBox
`
`wzoEEomma
`
`Emuflz.oz<
`
`Eda
`
`B:,
`
`E8:,
`
`onHwhimsy—
`Ego—SE
`9.mo
`
`.55
`
`ON
`
`:Ez_.
`
`
`
`
`
`
`

`

`US. Patent
`U.S. Patent
`
`Feb. 23, 1993
`Feb. 23, 1993
`
`Sheet 4 of 29
`Sheet 4 of 29
`
`5,189,608
`5,189,008
`
`
`
`FIG.BB
`
`:
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 5 of 29
`
`5,189,008
`
`FIG. 4
`
`CREATE
`
`DECTIONARY
`
`
`
`PERIOD
`
`
`
`ACC.
`DESCRIP
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 6 of 29
`
`5,189,008
`
`TYPE
`DICTIONARY
`
`ENTER
`PERIOD
`
`ENTER
`TYPE
`CODE
`
`DOES
`CODE
`EXIST
`
`ENTERTYPE
`DESCRIPTION
`
`ENTER
`FORMULA .
`
`FIG. 5
`
`ERROR
`MESSAGE
`
`
`
`
`
`
`
`
`
`UPDATE
`TYPEDICTIONARY
`
`(FORMULAIS
`VARANCE ENTERNO
`PERIODITYPE
`COMBINATION
`TOSUBTRACT
`
`
`
`NO
`
`
`
`F
`forts
`YES
`
`NO
`
`ERROR
`MESSAGE
`
`
`
`
`
`YES
`
`UPDATE
`TYPE
`DCTIONARY
`
`ENTER
`PERIODS
`OSUM
`
`ENTER
`TYPESOSUM
`
`
`
`UPDATE
`TYPE
`DICTIONARY
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 7 of 29
`
`5,189,008
`
`F.G. 6A
`
`CREATE
`
`CROSS
`NTEGRITY
`
`UPDATE
`
`LIST
`
`CALCULATE
`
`ADJUSTMENT
`
`
`
`SELECT
`SCHEDULES
`OUPDATE
`
`ERROR
`MESSAGE
`
`ENTER
`CROSS-INTEGRITY
`RULES
`
`VALID
`SCHEDULE
`
`YES
`
`ERROR
`MESSAGE
`
`
`
`NO
`
`
`
`ERROR
`MESSAGE
`
`
`
`WALD
`NOYCEL FOR
`SCHEDULE
`
`ERROR
`MESSAGE
`
`YES
`
`ERROR
`MESSAGE
`
`NO
`
`SELECT
`SCHEDULES
`TOXINTEGRITY
`CHECK
`
`SEARCH
`FOR DATA
`
`FDATA
`EXISTS
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 8 of 29
`
`5,189,008
`
`GENERATE
`STATUS/
`ERROR REPORT
`
`FIG.6B
`
`
`
`
`
`
`
`
`
`
`
`YES
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`GET
`SCHEDULE
`
`AST
`XNTEGRITY
`RULE
`
`
`
`GE
`XINTEGRITY
`RULE
`
`PARSE
`FORMULA AND CEL
`INFORMATION
`
`
`
`PUL
`WALUES
`FROM
`DAABASE
`
`PLUG
`VALUESNO
`RULE
`
`CHECK CACUAE
`VALUE AGAINST
`VALUE BEING
`VERIFED
`
`
`
`UPDATE
`XINTEGRITY
`STATUS ERROR
`REPORT
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 9 of 29
`
`5,189,008
`
`FIG. 7
`
`PROCESS
`CACULATION
`
`
`
`GENERATOR
`STATERROR
`REPORT
`
`YES
`
`NO
`
`GETENTITY
`
`CREATE
`S-EP
`RECORD
`
`DETERMINE
`CALCULATION
`
`
`
`
`
`
`
`NO
`
`NEEDED FOR
`CALCULATION
`
`YES
`
`UPDATE
`S-E-P-
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 10 of 29
`
`5,189,008
`
`FIG.8
`
`
`
`PERFORM
`CALCULATION
`
`
`
`MORE
`CELESTO
`CACULATE
`
`GETNEXT CEL
`FROM NEXT
`SEPT
`
`MORE
`S-EPS IN
`FORMULA
`
`ALL
`S-EP
`RECORDS
`FOUND
`
`
`
`CREATE
`CELL
`RECORD
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`sheet 11 of 29
`
`5,189,008
`
`
`
`SELECT
`CAPTION
`
`VERIFY
`OPTION
`
`YES
`
`...
`
`INPUT
`
`WORKSHEET
`
`FILE
`
`CALC, DATA
`
`MANUAL
`
`one
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 12 Of 29
`
`5,189,008
`
`FIG 10
`
`INPUT
`
`WO
`
`ANUAL -
`
`CALCULATED
`
`E.
`REATE
`DICTIONARY
`
`
`
`
`
`
`
`
`
`Y DO
`DCTIONARIES
`EXIST / .
`
`MODIFY
`
`-
`
`
`
`YES
`Ele No -6.
`REATE
`WORKS
`A MCYSE
`TEMPLATE
`EXIST
`YES
`
`
`
`SELECT
`WORKSHEET
`FILES
`
`PROCESS
`WORKSHEET
`DATA
`
`INQUIRY
`
`YES
`
`DELETE
`
`UNDELETE
`
`w
`
`BATCH
`UPDATE
`
`COPY
`
`
`
`
`
`DO
`GOTO N96ICTNARIES
`CREATE
`EXIST
`DICTIONARY
`
`YES
`
`
`
`
`
`SELECT
`S-E-F
`RECORD TO
`UPDATE
`
`ERROR
`MESSAGE
`
`NO
`
`YES
`
`ENTER
`MANUAL
`VALUES
`
`PROCESS
`MANUAL
`DATA
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 13 of 29
`
`5,189.608
`
`
`
`
`
`
`
`
`
`
`
`
`
`OUTPUT
`STATUS
`REPORT
`
`FIG 11A
`
`PROCESS
`WORKSHEET
`DATA
`
`LOOKUPSEPT
`CELL
`LOCATIONS
`
`LOOKUP
`LOCATION OF
`1st DATA CELL
`
`
`
`LOOKUP
`TEMPLATE
`DIMENSIONS
`
`ONES-EP
`RECORD
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 14 of 29
`
`5,189,008
`
`FIG. 11B
`
`
`
`
`
`
`
`
`
`AST
`S-E-PT
`RECORD
`
`YES
`
`
`
`
`
`NO
`
`READS-E-P-T
`FROM
`WORKSHEET
`
`OUTPUT
`STATUS
`ERROR
`REPORT
`
`ERROR
`CONDITION
`
`N01 FVALID NES PROCESS
`S-E-PT
`VALUES
`
`
`
`
`
`
`
`
`
`PROCESS
`RANGES
`
`READ
`IE
`
`
`
`CREATE
`RANGES
`N
`DATABASE
`
`
`
`
`
`
`
`
`
`UPDATE
`S-EP
`RECORD
`
`
`
`
`
`S-E-P-T INCL
`NONLINE
`HER.
`
`UPDATE
`HERARCHY
`
`GE RULE
`
`PLUG
`VALUES
`NTORULE
`
`CHECKCALC,
`VALUE
`AGAINST
`NPUT WALVE
`
`SUSPEND
`DATA
`
`NTEGRITY
`ERROR
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 15 of 29
`
`5,189,008
`
`FIG. 12
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`PROCESS
`VALUES
`
`READ
`WORKSHEET
`FILE
`
`READCELL
`INDICATOR
`
`DOES CEL
`CONTAIN
`
`NEXT
`LEVEL
`NDICATOR
`
`RECORD
`MESSAGE
`
`
`
`GOTO
`PROCESS
`RANGES
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 16 of 29
`
`5,189,008
`
`
`
`PROCESS
`RANGES
`
`FIG. 13
`
`UPDATE
`RANGES
`
`READINTEGRITY
`RULES
`
`READRANGE
`RECORDSIN
`FILE
`
`CALCULATE
`OFFSFROM
`FIRSTDATA
`CEL
`
`RECORD RANGE
`RECORD WITH
`OFFSET LOCATION
`N MEMORY
`
`READINTEGRITY
`RULES
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 17 of 29
`
`5,189,008
`
`FIG. 14
`
`UPDATE
`S-E-P-
`
`PERFORM
`CONVERSIONS
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`ADD NEW
`RECORD
`
`CALCULATE
`DIFFERENT
`CELS
`
`STORE
`DIFFERENT
`CELLS
`
`
`
`UPDATEAS
`CONVERTED
`FIELD
`
`MODIFY
`EXISTING
`RECORD
`
`
`
`CALCULATE
`DFFERENT
`CELS
`
`STORE
`DIFFERENT
`CELS
`
`DELETE
`O)
`RECORD
`
`
`
`UPDATEAS
`CONVERTED
`FIELD
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 18 of 29
`
`5,189,008
`
`
`
`
`
`
`
`
`
`
`
`
`
`FIG. 15
`
`(1)
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`ENTER
`MANUAL
`WALUES
`
`EXIT
`CONDITION
`SELECTED
`
`YES GOTO
`PROCESS
`MANUALDATA
`
`INPUT
`VALUES
`
`NO/AUTO-RECALC,
`SELECTED
`
`
`
`
`
`
`
`AST
`INTEGRITY
`RULE
`
`GET
`NTEGRITY
`RULE
`
`
`
`
`
`PLUG
`VALUES
`INTORULES
`
`CACULATE
`RULES
`
`DISPLAY
`CACULATED
`VALUE
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 19 of 29
`
`5,189,008
`
`
`
`()
`|| ".
`
`PROCESS
`
`CHECK
`SCHEDULE
`FOR INTEGRITY
`RULES
`
`UPDATE
`
`S-EP
`RECORD
`
`YES (G)
`
`
`
`
`
`
`
`
`
`
`
`GET RULE
`
`it's
`INTORULE
`
`
`
`cite
`VALUE AGAINST
`NPUT VALUE
`
`FIG. 16
`
`EXIT
`
`
`
`N0/INCLUDEDIN
`ONLINE
`ERARCH
`
`YES
`
`
`
`UPDATE
`HIERARCHY
`
`DISPLAY
`INTEGRITY
`ERROR
`WINDOW
`
`CORRECT
`
`SAVE AND
`i" ||
`
`OUT
`
`
`
`o:
`Morites
`
`SUSPEND
`DATA
`
`friar
`GEDAA
`PURGEDAA -QS
`
`
`
`
`
`OUTPUT
`ERROR REPORT
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 20 of 29
`
`5,189,008
`
`F.G. 17
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`UPDATE
`HERARCHY
`
`GETCE
`DIFFERENCES
`
`
`
`MULTIPLY
`DFFBY%
`OWNERSHIP
`OF ENTITY
`
`DETERMINE
`LOGICAL
`PARENT OF
`ENTITY
`
`
`
`ROUP
`VALUESTO
`LOGICAL
`PARENT
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 21 of 29
`
`5,189,008
`
`FIG. 18
`
`
`
`E.
`DICTIONARY
`
`
`
`
`
`
`
`
`
`
`
`CALCULATED
`DATA
`(LATERAL
`CONSOLIDATION)
`
`DODICT
`EXIST
`
`SELECT
`S-E-PT
`RECORDS TO
`CALC,
`
`
`
`PROCESS
`CALCULATION
`(LATERAL
`CONSOLIDATION)
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`sheet 22 of 29
`
`5,189,008
`
`FIG. 19
`
`
`
`SORT RULES
`
`
`
`
`
`
`
`
`
`GET LEFT
`WARIABLE
`
`SEARCH RIGHT
`YARIABLES
`ABOVEFOR
`MATCH
`
`YES
`
`SWITCH
`FORMULAS
`
`
`
`
`
`
`
`
`
`YES
`
`
`
`
`
`GE NEXT RULE
`
`
`
`CHECK FOR
`SIM.
`RELATIONSHIPS,
`GO TO22
`
`
`
`CIRCULAR
`REFERENCE
`DETECTE
`
`ERROR MESSAGE
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 23 of 29
`
`5,189,008
`
`FIG. 20
`
`QUERY
`
`ERROR
`
`N9
`
`
`
`DO
`OUERIES
`EXIST
`
`YES
`
`SELECT
`QUERY
`
`PROCESS
`QUERY
`
`
`
`
`
`
`
`
`
`IF
`OUTPUTTONYES
`WORKSHEE
`NO
`
`DISPLAY
`QUERY
`
`GENERATE
`WORKSHEET
`FILE
`
`
`
`SELECT
`ACROSS
`ATRIBUTES
`FORQUERY
`
`O
`SELECT
`S-E-PT
`VALUES FOR
`QUERY
`
`SELECT
`DOWN
`A TRIBUTES
`FORQUERY
`
`
`
`
`
`
`
`NO /SCHEDULE
`NE!
`
`YES
`
`DISPLAY
`. MODEL
`WORKSHEET
`
`ENER
`ODE
`RULES
`
`E.
`
`FORMAT
`
`TOAS
`
`SAVE
`
`FORMAT
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 24 of 29
`
`5,189,008
`
`FIG.21
`
`ENTER
`LEFT
`WARIABLE
`
`ENTER
`RIGHT
`VARIABLES
`
`WARIABLE
`
`FORMUAWAR,
`
`TIME
`VARIES
`WAR,
`
`
`
`CONSTANT
`
`GOTO
`COMPLEMODE
`
`YES
`
`ENTER
`RANGE
`WARIABLE
`
`CHECK
`DATABASE FOR
`RANGE
`
`at K). ()
`
`YES
`
`REVECT
`RULE
`
`ACCEPT 96.9SRANGE
`EXIST ON MULTIPLE)
`RULE
`SCHEDULES
`
`CONSTANT
`
`ACCEPT
`RULE
`
`YES
`
`DISPLAY
`SCHEDULE NAMES
`(ASSOCIATED WITH
`THE RANGE)
`
`FUNCTION
`
`SELECT
`RULE
`
`
`
`CONOTIONAL
`EXPRESSION
`
`ACCEPT
`RULE
`
`MATH
`EXPRESSION
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 25 of 29
`
`5,189,008
`
`coup E | FIG.22
`
`MODEL
`
`UNDEFINED
`WARIABLE
`
`SORT RULES
`
`ANY
`RULES INVOLVEDNNO
`N CIRCULAR
`REFERENCE
`
`YES
`
`GE
`
`ERROR
`MESSAGE
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`CORRECT
`
`PROCEED
`
`PURGE
`RULES
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 26 of 29
`
`5,189,008
`
`PROCESS
`QUERY
`
`FIG.23
`
`ERROR
`MESSAGE
`
`YES
`
`IS
`RULESET
`SUSPENDED
`
`NO
`
`
`
`
`
`
`
`SQUERYNNO
`AMODE.
`
`
`
`PULRANGE
`VALUES
`FROM
`DATABASE
`
`
`
`GENERAGE
`QUERY
`
`YES
`
`
`
`S
`RULESET
`SUSPENDED
`
`NO
`
`YES
`
`YES
`
`ERROR
`MESSAGE
`
`GENERATE
`MODE.
`
`
`
`
`
`
`
`O
`
`CALCULATE
`RULE
`
`GETMODEL
`RULE
`
`6OESRULEN
`CONTAINARANGE
`VARIABLE
`YES
`
`
`
`
`
`
`
`PURANGE
`WALUEFROM
`DATABASE
`
`CACULATE
`RULE
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 27 of 29
`
`5,189,808
`
`FIG.24
`
`for
`
`LINK
`
`VALIDATE
`
`LOAD
`
`DIRECTORY
`
`ERROR
`MESSAGE
`
`
`
`
`
`ENTER
`REFERENCE
`FILE NAME
`
`YES
`
`VALIDATE
`REFERENCE
`CODES
`
`ENTER
`REFERENCE
`FILE NAME
`
`ENTER
`OUTPUT
`FENAME
`
`
`
`WADAED
`REFFIE
`
`YES
`
`LOAD
`OUTPUT
`FILE
`
`GENERATE
`OUTPUT
`REPORT
`
`
`
`
`
`
`
`ERROR
`MESSAGE
`
`ERROR
`MESSAGE
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 28 of 29
`
`5,189.608
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`FIG.25
`
`
`
`VALIDATE
`REFERENCE
`CODES
`
`READPOUND
`SIGN CODES
`FROM
`WORKSHEET
`
`SYNTAX X
`CHECKCODES
`
`CHECKS-E-P-
`INTERSECTIONS
`
`CHECK FOR
`SEPTAND
`CELL REFERENCE
`VALUES
`
`CODE
`ERRORS
`
`YES GENERATE ERROR
`REPORT
`
`MARK
`WORKSHEET
`
`
`
`MARK
`YES WORKSHEET FOR
`BATCH
`PROCESSING
`
`

`

`U.S. Patent
`
`Feb. 23, 1993
`
`Sheet 29 of 29
`
`5,189,008
`
`F.G. 26
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`BATCH
`ITEMSON
`FILE
`
`SELECT
`BATCH
`VALUES
`
`READPOUND
`SIGNCODES
`FROM
`WORKSHEET
`
`
`
`MORE
`INTERSECTIONS
`
`GOTO
`GENERATE
`OUTPUREPORT
`
`GET
`NTERSECTION
`PONT
`
`DETERMINE
`CURRENT
`VALUESFOR
`S-EP
`
`RETRIEVE
`DATA
`FROM
`DAABASE
`
`FORMAT
`DATA
`
`
`
`WRITE OUTPUT
`TOWORKSHEET
`FILE
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`
`

`

`1.
`
`10
`
`15
`
`25
`
`METHOD AND APPARATUS FOR STORING AND
`GENERATING FINANCIAL INFORMATION
`EMPLOYING USERSPECIFIED INPUT AND
`OUTPUT FORMATS
`This is a continuation of application Ser. No.
`07/055,823 filed Jun. 1, 1987, now U.S. Pat. No.
`4,989,541.
`BACKGROUND OF THE INVENTION
`This relates generally to computer systems and more
`particularly to a computer software method and appara
`tus for advanced financial applications such as general
`ledger, inventory, accounts payable, accounts receiv
`able, financial and management reporting, and financial
`analysis and consolidation.
`Corporate software systems generally are divided
`into two categories. The first, basic financial systems,
`includes general ledger, accounts receivable and ac
`20
`counts payable systems. These systems include com
`puter worksheets and databases. The second, advanced
`financial systems and processes, uses information from
`the basic financial systems to perform financial analysis
`and reporting functions.
`At present many of the basic financial systems appli
`cations reside on micro computer software packages.
`Worksheet applications allow the user to keep a two
`dimensional chart of his financial data on an electronic
`worksheet. Illustrative of such spread sheet applications
`30
`is Lotus Development Corporation's LOTUS 1-2-3 (R).
`That program allows the user to set up two dimensional
`worksheets in the form of a grid made up of horizontal
`rows and vertical columns. Each intersection of a row
`35
`or column forms a cell in which data can be stored in
`the form of numeric data (such as an account balance),
`text (such as an account name), or arithmetic operators
`(such as a formula which manipulates the contents of
`other cells). To enter data into a worksheet, the user
`will usually enter data via a keyboard, cell by cell.
`40
`When users employ LOTUS 1-2-3 (R) to perform more
`detailed analyses it is likely that they have also created
`complicated strings of commands (i.e., macros) to facili
`tate data entry, management and reporting capabilities,
`Since these macros have been created by specific indi
`45
`viduals, they can be difficult to revise should business
`dictate. More important, because these macros are tai
`lored to a user's personal needs, the application's useful
`ness across the corporation is limited.
`These spreadsheet programs are also limited by their
`presentation of data in only two dimensions. This often
`requires considerable reorganization of the data before
`it can be used in advanced financial systerns.
`Database packages such as Ashton Tate's dBASE
`III (R) allow the user to keep a financial data base. Fre
`55
`quently, this information is needed for use in a report
`having a format different from that in which it is stored
`or in a spreadsheet such as that generated by one of the
`computer spreadsheets. However, report generation
`can be tedious and a great deal of data manipulation
`must be performed in order to load data from a data
`base into an electronic worksheet. For example, to load
`data from a data base to an electronic spreadsheet, the
`user must convert the data into an ASCII file and subse
`quently download it into an electronic worksheet.
`65
`When data is downloaded into a worksheet each field
`must be inserted into a cell. The downloading of data
`into the worksheet must be done with extreme care,
`
`5,189,.608
`2
`otherwise cells containing formulas may be overwrit
`ten.
`In addition to the above limitations, personal com
`puter programs also generally lack the capacity to in
`plement complex information management and finance
`controls such as audit trails and password protection
`capabilities needed in high-level financial applications.
`These programs also have the limitations that they
`are typing intensive with the result that the user must
`either acquire reasonable typing skills in order to use
`such programs efficiently or he must suffer considerable
`time penalties as he attempts to cope with extensive
`keyboard input.
`SUMMARY OF THE INVENTION
`The present invention is an advanced financial re
`porting and analysis software package. The package
`collects, organizes, manages and consolidates financial
`data and provides user defined capabilities for creating
`financial and corporate reports.
`Data can be loaded into the computer system manu
`ally as well as from known micro-computer packages
`such as LOTUS 1-2-3 (R) and Ashton-Tate's dBase (R)
`and also from departmental and corporate data bases
`and basic financial systems such as general ledger, ac
`counts payable and inventory applications. The soft
`ware package can also incorporate data from outside
`sources, such as Dow Jones News/Retrieval service to
`permit analysis of competitive financial data.
`Data is output from the financial data base of the
`present invention either into reports or directly into
`electronic worksheets. The data can be displayed in
`various ways allowing the user to use the system as an
`analysis tool as well as a production reporting system.
`The process of loading data base information into an
`electronic worksheet is far simpler than the method
`which must be employed when working with two sepa
`rate conventional packages.
`In accordance with the invention, financial data is
`organized into four business classifications or dimen
`sions: Schedule, Entity, Period and Type. Schedule
`identifies the kind of document the data comes from
`(e.g., an income statement, a tax schedule). Entity iden
`tifies the reporting group within the business organiza
`tion (e.g., departments, divisions, subsidiaries). Period
`identifies the range of time that the data represents (e.g.,
`FY 87, Q287). Type provides an additional dimension
`that can be used to further categorize the data (e.g.,
`actual, budget, forecast).
`Data is stored in the system in such a way that all data
`associated with a particular Schedule, Entity, Period
`and Type (SEPT) is identified by that particular SEPT
`value and is stored in a predetermined pattern relative
`to the location of that SEPT value in the data store.
`To accommodate automatic data entry, a mapping
`means or template is provided that specifies for each
`different input spreadsheet the location of the first data
`cell in the spreadsheet and the size of the spreadsheet.
`From this information, the system is able to locate the
`data in the spreadsheet and read it systematically into
`the data store.
`Data is read from the data store by various report and
`spreadsheet generating functions which convert data
`associated with particular SEPT values to desired out
`put formats. For example, one such function might map
`data associated with the same Schedule, Entity and
`Type but consecutive Periods over several years onto a
`spreadsheet having as many columns as there are Peri
`
`50
`
`60
`
`

`

`5, 189608
`4
`3.
`ods so as to produce a spreadsheet showing the varia
`memory of one of the computers 30 and accessible to all
`of them is the data base management program of the
`tion of such data over time.
`present invention which provides for advanced query
`One function of the present invention is to consoli
`date information that arrives at corporation's headquar
`and analysis functions.
`The personal computers illustratively are IBM-PC's
`ters in multiple formats from the corporation's numer
`ous divisions and subsidiaries. Through user-controlled
`or clones or any of the more advanced personal com
`dictionaries within its user interface, the computer ap
`puters now available. As is well known such computers
`plication standardizes the way financial information is
`include a processor, a read/write memory and means
`managed and analyzed within a corporation. In addi
`for writing data into said memory and reading data
`from said memory. Typical memory configurations
`tion, the system allows for hierarchical mapping so that
`10
`subsidiaries are attached to the controlling entities.
`used with the present invention should include at least
`Therefore, when data is input into the database so as to
`640 Kilobytes of semiconductor random access memory
`update an entry, all entities which are attached to the
`and at least a 10 megabyte hard disk. Each such com
`updated entity are also updated.
`puter includes a video display 32, a printer 34, and a
`Other features of the invention include a modeling
`keyboard 36that provides for alphanumeric input, func
`function which is integrated with the data store so that
`tion keys and a cursor control. Data can be input from
`data associated with any SEPT value can be recalled for
`the keyboard or from computer files such as electronic
`use in calculating the model or for comparison with the
`worksheets. Data can be output to printed reports and
`model.
`to electronic worksheets.
`In addition to financial and management reporting
`20
`Unlike conventional data base management systems
`and analysis, other application areas include interna
`or worksheet applications, the system of the present
`tional planning and analysis, consolidation and tax anal
`invention allows for a four dimensional analysis of all
`ysis and the like. Reporting functions include currency
`financial data. In particular, the data stored in the sys
`conversion, journal entries, hierarchy roll-ups and com
`tem is organized into four business classifications or
`putation of year to date totals and variances. Additional
`25
`dimensions, namely Schedule, Entity, Period and Type
`features include audit trails and data verification.
`(SEPT). Schedule identifies the type of document the
`The present invention may be used as a stand alone
`data comes from (e.g., income statements, budgets, tax
`system, but is preferably for departmental use. The
`schedules) Entity identifies a reporting group within the
`financial computer system and process is designed for
`organization (e.g., departments, subsidiaries). Period
`use by all levels of employees who are involved in finan
`30
`identifies the time that the data represents (e.g., FY 87,
`cial control, whether it be a firm's chief financial officer
`Q2 87). Type provides an additional dimension that
`or an end user in the financial department.
`allows the user to further categorize data (e.g., actual,
`The financial system of the present invention is pres
`budgeted, forecast).
`ently sold commercially by the assignee as the FAS
`In storage, all the data associated with a particular
`TARTM financial computer program. Further details
`35
`Schedule, Entity, Period and Type is identified by that
`of the operation of the system are set forth in the FAS
`particular SEPT value. Thus, the system database can
`TARTM Tutorial, Reference Guide, Quick Reference,
`be represented as follows:
`Modeling Guide, and Modeling Quick Reference avail
`able from the assignee, which are incorporated here by
`S1, E1, Pl, T, datacell, . . . datacell
`reference.
`40
`Sk, El P. T., datacell1, ... datacely
`BRIEF DESCRIPTION OF DRAWINGS
`These and other objects, features and advantages of
`where the number of SEPT values can be as great as the
`the invention will be more readily apparent from the
`product of the numbers of Schedules, Entities, Periods
`following description of a preferred embodiment of the
`45
`and Types (i.e., k"1"m"n) and the number of data cells
`invention in which:
`associated with each SEPT value can vary.
`FIG. 1 is a system overview of an illustrative com
`In addition to the data base, the system of the present
`puter system used in the practice of the invention;
`invention also provides a means of mapping input data
`FIG. 2 is a flow chart depicting the user's interaction
`from its source to the location in the database assigned
`with the system;
`50
`to the particular SEPT value with which it is associated
`FIGS. 3A-6B are flowcharts depicting the imple
`and means for mapping data from the database location
`mentation of the Create function of the present inven
`assigned to the SEPT values to an output format. The
`tion;
`input mapping means is referred to below as an input
`FIGS. 7-18 are flowcharts depicting the implementa
`template Several output mapping means are described
`tion of the Input function of the present invention;
`55
`below for the generation of output reports or files.
`FIGS. 19-23 are flowcharts depicting the implemen
`When retrieving data from the system, the user can
`tation of the Query function of the present invention;
`specify data from different categories in each of the
`and
`dimensions. For example, the user may have defined a
`FIGS. 24-26 are flowcharts depicting the implemen
`tation of the Pop-up function of the present invention.
`data base with the following SEPT entries:
`DETAILED DESCRIPTION OF PREFERRED
`EMBODIMENT
`As shown in FIG. 1, the preferred embodiment of the
`invention is a computer system 20 illustratively com
`65
`prising a plurality of personal computers 30 and an
`interconnection network 40. The system can be net
`worked to twenty-five users or more. Resident in the
`
`ENTITIES PERIODS
`Corporate
`Qi 87
`U.S.
`Q287
`Far East
`Q3 87
`Europe
`Q487
`
`TYPES
`Actual
`Budgeted
`Forecast
`Q4 Var
`
`15
`
`SCHEDULES
`Income statement
`Balance Sheet
`Sales Budget
`Tax Schedule
`
`

`

`5, 189,.608
`6
`5
`The user could then retrieve data on the basis of any
`The operations set forth in the main menu of Table I
`combination of the categories found in each of the four
`are as follows.
`dimensions. For example, the user could request:
`The CREATE function allows the user to build tem
`plates, define and modify schedules, hierarchies, dictio
`Schedule=Sales Budget
`5 naries, ranges, and certain system defaults.
`The INPUT function allows the user to input data
`Entity=U.S., Far East
`into a data base from electronic worksheets, computer
`iMr-
`files or a keyboard.
`Period=Q1 87
`The QUERY function allows the user to extract in
`Types= Actual, Budgeted.
`10 formation and create a report or a worksheet with the
`requested information.
`Or he could request:
`The ANALYZE function allows the user to modify
`an existing query without redefining the entire query.
`Schedule=Income Statement
`The REPORT function reformats a previously run
`15 query or model into print pages for viewing or printing.
`Entity=Corporate
`This function also allows the user to design custom
`reports by extracting data from the data base.
`Period=Q1 87, Q287
`The TRANSFER function allows the user to transfer
`Type=Forecast.
`data from one data base to another, to a file or to a
`20 diskette. For example, the user may wish to transfer all
`of his sales data to a file to be used in another computer
`This allows the user to work in a manner in which he is
`system.
`accustomed. Although most financial analysts manually
`MAINTAIN allows the user to perform various data
`analyze data by using this four dimensional approach,
`base management tasks such as creating, copying or
`no known other computer system allows for this
`25 restoring a data base and password protection. The
`'SEPT' method.
`system uses seven levels of passwords to ensure tight
`security. The levels of priority are:
`The General Flow of Operation of the Data Base
`1. System Administration
`Management System
`2. Management Control
`The user enters the database management system by
`30 3. Dictionary Maintenance
`typing the name of the system. As illustrated in Table I,
`4. Data Transfer/Purge
`a screen will appear which will provide (1) the date the
`5. Input Entry
`user entered the system, (2) a copyright notice; (3) a
`6. Input Data
`menu of available operations, (4) a work area, (5) the
`7. Inquiry
`system status, (6) an indication from which database the
`35 X-RUN allows the user to access other software
`computer system is reading, (7) the default drive, (8) the
`packages without leaving the data base management
`SEPT selections and (9) the amount of available mem
`system.
`ory. The last line (10) is a prompt line which describes
`EXIT allows the user to log off. Two options are
`the purpose of a highlighted menu or sub-menu item.
`TABLE I
`
`(1)
`(2)
`(5)
`May 20, 1987
`copyright (C) 1986
`Corp. Class Software
`NUM CAP
`(3) CREATE INPUT QUERY ANALYZE REPORT TRANSFER MAINTAIN X-RUN
`(4)
`
`READY
`EXIT
`
`(7)
`(6)
`Entity
`Schedule
`Drive
`Database
`NCOME ACME
`C
`C: EDEMO.DB
`Create define and modify input schedule, hierarchies, dictionaries & ranges.
`(10)
`
`(8)
`
`Period
`JAN 87
`
`(9)
`Memory
`Type
`ACTUAL 786.96
`
`available: QUIT and BACKUP. BACKUP permits the
`user to backup his database before he logs off.
`The menu of available operations (3) lists the main
`A "POP-UP' function is available throughout the
`functions of the computer system and highlights that
`one of them which is then available to the user. In Table 55 operation of the system. This function is used to extract
`I the lines above and below CREATE identify the
`data and transfer it between files, validate syntax codes
`highlighted function and the prompt line 10 describes
`and view the contents of a specified data cell, schedule,
`the purpose of this function. The user selects a function
`range or dictionary.
`by advancing the highlighter to that function by means
`The operation of the system of the present invention
`of the cursor keys and confirms this selection by de- 60 falls into three phases, namely set-up, production re
`pressing an appropriate function select key such as the
`porting and ad-hoc analysis. Each phase involves spe
`ENTER key. The system will then display a window
`cific computer functions, but all functions are available
`on the screen containing a menu of subfunctions of the
`for use even after set-up has been completed.
`selected function, the first of which will also be high-
`In the "set-up' phase, the user creates user pass
`lighted. The user can then select a subfunction by ad- 65 words, enters data into system dictionaries, sets default
`vancing the highlighter through the menu of subfunc-
`periods and types, specifies printer configurations and
`tions. Upon selection of a subfunction, the system will
`configures the data base management system for input
`then display a menu of further subfunctions and so on.
`by creating input templates and defining hierarchies and
`
`

`

`O
`
`15
`
`20
`
`45
`
`5,189,608
`8
`7
`shown by a line above and below each of these func
`ranges. This phase uses the CREATE and INPUT func
`tions.
`tions.
`The INPUTTEMPLATE function allows the user
`In the "production' phase, the user periodically in
`to build templates which are used as structured gate
`puts data into the computer system, converts and con
`ways for inputting data. All data passes through one of
`solidates it as needed, and outputs the results to work
`these templates before being stored, in the data base.
`sheets or reports for review and distribution. This phase
`The HIERARCHY function allows the user to define
`uses the INPUT, QUERY, ANALYZE, REPORT,
`the structure of the corporation for financial analysis. A
`TRANSFER, MAINTAIN and X-RUN functions.
`hierarchy entity is the entity into which a specified
`The "ad-hoc' analysis phase allows the user to re
`group of other entities, called detailed entities, can be
`view and create analytical models without the con
`straints of formal production reports. This phase uses
`consolidated. The HIERARCHY function defines the
`order in which data can be automatically rolled-up from
`the QUERY and ANALYZE functions.
`detailed e