US20020055953A1

US20020055953A1 - Method and apparatus for handling scenarios in spreadsheet documents

Info

Publication number: US20020055953A1
Application number: US09/985,277
Authority: US
Inventors: Falko Tesch; Matthias Breuer
Original assignee: Sun Microsystems Inc
Current assignee: Sun Microsystems Inc
Priority date: 2000-11-03
Filing date: 2001-11-02
Publication date: 2002-05-09
Also published as: EP1204053A1; US7155666B2; US20020055948A1; EP1204048A1

Abstract

Methods and systems consistent with the present invention include an improved spreadsheet program which is user-friendly, and allows the user to view all previous spreadsheet documents at one time in a tree scenario. Specifically, the user enters data into a spreadsheet of the spreadsheet program, saves each data set entered into the spreadsheet as a scenario using a name or other identifier, and then the user can view all the scenarios created at one time on screen as a tree structure. The user can then compare the different scenarios the user has created, and move between the different scenarios easily.

Description

BACKGROUND OF THE INVENTION

1. Field of the Invention

The present invention relates to the creation of a history of alternative data sets or scenarios in spreadsheet documents having cells arranged in rows and columns.

2. Description of the Related Art

Spreadsheet software is one of the most commonly used software tools used nowadays, apart from word processing applications. A spreadsheet program allows the user to input data into cells which are arranged into columns and rows, and by providing calculation tools, it enables the user to easily evaluate data which can be arranged in a table form. This evaluation can be done by calculating the values of cells by referring to the content of other cells, or it can be done by graphically displaying the content of the spreadsheet document, or by a combination of both.

In known spreadsheet programs, the user is provided with a graphic display of a table (the spreadsheet) having rows of individual cells. The data in the table is arranged in columns, for example, named “A” to “E”, and into rows, for example, numbered from “1” to “4”. A user can then enter data into the individual cells, such as, for example, the number “6” in cell “A1”.

Apart from the graphic visualization tools provided by spreadsheet programs, probably the most important feature which such spreadsheet programs offer to the user is the possibility of calculating the content of the individual cells by referring to other cells. Known spreadsheet programs such as “StarCalc” produced by Sun Microsystems, and others, provide the possibility of entering functions or formulae into individual cells, which define how the content of those cells is to be derived by referring to the content of other cells. For that purpose, usually on the graphical user interface, there is some location where the user can define the reference and the function or formula for the individual cells.

In many cases it is useful to carry out a calculation represented by the spreadsheet based on different alternative starting values or parameters and to compare how the result changes in dependence on the different input parameters. An example may be a proposed loan. The given parameters are the interest rate, capital stock, available funds, and the repayment period. For bank and customer, it is useful to modify some of these parameters and observe the influence on the other parameters in order to optionally adjust the loan to the customer's needs.

Scenarios can thus be utilized where values of spreadsheet cells are altered to compute and visualize various results based on different input data. Multiple scenarios within one spreadsheet can only be manipulated on a one-by-one basis. The user is not able to easily alter between different ways of trying scenarios. Setting up a new scenario at a given point always forces the user to travel back manually, all previous scenarios set up before. This situation worsens with every new generation moving away from the initial data/scenario settings. This is cumbersome and time-consuming in particular if the scenarios relate to more complex spreadsheets.

For instance, in the above example, the user may fix the amount of money the user wants to loan. The user then changes the interest rate, and then the repayment period. The user probably may want to change the interest rate again, but with the original value for the repayment period. However, using known spreadsheet programs, the user has to change all scenarios, the interest rate and the value for the repayment period. In the case of more complex scenarios, the user has to alter numerous scenarios just to go back to the original situation and then again if he wants to follow the same path once more.

It would therefore be desirable to provide a more user-friendly handling of scenarios for spreadsheet programs.

SUMMARY OF THE INVENTION

Methods and systems consistent with the present invention include an improved spreadsheet program which is user-friendly, and allows the user to view all previous spreadsheet documents at one time in a tree scenario. Specifically, the user enters data into a spreadsheet of the spreadsheet program, saves each data set entered into the spreadsheet as a scenario, using a name or other identifier, and then the user can view all the scenarios created at one time on screen as a tree structure. The user can then compare the different scenarios the user has created, and move between the different scenarios easily.

More particularly, methods and systems consistent with the present invention relate to a data processing system which processes a document having cells arranged in rows and columns, and which includes the steps of receiving a data set defining a content of a plurality of cells, wherein a content of one of the plurality of cells is related to a content of at least one of another of a plurality of cells; creating an alternative data set by receiving an input defining an alternative content of at least one of the plurality of cells of the data set; calculating a content of the plurality of cells referencing at least one of the plurality of cells having alternative content to result in the alternative data set having changed data in the plurality of cells; automatically storing the changed data of the alternative data set; assigning an identifier to the alternative data set to enable access to the alternative data set; recognizing and storing an order of creation of each alternative data set; and arranging each stored alternative data set in a tree structure.

The user is thus able to access all previous alternative data sets or scenarios the user has set up. Using the identifier, the user can find a specific alternative data set or scenario which the user wants to use as a basis for calculating further alternative data sets or scenarios.

The data of the spreadsheet may be entered by the user or copied from different computer readable documents.

The order in which the alternative data sets or scenarios are created by the user, is recognized and stored, so that the user can follow the way the user has made the alternative data sets or scenarios. The user may then move forward and backward between different alternative data sets or scenarios, or even move between arbitrary ones of the stored alternative data sets or scenarios. Thus, the user is enabled to follow the history of the alternative data sets or scenarios the user has created, and to easily navigate through these scenarios which may serve as a basis for a new alternative data set or scenario.

The history of alternative data sets or scenarios in the tree structure can be displayed on a display medium, enabling the user to select previous alternative data sets or scenarios using a graphical user interface. The display medium and the graphical user interface include part of the data processing system consistent with the method of practicing the present invention.

The identifier which identifies an alternative data set or scenario in the history of alternative data sets or scenarios, may include a name. This name may be automatically created by the program in memory of the data processing system, and/or may be selectable or changeable by the user.

A further implementation of methods and systems consistent with the present invention include a data processing system for processing a document having cells arranged in rows and columns, the computer including: a memory containing a program that receives a data set defining a content of a plurality of cells, wherein a content of one of the plurality of cells is related to a content of at least one of a plurality of other cells; creates an alternative data set by receiving an input defining an alternative content of at least one of the plurality of cells of the data set; calculates a content of the plurality of cells referencing at least one of the plurality of cells having alternative content to result in the alternative data set having changed data in the plurality of cells; automatically saves the changed data of the alternative data set; assigns an identifier to the alternative data set to enable access to the alternative data set; recognizes and stores an order of creation of each alternative data set; and arranges each stored alternative data set in a tree structure; and a processor which runs the program.

In yet another implementation of methods and systems consistent with the present invention includes an apparatus for processing a document comprising cells arranged in rows and columns, the apparatus including: means for receiving a data set defining a content of a plurality of cells, wherein a content of one of the plurality of cells is related to a content of at least one of another of a plurality of cells; means for creating an alternative data set by receiving an input defining an alternative content of at least one of the plurality of cells of the data set; means for calculating a content of the plurality of cells referencing at least one of the plurality of cells having the alternative content to result in the alternative data set having changed data in the plurality of cells; means for automatically storing the changed data of the alternative data set; means for assigning an identifier to the alternative data set to enable access to the alternative data set; means for recognizing and storing an order of creation of each alternative data set; and means for arranging each stored alternative data set in a tree structure.

In still another implementation of the methods and systems consistent with that of the present invention include a computer-readable memory device encoded with a tree data structure having entries which are accessed by a program that processes a document comprising cells arranged in rows and columns, wherein the program is encoded in the memory device and is run by a processor, each entry comprising an identifier for a data set for the cells of the document to provide access to said data set.

It can be seen from the above embodiments or implementations of the present invention, that the handling of alternative data sets or scenarios is greatly facilitated for the user compared with conventional spreadsheets.

There has thus been outlined, rather broadly, some features of the invention in order that the detailed description thereof that follows may be better understood, and in order that the present contribution to the art may be better appreciated. There are, of course, additional features of the invention that will be described below and which will form the subject matter of the claims appended hereto.

In this respect, before explaining at least one embodiment of the invention in detail, it is to be understood that the invention is not limited in its application to the details of construction and to the arrangements of the components set forth in the following description or illustrated in the drawings. The invention is capable of other embodiments and of being practiced and carried out in various ways. Also, it is to be understood that the phraseology and terminology employed herein, as well as the abstract included below, are for the purpose of description and should not be regarded as limiting.

As such, those skilled in the art will appreciate that the conception upon which this disclosure is based may readily be utilized as a basis for the designing of other structures, methods and systems for carrying out the several purposes of the present invention. It is important, therefore, that the claims be regarded as including such equivalent constructions insofar as they do not depart from the spirit and scope of the present invention.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 depicts a data processing system suitable for practicing methods and systems consistent with the present invention. [0025]
FIG. 2 depicts a client-server configuration suitable for practicing methods and systems consistent with the present invention. [0026]
FIG. 3 depicts in greater detail, the data structure depicted in FIG. 1. [0027]
FIG. 4 depicts an exemplary spreadsheet displayed by the program depicted in FIG. 1. [0028]
FIG. 5 depicts a flowchart of the steps performed by the program depicted in FIG. 1.[0029]

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

Methods and systems consistent with the present invention include an improved spreadsheet program which is user-friendly, and allows the user to view all previous spreadsheet documents at one time in a tree scenario (see FIG. 3). Specifically, the user enters data into a spreadsheet of the spreadsheet program, saves each data set entered into the spreadsheet as a scenario using a name or other identifier, and then the user can view all the scenarios created at one time on screen as a tree structure. The user can then compare the different scenarios the user has created, and move between the different scenarios easily. [0030]
An embodiment consistent with that of the present invention is now discussed with reference to FIG. 1, which depicts a [0031] data processing system 30 for practicing methods and systems consistent with the present invention. The data processing system 30 may comprise a display 25 and system chassis 20 housing a central processing unit CPU 26, an input/output I/O unit 21, a memory 22 with spreadsheet program 29 (hereafter, “the program 29”), and a secondary storage device 24. The program 29 is run from memory 22 in the computer system 30, as shown in FIG. 1, and as described below. Further, the program 29 includes a data structure 40 which has entries that are the stored identifiers or names which enable access to the scenarios presented in the tree structure.
The computer may further comprise standard input devices as input means, like a [0032] keyboard 23, a mouse 28, or a speech processing means (not shown).
Although aspects of one implementation are depicted as being stored in memory, one skilled in the art will appreciate that all or part of systems and methods consistent with the present invention may be stored on or read from other computer-readable media, such as secondary storage devices, like hard disks, floppy disks, and CD-ROM; a carrier wave received from a network such as the Internet; or other forms of ROM or RAM either currently known or later developed. Further, although specific components of data processing system have been described, one skilled in the art will appreciate that a data processing system suitable for use with methods and systems consistent with the present invention may contain additional or different components. [0033]
One skilled in the art will also appreciate that methods, systems, and articles of manufacture consistent with the present invention, may also be implemented in a client-server environment, like the one depicted in FIG. 2. FIG. 2 depicts a [0034] client computer system 60 and server computer system 50 each connected to a network, such as a Local Area Network, Wide Area Network, or the Internet. The spreadsheet may be displayed on a display screen of the client device 60 while some or all steps of the method and system consistent with the present invention, are carried out on the server computer system 50 accessible by the client computer system 60 over, for example, the Internet, using a browser application or the like.
Methods and systems consistent with the present invention will now be explained in more detail, as shown in FIGS. [0035] 3-5.
FIG. 4 depicts a spreadsheet displayed by the program [0036] 29, the spreadsheet comprising cells in rows 1, 2, 3, . . . and columns A, B, C etc. For the sake of simplicity, in FIG. 4, the formulas defining the references, are shown directly in the individual cells.
In method step S[0037] 11, as shown in FIG. 5, the cells of the spreadsheet are filled with content, e.g. by manual input by the user and received by the program 29. It is, however, also possible to input the data by copying them from another spreadsheet or a data base or the like, and the program 29 saves the inputted information.
After having input the content into the cells, the resulting content of the so-defined cells (in the case of FIG. 4, cells C[0038] 1, D1, and E1) is automatically calculated by the program 29 by referring to the content of the referenced cells.
In the example shown in FIG. 4, the cells A[0039] 1 and B1 comprise the parameter values 6 and 10, respectively. The content of the cells C, D and E are calculated on the basis of the parameter values A1 and B1. In the present example, C1 has the value 3, D1 has the value 30, and E1 has the value 33.
In method step S[0040] 12, the program 29 detects whether or not the user intends to create a scenario through user input via a pull-down scenario or the like. Thus, if the user wishes to know the end result E1 based on a different set of parameter values or an alternative data set A, B, the user creates a scenario, for example by replacing value A1=6 with A1=7. Then, if the user then wants to see the result for the combination of parameters A=6 and B=12, the values of both cells A1 and B1 have to be changed.
Accordingly, in step S[0041] 13, if the program 29 detects by the changed parameter values being inputted by the user as an alternative data set (as described above), that the user wishes to create a scenario, the program 29 receives and saves the changed parameter values.
Then, in method step S[0042] 14, the program 29 performs calculations represented by the spreadsheet, using known techniques, based on the changed parameter values, and the result is displayed by the program 29 on a display medium 25.
Subsequently, in step S[0043] 15, the scenario is stored in a storage medium 22 or 24 by the program 29. In order to save storage space it is, of course, possible only to store those values of the data set of the scenario which have been changed with respect to an earlier data set which is already stored in memory 22 or 24 by the program 29.
In step S[0044] 16, the program 29 automatically assigns a set name or identifier to the scenario created by the user, and the scenario is then stored together with the identifier in a storage medium 22 or 24 in a tree structure (see FIG. 3). Alternatively, the name or identifier is assigned to a scenario by the user and received by the program 29. The identifier allows a later user access to the scenario by reference to the identifier when shown in the tree structure. It is possible that the user can change this assigned identifier or name to a more descriptive name the user can easily recognize, which is then saved in memory 22 or 24 by the program 29. Thus, navigation between different scenarios of a history of scenarios as shown in the tree structure, is facilitated.
It should be acknowledged here that the order of execution of method steps S[0045] 15 and S16 may be interchanged.
In method step S[0046] 17, the history of scenarios created by the user is retrieved by the program 29, and displayed by the program 29 on a display screen 25 as a tree structure. The scenarios can also be displayed as a list instead of the tree structure shown in FIG. 3. Any other suitable type of display is also possible.
In method step S[0047] 18, the user can move forward and backward or jump between the earlier scenarios already stored in the memory 22 or 24 by the program 29 and displayed in the tree structure. Preferably the user is enabled by the program 29, to move along the branches of the tree structure using an input device like a mouse 28 or jumping between the different scenarios of the tree structure with the help of a particular navigation tool.
In method step S[0048] 19, if the user decides to change a scenario, and selects one of the earlier scenarios, the program 29 retrieves the selected scenario from the tree structure and displays the earlier scenario on the display screen 25 as a spreadsheet.
In step S[0049] 20, based on this retrieved scenario, the user may decide to change the scenario, and then go back to step S13 to change one of the parameters in the spreadsheet again and create a new scenario (method steps S13-S16). Instead of manually inputting all parameter values again the user can select the closest scenario from the scenario list or tree structure, and take this as the starting point of a new scenario.
If the user decides not to change the scenario in step S[0050] 20, then the user proceeds to the end of the document in step S21. Then, the user can then either end the application, or return to step S12 to create a new scenario.
Accordingly, the use and application of scenarios, in particular to complex calculations, is greatly improved. [0051]
FIG. 3 shows an example of a tree structure data structure representing a history of scenarios, the names of which are in this example, [0052] scenario 1, scenario 2 . . . Below the name in brackets, the parameter values on which the scenario is based, are indicated.
In the example shown in FIG. 3, i means the interest rate of a loan, r the repayment period and c the capital sum. Two scenarios are connected by straight lines if only one parameter has been changed, i.e., c=60,000 instead of c=50,000 in [0053] scenario 2 with respect to scenario 1, or r=11 compared to r=10 in scenario 6 with respect to scenario 3. Two scenarios are connected by a broken line if they differ in two parameters, as for example scenario 4 and scenario 5. The tree structure thus allows the user an easy navigation through the scenarios.
If only two parameters are changed in the scenarios, a representation as a two dimensional table by the program [0054] 29, may be advantageous. Changes of one parameter are represented by a position in the horizontal direction by the program 29, and changes of the other parameter are represented by a position in the vertical direction by the program 29. In the case of changing three parameters, a three dimensional representation depicted by the program 29 would be desirable.
It should be appreciated by one skilled in the art that the program [0055] 29 comprises means for receiving the inputted data set, means for creating an alternative data set by receiving the input defining an alternative content of the data set, means for calculating a content of the cells referencing the alternative content to result in the alternative data set having changed data, means for automatically storing the changed data of the alternative data set, means for assigning an identifier to the alternative data set to enable access to the alternative data set, means for recognizing and storing an order of creation of each alternative data set, and means for arranging each stored alternative data set in a tree structure.
While the invention has been particularly shown with reference to the above embodiments, it will be understood by those skilled in the art that various other changes in the form and details may be made therein without departing from the spirit and the scope of the invention. [0056]

Claims

What is claimed is:

1. A method in a data-processing system for processing a document comprising cells arranged in rows and columns, said method comprising the steps of:

receiving a data set defining a content of a plurality of cells, wherein a content of one of said plurality of cells is related to a content of at least one of another of a plurality of cells;

creating an alternative data set by receiving an input defining an alternative content of said at least one of said plurality of cells of said data set;

calculating a content of said plurality of cells referencing said at least one of said plurality of cells having said alternative content to result in said alternative data set having changed data in said plurality of cells;

automatically storing said changed data of said alternative data set;

assigning an identifier to said alternative data set to enable access to said alternative data set;

identifying and storing an order of creation of each said alternative data set; and

arranging each said stored alternative data set in a tree structure.

2. The method of claim 1, wherein the document is a spreadsheet document.

3. The method of claim 1, wherein said data set is inputted by a user.

4. The method of claim 1, wherein said stored alternative data set is transmitted from a computer memory device.

5. The method of claim 1, further comprising the step of moving between each said different stored alternative data set.

6. The method of claim 1, wherein said identifier allows access to an arbitrary one of each said stored alternative data set.

7. The method of claim 1, further comprising the steps of:

displaying said tree structure on a display medium; and

enabling the user to select said alternative data set represented in said tree structure using a graphical user interface.

8. The method of claim 1, wherein said identifier of said alternative data set comprises a name.

9. The method of claim 8, wherein said name is automatically created when said alternative data set is stored.

10. The method of claim 8, wherein said name is selectable by the user.

11. The method of claim 6, further comprising:

providing the user a navigation tool to move between each said different alternative data set.

12. A computer readable medium containing instructions that cause a data processing system to perform a method of processing a document comprising cells arranged in rows and columns, the method comprising the steps of:

receiving a first data set in at least one of a plurality of cells;

creating an alternative data set by receiving input into said plurality of cells and automatically performing a predetermined calculation based on said first data set;

automatically storing said alternative data set;

assigning an identifier to said alternative data set; and

storing said data set and said alternative data set in a tree structure.

13. The method of claim 12, wherein said first data set in said at least one of said plurality of cells is derived based on a relationship to a content in at least another of said plurality of cells.

14. The method of claim 12, wherein said identifier allows access to said alternative data set.

15. The method of claim 12, wherein each said alternative data set is stored in an order of creation.

16. The method of claim 12, wherein the document is a spreadsheet document.

17. The method of claim 12, wherein said data set is inputted by a user.

18. The method of claim 12, wherein each said stored alternative data set is transmitted from a computer memory device.

19. The method of claim 12, further comprising the step of moving between each said different stored alternative data set.

20. The method of claim 12, wherein said identifier allows access to an arbitrary one of each said stored alternative data set.

21. The method of claim 12, further comprising the steps of:

displaying said tree structure on a display medium; and

enabling the user to select each said alternative data set represented in said tree structure using a graphical user interface.

22. The method of claim 12, wherein said identifier of each said alternative data set comprises a name.

23. The method of claim 22, wherein said name is automatically created when each said alternative data set is stored.

24. The method of claim 23, wherein said name is selectable by the user.

25. The method of claim 19, further comprising:

26. A data processing system for processing a document comprising cells arranged in rows and columns, said system comprising:

a memory containing a program that receives a data set defining a content of a plurality of cells, wherein a content of one of the plurality of cells is related to a content of at least one of a plurality of other cells; that creates an alternative data set by receiving an input defining an alternative content of at least one of the plurality of cells of the data set; that calculates a content of the plurality of cells referencing at least one of the plurality of cells having alternative content to result in the alternative data set having changed data in the plurality of cells; that automatically saves the changed data of the alternative data set; that assigns an identifier to the alternative data set to enable access to the alternative data set; that identifies and stores an order of creation of each alternative data set; and that arranges each stored alternative data set in a tree structure; and

a processor which runs the program.

27. A computer readable medium containing instructions that cause a data processing system to perform a method of processing a document comprising cells arranged in rows and columns, the method comprising the steps of:

automatically storing said changed data of said alternative data set;

recognizing and storing an order of creation of each said alternative data set; and

arranging each said stored alternative data set in a tree structure.

28. An apparatus for processing a document comprising cells arranged in rows and columns, said apparatus comprising:

means for receiving a data set defining a content of a plurality of cells, wherein a content of one of said plurality of cells is related to a content of at least one of another of a plurality of cells;

means for creating an alternative data set by receiving an input defining an alternative content of said at least one of said plurality of cells of said data set;

means for calculating a content of said plurality of cells referencing said at least one of said plurality of cells having said alternative content to result in said alternative data set having changed data in said plurality of cells;

means for automatically storing said changed data of said alternative data set;

means for assigning an identifier to said alternative data set to enable access to said alternative data set;

means for recognizing and storing an order of creation of each said alternative data set; and

means for arranging each said stored alternative data set in a tree structure.

29. A computer-readable memory device encoded with a tree data structure having entries which are accessed by a program that processes a document comprising cells arranged in rows and columns, wherein the program is encoded in the memory device and is run by a processor, each entry comprising:

an identifier for a data set for the cells of the document to provide access to the data set.

30. A method in a data processing system for processing a document having cells, the method comprising the steps of:

receiving a first data set for the cells;

receiving a second data set for the cells; and

displaying both the first data set and the second data set at substantially the same time.

31. The method of claim 29, wherein the data set is displayed in a tree structure.

32. The method of claim 29, wherein said first data set and said second data set each has values, and wherein the method further comprises the step of receiving an edit to one of said values.