Method and apparatus for graphic analysis of variation of economic plans5727161
Abstract
Graphic analyses are displayed on a computer display screen showing graph lines of mathematical relationships between goals and factors in a spreadsheet plan and depicting continuums of what-ifs across ranges of variation from the spreadsheet plan, providing information for planning and management of the factors to improve results for the goals. Goals and factors reflect elements of the plan for which, in the spreadsheet, values are contained in output and input cells respectively. The goals and factors are selected by the user from lists that are created and displayed. After such selection, the designated graphic analysis is performed and the graph lines for the selected goal and factor(s) are displayed. The user is able to analyze and evaluate entire ranges of "what-if" possibilities using the graph lines. Desired graph points can be moved to interactively in order to arrive at desired what-if possibilities. At any selected what-if possibility, print and numeric text can be displayed that provides information about the selected what-if possibility. For any desired graph point, a new spreadsheet plan model can be generated with new data reflecting the selected graph point. Selected graphic analyses can be saved in a method and customized user interface that simplify later redevelopment of the graphic analyses ready for further interactive moves to what-if possibilities.
Claims
What is claimed is:
1. A method that relates to graphic analysis of variation of economic plans, comprising:
accessing a plan-model having variables including inputs defined to have externally entered input values and outputs defined to have output values determined through plan-model processes affected by one or more of said input values, said plan-model being changeable into any of a number of what-if states, each of said what-if states being a particular combination of particular values for said inputs and any change in any of said input values being a change of said plan-model from one what-if state to another what-if state;
choosing at least a first goal from said outputs and at least a first factor from said inputs, said first factor being chosen based on said first goal and being an economic parameter, that is useful to a user in planning and managing said first goal and based on desire for information associated with a functional relationship between said first goal and said first factor contained in said plan-model wherein a change in the value of said first factor causes a change in the value of said first goal;
obtaining input values for an initial what-if state of said plan-model, in which what-if state all input values and all output values of said plan-model including the values of said first factor and said first goal are plan values;
establishing at least a first magnitude of variation for the value of said first factor, said first magnitude of variation being established based on said first factor plan value, said variation being in an axis of value variation independent of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model; and
providing a first graph having at least a first graph line in which each graph point reflects a different what-if state of said plan-model, said first graph line developed by determining representative graph points each representing a different first factor substitute value and a corresponding goal variant value for said first goal, wherein:
values of said representative graph points are determined and used to display said first graph and said first graph line in a computer process that includes generation of new values for said representative graph points using said plan-model and said plan values and display of said first graph with said first graph line using said representative graph points, which computer process is independent of user setup of additional modeling to create a table of such values external to said plan-model, said user setup of additional modeling including separate user steps to enter in spreadsheet cells external to said plan-model a plurality of first factor values and a formula reference for calculation of corresponding first goal values;
each of said first factor substitute values is determined based on said first factor plan value and said first magnitude of variation, each of said first factor substitute values is different from and a replacement for said first factor plan value, said first factor substitute values differ from any value of said first factor obtained from said initial what-if state of said plan-model and used in determination of said first factor substitute values, and said first factor substitute values differ from any value of said first factor obtained from the user and used in determination of said first factor substitute values; and
each of said corresponding first goal variant values is the value of said first goal using said plan-model processes with said first factor plan value replaced by the first factor substitute value to which said first goal variant value corresponds, and depending on said relationship between said first goal and said first factor contained in said plan-model said corresponding goal variant values for said first goal are different from the plan value of said first goal, differ from any value of said first goal obtained from said initial what-if state of said plan-model and used in determination of said first goal variant values, and differ from any value of said first goal obtained from the user and used in determination of said first goal variant values;
in which graph line information is provided for said relationship between said first goal and said first factor and the value of said first goal at each value of said first factor for at least ten values of said first factor differing from any value of said first factor obtained from said initial what-if state of said plan-model and differing from any value of said first factor obtained from the user.
2. A method, as claimed in claim 1, wherein:
said accessing step includes linking to said plan-model and software providing said plan-model to enable interaction with said software and said plan-model.
3. A method, as claimed in claim 1, wherein:
said accessing step includes providing said plan-model in an electronic spreadsheet.
4. A method, as claimed in claim 1, wherein:
said choosing step includes creating menu lists of a number of said outputs for user selection of goals and of a number of said inputs four user selection of factors.
5. A method, as claimed in claim 1, wherein:
said choosing step includes identifying said outputs of said plan-model for user selection of goals or said inputs of said plan-model for user selection of factors.
6. A method, as claimed in claim 1, wherein:
said choosing step includes identifying said inputs of said plan-model affecting any particular output for user selection of factors.
7. A method, as claimed in claim 1, wherein:
said choosing step includes locating a number of said outputs to be offered for selection of goals and a number of said inputs to be offered for selection of factors in particular locations in said plan-model.
8. A method, as claimed in claim 1, wherein:
said choosing step includes designating a name for each of a plurality of said outputs which are selectable as goals including said first goal and each of a plurality of said inputs which are selectable as factors including said first factor.
9. A method, as claimed in claim 1, wherein:
said obtaining step includes acquiring a number of said input plan values from said plan-model or software containing said input plan values.
10. A method, as claimed in claim 1, wherein:
said providing step includes displaying a plurality of graph lines representing variation from plan values of a plurality of factors including said first graph line representing variation from plan value of said first factor, said displaying step including displaying said graph lines relative to a vertical axis associated with goal variant values of said first goal and a horizontal axis of factor substitute values associated with said plurality of factors including said first factor chosen from said inputs, with each of said graph lines illustrating said first goal variant values for a plurality of factor substitute values for a different factor of said plurality of factors, wherein each of said plurality of graph lines represents an axis of value variation of said plan-model established independently of said axes of value variation represented by the input and output values in said initial what-if state of said plan-model and contains at least ten graph points each:
representing for at least one of said plurality of factors a factor substitute value different from all values of said factor obtained from said initial what-if state of said plan-model and from the user and used in determination of said factor substitute value;
representing for said first goal a corresponding goal variant value determined using said factor substitute value and, depending on said relationship between said first goal and said first factor contained in said plan-model, different from all values of said first goal obtained from said initial what-if state of said plan-model and from the user and used in determination of said first goal variant value; and
reflecting a what-if state of said plan-model different from said initial what-if state of said plan-model and different from the what-if state of said plan-model reflected by each of the rest of said ten graph points.
11. A method, as claimed in claim 10, wherein:
said displaying step includes illustrating said first graph line with said first goal variant values as a function of percentage-related deviation from plan value of said first factor.
12. A method, as claimed in claim 1, wherein:
said providing step includes displaying a plurality of graph lines representing combined variation from said plan values of two independently varying factors including said first graph line, said displaying step including displaying a vertical axis associated with goal variant values of said first goal and displaying a horizontal axis associated with factor substitute values of said first factor, with each of said graph lines illustrating said first goal variant values as a function of said first factor substitute values and a different value for said second factor, wherein said plurality of graph lines contains at least ten graph points each representing:
factor substitute values for each of two factors, each of said factor substitute values different from all values of that factor obtained from said initial state of said plan-model and from the user and used to determine said factor substitute values;
a corresponding goal variant value for said first goal different from all values of said first goal obtained from said initial state of said plan-model and from the user and used to determine said goal variant value; and
a what-if state of said plan-model different from said initial what-if state of said plan-model, and said plurality of graph lines represents value variation of said plan-model in a combination of two axes of value variation each established independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model.
13. A method, as claimed in claim 12, wherein said displaying step includes:
defining a factor substitute values axis and a goal variant values axis with units associated therewith;
determining a factor substitute values range and axis-scale increment for said first factor using said first factor plan value;
determining a factor substitute values graph line-set range and increment for said second factor using the plan value of said second factor;
determining a goal range and axis-scale increment for said first goal;
determining appropriate changes in ranges and increments for said factor and goal axes and said graph line-set;
displaying said plurality of graph lines including said first graph line; and
showing labels for said factor axis, said goal axis and said graph line-set.
14. A method, as claimed in claim 13, wherein:
said step of determining said first goal range and axis-scale increment includes obtaining high and low first goal values across combined ranges of said first factor substitute values and second factor substitute values using said substitute values of said first factor and said second factor and the relationship contained in said plan-model between said first goal and said first factor and said second factor, and arriving at said first goal range and axis-scale increment using said high and low first goal values.
15. A method, as claimed in claim 1, wherein:
said providing step includes developing said graph points of said first graph line by calculating a plurality of first goal variant values using a plurality of said first factor substitute values.
16. A method, as claimed in claim 1, wherein:
said providing step includes displaying said first graph line.
17. A method, as claimed in claim 16, wherein:
said displaying step includes illustrating said first graph line on a display screen.
18. A method, as claimed in claim 1, wherein said providing step includes:
producing said first graph line associated with said plan-model wherein said relationship between said first goal and said first factor contained in said plan-model that said first graph line reflects is defined in said plan-model by a plurality of formulas separately expressed and separately located in said plan-model.
19. A method, as claimed in claim 1, further comprising:
choosing from said inputs of said plan-model a second factor different from said first factor;
obtaining for said second factor a second factor plan value
establishing for said second factor at least a first magnitude of variation in an axis of value variation determined independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model; and
providing a number of graph lines, including said first graph line, containing a plurality of graph points representing factor substitute values for said first factor and corresponding goal variant values for said first goal and a plurality of graph points representing factor substitute values for said second factor and corresponding goal variant values for said first goal.
20. A method, as claimed in claim 19, wherein:
said providing step includes determining each of said first goal variant values using a factor substitute value for at least one of said first and second factors.
21. A method, as claimed in claim 1, wherein:
said providing step includes developing and displaying a plurality of graph lines including said first graph line, with said plurality of graph lines illustrating goal variant values of said first goal for factor substitute values for a plurality of factors, wherein:
said plurality of graph lines represents variation of said plan-model from said initial what-if state of said plan-model in a plurality of axes of value variation each established independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model; and
for each of a plurality of factors, said plurality of graph lines contains at least ten graph points representing a number of factor substitute values for said factor, wherein each of said ten graph points:
represents for at least said factor a factor substitute value differing from all values of said factor obtained from said initial what-if state of said plan-model and from the user and used in determination of said factor substitute value;
represents for said first goal a corresponding variant value determined using said factor substitute value and, depending on said relationship between said first goal and said factor contained in said plan-model, differing from all values of said first goal obtained from said initial what-if state of said plan-model and from the user and used in determination of said first goal variant value; and
reflects a what-if state of said plan-model different from said initial state of said plan-model and different from the what-if state of said plan-model reflected by each of the rest of said ten graph points.
22. A method, as claimed in claim 1, wherein:
said accessing step includes providing at least portions of said plan-model in software that includes at least one of the following: spreadsheet software, accounting software, data base software and innovative graphic analysis software.
23. A method, as claimed in claim 22, wherein:
said providing step includes linking to said software to enable interaction with said software and said plan-model.
24. A method, as claimed in claim 1, wherein said providing step includes:
defining a factor substitute values axis and a goal variant values axis and units associated therewith;
determining a factor substitute values range and a factor substitute values axis-scale increment for said first factor using said first factor plan value;
displaying said factor substitute values axis with increment scaling and labeling;
determining a goal variant values range and a goal variant values axis-scale increment for said first goal;
displaying said goal variant values axis with increment scaling and labeling; and
displaying said first graph line.
25. A method, as claimed in claim 24, wherein said step of determining said first goal variant values range and axis-scale increment includes:
determining first goal variant high values and low values using said first factor substitute values and said relationship between said first goal and said first factor contained in said plan-model; and
arriving at said first goal variant values range and goal variant values axis scale increment using said high values and said low values.
26. A method, as claimed in claim 24, further including:
changing by the user when appropriate at least one of said first factor range and said first goal range.
27. A method, as claimed in claim 26, wherein:
said changing step further includes modifying said axis-scale increment for at least one of said factor axis and said goal axis.
28. A method, as claimed in amended claim 1, wherein:
said providing step includes analyzing a shape of said first graph line which is a functional relationship between said first goal and said first factor, for the purpose of determining a series of values of said first factor which in combination with corresponding values of said first goal will define a series of graph point that upon display with straight line segments connecting consecutive graph points produce a graph line providing a visual representation of said functional relationship.
29. A method, as claimed in claim 28, wherein:
said analyzing step includes determining a plurality of graph points along said first graph line, with each of said graph points comprised of one of said first factor substitute values and one of said first goal variant values determined using said first factor substitute value and said relationship between said first goal and said first factor.
30. A method, as claimed in claim 28, wherein
said analyzing step includes analyzing a direct expression of a number for formulas in said plan-model.
31. A method, as claimed in claim 1, wherein:
said providing step includes using said plan-model to calculate said first goal variant values based on said first factor substitute values.
32. A method, as claimed in claim 1, further including:
creating a user interface for said graph that involves at least one of the following: creation of methods of choosing said goals and factors, methods of moving interactively on said graph, and methods of illustrating information relative to values of factors and goals for graph points moved to on said graph.
33. A method, as claimed in claim 1, wherein:
said relationship between said first goal and said first factor includes a plurality of formulas separately expressed and separately located in said plan-model.
34. A method, as claimed in claim 1, wherein:
said establishing step includes establishing said first magnitude of variation in an axis of plan-model value variation determined independently of the axes of value variation represented by any series of values in said initial state of said plan-model including any series of values contained in a horizontal row or a vertical column or a series of pages or a list in said initial state of said plan-model.
35. A method, as claimed in claim 1, wherein:
said providing step includes showing said first graph line wherein each of a number of segments of said first graph line represents a continuum of graph points with each of said graph points depicting a different first factor substitute value and a corresponding first goal variant value and reflecting a different what-if state of said plan-model.
36. A method, as claimed in claim 1, wherein:
said providing step includes determining a plurality of graph line definition first factor values which together with corresponding first goal variant values represent a series of graph line definition graph points such that straight line segments connecting consecutive graph points in said series show additional graph points reflecting the relationship between said first goal and said first factor in said plan-model.
37. A method, as claimed in claim 36, further including:
changing by the user said determining of graph line definition first factor values.
38. A method that relates to graphic analysis of variation of economic plans, comprising:
accessing a plan-model having variables including inputs defined to have externally entered input values and outputs defined to have output values determined through plan-model processes affected by one or more of said input values, said plan-model being changeable into any of a number of what-if states, each of said what-if states being a particular combination of particular values for said inputs and any change in any of said input values being a change of said plan-model from one what-if state to another what-if state;
choosing at least a first goal from said outputs and at least a first factor from said inputs, said first factor being chosen based on said first goal and being an economic parameter that is useful to a user in planning and managing said first goal and based on desire for information associated with a relationship between said first goal and said first factor contained in said plan-model wherein a change in the value of said first factor causes a change in the value of said first goal;
obtaining input values for an initial what-if state of said plan-model, in which what-if state all input values and all output values of said plan-model including the values of said first factor and said first goal are plan values;
establishing at least a first magnitude of variation for the value of said first factor, said first magnitude of variation being established based on said first factor plan value, said variation being in an axis of value variation independent of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model;
providing a first graph having at least a first graph line in which each graph point reflects a different what-if state of said plan-model, said first graph line including graph points each representing a different first factor substitute value and a corresponding goal variant value for said first goal, wherein:
each of said first factor substitute values is determined based on said first factor plan value and said first magnitude of variation, each of said first factor substitute values is different from and a replacement for said first factor plan value, said first factor substitute values differ from any value of said first factor obtained from said initial what-if state of said plan-model and used in determination of said first factor substitute values, and said first factor substitute values differ from any value of said first factor obtained from the user and used in determination of said first factor substitute values; and
each of said corresponding first goal variant values is the value of said first goal using said plan-model processes with said first factor plan value replaced by the first factor substitute value to which said first goal variant value corresponds, and depending on said relationship between said first goal and said first factor contained in said plan-model said corresponding goal variant values for said first goal are different from the plan value of said first goal, differ from any value of said first goal obtained from said initial what-if state of said plan-model and used in determination of said first goal variant values, and differ from any value of said first goal obtained from the user and used in determination of said first goal variant values;
in which graph line information is provided for said relationship between said first goal and said first factor and the value of said first goal at each value of said first factor for at least ten values of said first factor differing from any value of said first factor obtained from said initial what-if state of said plan-model and differing from any value of said first factor obtained from the user;
moving interactively along said first graph line to arrive at a first graph point of a plurality of graph points represented by said first graph line, said first graph point representing a first factor substitute value selected by the user from a plurality of factor substitute values represented by the range of a factor axis and reflected by said plurality of graph points along said first graph line, for which first factor substitute value information is desired regarding the corresponding goal variant value of said first goal;
determining said corresponding goal variant value of said first goal represented by said first graph point using said first factor substitute value and the relationship between said first goal and said first factor contained in said plan-model; and
illustrating said factor substitute value and said corresponding first goal variant value represented by said first graph point,
wherein said first graph line represents at least ten graph points including said first graph point to which the user can move interactively, each of said graph points representing a factor substitute value of said first factor different from any value of said first factor obtained from said initial what-if state of said plan-model and representing a what-if state of said plan-model different from said initial what-if state of said plan-model.
39. A method, as claimed in claim 38, further comprising:
creating a plan-model revised version reflecting factor and goal values represented by said first graph point, differing from said plan-model by having at least one factor substitute value different from said factor's plan value.
40. A method, as claimed in claim 39, wherein:
said creating step includes making a copy of said plan-model and in said copy changing plan values of a number of factors to substitute values of said factors that correspond to said first graph point.
41. A method, as claimed in claim 38, wherein:
said moving interactively step includes moving interactively to one of a plurality of graph lines selected by the user to move along and illustrating which of said plurality of graph lines has been moved to.
42. A method, as claimed in claim 41, further comprising:
Moving interactively relative to a plurality of graph lines including said first graph line each representing said relationship between said first goal and said first factor relative to a first factor axis and a first goal axis with each of said graph lines representing said relationship with a different value for a second factor, to a graph point that is not on a displayed graph line but between two of said graph lines, wherein said graph point represents a first factor substitute value relative to said first factor axis and also associated with said first factor substitute value represents a factor substitute value for said second factor that is defined by the position of said graph point relative to said two graph lines and the goal variant value represented by said graph point is determined using said factor substitute values of said first and second factors and the relationship between said first goal and said first and second factors contained in said plan-model.
43. A method, as claimed in claim 38, wherein:
said illustrating step includes displaying markers located relative to said first graph point moved to and said factor axis and a goal axis illustrating the selected factor substitute value of said first graph point moved to relative to said factor axis and the corresponding goal variant value of said first graph point relative to said goal axis.
44. A method, as claimed in claim 38, wherein:
said illustrating step includes presenting a number indicating said first factor substitute value moved to relative to said factor axis to arrive at said first graph point along said first graph line and a number for the first goal variant value of said first graph point determined using said first factor substitute value and said relationship between said first goal and said first factor contained in said plan-model.
45. A method, as claimed in claim 38, wherein:
said illustrating step includes displaying numbers showing values associated with said first graph point for a plurality of factors including said first factor substitute value and for a plurality of goals including said first goal variant value.
46. A method, as claimed in claim 45, wherein:
said displaying step includes displaying for each of a plurality of graph points, including said first graph point, numbers showing values associated with said graph point for a plurality of factors and a plurality of goals.
47. A method, as claimed in claim 38, wherein:
said moving interactively step includes locating a marker at each of a number of said graph points and displaying at least one text area for user entry and display of information related to at least one of said graph points having one of said markers.
48. A method, as claimed in claim 1, further comprising:
saving in computer memory a file of a number of said graphs including said first graph having at least said first graph line, said file having for each of said graphs information to reestablish relinking to said plan-model and reproduce said graph and enable moving interactively to graph points along at least said first graph line on said graph by moving to factor substitute values relative to a factor axis and illustrate information on said graph points moved to including goal variant values of said graph points determined using said factor substitute values moved to and the relationship between the goal and the factors represented on said graph.
49. A method, as claimed in claim 38, wherein:
said moving interactively step includes moving to a graph point representing a factor substitute value for at least one factor different from every value of said factor used to determine how to display said graph line.
50. A method, as claimed in claim 38, wherein:
said moving interactively step includes:
determining a first increment of consecutive values of said first factor moveable to along said graph line relative to said factor axis; and
user adjusting of the values of said first factor that can be moved to along said graph line relative to said factor axis including user adjusting of the increment between consecutive factor values moveable to along said graph line.
51. A method, as claimed in claim 38, wherein:
said moving interactively step includes moving interactively to any of at least 100 graph points along said first graph line representing different factor substitute values of said first factor.
52. A method that relates to graphic analysis of variation of economic plans, comprising:
accessing a plan-model having variables including inputs defined to have externally entered input values and outputs defined to have output values determined through plan-model processes affected by one or more of said input values, said plan-model being changeable into any of a number of what-if states, each of said what-if states being a particular combination of particular values for said inputs and any change in any of said input values being a change of said plan-model from one what-if state to another what-if state;
choosing at least a first goal from said outputs and at least a first factor from said inputs, said first factor being chosen based on said first goal and being an economic parameter that is useful to a user in planning and managing said first goal and based on desire for information associated with a relationship between said first goal and said first factor contained in said plan-model wherein a change in the value of said first factor causes a change in the value of said first goal;
obtaining input values for an initial what-if state of said plan-model, in which what-if state all input values and all output values of said plan-model including the values of said first factor and said first goal are plan values;
establishing at least a first magnitude of variation for the value of said first factor, said first magnitude of variation being established based on said first factor plan value, said variation being in an axis of value variation independent of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model;
providing a first graph having at least a first graph line in which each graph point reflects a different what-if state of said plan-model, said first graph line including graph points each representing a different first factor substitute value and a corresponding goal variant value for said first goal, wherein:
each of said first factor substitute values is determined based on said first factor plan value and said first magnitude of variation, each of said first factor substitute values is different from and a replacement for said first factor plan value, said first factor substitute values differ from any value of said first factor obtained from said initial what-if state of said plan-model and used in determination of said first factor substitute values, and said first factor substitute values differ from any value of said first factor obtained from the user and used in determination of said first factor substitute values; and
each of said corresponding first goal variant values is the value of said first goal using said plan-model processes with said first factor plan value replaced by the first factor substitute value to which said first goal variant value corresponds, and depending on said relationship between said first goal and said first factor contained in said plan-model said corresponding goal variant values for said first goal are different from the plan value of said first goal differ from any value of said first goal, obtained from said initial what-if state of said plan-model and used in determination of said first goal variant values, and differ from any value of said first goal obtained from the user and used in determination of said first goal variant values;
in which graph line information is provided for said relationship between said first goal and said first factor and the value of said first goal at each value of said first factor for at least ten values of said first factor differing from any value of said first factor obtained from said initial what-if state of said plan-model and differing from any value of said first factor obtained from the user; and
moving interactively including moving a reference marker to at least a first goal variant value of said first goal relative to a goal axis on said graph and illustrating at least one graph point on said first graph line of said graph that corresponds to said goal variant value moved to, wherein said graph point represents a factor substitute value for said first factor which if substituted for the plan value of said first factor in said plan-model will change said plan-model to a what-if state in which the value of said first goal corresponds to the first goal variant value moved to.
53. A method, as claimed in claim 52, wherein:
said moving interactively step includes displaying graphically along each of a plurality of graph lines including said first graph line at least one graph point along said graph line, including said graph point on said first graph line, representing said goal variant value of said first goal moved to and a factor substitute value relative to a factor axis which in association with said graph line and the relationship between said first goal and factors associated with said graph line will produce said goal variant value moved to for said first goal.
54. A method, as claimed in claim 52, wherein:
said illustrating step includes displaying a number for said first factor substitute value of said graph point determined using said first goal variant value moved to and said relationship between said first goal and said first factor contained in said plan-model.
55. A method, as claimed in claim 54, wherein:
said displaying step includes manipulating said plan-model to find said first factor substitute value.
56. An apparatus that relates to graphic analysis of variation of economic plans, comprising:
a plan-model having variables including inputs defined to have externally entered input values and outputs defined to have output values determined through plan-model processes affected by one or more of said input values, said plan-model being changeable into any of a number of what-if states, each of said what-if states being a particular combination of particular values for said inputs and any change in any of said input values being a change of said plan-model from one what-if state to another what-if state, from which at least a first goal is chosen from said outputs and at least a first factor is chosen from said inputs, said first factor being chosen based on said first goal and being an economic parameter that is useful to a user in planning and managing said first goal and based on desire for information associated with a functional relationship between said first goal and said first factor contained in said plan-model wherein a change in the value of said first factor causes a change in the value of said first goal, and for which inputs input values are entered for an initial what-if state of said plan-model, in which what-if state all input values and all output values of said plan-model including the values of said first factor and said first goal are plan values; and
first means for providing a first graph having at least a first graph line in which each graph point reflects a different what-if state of said plan-model, said first graph line developed by determining representative graph points each representing a different first factor substitute value and a corresponding goal variant value for said first goal, wherein:
values of said representative graph points are determined and used to display said first graph and said first graph line in a computer process that includes generation of new values for said representative graph points using said plan-model and said plan values and display of said first graph with said first graph line using said representative graph points, which computer process is independent of user setup of additional modeling to create a table of such values external to said plan-model, said user setup of additional modeling including separate user steps to enter in spreadsheet cells external to said plan-model a plurality of first factor values and a formula reference for calculation of corresponding first goal values;
each of said first factor substitute values is determined based on said first factor plan value and a first magnitude of variation established for the value of said first factor, said first magnitude of variation being established based on said first factor plan value, said variation being in an axis of value variation determined independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model, each of said first factor substitute values is different from and a replacement for said first factor plan value, said first factor substitute values differ from any value of said first factor obtained from said initial what-if state of said plan-model and used in determination of said first factor substitute values, and said first factor substitute values differ from any value of said first factor obtained from the user and used in determination of said first factor substitute values; and
each of said corresponding first goal variant values is the value of said first goal using said plan-model processes with said first factor plan value replaced by the first factor substitute value to which said first goal variant value corresponds, and depending on said relationship between said first goal and said first factor contained in said plan-model said corresponding goal variant values for said first goal are different from the plan value of said first goal, differ from any value of said first goal obtained from said initial what-if state of said plan-model and used in determination of said first goal variant values, and differ from any value of said first goal obtained from the user and used in determination of said first goal variant values;
in which graph line information is provided for the relationship between said first goal and said first factor and the value of said first goal at each value of said first factor for at least ten values of said first factor differing from any value of said first factor obtained from said initial what-if state of said plan-model and differing from any value of said first factor obtained from the user.
57. An apparatus, as claimed in claim 56, further comprising:
an electronic display screen for displaying said first graph line.
58. An apparatus, as claimed in claim 56, further including:
linking software for linking to said plan model to enable interaction with software of said plan model.
59. An apparatus, as claimed in claim 56, wherein:
said plan model is entered in an electronic spreadsheet which is activatable on a computer display screen.
60. An apparatus, as claimed in claim 56, further comprising:
means for identifying outputs for user selection of goals and identifying inputs for user selection of factors.
61. An apparatus, as claimed in claim 56, further comprising:
second means for identifying all of said inputs from said plan-model that affect said first goal.
62. An apparatus, as claimed in claim 56, wherein:
said first means includes means for determining said first factor substitute values, using said first factor plan value.
63. An apparatus, as claimed in claim 56, wherein:
said first means includes means for establishing a factor axis related to said first factor and a goal axis related to said first goal.
64. An apparatus, as claimed in claim 56, wherein:
said plan model contains a plurality of formulas that collectively determine a relationship between said first goal and said first factor but contain no formula expressing the relationship between said first goal and said first factor.
65. An apparatus, as claimed in claim 56, further including:
third means for selecting by the user of a plurality of factors including said first factor from said inputs of said plan-model and providing said first graph with a plurality of graph lines including said first graph line, wherein said plurality of graph lines illustrates a goal variant value for each of a plurality of factor substitute values for each of said plurality of factors selected by the user, contains at least ten graph points each representing at least one factor substitute value and a goal variant value not obtained from either the initial what-if state of the plan-model or from the user and each representing a different what-if state of said plan-model, and illustrates variation of plan-model values in a plurality of additional axes of value variation of said plan-model each established independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model.
66. An apparatus that relates to graphic analysis of variation of economic plans, comprising;
a plan-model having variables including inputs defined to have externally entered input values and outputs defined to have output values determined through plan-model processes affected by one or more of said input values, said plan-model being changeable into any of a number of what-if states, each of said what-if states being a particular combination of particular values for said inputs and any change in any of said input values being a change of said plan-model from one what-if state to another what-if state, from which at least a first goal is chosen from said outputs and at least a first factor is chosen from said inputs, said first factor being chosen based on said first goal and being an economic parameter that is useful to a user in planning and managing said first goal and based on desire for information associated with a relationship between said first goal and said first factor contained in said plan-model wherein a change in the value of said first factor causes a change in the value of said first goal, and for which inputs input values are entered for an initial what-if state of said plan-model, in which what-if state all input values and all output values of said plan-model including the values of said first factor and said first goal are plan values;
first means for providing a first graph having at least a first graph line in which each graph point reflects a different what-if state of said plan-model, said first graph line including graph points each representing a different first factor substitute value and a corresponding goal variant value for said first goal, said first means also for interactively moving relative to said first graph line by selecting by the user of a position relative to a first axis of said first graph, and determining for a graph point on said first graph line at the selected position relative to said first axis the value and position of said graph point relative to a second axis of said first graph according to said relationship between said first goal and said first factor contained in said plan-model, wherein;
each of said first factor substitute values is determined based on said first factor plan value and a first magnitude of variation established for the value of said first factor, said first magnitude of variation being established based on said first factor plan value, said variation being in an axis of value variation determined independently of the axes of value variation represented by the input and output values in said initial what-if state of said plan-model, each of said first factor substitute values is different from and a replacement for said first factor plan value, said first factor substitute values differ from any value of said first factor obtained from said initial what-if state of said plan-model and used in determination of said first factor substitute values, and said first factor substitute values differ from any value of said first factor obtained from the user and used in determination of said first factor substitute values; and
each of said corresponding first goal variant values is the value of said first goal using said plan-model processes with said first factor plan value replaced by the first factor substitute value to which said first goal variant value corresponds, and depending on said relationship between said first goal and said first factor contained in said plan-model said corresponding goal variant values for said first goal are different from the plan value of said first goal, differ from any value of said first goal obtained from said initial what-if state of said plan-model and used in determination of said first goal variant values, and differ from any value of said first goal obtained from the user and used in determination of said first goal variant values;
in which graph line information is provided for the relationship between said first goal and said first factor and the value of said first goal at each value of said first factor for at least ten values of said first factor differing from any value of said first factor obtained from said initial what, if state of said plan-model and differing from any value of said first factor obtained from the user.
67. An apparatus, as claimed in claim 66, further including:
second means for saving said first graph with an identifier, wherein upon selection of said identifier said first graph is reproduced on a display screen and on the reproduced said first graph the user can move interactively along said first graph line to a graph point representing a factor substitute value of said first factor relative to a factor axis and said factor substitute value and the first goal variant value of said graph point moved to are illustrated, said first goal variant value determined using said factor substitute value moved to and said relationship between said first goal and said first factor contained in said plan-model.
68. An apparatus, as claimed in claim 66, wherein:
said first means includes means for selecting from a number of graph lines said first graph line to move along and indicating that said first graph line is selected to be moved along.
69. An apparatus, as claimed in claim 66, wherein:
said first means includes means for interactively moving to at least a first graph point on said first graph line by selecting by the user of a factor substitute value for said first factor relative to a factor axis and illustrating a corresponding goal variant value for said first goal of said first graph point relative to a goal axis in accordance with the relationship of said first goal to said first factor in said plan-model.
70. An apparatus, as claimed in claim 66, wherein:
said first means includes means for displaying a number showing the value of said position relative to said first axis selected by the user and a number showing the value of said position of said graph point on said first graph line relative to said second axis determined using said value of said position relative to said first axis and the relationship between said first goal and said first factor contained in said plan-model.
71. An apparatus, as claimed in claim 66, wherein:
said first means includes reference markers illustrating the position of a graph point moved to along said first graph line relative to a factor axis and relative to a goal axis with position relative to one axis being user selected and position relative to the other axis being determined using said relationship between said first goal and said first factor contained in said plan-model.
72. An apparatus, as claimed in claim 66, wherein:
said first means includes means for interactively moving to a user selected goal variant value of said first goal relative to a goal axis and illustrating a graph point on said first graph line representing the goal variant value moved to and a first factor substitute value of said graph point relative to a factor axis determined using said goal variant value moved to and said relationship between said first goal and said first factor contained in said plan-model.
Description
FIELD OF THE INVENTION
The present invention relates to providing economic information in particular, to the production and displaying of a continuum of "what-if" scenarios of causative factor data and resulting goal data for a plan entered in an electronic spreadsheet.
BACKGROUND OF THE INVENTION
In planning and managing economic investments and operations, results for numerical goals are typically determined by scores of quantifiable factors that managers and investors plan, predict and manage, for which there are thousands of possible combinations that lead through chains of mathematical calculations to thousands of possibilities for results for the goals. For zeroing in on optimal combinations of factors leading to best results for the goals, investors and managers need best information on how the factors and combinations of factors affect the goals across the ranges and panoramas of possibilities for the plan. Modern computer technology offers calculation power to develop and deliver such information. But to date, no system has been developed that fully applies the potential of computer technology to develop and deliver such information.
With respect to providing information that assists the decision-maker, electronic spreadsheets are well-known and powerful planning and management tools. Spreadsheets organize and present financial or accounting information. The spreadsheet includes a number of cells that contain data entered by the preparer or user that comprise the spreadsheet plan-model. Each of these cells is separately identifiable by column and row, and typically each row has a name in a cell at its left and each column has a heading in a cell at top. Some of the cells have formulas associated with them. For example, data in one cell may be a function of the data or the value in another cell.
The spreadsheet format of columns and rows has been used for centuries. However, relative to the information on ranges of possibilities that managers and investors needed to make decisions, such spreadsheets have had a number of drawbacks, including: each spreadsheet was very expensive, in human time and cost, to prepare; each spreadsheet was prone to human error in calculation and writing; in comparison to the number of what-if possibilities for business factors and goal results, a single spreadsheet reflected only a single dot or single possibility. Adding additional spreadsheets added more numbers tending to cause most managers and investors to be overwhelmed by all of the numbers. Furthermore, each spreadsheet did not reveal relationships between key factors and key goals. Typically, in proceeding from business factors to goal results, a number of calculations or chains of calculations had to be stepped through.
In more recent times, modern processors have speeded calculations and eliminated human calculation mistakes. Even more recently, spreadsheet software packages have been developed and commercialized, such as LOTUS 1-2-3.RTM. available from Lotus Development Corporation, EXCEL.RTM. available from Microsoft Corporation, and QUATTRO PRO.RTM. available from Novell, Inc. Such software not only displays a spreadsheet on a computer display, but also uses the processing power of the personal computer to perform desirable or appropriate functions, such as calculating spreadsheet data using formulas that interrelate different cells of the spreadsheet. These electronic spreadsheets receive input data that is inserted into some cells using a keyboard or a mouse. Automatic calculations are made to determine the value of data in other cells using the afore-noted formulas. In this manner, the electronic spreadsheet includes inputted data comparable to that inputted by hand using paper generated spreadsheets, as well as data calculated using the processing power of the computer.
In one embodiment of an electronic spreadsheet, it is known to adjust spreadsheet data to reflect any one of a number of "what-if" scenarios. Each of these is displayed as a table of data containing different user-entered input data representing an "if" together with output data calculated to reflect the resultant "what." According to this method, by changing input data to reflect one and then another "if," a spreadsheet plan-model can be changed into one and then another of a number of what-if scenario data tables, which can be viewed sequentially on the computer display for analysis by the user.
Such electronic spreadsheets still suffer from major deficiencies. They only develop and show what-if scenarios for a plan one at a time, with each in the format of a table of numbers. The manager or investor must examine a number of tables and can easily become lost or confused in the spreadsheet numbers. The current modern spreadsheet also does not show relationships between business factors and goal results any more than hand generated and calculated spreadsheet data.
Current spreadsheet software also offers business graphics for visual presentation and comparisons of individual numbers displayed in spreadsheets, including pie graphs, bar graphs and "line" graphs (dots representing several numbers that are connected by lines with no informational content). Such business graphics are merely graphic depictions of parts of individual spreadsheet data tables. They depict individual discrete data elements contained in particular individual spreadsheet cells, rather than mathematical relationships between inputs and outputs and continuums of what-ifs. Like the spreadsheet itself, each such graph depicts values contained in the spreadsheet at one time and do not effectively respond to the above-noted spreadsheet deficiencies.
Hence, it would be advantageous to develop and display what-if related spreadsheet scenarios in a way that optimizes the organization and understanding of such information by showing key factor(s)/goal(s) relationships and visually represent what-if scenarios involving spreadsheet factors and goals associated with a vast number of possible what-if scenarios on a single graph.
SUMMARY OF THE INVENTION
In accordance with the present invention, method and apparatus are provided for graphically developing and displaying a continuum of what-if scenarios derived, in a preferred embodiment, from spreadsheet plan-model data entered by a user. The present invention develops and displays graphic analyses of entire ranges of hundreds and thousands of what-if possibilities for an economic plan in which each possibility is defined in terms of values of economic factors and goal results, in graphic-analysis formats that show priorities, trade-offs, alternative routes to higher results for the goal, and combinations of factors that represent thresholds of risks. For a typical spreadsheet plan-model, the present invention offers thousands of graphic analyses of different sets of factors and goal results, which are selectable from simple menus shown on a computer display. With respect to the what-if possibilities for the spreadsheet plan-model, on each graphic analysis, the user is able to move to various what-if graph points and at each graph point have factor and goal values displayed.
The apparatus of the present invention preferably includes a computer system that executes software for manipulating and displaying spreadsheet input and output data in the form of a graph. In one embodiment, this software includes a spreadsheet software package that displays an electronic spreadsheet having a number of columns and rows that define identifiable cells.
The software further includes a novel graphic analysis software package that links with the spreadsheet software and a plan-model it contains to request and manipulate plan-model data that is controllably organized and manipulated to develop one or more graphic analyses. Each of the graphic analyses contains one or more curved or straight lines that depict plan-model goal-factor relationships. More particularly, the spreadsheet plan-model includes cells containing externally entered input data and other cells that have associated formulas and display output data calculated to reflect the input data. After invoking the graphic modeling software, the user selects a first goal and at least a first factor for which he wants a graphic analysis presenting a goal-factor relationship and a range of variant what-ifs. Specifically, he selects a plan-model output as a first goal, for which he wants to see "what" results for various factor-value "ifs," and selects at least one plan-model input as a first factor, for which he wants to see effects of various "if" values on the "what" measured by the goal. Each goal and factor is identified by a name indicating its meaning to the user such as price, interest rate, depreciation, period(s) of time, sales, profit, rate of return, net present value, etc. For this selection process, the graphic modeling software identifies and provides lists of spreadsheet plan-model inputs and outputs and associated names, from which the user can develop lists of goals and factors identified by names for which graphic analyses are to be offered. In one embodiment of the invention, in the spreadsheet plan-model, the user determines those outputs to be offered as goals and those inputs to be offered as factors for graphic analyses, and structures the plan-model with these goals and factors and their names located in particular ranges of the spreadsheet plan-model to facilitate development of the goal and factor lists in the graphic modeling software.
Regardless of the embodiment employed to provide goal and factor lists, such lists are displayable on a display screen of the computer system in the form of menus. In one embodiment, a menu list of goals identified by name and a menu list of factors identified by name are displayed on a display screen of the computer system. The user is able to select one or more graphic analyses presenting different goal-factor relationships using such menu lists.
In one embodiment, from the two menu lists one goal and one factor are selected. The graphic modeling software then carries out the selected graphic analysis and creates a graph line showing the relationship of the goal to the factor and showing a range of variant what-ifs each defined by variant factor value and resultant variant goal value. In creating the graph line, variant data are determined for the factor relative to the factor data contained in the spreadsheet and corresponding goal values are determined using the spreadsheet as a calculator. A range of factor values above and below the spreadsheet data is determined. Within this range, factor values are determined for variant what-if graphpoints required to determine the graphic analysis goal value range and to develop the graph line. For each such factor value, the corresponding goal value is determined using the spreadsheet as a calculator, through insertion of the factor value into the plan-model and obtaining the resultant value for the goal calculated by the spreadsheet. In one embodiment, a direct goal-factor relationship expression is derived from the plan-model and used for such calculations. A graph of the goal variant values as a function of the factor variant values is created from the variant what-if value-sets each comprising determined factor variant data and associated calculated goal variant data. In the case of this first embodiment, for any selected goal a number of different graph lines setting out relationships of the goal to different factors can be created by selecting from the menu lists a goal and a number of factors. In accordance with this embodiment, in one graphic analysis a desired number of these different graph lines can be simultaneously developed and displayed.
In another embodiment, in addition to the first menu list of goals and the first menu list of factors a second menu list of the factors is also generated. In this embodiment, after selection of a goal, one factor is selected from each of the two factors menu lists. Similarly to the previous embodiment, graph lines are created setting out the relationship of the selected goal to the two selected factors in combination.
In the case of this second embodiment, a number of different graph lines are created, with each graph line representing a variant value of one of the two factors. Specifically, each graph line is representative of a different value for one of the two selected factors. The other factor is represented by horizontal position relative to the x-axis, and the goal is represented by vertical position relative to the y-axis.
For each of the two embodiments, once the graph is displayed, the graphic modeling software enables the user to obtain information and data related to the selected factor(s) and goal. In particular, when more than one graph line is displayed, the user is able to choose one of the plurality of graph lines for evaluating or analyzing factor and goal variant data that varies from the plan data that was previously entered as part of the plan-model spreadsheet. The chosen graph line is illustrated on the display screen so that it is distinguished from the other graph lines (e.g., by color). The user is able to cause a marker to "move along" this selected graph line in desired increments to one or more different graph points. In accomplishing this, a horizontal scroll bar is utilized. The horizontal scroll bar is illustrated or found along the factor or x-axis of the graph and is activated to cause horizontal marker movement in the desired direction (+or -) relative to plan data. The horizontal scroll bar functions with a dotted indicator line that is useful in identifying the factor variant data or value that was moved to along the selected graph line. The indicator line for the horizontal scroll bar is a dotted vertical line on the display screen and is horizontally movable relative to the horizontal axis.
Associated with this movable horizontal axis position indicator is a horizontal axis position meter, showing in the form of a number the value represented by the horizontal axis position moved to.
Where the vertical dotted line representing horizontal axis position meets the selected graph line, directly above the selected horizontal axis position, a dot is displayed representing the what-if possibility or scenario moved to. And from that dot, a vertical axis position indicator dotted line extends horizontally leftward to the vertical goal axis, showing graphically the resultant goal value of the what-if possibility moved to. This vertical goal axis position indicator also has an associated axis meter, showing in the form of a number the resultant goal value of the what-if moved to.
With this interactive move system, on a graphic analysis the user can move to any or each of a large number of what-if scenarios defined in terms of variant factor values and, for each, see the resultant goal value. In one embodiment, in which each graph line represents a different factor and the horizontal axis has a common denominator scale for variant values of any of the factors, on a single graphic analysis the user can select any of the depicted factors (graph line) and any of a range of variant values for that factor (horizontal axis position), and for each of these what-if possibilities see the resultant goal value. In another embodiment in which the horizontal axis represents a range of variant values for one factor and a set of graph lines, where each represents a different value for a second factor, on a single graphic analysis the user can move to any of a large number of combined variant values of two factors (graph line and horizontal axis position), and for each such what-if possibility see the resultant goal value.
Further, at any what-if graph point moved to, the system offers additional information on that what-if, including a numerical summary of values for all factors and goals at that what-if and comparisons of these values with those at other designated what-if graph points, including the factor and goal values of the graph point representing the originally entered spreadsheet plan-model.
Additionally, on each graphic analysis, the graphic modeling software provides a method for moving to various goal values, enabling the user to see various factor values or factor-value combinations that would produce particular goal results. A vertical scroll bar is displayed along the vertical goal axis, usable for moving to various vertical positions each representing a particular goal value. A "goal-line" extends horizontally across the graphic analysis at the height of the goal value moved to relative to the vertical axis, and an associated goal-line value meter shows the goal value moved to in the form of a number. By observing where this goal-line intersects each of one or more graph lines, the user can see one or more graph points each representing what-if factor values that will produce the goal value the user has moved to. What each of these graph points represents in factor values is readable by observing what the graph line represents as shown by its label and observing the position of the graph line's intersection of the goal-line relative to the horizontal axis. Furthermore, to assist the user in reading factor values that meet a goal value moved to, the graphic modeling software offers a system through which, after moving the goal-line to a desired goal value, for any one selected graph line or for all graph lines additional information is displayed both graphically and in the form of numbers to specify one or a plurality of factor values or factor-value combinations required to meet the designated goal value.
Returning to the graphic modeling software method for moving to individual what-if scenario graph points, each selected in terms of factor values, at any such graph point the user has selected, the user is able to create a new or modified spreadsheet that reflects the variant data for the selected graph point. From this, the display screen is able to display this new or revised spreadsheet, with the selected graph point being used as the new input or what-if for this new spreadsheet. Additionally, or alternatively, each graph that presents different input/output relationships can be saved or stored. When a number of graphs is saved, a menu can be created that includes names or labels of the saved graphs.
Based on the foregoing summary, a number of benefits and/or advantages of the present invention are readily seen. Information is developed and delivered on entire panoramas of possibilities for a plan and on the underlying relationships among the factors and goals, across ranges of possibilities above and below the values in the spreadsheet plan-model. Through the development and delivery of such information, priorities among factors relative to their effects on goals, trade-offs between alternative factor combinations relative to resulting goal values, alternative combinations of factors that result in particular user-defined goals, and combinations of factor values that force goal values below any particular user-defined minimum goal levels are provided. Such information based on spreadsheet plan-model data is developed and delivered using spreadsheet software products, as well as other plan-model entry devices. More particularly, from the spreadsheet plan-model, the present invention enables the operator/user to select inputs (factors) and outputs (goals) for creating a desired graph. In one embodiment, a number of different graph lines are displayed that illustrate relationships between each of a number of different factors and the same goal. In another embodiment, a graphic relationship is illustrated between a selected goal and any two selected factors in combination. In both embodiments, the developed and displayed information covers ranges of factor and goal values above and below the original or plan data entered in the spreadsheet plan-model, including goal-factor relationships not ever defined or shown in any formula in the typical spreadsheet plan-model. The graphic presentation of the present invention provides a concise visual delivery of spreadsheet plan-model analysis information for which users of all skill levels can benefit from assistance in both learning and ongoing application. In that regard, interactive moving is provided to various factor-goal graph points in the realm of what-if possibilities shown by each graphic analysis and presenting information associated with each such graph point that is moved to. The present invention is also able to create a new detailed spreadsheet plan-model reflecting the chosen graph point. Additionally, customized menus of particular graphic analyses and other customized user interfaces can be created, as well as saving the graphic analysis in storage memory using, for example, disk files.
Additional advantages of the present invention will become readily apparent from the following discussion, particularly when taken together with accompanying drawings.
BRIEF DESCRIPTION OF THE DRAWINGS
FIG. 1 is a block diagram generally identifying the hardware and software of the present invention;
FIG. 2 illustrates a computer displayed spreadsheet that is generated using a spreadsheet software package that includes a number of identified cells, any of which can be used for containing cell data that is a function of data in one or more other cells;
FIG. 3 illustrates a plan-model that includes data and other information of a representative, hypothetical company;
FIG. 4 illustrates the same spreadsheet plan-model of FIG. 3 except for a change to reflect a different what if;
FIG. 5 illustrates an example of a "formula tree" associated with a financial plan;
FIG. 6 identifies principal steps and processes of the graphic modeling software;
FIG. 7 illustrates a spreadsheet plan-model that displays goal and factor cells that will be linked to using the graphic modeling software;
FIG. 8 illustrates an embodiment for use in linking between the graphic modeling software and the spreadsheet software;
FIG. 9 illustrates a computer display screen having a "Modify Lists" window for changing factor and goal lists and names;
FIG. 10 illustrates a computer display screen having a user interface that enables the user to select the external software, such as the spreadsheet software, and the plan-model file using the graphic modeling software;
FIG. 11 illustrates a computer display screen that enables the user to select data from the external software;
FIG. 12 illustrates a computer display screen for an invention embodiment in which the graphic modeling software works as a part of or add-in for another software product;
FIG. 13 illustrates a computer display screen involving use of both the Lotus 1-2-3.RTM. software product of Lotus Development and the Excel.RTM. software product of Microsoft in which these external software products are an integral part of or add-in for the graphic modeling software;
FIG. 14 illustrates a process for developing a list of outputs from a plan-model to assist the user in selecting goals;
FIG. 15 illustrates a process for developing a list of inputs from a plan-model to assist the user in selecting factors;
FIG. 16 illustrates a computer display screen for use in linking and using plan-model data entered or stored in various types of software products including the graphic modeling software;
FIG. 17 illustrates a computer display screen having a window for selecting a prioritizer type of graphic analysis;
FIG. 18 illustrates a computer display screen having a window for selecting an optimizer type of graphic analysis;
FIG. 19 illustrates a computer display screen for another embodiment used in connection with choosing a goal and factor(s);
FIG. 20 illustrates a computer display screen having a prioritizer type graphic analysis;
FIG. 21 illustrates a computer display screen having an optimizer type graphic analysis;
FIG. 22 illustrates an example of steps for deriving a relationship between factor and goal by listing dependent cells for a selected factor;
FIG. 23 illustrates a step diagram for determining goal data and producing a graphic analysis showing a goal-factor relationship without the intermediate step of deriving direct goal-factor formulas;
FIG. 24 illustrates a computer display screen having a window for obtaining desired axes ranges and increments for a prioritizer graph;
FIG. 25 illustrates a computer display screen having the prioritizer graph lines of FIG. 20 but with different axes, ranges and increments;
FIG. 26 illustrates a computer display screen having a window for obtaining desired axes ranges and increments for an optimizer graph;
FIG. 27 illustrates a computer display screen having the optimizer type graph lines with the graph lines increment changed by the user from that illustrated in FIG. 21;
FIG. 28 illustrates a computer display screen having a prioritizer type graphic analysis illustrating range limits for certain individual factor graph lines;
FIG. 29 illustrates a computer display screen having a window for user entry of factor ranges for prioritizer graphic analyses;
FIG. 30 illustrates a computer display screen having a window for user entry of particular graph line ranges for optimizer graphic analyses;
FIG. 31 illustrates a computer display screen having an optimizer graphic analysis with factor1 range limits affected by factor2 values;
FIG. 32 illustrates a computer display screen having a window for entering expressions relating one factor to other factors;
FIG. 33 illustrates a computer display screen having graphic analyses of multiple plan-models in a single graph;
FIG. 34 illustrates a computer display screen having multiple windows for simultaneously displaying multiple graphic analyses;
FIG. 35 illustrates a computer display screen having a prioritizer type graphic analysis with tools for interactive analysis exploration;
FIG. 36 illustrates a computer display screen in which movement along a prioritizer graph line is indicated and information on the current graph point position is displayed;
FIG. 37 illustrates a computer display screen similar to that of FIG. 36, but in which a smaller movement along a graph line is indicated;
FIG. 38 illustrates a computer display screen having prioritizer graph lines in which movement is made from one graph line to another;
FIG. 39 illustrates a computer display screen having optimizer graphic analysis on which interactive exploration is conducted;
FIG. 40 illustrates a computer display screen having prioritizer graph lines with graph point markers;
FIG. 41 illustrates a computer display screen having prioritizer graph lines, markers and a note referring to a particular marker;
FIG. 42 illustrates a computer display screen having prioritizer graphic analysis with data displayed for all goals and factors relative to current graph point location;
FIG. 43 illustrates a computer display screen similar to FIG. 42 but also displaying values for plan data and differences for goal and factor data based on current and plan data;
FIG. 44 illustrates a computer display screen having optimizer graphic analysis with a goal-line indicator;
FIG. 45 illustrates a computer display screen similar to FIG. 44 but providing additional information on a factor value required to produce the goal value the user has moved to;
FIG. 46 illustrates use of the spreadsheet software as a calculator to determine a factor value required to produce a goal value the user has moved to;
FIG. 47 illustrates a computer display screen having a new spreadsheet plan-model created from a selected graph point from an optimizer type graphic analysis;
FIG. 48 illustrates a computer display screen having prioritizer graphic analysis developed from a new spreadsheet plan-model;
FIG. 49 illustrates a computer display screen having a "save" indicator for saving and opening a file of a user's graphic analysis work;
FIG. 50 illustrates a computer display screen having a window for creating a customized menu of selected graphic analyses;
FIG. 51 illustrates a computer display screen having a window for assigning a selected graphic analysis a name and adding it to a customized menu;
FIG. 52 illustrates a computer display screen having a customized menu for use in redeveloping a selected graphic analysis;
FIG. 53 illustrates a computer display screen having a menu of multiple graphic analyses selected and named by the user;
FIG. 54 illustrates a computer display screen having a customizer window for developing objects, properties and code; and
FIG. 55 illustrates a computer display screen having an example of an initial display when the customization feature is invoked.
DETAILED DESCRIPTION
The description of the present invention that follows is directed to an embodiment for use (a) on an IBM-compatible PC system, (b) in the Microsoft "Windows" software environment, in general conformance with the Microsoft "Windows" user-interface style conventions, (c) for analysis of spreadsheet plan-models, (d) with the graphic analysis performed by novel graphic modeling software external to spreadsheet software, (e) with interaction between the graphic modeling software and the spreadsheet software accomplished through Microsoft "Windows" dynamic data exchange (DDE) and object linking and embedding (OLE). However, the present invention is not limited to the elements of the described embodiment. It could be used on other computer systems, such as computer work stations, in other software environments (e.g., UNIX), with user-interface style conventions different from those of Microsoft "Windows," such as those found with Macintosh computers. The invention could be embodied in systems including graphic modeling software different from that described hereinafter. The graphic modeling software of the present invention could, with respect to spreadsheet plan-model entries, use either or both of data in separate software products other than spreadsheets, such as financial modeling or database or accounting software, and/or entries made in the graphic modeling software itself. Interactions between the graphic modeling software and other software containing plans, models and/or data, if any, could be accomplished by methods other than DDE and OLE, such as various programming languages.
With reference to FIG. 1, this preferred embodiment of the present invention is schematically illustrated in a block diagram. The present invention is embodied in a computer system 100 that includes the IBM-compatible PC having a processor 104 processing the data and other information inputted or otherwise provided to or otherwise developed by the computer system 100. The processor 104 executes software that enables the user to create for display from a substantial number of possible graphic analyses a desired one or more of them. In one embodiment, the software is categorized as spreadsheet software 108 for, among other things, displaying a traditional spreadsheet comprised of a number of columns and rows with each rectangle in a particular column and a particular row being defined as a cell. The spreadsheet software 108 is also useful in determining outputs using formulas that interrelate input and output data. In this embodiment, the spreadsheet software 108 is communicated or linked by novel software that is identified as graphic modeling software 112, although it should be appreciated that the spreadsheet software 108 and the graphic modeling software 112 could be integrated as a single software package. The graphic modeling software 112 performs a number of advantageous functions, which will be explained in detail later herein, including the development and display of graphic analyses, numbers or values, printed text, as well as displaying, for activation by the operator or user, menus, icons, horizontal/vertical scroll bars and horizontal/vertical indicator lines.
The computer system 100 also includes a memory 116 that communicates with and is accessed by the processor 104 for performing the desired functions, including obtaining data from memory in connection with execution of the software 108, 112. In one embodiment, the storage memory 116 includes a random access memory (RAM) that typically stores data involved in processing such as calculated data or interim calculated data. The memory 116 also includes one or more hard or floppy disks 124 for storing the executable software, as well as saving data or other information, such as information for reproducing graphic analyses that were created and are stored for later reference.
The computer system 100 further includes a computer terminal display screen 128 that illustrates or displays information in a desired and advantageous format, such as the afore-noted spreadsheet and created/selected graphic analyses that interrelate spreadsheet input and output variables. One or more input devices 132 enable the user to communicate the user's inputs to the computer system 100. The input devices 132 typically include a keyboard and mouse. One or more output devices 136 may also be provided and could include, for example, a printer that provides desired hard copies of displayed information, including the graphic analysis or analyses selected by the user.
Referring now to FIG. 2, the description of the spreadsheet software 108 is continued. FIG. 2 shows the work area of an electronic spreadsheet that can be generated on the display screen 128. The spreadsheet includes a matrix of columns 201 and rows 202. In a spreadsheet matrix, typically each column is identified by a letter shown above the column --A, B, C, etc. Each row is typically designated by a number as shown at the row's left--1, 2, 3, etc. The rectangles created by intersections of columns and rows are called cells. Each cell is commonly designated by the letter of its column and the number of its row. For example, the cell 203 in column B and row 3, shown in FIG. 2 with a heavier border, is designated as cell B3.
Spreadsheets have far more cells than the computer display screen 128 shows at any one time. The user can pan the computer display screen 128 across the spreadsheet to illustrate other columns. For example, columns T through Z or AF through AM can be seen by panning to the right. The computer display screen 128 can be panned downwardly to see other rows, such as rows 55 through 77. Current spreadsheets have huge numbers of cells. For example, at this writing, the current versions of the Lotus 1-2-3.RTM. and Quattro Pro.RTM. spreadsheets each offer a matrix of over 250 columns and over 8000 rows providing over 2 million cells. The Microsoft Excel.RTM. matrix has twice as many rows, providing a matrix of over 4 million cells.
In any cell, a user can enter text (such as "sales," as shown in FIG. 2 cell D4), or a number (such as "300," entered in cell E4). With these simple capabilities, spreadsheets are useful for almost every situation in which a user wants to produce either numbers or words or both laid out in columns and rows. Spreadsheets are used widely for such purposes.
In any cell, instead of simply entering text or a number, a user can make a third kind of entry: a formula which calculates a number, such as =2+2. For formula entries, spreadsheets provide the following key characteristics and capabilities:
(a) When a user enters a formula in a cell, the cell displays the formula answer or result. For example, in FIG. 2, in cell B10, the user has entered the formula =2+2, and in that cell what is displayed is the result, 4.
(b) In a cell's formula, the user can include references to other cells, such as cell E6. In place of each such cell reference, the formula will use the number displayed in the referred-to cell. For example, in the example of FIG. 2, in cell E6, assume the user has entered the formula =E4-200, and since the number displayed in E4 is 300, the formula in E6 calculates 300-200 and its cell E6 displays the E6-formula result, 100.
(c) In a formula a user can include predefined mathematical components, commonly called functions, which greatly expand user ability to enter formulas and simplify user entries of formulas. For example, for certain calculations of loan payments, most users would have difficulty defining the right algebraic entries, but spreadsheets have functions that have loan-payment algebra built in and are very easy to enter.
(d) When the number displayed in any cell is changed, any other cell with a formula affected by the changed number will recalculate its result based on the change. For example, if in the spreadsheet matrix shown in FIG. 2, the user changes the number in cell E4 from 300 to 600, the formula in E6, which is =E4-200, will recalculate, using for E4 that cell's new number of 600. The result will be displayed in cell E6, which will change from its prior result of 100 to display the new result of 400.
(e) Any cell's formula can include references to other formula cells, and for each such reference will use the displayed result number of the referred-to formula cell. For example, in FIG. 2, cell D7 could have a formula referring to the formula in cell E6, and cell F9 could have a formula referring to the formula in cell D7 which in turn refers to the formula in cell E6. In every case, wherever a formula has a reference to another cell having a formula, it will use the result-number displayed in that referred-to formula cell. And if any cell's number is changed, all cells with formulas will recalculate as necessary to reflect the change. For example, if the user makes the entries just described, and then changes the number in cell E4, that change of cell E4 will cause recalculation and change in cell E6, which will cause recalculation and change in cell D7, which will cause recalculation and change in cell F9.
(f) Current spreadsheet products enable a user to make entries on multiple spreadsheet pages, and on any spreadsheet page enter formulas that refer to cells on other spreadsheet pages. Each spreadsheet page, containing its own matrix of columns, rows, and cells is called a worksheet; a set of worksheets is commonly called a workbook. For example, an analyst could use five worksheets for five components of an analysis, and a sixth worksheet for a summary in which he enters formulas with references to cells in the other worksheets to calculate the summary numbers. Current spreadsheet products provide workbooks of many worksheets which together contain vast numbers of cells. Each of the cited spreadsheets offers workbooks of over 250 worksheets, and with each worksheet offering 2 to 4 million cells, in such a workbook a user can build a model in a three-dimensional space of a half billion to a billion cells.
FIG. 3 shows an example of a typical single-worksheet business-financial plan-model, in this case a startup plan for a hypothetical new software company, simplified so it can be fully shown in the illustration. FIG. 4 shows the same plan-model after the user has changed an input representing rate of growth of unit sales. Together these two figures provide a simplified example of typical use of spreadsheets for viewing alternative possibilities for a plan. A more realistic and typical example would show a model with more columns, rows, and numbers, and show more tables representing more possibilities for the plan.
For business-financial plans in particular, two aspects of spreadsheets are widely considered major advantages. One is that the columns-and-rows format (and large numbers of available columns and rows) conforms to the centuries-old traditional practice of producing business-financial projections and reports in the format of large tables of columns and rows of many numbers.
The second is that with formulas in spreadsheet cells able to use results of other formulas in other cells, spreadsheet users can build models that reflect complex input-output relationships by entering much simpler formulas, for individual small steps in the inputs-to-outputs calculation processes. With a very wide range of business-financial users, most of which are not mathematical experts, this spreadsheet characteristic facilitates wider business-financial use.
In spreadsheet entries of business-financial plans and other models, it is common that effects of particular input values on particular output values are not expressed through single formulas, but rather are implied through chains of formulas. To illustrate this point, and to illustrate that it is true for most of even the simplest spreadsheet models, FIG. 5 shows a "formulas tree" for a typical entry of a very simple business-financial plan. In this diagram, each rectangle containing the word "Formula" represents a location of a formula that calculates the value of the variable to the formula's right using values of items to the formula's left. For example, Formula 1 nearest top left is:
Sales of A=Price of A.times.Units of A In this example, the value of Units of A, which is the second item at far left and may be an input, affects the value of Profit which is at far right and is an output. This effect is not realized through a single formula, but through a "double chain" of six formulas. Units of A affects Sales through Formula 1 and Formula 7, and also affects Costs through Formula 2 and Formula 8; and these two formula chains affect Profit through Formula 9. While in the model as a whole, the value of Units of A affects the value of Profit, the builder of this model has not entered and the model does not display or contain any formula that defines the value of a typical key output, e.g., Profit, in terms of the value of a typical input with important effect on that output, such as Units of A.
This is typical of business-financial spreadsheet plan-models. For most relationships between inputs and outputs (especially most final outputs the user considers most important), the user has not formulated or made any entry expressing the relationship between the input and the output. Additionally, the plan-model does not contain or display any formula or equation expressing the relationship between the input and the output. The significance of this to advances and advantages of the present invention will be described further on.
As has been noted, a spreadsheet plan-model can be rapidly changed from one combination of input and output values to another; however, these combinations are shown one at a time, with each combination of input and output values depicting one what-if scenario as a table of numbers. The principal most recent innovation for improving use of multiple input-output value combinations for a spreadsheet plan-model is a feature called version manager or scenario manager. This feature enables users to, for a particular spreadsheet plan-model, create, manage, and view multiple input-output value combinations or what-if scenarios more efficiently and quickly. Nonetheless, with this new feature, it remains true that the spreadsheet displays alternative input-output value combinations (what-if possibilities) one at a time with each displayed as a table of numbers that is typically large.
With reference initially to FIG. 6, the present invention displays the what-if possibilities using a novel graphic analysis process that advances the user beyond these vast tables of numbers. This is primarily accomplished using the graphic modeling software 112. For steps 611-615 of FIG. 6, the graphic modeling software "GMS" is the sole or principal application software. With regard to step 610, the GMS could be the sole application software or this function could be shared by other software product(s) used for some or all of the spreadsheet plan-model entry and storage, such as the spreadsheet software 108.
Before proceeding to a more detailed discussion of the steps of FIG. 6, an overview thereof is described. Step 610 recites the step of entering the spreadsheet plan-model, including input values, formulas and labels. If the spreadsheet plan-model is external to the graphic modeling software 112, such as in the embodiment that includes the spreadsheet software 108, a link is made between the spreadsheet software 108 and the graphic modeling software 112. In step 611, a selection is made as to which of hundreds or thousands of possible graphic analyses will be displayed on the display screen 108 for analysis. The graphic analysis of one or more graph lines is developed and subsequently displayed in accordance with step 612. After display, the user is able to interactively use the displayed graphic analysis to conduct desired what-if analysis, as recited in step 613. As part of the what-if analysis using the displayed graph lines, the user is able to select one or more graph points from which one or more new spreadsheet plan-models can be created, in accordance with step 614. Finally, the user can save and reproduce the graphic analyses and their interactive graph usage by means of customized analysis files stored on disk or other memory in accordance with step 615.
Although six major steps are noted, it should be understood that a number of integrated processes are definable using less than all of the six steps 610-615. In a first integrated or subcombination process, steps 610-612 are performed. From these steps, the computer system 100 through user selection of a goal and one or more factors develops and displays graphic analyses showing goal-factor relationships and panoramas of combinations of factor variant data and goal variant data across ranges above and below values contained in the spreadsheet plan-model, representing a great number of what-if possibilities. A second integrated process or subcombination adds the step 613 to steps 610-612. Specifically, after the created graphic analysis with graph lines is displayed, interactive explorations of what-if possibilities are conducted. A third integrated process or subcombination adds the step 614 to steps 610-613 whereby a new or modified spreadsheet is generated using the results of the interactive usage of the displayed graph. A fourth process adds step 615 through which user interface is customized for easiest access to and use of particular graphic analyses.
FIG. 6 also illustrates the main procedures for conducting steps 610-615. In particular, a representation is provided of the cycling of steps conducted by the user. With respect to cycles a, b, after step 612 or 613, the user will typically cycle back to step 611 in order to select, then create and then explore another of the many graphic analyses that are available. Similarly, in accordance with cycle C, the user will commonly proceed to select, create and explore graphic analyses of the new or modified spreadsheet plan-model that was created. In accordance with cycle d, after a user has finished step 615, new cycles of use will typically be carried out by other users for whom the graphic analyses has been customized.
FIG. 7 illustrates an example of a spreadsheet plan-model that is to be entered and linked to using the graphic modeling software 112. The illustrated plan-model is an investment in a condominium which is to be rented out for five years and then sold. Except for the aspect of plan-model layout and structure described just below, standard spreadsheet modeling entries and methods are used.
As FIG. 7 shows, the spreadsheet plan-model is laid out in three major sections: a two-column list of factor names and values 701, which contains names and input plan data or values for which the user will or may want to explore effects of variations in values; a typical plan-model projection table 702, which shows the results of formulas for the progressions of calculations from inputs to key outputs, with the formulas being part of the cells; and a second two-column list of goal names and goal values data 703, which are representative of output values that the user considers critical for assessing results. The table 702 is built with the projection table data being based on the data or values in the cells in the factors list, so that whenever values in the factors list are changed, the table results change to reflect the current data in the factors list. For example, if the price of 200 in the factors list is changed to 210, the data in cell F6 changes using its associated or contained formula (which is -Price) to -210.00. Similarly, the cells in the goals list contain references to projection table cells so that the goal data reflects current results in the table.
It should be understood that spreadsheet plan-model data already entered without this layout can be adapted to this layout easily. The key step is to enter the factors list, and then in the existing plan-model, change the original entry of each factor value into a reference to that value's cell in the factors list.
As will be seen, this spreadsheet layout is part of the system for the entry-and-linking process in the embodiment of the present invention under current discussion; but it is also good modeling structure with important advantages for plan-modeling within the spreadsheet itself. With this spreadsheet layout, the model-builder and other users can (i) quickly see what input values (factors) and output results (goals) the model has been structured to test, and (ii) in the concise spreadsheet area of just the two lists, see and test changes for values of all key inputs, or factors and resulting values of all key outputs, or goals.
FIG. 8 illustrates one embodiment of a user interface for the graphic modeling software to link to the spreadsheet plan-model. This particular interface design is tailored for spreadsheet plan-models entered in the layout described just above and illustrated in FIG. 7. In the exemplary design shown in FIG. 8, in the graphic modeling software, the user has selected the menu item Setup 801, which caused display of a dropdown list of subchoices from which the user selected Spreadsheet Plan Links ("SpredLink"). This selection causes appearance of the window titled SpredLink 802.
In the SpredLink window, to indicate the spreadsheet software 108 in which the plan-model is entered, the user selects an option-button 803. Next he enters disk path and file name of the spreadsheet plan-model in a textbox 804 labeled for that purpose. At lower left in the SpredLink window in the goals column 805, the user provides information on spreadsheet location of the goals list: letters of name and data columns, and numbers of top and bottom rows. At lower right in the SpredLink window in the factors column 806, the user provides analogous information on the spreadsheet location of the factors list. When the user has completed his entries in the SpredLink window, he clicks its OK button 807. The SpredLink window disappears from view.
Once the user has completed provision of information in the SpredLink window, in the graphic modeling software the user can create a disk file which will contain this SpredLink information, so that for future graphic modeling of the same plan-model, he need not provide the information in SpredLink again. Instead, with the graphic modeling software open, the user simply opens the file, at which point the graphic modeling software automatically re-enters the SpredLink information. This file could also contain additional information on the plan-model and its graphic analysis, exploration, and customization which will be discussed in the following sections.
With the information provided in the SpredLink window, the graphic modeling software can link to and interact with the spreadsheet plan-model, including factor data and goal data, using DDE and OLE, to carry out procedures and processes described in following sections. As soon as the user indicates completion of SpredLink entries by activating or clicking OK, the graphic modeling software uses the information the user has entered to establish the linking and obtain the plan-model names and data to prepare for rapid execution of steps and processes described in the following sections.
Once the user has provided the SpredLink-window information required for the linking, the graphic modeling software offers the user means for modifying the factors and goals lists to be used by the graphic modeling software. To enable this modifying, the graphic modeling software displays a Modify Lists window 901 shown in FIG. 9. To produce this window, the graphic modeling software obtains from the spreadsheet plan-model the names of all goals and factors using DDE or OLE, and displays them in the Modify Lists window, in two user-modifiable lists of goals 902 and factors 903.
In this Modify Lists window, the user can edit names of goals or factors. He may want to modify some of these names specifically for use in the graphic modeling software for various reasons, such as to make a goal or factor name more descriptive to others who will use the graphic modeling software, or to shorten some names for display as labels on the graphic analyses. To edit names, the user first selects the Edit option button 904. With this option button selected, the user can select and edit the name of any goal or factor, using standard text entry and editing procedures.
In the Modify Lists window, the user can also delete and restore any goal on the list for graphic analysis. The user can also delete and restore any factor on the list, either (a) for all graphic analyses or (b) for graphic analyses focused on any particular goal. A principal reason users are likely to do this is that some factors are important for one goal but irrelevant for another--for example, in an overall business-financial plan-model, the Number of Shares input has important effect on the Earnings per Share goal, and should be offered for graphic analyses focused on that goal; but has no effect on the Total Profit goal, for which reason the user may want to exclude it from options for graphic analyses focused on that goal.
To delete and/or restore goals or factors being identified in the Modify Lists window, the user first selects the Delete/Restore option button 905. With this option button selected, the user can delete any nondeleted output by selecting its name, which on this list grays it (makes it appears semi-erased); and restore any deleted goal by selecting it. When any goal has just been restored, the factors list is specific to that goal, and the user can use the same procedures on the factors list to delete and restore factors for that specific goal. When the modification of lists is completed, the user selects the OK button 906.
The entry-and-linking system of the present invention is not limited to the exemplary design described above. The invention can use any system or combination of systems that enables the user to enter and/or link to the graphic modeling software a plan-model or parts of the plan-model in a manner such that the graphic modeling software can, as in the embodiment described herein, or otherwise, determine goal data or values for various combinations of factor data or values.
In another embodiment for entry-and-linking, FIG. 10 illustrates a design of user interface through which the user can, by selection only, indicate to the graphic modeling software the disk path and file of external software containing the plan-model or part thereof. The graphic modeling software displays a dialog box 1001, which in the illustration is titled SpredPlan File. This dialog box 1001 contains a rectangle 1002 through which the user can by selection (e.g., by mouse) peruse all file directories on all storage disks and select any directory for display of files it contains. This box also has a second rectangle 1003 in which the user can see a list of files in any directory he has selected, and select any displayed file for the linking. Procedures for using such a dialog box are identical to those used for opening all Windows application software files, with which the user must be familiar for use of any Windows software product and which any user of the invention will know well. Such a user interface design can be used for the user's designation to the graphic modeling software of the disk path and file for any external software containing the plan-model or any portion thereof, including spreadsheets, database software, accounting software, or other software products external to the graphic modeling software.
Similarly, in any embodiment of the invention in which all or any of the plan-model or data is contained in any external software, user indication of where it is located in the external software could be made by selection. FIG. 11 illustrates an example of this approach for the spreadsheet entry of the spreadsheet plan-model shown in FIG. 7. In FIG. 11, the user has the graphic modeling software's SpredLink window open, with the lower part 1101 of this window for indication of spreadsheet location of goals currently visible. To enter the location of the goals list, the user has the spreadsheet and its plan-model open 1102, and has selected the columns-and-rows rectangle of the spreadsheet (called a range) containing the goals list 1103. This selection of the goals-list area of the spreadsheet plan-model 1103 causes entry of the appropriate column letters and row numbers in the goals part of the graphic modeling software's SpredLink window 1104.
In an embodiment of the present invention, the graphic modeling software need not be a separate software product--it can be partly or wholly integrated with a spreadsheet or some other software product used for all or some of the plan-model entry. For example, the invention can be implemented with the graphic modeling software as an "add-in" software product designed to work as an optional extra for the spreadsheet or other software product, or as an integral part of a spreadsheet or some other software product, as illustrated in FIG. 12. In an alternative embodiment, a spreadsheet or other software product could be an add-in to the graphic modeling software or an integrated part of the graphic modeling software, as illustrated in FIG. 13. Through integration of the graphic modeling software with the spreadsheet or other software product used for plan-model entry, some or all of the linking steps otherwise taken by the user can be automated or simplified through programming of the integrated product(s).
Automation of the invention's linking can include automatic listing of all items in the plan-model that the user may want to designate as goal cells, so that instead of the user carrying out the linking steps of determining and indicating locations of goal cells, the user can select goals from a list of goal candidates developed and provided for him. FIG. 14 provides an illustration of a system that can be used for this method of aiding the user in linking. In FIG. 14, the audit feature 1401 of the Lotus 1-2-3.RTM. spreadsheet has been used in the condominium plan-model to display a list of "all formulas" 1402, starting in cell E2, which causes the spreadsheet to display in its column E a list of cell addresses and contents of all condominium-model cells containing formulas, which are all output cells 1403. Since all goals must be results of contents of other cells, all goal cells with data or values must contain or be associated with formulas. Potential goals for the graphic analysis must be in such a list. The graphic modeling software can use OLE or DDE to command the spreadsheet to display such a list for the user's plan-model. Then, the label or name defining each of these cells is found by searching cells to left of and/or above the cell containing the data. A list is all these names as potential goals, from which the user can select all those he wants in his list of goals for the graphic analysis.
Similarly, the graphic modeling software can use the audit feature of Lotus 1-2-3.RTM. as shown in FIG. 15 to display, for each goal, a list of all input cells that affect that goal's value, and thus for each goal provide the user a list from which he can select the factors he wants on that goal's factors lists for the graphic analysis. In FIG. 15, in the spreadsheet plan-model, the Net Present Value goal (C5) 1501 is selected. The audit feature's Formula Precedents 1502 is used to display a list 1503 of all cells whose values affect the selected Net Present Value goal. For each goal the user has selected for his goals list, the graphic modeling software can command the spreadsheet to display such a list. Then from this list, the graphic modeling software can find all cells in the list that contain inputs, and for each formula found, repeat the process until all inputs affecting the goal directly or through formulas are identified. For each such cell, the label or name is found and a list of these potential factor names is provided to the user for his selection of which inputs affecting the goal user wants to select as factors for the graphic analysis.
As has been noted, the present invention's entry-and-linking process is not limited to entries made or contained in a spreadsheet. The entry-and-linking part of the invention process can be implemented through entries or data made or contained in other kinds of application software, which are then linked to or copied to the graphic modeling software or through entry directly in the graphic modeling software itself or through combinations of these two. An example of the basic framework of one design for all such entry-and-linking is shown in FIG. 16. This figure shows a design for a window of the graphic modeling software titled Plan Entry & Link 1601, which contains a matrix for Numbers 1602 and a form for Formulas 1603. In the Numbers matrix, the user can enter data (a) directly and (b) by linking to elements of external software. To obtain the user interface for indicating to the graphic modeling software the external source, the user selects the Link button 1604. In the Formulas 1603, the user can make entries in the same ways, directly and/or by linking to external software products. As an example of the flexibility of this design, a user could enter an entire spreadsheet plan-model in the Numbers matrix by linking, and then define further output relationships by entering formulas in the Formulas 1603 that contain references to elements of the linked spreadsheet in the Numbers matrix. Once the entries are made in this window, by either direct entry or linking or both, no further linking is required because the entries in this window are already contained by the graphic modeling software in locations and formats it can use for the graphic analyses.
For users who already have plan-models entered in spreadsheets or prefer to enter them in spreadsheets, an embodiment and user interface designed especially for linking to spreadsheet plan-models as exemplified by FIG. 8 may be most convenient, perhaps with user interface options as exemplified in FIGS. 9-11. As of this writing, in light of widespread use of spreadsheets and resulting possession of plan-models already entered in spreadsheets and skills for entry of plan-models in spreadsheets, such embodiments may be preferred embodiments of the present invention at this time. But in the future, more flexible embodiments of the invention designed for entry directly within the graphic modeling software and/or in various types of external software, as exemplified by the design example shown in FIG. 16, may become preferred, and represent more fully the scope of the entry-and-linking part of the invention.
With desired linking in place, the user has options to select substantial numbers of graphic analyses with each involving variations in the data or values of different combinations of goals and factors. In the preferred embodiment, multi-list menus are provided in which (a) each list is a list of either goals or factors and (b) one or more selections on each of two or more lists constitutes selection of one graphic analysis. The preferred embodiment of the graphic analyses includes, but is not limited to, the two types described next. A first type of graphic analysis shows relationships and values of factors and goals across ranges above and below original or plan data (variants) for a selected goal relative to each of any number of selected factors. This is identified as a "prioritizer" type. A second type of graphic analysis illustrates relationships and values across ranges above and below original or plan data (variants) for a selected goal relative to a combination of any two selected factors. This is identified as an "optimizer" type.
With reference to FIG. 17, a window of the graphic modeling software for identification and user selection of graphic analyses of the prioritizer type is displayed on the computer display screen 128. In this illustration, the graphic analyses offered are those for the condominium example set out in the spreadsheet of FIG. 7 and linked to the graphic modeling software in accordance with FIG. 8.
In accordance with this example, at any time after plan-model entry and linking, to see available graphic analyses and make a selection, the user first selects graph 1701 on the menu, which causes display of a dropdown list of graphic analysis types including prioritizer and optimizer. The user's selection of prioritizer causes display of the prioritizer graphic analyses menu 1702, containing a list of goals 1703 and a list of factors 1704. In this menu, the user can select any item in the goal list. (In the illustration, the goal rate of return is selected.) As the user makes or changes the goal selection, if the factors associated with the selected goal differ from those in the menu's factors list, the factors list changes so that it always shows the factors associated with the currently selected goal. After selecting a goal, the user then selects any one or more items in the factors list. (In the illustration, the first, second, fourth, and fifth items in this list are selected.) The user can select any goal together with any one or more factors, and the combination of all selected items in both lists constitutes selection of a graphic analysis. Once the user has completed these selections, to initiate creation and delivery of the designated graphic analysis he selects the Graph button 1705.
Similarly, FIG. 18 illustrates a design for a window of the graphic modeling software for identification and user selection of graphic analyses of the optimizer type. If after selecting Graph on the menu, in the dropdown list the user chooses optimizer, his selection causes display of the optimizer graph menu 1801. This window contains a list of the goals 1802 and two lists of factors labeled respectively factor1 1803 and factor2 1804. In this menu, the user can select any item in the goal list, at which the factor lists change as necessary to list factors associated with the selected goal and then select any one factor in each of the two factors lists (but not the same factor on both lists). The combination of selected items in all three lists constitutes selection of a graphic analysis. Once the user has completed these selections, to initiate creation and delivery of the designated graphic analysis the user selects the Graph button 1805.
It should be noted that choosing goals and factor(s) for particular graphic analyses is not limited to the illustrated user interfaces for this choosing discussed in preceding pages, and can be accomplished without the step of creating lists of goals and factors from which individual goal/factor combinations are to be chosen for individual graphic analyses. In another embodiment, the graphic modeling software and spreadsheet software are integrated and the menu at top of the integrated product includes a choice for going to graphic analyses of a plan, and when, with a spreadsheet plan-model displayed, the user selects this menu choice, a dialog box 1901 appears in front of the spreadsheet plan-model as shown in FIG. 19. The top section of the dialog box 1902 offers alternatives for graphic analysis type, including prioritizer and optimizer. In the illustration, the user has selected optimizer 1903. In the lower section 1904 of the dialog box are text boxes for choosing goal and factor(s) for a particular graphic analysis of the selected type and indicating a name for each chosen goal and factor. These text boxes are laid out in two columns: a left column 1905 for choosing goal and factor(s) and a right column 1906 for designating a name for each goal and factor. In the left column 1905, in each textbox, the user indicates the appropriate plan-model cell containing the value of an output that is to be the goal or of an input that is to be a factor, either by typing that cell (such as B2) in the text box or, with the typing cursor located in the text box, pointing to the cell in the plan-model and clicking. In any text box for a goal or factor name, in the right text box column 1906 of the dialog box, the user may indicate a plan-model cell containing the appropriate name, in either of these ways, or alternatively type the desired name directly in the text box. When the dialog box indicates the graphic analysis type, goal, factor(s), and goal and factor names that the user desires, to proceed to development and display of the designated graphic analysis the user selects the OK button 1907.
In this embodiment, the lower section of the dialog box changes in response to user change of the upper section's selection of graph type. In the example illustrated in FIG. 19, in which the user has selected optimizer graphic analysis type, the lower section of the dialog box provides text boxes for choosing a factor1 and a factor2, which are the appropriate factor choices for an optimizer graphic analysis. These factor choice options correspond to the factor choices illustrated in the preceding FIG. 18, which illustrates a menu-lists window customized for choosing goal and factors for an optimizer graphic analysis. In fact, the graphic analysis chosen in the dialog box in FIG. 19 is exactly the same as that chosen from the menu lists system in FIG. 18. If instead in the upper section of the dialog box the user changes the selection of graphic analysis type to prioritizer, the lower section of the dialog box changes to enable the user to choose as many factors as he desires, corresponding to the menu lists window customized for prioritizer graphic analyses illustrated in FIG. 17, which enables choosing any number or combination of the listed factors. In the dialog box in FIG. 19, when the selected graphic analysis type is prioritizer, the user can obtain more text box rows to choose more factors, either by resizing the dialog box vertically to make it "taller," or by scrolling down to more text box rows using a displayed scrollbar.
After the goal and factor(s) are selected by the user, analytic graph lines or curves can be created and displayed. The graph lines show the mathematical relationships involving a goal and one or more factors singly or in combination. The graphs represent a vast number of "what-ifs" on a single display, with the graph line points representing data or values above and below the plan data entered (e.g., via spreadsheet) by the user. To accomplish this the graphic modeling software 112 manipulates the plan data or values in order to create and display the graph lines illustrating relationships between a designated goal and one or more factors.
FIG. 20 shows an example of a prioritizer graph. This particular graph is a graphic analysis developed from the condominium-investment plan-model entered in the spreadsheet as shown in FIG. 7, specifically the graphic analysis chosen by the combination of prioritizer graph menu selections shown in FIG. 17.
For prioritizer graphs, as illustrated by the example in FIG. 20, the vertical axis is a scale for measuring or identifying goal data or values. This axis measures goal data for which the user selected a graphic analysis in the goals list of the prioritizer graph menu. In the graph shown in FIG. 20, it measures goal data for rate of return as was selected from the goals list in the menu of FIG. 17.
The horizontal axis is a scale of potential deviations from plan data for factors. At or near the center of the horizontal axis is a zero-deviation point, meaning plan data entered in the plan-model. To the right is a scale of higher-than-plan data, and to left a scale of lower-than-plan data. This scale is measured in a common-denominator measure of deviations from plan data that can apply to any factor, in this example percent deviation from plan data. In the graph example shown in FIG. 20, the scale runs from 30% below plan (-30%) at the left of the graph, to 30% above plan (+30%) at the right of the graph.
The dot near the center of the graph represents the plan data entered in the plan-model. This plan-data dot is directly above the zero-deviation-from-plan position along the horizontal-axis scale. This means that all factors are at plan data or values. The height of the plan-data dot along the y-axis represents plan data for the goal, as shown in the entered plan-model. In the graph shown in FIG. 20, the height of this graph point represents the rate of return for the condominium plan according to the entered plan data, in this case 23.48% as was shown in the spreadsheet entry of the plan-model in FIG. 7.
Each graph line shows the relationship of the goal to one factor, with each particular factor identified by the label at that graph line's right end. There is a graph line for each factor selected from the factors list of the prioritizer graph menu on which the user selected this g |
