Populating cells of an electronic financial statement

Abstract

A computer-implemented method of populating row and column cells of an electronic financial statement document containing with formulas and data, and related apparatus. A formula for a cell in a financial statement is generated from a row definition for the row and a column definition for the column of the cell, where the row definition defines a term of the statement and the column definition specifies a period of time. The resulting statement is displayed to a user, and new formula expressions can be generated to reflect changes in the statement. The statement can have three kinds of columns, namely base columns, subtotal columns, and grand total columns. Data in columns of an input database corresponds to a database period of time, and the base columns in the statement have statement period unit. For each base column of the statement, a correspondence to more than one column of the input database can be computed dynamically. The cells of the statement column can be populated using data from the corresponding database columns. The cells can be repopulated in response to a change in the statement period unit.

Claims

What is claimed is:

1. A computer-implemented method of generating a formula expression for a cell in an electronic financial statement, the cell being at an intersection of a row and a column of the financial statement, the method comprising:

obtaining a row definition for the row, the row definition being applicable to the entire row and defining a term of the statement;

obtaining a column definition for the column, the column definition being applicable to the entire column and defining a period of time; and

deriving from the row definition and the column definition a formula expression for the cell, the formula expression being completely defined by the term and the period.

2. The method of claim 1, wherein:

deriving from definitions a formula expression is done with reference to the position of the cell with respect to other columns in the statement.

3. The method of claim 1, further comprising:

generating the formula expression for the cell in response to a change in either the row definition or the column definition.

4. The method of claim 3, further comprising:

evaluating the formula expression in each cell of the statement having a formula expression and displaying the resulting statement to a user; and

obtaining data for the formula expressions from a database.

5. The method of claim 4, further comprising:

providing a statement base period, being the smallest time duration represented in a column of the statement; and

providing a database base period, being the smallest time duration represented in a record of the database, the database base period being no greater than the statement base period.

6. The method of claim 5, wherein:

the database base period is automatically detected from column labels read from the database.

7. The method of claim 3, further comprising:

displaying the statement to a user;

receiving from the user a command to change a current time period style of the statement to a new time period style, a time period style specifying the period of time covered by the statement and the duration of a period unit defining the temporal granularity of the statement; and

generating a new formula expression for the cell according to the new time period style, and displaying the resulting statement to the user.

8. The method of claim 3, further comprising:

displaying the statement to a user; and

receiving from the user a command to use a new database for input data having a new base period different from a current base period, generating a new formula expression for the cell reflecting the new base period, and displaying the resulting statement to the user.

9. The method of claim 3, further comprising:

providing three kinds of columns, namely base columns, subtotal columns, and grand total columns, where for a row holding a flow term, a subtotal column has a row value defined as the sum of base column values, and a grand total column has a row value defined as the sum of subtotal columns, and for a row holding a stock term, a subtotal column has a row value defined as a preceding base column value, and a grand total column has a row value defined as a preceding subtotal column.

10. The method of claim 9, further comprising:

collapsing the period columns to show the user a view of the statement consisting of the term column and total columns.

11. The method of claim 9, further comprising:

receiving from a user a subtotal time period for a subtotal column, comparing the subtotal time period to the period of time covered by the statement, and extending the period of time covered by the statement to allow computing the subtotal column.

12. The method of claim 9, further comprising:

receiving from the user a subtotal time period for a subtotal column, determining whether the beginning of the user-input subtotal time period is earlier than, later than, or the same as the beginning of the period of time covered by the statement, and leaving a row value in the subtotal column blank according to the result of the determining step if the row has a flow term.

13. The method of claim 9, further comprising:

accepting from a user a command to insert a subtotal column in the statement and generating new formula expressions in cells of the statement reflecting the command to insert a subtotal column.

14. The method of claim 13, further comprising:

accepting from a user a command to insert a grand total column in the statement and generating new formula expressions in cells of the statement reflecting the command to insert a grand total column.

15. A computer program residing on a computer-readable medium for generating a formula expression for a cell in an electronic financial statement, the cell being at an intersection of a row and a column of the electronic financial statement, the computer program comprising instructions to:

obtain a row definition for the row, the row definition being applicable to the entire column and defining a term of the statement;

obtain a column definition for the column, the column definition being applicable to the entire column and defining a period of time; and

derive from the row definition and the column definition a formula expression for the cell, the formula expression being completely defined by the term and the period.

16. A computer-implemented method of generating content in an electronic financial statement having cells at intersections of rows and columns, each cell having a cell value, the method comprising;

obtaining a row definition associated with an entire row of the financial statement;

obtaining from the statement data a first column definition associated with an entire first column of the financial statement and a second column definition associated with an entire second column of the financial statement, each column definition specifying a period of time, the first and second columns being different columns of the financial statement; and

generating from the row definition and the first and second column definitions a first cell value for a first cell at the intersection of the row and the first column and a second cell value for a second cell at the intersection of the row and the second column, the first cell value being generated by evaluating a first formula expression generated automatically and solely from the row definition and the second column definition, the second cell value being generated by evaluating a first formula expression generated automatically and solely from the row definition and the second column definition.

Description

MICROFICHE APPENDIX

This application includes as Appendix A a microfiche appendix titled Appendix A--Microfiche of Financial Knowledge Base, having 3 fiche with a total of 156 frames.

The foregoing portion of the disclosure of this patent document contains material that is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.

BACKGROUND

The invention relates to creating and editing computer-readable electronic documents, and more particularly to creating and editing domain-specific documents including a report, such as a financial statement.

Reports can be used to summarize, organize, calculate, and analyze data. For example, financial data or sales data can be summarized by one or more financial or sales reports that represent different aspects of the condition or operation of a business entity. To create a financial statement or a sales report, it is often useful to calculate totals, subtotals, averages, counts, or other summaries for different aspects of a business (e.g., total sales for each relevant region, or total sales across regions). Electronic reports are particularly useful because they can be automatically updated when new or different information becomes available. For example, a spreadsheet program such as Microsoft.RTM. Excel can be used to create a report in the form of an electronic spreadsheet that consists of a matrix of rows and columns for displaying values and text and for calculating values automatically based upon user-defined formulas. An electronic spreadsheet can also be formatted to vary the presentation of information contained within the report. To simplify the creation of an electronic spreadsheet, a spreadsheet program can automatically enter certain information (e.g., the same information, or an incremental series, such as numerals, ordinals, dates, and months) into a range of cells based upon a user's initial entry of information into one or more cells. A user can also insert a formula into a cell for calculating values on the spreadsheet. Values can be entered directly into a formula, or the values in other cells can be used in a formula by including references to the other cells in the formula. Spreadsheet programs typically include many built-in formulas that can be used alone or in combination with other formulas.

SUMMARY OF THE INVENTION

In general, in one aspect, the invention features a method of generating a formula expression for a cell that is at an intersection of a row and a column in a financial statement. The method includes obtaining a row definition for the row and a column definition for the column, and interpreting the row definition and the column definition to generate a formula expression for the cell. The row definition defines a term of the statement and the column definition specifies a period of time.

Implementations of the invention may include one or more of the following features. The definitions may be interpreted with reference to the position of the cell with respect to other columns in the statement. The formula expression for the cell may be generated on the fly in response to a change in either the row definition or the column definition. The formula expression in each cell of the statement having a formula expression may be evaluated and the resulting statement may be displayed to a user. Data for the formula expressions may be obtained from a database.

A statement base period and a database base period may be provided. The statement base period is the smallest time duration represented in a column of the statement, and the database base period is the smallest time duration represented in a record of the database, the database base period being no greater than the statement base period. The database base period may be detected automatically from column labels read from the database. The statement may be displayed to a user. A command may be received from the user to change a current time period style of the statement to a new time period style, the time period style specifying the period of time covered by the statement and the temporal granularity of the statement. A new formula expression for the cell may be generated according to the new time period style, and the resulting statement may be displayed to the user. A command may be received from the user to use a new database having a new base period that is different from a current base period, a new formula expression for the cell reflecting the new base period may be generated, and the resulting statement may be displayed to the user.

Three kinds of columns may be provided, namely base columns, subtotal columns, and grand total columns. For a row holding a flow term, a subtotal column may have a row value defined as the sum of base column values, and for a row holding a stock term, a subtotal column may have a row value defined as a preceding base column value. A grand total column has a row value defined as the sum of subtotal columns for a row holding a flow term and has a row value defined as a preceding subtotal column for a row holding a stock term. The period columns may be collapsed to show the user a view of the statement consisting of the terms column and total columns. A subtotal time period for a subtotal column may be received from the user, the subtotal time period may be compared to the period of time covered by the statement, and the period of time covered by the statement may be extended to allow computing the subtotal column.

The beginning of the subtotal time period may be compared to the beginning of the period of time covered by the statement, and a row value in the subtotal column may be left blank if the row has a flow term. A command may be accepted from the user to insert a subtotal column or a grand total column in the statement and generate new formula expressions in cells of the statement reflecting this insertion.

In another aspect, the invention is directed to a method of populating a financial statement having columns and rows. The method includes having columns as a source of input data for the statement. The data in the columns corresponds to a database period unit of time. A statement period unit for the base columns of the statement is identified. The statement period unit is greater than the database period unit. For each base column of the statement, the method dynamically computes a correspondence to more than one column of the input database. The cells of a statement column are populated using data from the corresponding database columns.

The statement may be displayed to a user, the user may change the statement period unit, and the cells may be repopulated in response to a change in the statement period unit. The database base period unit may be detected automatically from column labels read from the database. The statement may be displayed to the user. In response to the user request subtotal columns may be inserted in the statement, and the statement including the subtotal columns may be populated with cell formulas for calculating cell values that include values for the subtotal columns. A row may hold a flow term or a stock tern. The cell formula for a row holding a flow term may define a sum of base column values, and a row holding a stock may define a copy of a preceding base column value. A grand total column may be inserted in the statement in response to a user request. Cells of the grand total column may be populated with cell formulas for calculating cell values. For a row holding a flow term, a grand total column may have a cell value defined as the sum of subtotal column values.

In another aspect, the invention is also directed to, and apparatus characterized by, computer programs tangibly stored on a computer-readable media having instructions for causing a processor to carry out the methods of the invention.

The invention may be implemented as one or more computer programs, program modules, and computer-readable data that are stored on computer-readable media and that have instructions for causing a processor to perform the actions described.

Among the advantages of the invention are the following. The tasks of creating and editing documents are simplified by the separation of the presentation of information contained in a document from underlying representation of calculations and interrelationships. A user may define and customize standard reports with a simple palette of powerful editing tools that implement and maintain the semantics of the items in a document. This frees the user from having to handle the underlying references directly, allowing the user to focus instead upon concepts and contexts contained within the document. The invention provides for a component library of standard financial concepts and definitions, including definitions of financial statements, terms, formulas, and projection operation; for immediate interactive redefinition of column time period attributes; and for immediate interactive redefinition of periodic subtotals and grand totals. This allows users to manipulate the content and appearance of financial analyses without repeatedly having to define and verify calculations. An input document, such as an input spreadsheet, on which input terms are collected provides a highly-useful perspective from which a user may view the data obtained from a database and perform what-if analyses. Use of an input document simplifies creation and maintenance of a report using information from a database. An input document also provides a user-friendly interface with one more data warehouses. The invention provides the ability to create database templates customized for particular database vendors, financial data suppliers, or according to company-specific formats; such templates link a user's database to the user's dictionary of terms and hence to the financial statement generator.

Other features and advantages will become apparent from the following description, including the claims.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 is a block diagram of a system for creating and editing a financial statement.

FIG. 2 is a diagrammatic view of a financial statement as displayed to a user.

FIG. 2A is a diagrammatic view of an example of a financial workbook, which includes an income statement, a balance sheet statement, a cash flow statement, and a ratio statement.

FIG. 2B is a diagrammatic view of an input statement for the financial workbook of FIG. 2A.

FIG. 3 is a flow diagram of a method by which a user operates the system of FIG. 1 to create and edit a financial statement.

FIG. 3A is a flow diagram of an initialization method used in creating and editing a financial statement.

FIG. 3B is a flow diagram of a method of creating a new financial statement.

FIG. 3C is a diagrammatic view of a three-pane browser for viewing terms defined in a financial dictionary.

FIG. 4 is a diagrammatic view of the section headings and associated terms appearing in an income statement.

FIG. 5 is a flow diagram of a method of creating a section in a financial statement.

FIG. 6 is a diagrammatic view of a menu of financial statement editing tools.

FIG. 7 is a flow diagram of a method of inserting a term into a financial statement.

FIG. 8 is a flow diagram of a method of creating an alias for a term appearing in a financial statement.

FIG. 9 is a flow diagram of a method of duplicating a row in a financial statement.

FIG. 10 is a flow diagram of a method of itemizing a row in a financial statement.

FIG. 11 is a flow diagram of a method of deleting a row in a financial statement.

FIG. 12 is a diagrammatic view of an input statement for the income statement of FIG. 4.

FIG. 13 is a flow diagram of a method of updating an input statement.

FIGS. 13A and 13B are flow diagrams of a method of copying input terms from a financial statement into an input statement.

FIGS. 13C, 13D, and 13E are flow diagrams of a method of defining formulas on a financial statement.

DETAILED DESCRIPTION

Referring to FIG. 1, a system 22 for creating and editing a financial statement report is implemented as a group of computer programs, program modules, and computer-readable data that are stored on computer-readable media and that operate to cause a computer to perform the actions described in this specification. System 22 includes a financial statement editor 24 that has a financial knowledge base 23 and editor objects and methods 25. The financial knowledge base 23 includes a financial dictionary of terms representing financial concepts with predefined properties and interrelationships. The editor objects and methods 25 control the operation of an electronic spreadsheet program 26 in response to user actions directed to the editor objects and methods 25 through a user interface 28, such as a graphical user interface operated under control of system 22. Statement editor 24 embodies and manifests a general financial model of possible contexts, parameters and values, as well as hierarchies and rules relating them. A user may provide overrides 29 to change one or more aspects of financial statement editor 24. In operation, system 22 separates the presentation of financial data and analyses from the underlying data and numeric calculations. Using system 22, a user indicates the financial model elements to be contained within a financial statement. In response, system 22 manages the conceptual and numeric relationships among the selected elements, indicates what information is needed to produce the statement--which information may be entered by hand or acquired under program control from computer-readable source such as a financial database 30--and ensures that this information is used and calculated consistently within one or more financial statements. Information in database 30 can be stored in the form of an electronic spreadsheet, a relational database, or some other electronic form.

In the particular embodiment being described, system 22 is implemented as a computer program running on a personal computer. Spreadsheet program 26 is the Microsoft.RTM. Office 97 version of Excel ("Excel"). Financial statement editor 24 is implemented as an add-in to Excel and includes an object-oriented program module that is written in the Microsoft.RTM. Visual Basic.RTM. for Applications programming language (version 5.0). In particular, financial statement editor 24 includes a financial knowledge base 23; maintenance routines for verifying and organizing financial knowledge base 23; and editing objects and methods 25. The financial knowledge base 23 includes definitions of financial terms representing financial concepts. Editing objects and methods 25 set up menus and tool bars, open and manipulate a project workbook, provide dialogue sheets, provide a browser for displaying the contents of financial knowledge base 23, represent the contents of one or more financial statements, and carry out editing functions. In this particular implementation, editor objects and methods 25 invoke Excel objects and methods. Editor objects and methods 25 also access financial knowledge base 23 to provide editing menus and editing tools. Editor objects and methods 25 may be invoked when a user applies one of the editing tools, as described below. In this embodiment, financial database 30 is in the form of an electronic Excel spreadsheet. User interface 28 provides an Excel spreadsheet and other user interface elements that are displayed on a computer display device (such as a monitor or liquid crystal display screen). User interface 28 responds to user actions such as typing keys on a computer keyboard, moving a mouse or other pointing device to move a cursor across the computer display, or activating a computer mouse button. System 22 stores in computer memory a project workbook representing a financial project. A project workbook contains one or more financial statements, an input statement (which in the implementation being described is a financial statement spreadsheet used for collect input values), a hidden status spreadsheet, saved objects corresponding to financial statements, and macros for initializing links to spreadsheet program 26 and for saving the financial statement objects when the corresponding Excel spreadsheets are opened and closed.

As shown in FIG. 2, user interface 28 displays a mockup financial statement. The financial statement spreadsheet is referred to as a "mockup" because it is being created and edited. The mockup shows the current appearance of the financial statement so that a user can readily see how an editing operation changes the appearance of the financial statement. User interface 28 shows a statement name area 32 that identifies the kind of statement being represented, a project name area 34 that identifies the name of the project in which the financial statement is contained, a time periods area 36 that identifies the time periods covered by the financial statement, a user-modifiable terms area 38 that represents financial concepts contained within the financial statement, and a values area 40 that can be automatically populated with spreadsheet formulas and/or values corresponding to the associated financial terms. The values in values area 40 are copied from an input statement (described below) which obtains values from the user, from database 30, or from both of these sources. Financial statement editor 24 prevents a user from editing cells within values area 40 that are automatically populated with spreadsheet formulas and/or values. A user can, however, create additional rows and columns that can incorporate calculations and data contained within other spreadsheet cells.

Referring to FIGS. 2A and 2B, a computer-readable project workbook 42 includes one or more user-specified financial statements, such as an income statement 44, a balance sheet statement 46, a cash flow statement 48, and a ratio statement 50, each representing a different aspect of a financial project over the selected time range. Project workbook 42 may also include a customized financial statement containing one or more user-selected financial sections and terms. After a user has specified the contents of any of the financial statements in workbook 42, system 22 automatically generates an input statement 52, also called an input sheet, which identifies all of the information (inputs) needed to populate the financial statements with formulas and values. Input statement 52 includes a project name area 54, an area 56 that identifies the statement as an input statement, an area 58 that contains the time periods covered by the financial statements of project workbook 42, a terms area 60, and an inputs (values) area 62. Some of the terms contained in terms area 60 may not be displayed in any of the financial statements but are needed to calculate the values associated with the terms that actually appear on one or more of the financial statements in workbook 42. Input statement 52 contains only those inputs (and associated terms) needed to populate the financial statements of workbook 42; any input that is not needed is automatically removed from input statement 52 when the input statement is updated. Input values appearing on input statement 52 are color-coded to identify the source or other properties of the input values: values copied from database 30 are displayed in blue, user-supplied values that override database values are displayed in magenta, and user-supplied values that do not override database values are displayed in green.

Referring to FIG. 3, a user may open a previously-created project workbook that was saved in a computer memory such as a disk (step 64). The user may then edit the one or more statements in the project, as described in detail below (step 66). Alternatively, a user may create a new project (step 68). The user may then create a new statement based upon a financial statement template containing financial concepts arranged in a predefined format, or the user may open an empty statement (step 70). For example, the user may select a template for an income statement, a balance sheet statement, a ratio statement, or a cash flow statement (step 72). After selecting a template, the user may specify the time range and time periods to be displayed in the financial statement (step 74). The time periods of the mockup financial statement are represented as a finTime_Styles object in financial statement editor 24. The user may then edit the financial statement with one or more financial statement editing tools (step 66). If the user does not select a financial statement template, the user may create a customized financial statement by specifying the time range and time periods to be displayed in the financial statement (step 74) and by creating and editing the financial statement with one or more financial statement editing tools (step 66). The user may insert financial terms in the terms area of the mockup financial statement one at a time, or the user may insert an entire financial section into the mockup financial statement. In order to populate the mockup financial statement with data, the user selects an UPDATE editing tool (described below) or selects the spreadsheet tab which corresponds to the input statement at the bottom of the project workbook interface window (step 67). System 22 creates (or updates) input statement 52 with all of the required input terms, populates input statement 52 with data from financial database 30 (if supplied), and inserts formulas that refer to this data in the mockup financial statement. The database is handled by a Visual Basic module that to the financial statement editor 24 has the appearance of an object.

Referring to FIG. 3A, in response to a user's command to create a new financial statement or project (step 68; FIG. 3), system 22 initiates a new project as follows (step 80). System 22 opens a template project workbook that contains one or more Excel spreadsheets (step 82). System 22 renames and saves the workbook in a memory device, such as a computer hard disk (step 84). System 22 opens and verifies the format of financial database 30 (step 86), the identity of which is a property of the project. System 22 sets the globals for the project (step 88). System 22 then creates a new financial statement (step 90). Once the financial statement has been created, system 22 returns control to an event loop and responds to subsequent user actions (step 92). The methods and data related to a project are implemented in a Visual Basic module that has the appearance of an object.

Referring to FIGS. 3B and 3C, after a user has directed system 22 to create a statement, the user is prompted to supply the name of the statement ("STMTNAME"). System 22 then initializes statement globals (step 100). If the requested statement name corresponds to a previously saved statement, system 22 obtains from the project workbook an object (of type finStatement) which contains all of the information needed to reproduce the saved mockup financial statement (spreadsheet) and enable a user to modify the statement with the financial statement editing tools of system 22; otherwise, system 22 creates a new object (of type finStatement) for a new mockup financial statement (step 102). System 22 then opens and clears the mockup financial statement (step 104). If the time periods to be displayed in the financial statement have not been specified (step 106), system 22 invokes a time period wizard to prompt the user to supply the time periods to be displayed in the financial statement (step 108).

The time period wizard creates a sequence of time period labels in a scratch sheet in the project workbook corresponding to the time style (defined by finTime_Styles)--a sequence of dates starting the start date and running through the end date, with a duration of the base period unit. If there are any totals columns (subtotals, or subtotals and grand total) the ends of totals periods are defined as follows: the end of a Year is December or Quarter 4 or last week or 365; the end of a Quarter is March, June, September, or December, or last week in quarter, or last day in quarter; the end of a Month is last week in month or last day in month; and the end of a Week is Saturday (last day in week). A total is inserted after the date that is the last in the total's period. Total columns are described later, particularly in reference to the MODIFY PERIODS editing tool 187 (FIG. 6).

System 22 enters a header (namely, the statement name and project name) and the specified time periods into the mockup financial statement, as shown in FIG. 2 (step 110). System 22 displays on the computer display device a three-pane financial browser 111 (FIG. 3C). The user can interact with the browser to insert into a mockup financial statement a full financial statement, a full financial statement section, or an individual financial term. The fill financial statement may be selected from a list shown in the left pane of the browser, which list may include an income statement, a balance sheet statement, a cash flow statement, or a ratio statement. A full financial statement section may be selected from a list shown in the center pane of the browser as a type of a selected full statement. An individual financial term may be selected from a list shown in the right pane of the browser as a subtype of a selected section type. A financial term may be, for example, a ratio expression, a total expression, a net expression, another compound expression, or an arithmetic expression of a specified type, such as sales, costs, income, dividends, change in retained earnings, and other types. In effect, financial browser 111 provides a three-pane hierarchical display that shows subtypes and subparts of the conceptual graph of properties stored as a table in a KBProps spreadsheet, which is described later. If the user selects a predefined, full financial statement (step 112), system 22 automatically populates the mockup financial statement with the section headings and financial terms for the selected statement according to its definition (step 114). System 22 displays a palette or menu of spreadsheet editing tools, initializes event handlers, unlocks the first column of the mockup financial statement (corresponding to financial terms area 38 in FIG. 2), and protects the mockup financial statement so that a user cannot directly edit name area 32, project name area 34, time periods area 36, or values area 40 (step 115). These areas may later be edited when the user applies to a selected area one or more of the editing tools described below. System 22 then returns control to the event loop and responds to subsequent user actions (step 92).

A financial statement generally has one or more financial sections, each having one or more associated financial terms. For example, income statement 44, shown in FIG. 4, was created based on a predefined income statement template in financial statement editor 24, and includes a sales section 116, a costs section 118, an income section 120, a dividends section 122, and a change in retained earnings section 124. Each of these sections includes one or more financial terms. For example, sales section 116 includes a gross sales term 126, a discounts term 128, an allowances term 130, a returns term 132, and a net sales term 134. Each term is characterized as either an input term or a calculated term. Input terms (e.g., gross sales term 126) have values that are obtained from financial database 30 or are input directly by a user. Calculated terms (e.g., net sales term 134) have values that are derived from spreadsheet formulas. For example, net sales term 134 has a value that is derived from the following spreadsheet formula:

net sales=gross sales-(discounts+allowances+returns).

The value associated with net sales term 134 therefore depends upon the values for gross sales term 126, discounts term 128, allowances term 130, and returns term 132, each of which will be referred to as a "direct predecessor" of net sales term 134. Gross profit term 136 has a value that is derived from a spreadsheet formula 135 that includes two direct predecessors which are calculated terms (net sales and cost of sales); the terms from which values are obtained for direct predecessor calculated terms (e.g., net sales and cost of sales) will be referred to simply as "predecessors" of the original calculated term (e.g., gross profit term 136). Thus, net sales term 134 and cost of sales term 137 are direct predecessors of gross profit term 136. Gross sales term 126, discounts term 128, allowances term 130, returns term 132, cost of sales cash term 138 and depreciation & amortization term 140 are predecessors of gross profit term 136. Conversely, net sales term 134 is a "direct dependent" of gross sales term 126, discounts term 128, allowances term 130, and returns term 132.

Financial knowledge base 23 is stored as a matrix of terms and associated relations, which define a conceptual graph. This matrix is conveniently stored in an electronic spreadsheet called KBProps (reproduced in the attached Appendix) that is accessed by finObjects modules routines. Each term (referred to as a KBATOM) in financial knowledge base 23 is characterized by the following relations: ISA, PNAME, PARTOF, SUBPARTS, PREDEFINEDALIASES, ACTUALFORMULA, STOCKFLOW, UNITS, SECTIONHEADER, SECTIONFORMAT, NAMEFORMAT, LINEFORMAT, and INFORMULA. The properties PARTOF and INFORMULA are calculated by a dictionary maintenance program, and for that reason are not shown in the attached appendix. For example, net sales term 134 is characterized as follows:

                   Definition of Net Sales Term
    KBATOM               Net_Sales
    ISA                  Net_Expression
    PNAME                Net Sales
    PARTOF               SALES_SECTION
                         MARGIN_ANALYSIS
    PREDEFINEDALIASES    "Revenue"
                         "Revenues"
                         "Sales-Net"
                         "Net Revenues"
    ACTUALFORMULA        Gross_Sales - (Discounts + Allowances +
                         Returns)
    STOCKFLOW            Flow
    NAMEFORMAT           (Income_statement grayshade)
    LINEFORMAT           (income_statement skipafter)
    INFORMULA            Contribution_Margin
                         Gross_Profit
                         Net_Sales_Cash
                         Sales_Growth_Percent
                         Contribution_Margin_Percent
                         Gross_Margin_Percent
                         SG_and_A_Percent_Of_Sales
                         R_and_D_Percent_of_Sales
                         Operating_Income_Margin_Percent
                         Return_on_Sales

    finStatement Properties Type      Description
    Name                 String    Name of spreadsheet where
                                   statement appears
    finRows()            finRow
    numRows              Integer   Number of rows in this statement
    fincolumns()         finColumn
    numColumns           Integer   Number of columns in this
                                   statement
    IsINPUTSheet         Boolean   True if this is the input sheet
    TimePeriodRange      Range     Row corresponding to the
                                   time period labels
    TimeStyleSelected    Integer   Index of selected time style for this
                                   statement

    Additional finStatement
    Properties           Type    Description
    Formatted            Boolean Input sheet has been set
                                 up with headers and labels
    DataRequired         Boolean Blanks exist in the data area

    finRow Properties    Type    Description
    Name                 String  Built-in financial term
                                 in knowledge base
    Alias                String  User's name for this term
    ChangedStyle         Boolean User changed the dictionary-defined
                                 formatting style of the
                                 name or the line
    DuplicateOf          String  Existing term user duplicated
                                 to create this row
    NumDuplicates        Integer Number of duplicates user has
                                 created of this existing term
    DuplicateCode        Integer Unique identifier for this duplicate
    ItemizationOf        String  User has itemized this existing term
    ItemizationCode      String  Unique identifier for this itemization
    NumItemizations      Integer Number of itemizations user
                                 has created of this term
    Hidden               Boolean This row is currently hidden in the
                                 currently displayed statement
    UserDefinedInput     Boolean User converted this to an input
                                 (by editing mockup financial
                                 statement)
    AssumeZero           Boolean User wants to assume values are zero
    DependentMadeInput   Boolean User made parent an input
                                 and hid this predecessor
    HistDataSource       Integer Source of historical data (e.g., user
                                 database)
    HeaderOf             String  This row is a header for the indicated
                                 section
    Calculated           Boolean Term is calculated (not an input)
                                 according to dictionary definition
    Format               Integer Current style of name (e.g.,
                                 bold, italic) and line shading,
                                 spacing
    Parent               String  finStatement name
    directPredecessors() String  Terms directly referenced in
                                 this item's formulas
    NumDirectPredecessors Integer Number of direct predecessors
    DirectDependents()   String  Terms using this term in a formula
    NumDirectDependents  Integer Number of direct dependents
    RefersTo             Variant Entire row in spreadsheet (an
                                 Excel range object)
    NumSecondaryRefs     Integer Number of times this term has been
                                 cloned
    SecondaryReferenceOf Range   Term this is a clone of
    UserDefinedRow       Boolean Entire row is set by user (ignored by
                                 financial statement editor)
    PriorYrHistory       String  Value = X for term having
                                 X.sub.13 Prior in its formulas

    Additional Input finRow
    Properties           Value   Description
    InputforStmts()      Integer finStatement numbers for
                                 which this row is an input
    NumInputStmts        Integer Number of statements for
                                 which this row is an input
    ExclusivelyOneFstmt  Boolean Row is only an input for the
                                 statement being processed

    finTime_Styles Property Type    Description
    Name                 String  Name user has defined for this view
    BaseDate             Date    Date to use for "this period"
                                 (generally last year)
    BeginDate            Date    Beginning date of period (may be
                                 historical)
    EndDate              Date    Ending date of period
    Periodunit           String  Duration of one period (e.g., year)
    numPeriods           Integer Number of period labels in the range
    BasePeriodsAreTotals Boolean Period unit is greater than
                                 database unit and therefore base
                                 columns are totals
    NumInputPeriods      Integer Number of period labels
                                 generated for input spreadsheet
    ActuallyWroteTotals  Boolean Total labels were included (property
                                 is ignored if duration is too short)
    SubTotalUnit         String  Period unit of subtotals; non-blank
                                 causes subtotal columns to be
                                 computed and shown
    GrandTotalUnit       String  Period unit of grand totals; non-blank
                                 causes grand total columns to be
                                 computed and shown
    InvalidRange         Boolean Indicates that defined time style has
                                 invalid dates
    DBInconsistent       Boolean Previous style is inconsistent
                                 with new database

    finColumn Properties Type    Description
    Name                 String  Defined name of the column
    Time                 Date    Actual date corresponding to label in
                                 spreadsheet
    Parent               String  finStatement name
    ColumnNumber         Integer Number of column in spreadsheet
    LabelCell            Range   Cell corresponding to the time label
    PeriodKind           String  Period of Time (e.g., year, month)
    Refers to            Variant Entire column in spreadsheet
    TotalStartColumn     Integer Spreadsheet column that is first in
                                 sequence for computing total
    TotalEndColumn       Integer Input column that is last in
                                 sequence for base period of statement
                                 (when base periods are totals)
    TotalType            String  "", "SubTotal", or "GrandTotal"
    IncompleteTotal      Boolean Too few preceding columns to
                                 calculate this total

    Database Object
    Properties      Type    Description
    Exists          Boolean True if database is linked to this project
    PeriodUnit      String  Period unit represented by columns of this
                            database

                         Editing Options
    User Action                               System Response
    Press Enter (or Return) key when cursor positioned Insert a new term
    on a blank row
    Type over term name                       Create an alias
    Press backspace key followed by Enter (or Return) Delete a term
    key
    Select search tool in financial knowledge base Display an
    dialogue                                  alphabetical list of
                                              all terms in the
                                              financial
                                              knowledge base
    Select statement name then pull down menu Add a blank row
    INSERT/Rows                               above the first term
    Type over statement name (automatically changes Replace statement
    statement tab)                            name
    Use Excel's formatting tool bar (automatically Reformat statement
    displayed when a cell is selected in formattable name, period labels,
    areas)                                    data, and borders
    Create a blank row before and after terms to be Create a new section
    grouped, then type a section header name in the first
    blank row
    Select the header for a section, then apply the Move Move an entire
    Up or Move Down editing tool              section
    Select either an Itemization TOTAL or an existing Create a new
    item, then apply the Itemization editing tool subitem
    Apply the Show Hidden editing tool        Reveal hidden
                                              terms created
                                              when formulas are
                                              generated
    Type a new number over a calculated value Temporarily reveal
                                              the effect
                                              of changing a
                                              calculated value;
                                              the number will
                                              revert to its
                                              calculated value
                                              when user
                                              selects another cell
    Enter values in the input spreadsheet (user-defined Enter or modify
    values appear in magenta there and in the mockup input values,
    statement if they overwrite database values, in green propagate through
    if user supplies initial values, and in blue if values formulas
    correspond to database values)
    Create a new statement or revise an existing full Create a statement
    statement, then save the project          template
    Save a project before closing, then open the saved Re-edit a project
    project                                   during a later
                                              Excel session
    To clone a term, insert term again on any Create a secondary
    spreadsheet                               reference

                                    Base period total  Additional total
                    Regular column  (smallest unit of  (subtotal or grandtotal
                    (period unit same statement > database unit > smallest unit
     of
    Term X          as database unit) unit)              statement)
    Input Stock     Input!X         Last column of this Previous column in the
                                    database input     statement
                                    sequence
    Input Flow      Input!X         Sum of inputs for this Sum of columns of
     previous
                                    database sequence  type
    Calculated      Formula as is   Formula as is; inputs Previous column,
     except
    Stock                           must be in the sheet "prior" refers to
     previous
                                                       column of same period
     type
    Calculated Flow Formula as is   Formula as is; inputs Formula as is (sums
     up the
                                    must be in the sheet column)

• Inventors
  Clancey, William J.; Hecht, Lee; Helfert, Erich A.; Wu, John; Canty, Edgar P.;
• Assignee
  Modernsoft, Inc. (Palo Alto, CA)
• Published
  Sep-18-2001
• Current US Classes:
  705/30
  705/35
  715/503
• Application #
  114590
• International Classes
  G06F 017/21
• Field of Search
  707/503 707/504 707/538 707/540 705/30 705/35 705/36
• Examiner
  Feild; Joseph H.
• Agent
  Fish & Richardson, P.C.
• US Patent References:
  4642767
  4989141
  5293615
  5359724
  5537590
  5553215
  5644727
  5761508
  5768158
  6002865
  6112199
  6134563
  6134564