MXPA06001774A - Method and computer-readable medium for providing spreadsheet-driven key performance indicators - Google Patents

Abstract

A method, system, apparatus, and computer-readable medium for providing spreadsheet-driven key performance indicators are disclosed. According to the method, a spreadsheet-driven key performance indicator is created that includes a location of a spreadsheet, a location of a cell within the spreadsheet to be utilized as an indicator value, and a goal value. The key performance indicator may be evaluated by retrieving the indicator value from cell of the spreadsheet, comparing the indicator value to the goal value, and providing a visual indication based on the result of the comparison. The goal value may also be expressed as a location within a spreadsheet. The spreadsheet locations may contain references to databases containing the data that should be utilized as the indicator and goal values. A warning value may also be specified, compared to the indicator, and a visual indication may be provided based upon the result of the comparison.

Description

METHOD AND LEGIBLE MEANS BY COMPUTER TO PROVIDE KEY PERFORMANCE INDICATORS DRIVEN BY CALCULATION SHEET BACKGROUND TO THE INVEN C I O N Key performance indicators (also referred to here as "KPIs" or "indicators") help organizations define and measure progress toward organizational goals. In particular, KPIs are quantifiable measures that reflect progress toward an agreed numerical goal. KPIs can differ significantly based on the organization. For example, a business can have as one of its KPIs the percentage of income that comes repeat customers. A customer service department within the business can have as one of its KPIs the percentage of customer telephone calls that were answered within the first minute. Other types of organizations, and groups within those organizations, can define and use other types of KPIs. In order to make KPIs accessible to executives and other employees of an organization, software components are typically built to extract the required data from complex databases, evaluate the KPI and present the results in the form of a balanced logger. which shows the values of one or more KPIs. The creation of these types of complete software components requires programming experts beyond those of the average computer user. Therefore, it is typically necessary to use the services of a professional programmer, which can be very expensive. In addition, data that is created in applications used by many computer users has not previously been available for use in KPIs. For example, although millions of users use spreadsheet application programs for a wide variety of financial and business purposes, data created and stored in spreadsheets has not previously been available for use within KPIs. With respect to these considerations and others, various embodiments of the present invention have been made.

COMPENDIUM OF THE INVENTION In accordance with the present invention, the above problems and other problems are solved through a method, system, apparatus, and readable computer means to provide key performance indicators based on spreadsheet. Through the use of the embodiments of the invention, computer users familiar with only a web browser application program and a spreadsheet application program can create KPIs that incorporate data from common sources, such as spreadsheets. . The KPIs can then be evaluated using the data from the spreadsheet and views by users using a web browser application program. In accordance with one embodiment of the invention, a method for creating and evaluating spreadsheet-based KPIs is provided. According to the method, a user interface is provided to easily create a KPI driven spreadsheet. Through the user interface, a user can provide the location of a calculation envelope, the location of a cell within the spreadsheet to be used as an indicator value for the KPI, and a target value. The goal value can be specified as a value or as the location of a cell within it or another spreadsheet. The user will also be allowed to provide a warning value or the location of a cell within a spreadsheet to be used as the warning value. Once the user has provided this information, a KPI definition can be saved and includes the data. In order to value the defined KPI, the indicator value is retrieved from the defined spreadsheet cell. In order to perform this procedure, the spreadsheet is loaded and calculated. It should be appreciated the spreadsheet cell can refer to other external data sources, such as a database. If the user specified values for the meta and warning values, these numbers are used directly. If the user specified spreadsheet cells for the meta and warning values, the spreadsheets containing the cells are loaded, calculated, and the appropriate values are retrieved, including queries from an external database if specified. Once the indicator value, target value, and warning value have been obtained, the KPI is evaluated by comparing the indicator value with the target and warning values. A visual indicator is then presented based on the results of the comparison. In particular, a visual indicator can be presented if the target value is greater than the indicator value and another visual indicator can be presented if the target value is less than or equal to the indicator value. Similarly, a visual indicator may be presented if the warning value is greater than the indicator value and another visual indicator may be presented if the warning value is less than or equal to the indicator value. The visual icon may comprise a computer icon, a text message, or another type of visual or audible indicator configured to indicate that the target and warning values are less than, greater than, or equal to the target value. The invention can be implemented as a computer procedure, a computer system, or as an article of manufacture such as a computer program product or computer readable medium. The computer program product may be a computer storage medium readable by a computer system and which encodes a computer program of instructions for executing a computer procedure. The computer program product may also be a signal propagated in a vehicle readable by a computer system and which encodes a computer program of instructions for executing a computer procedure. These and various other features, as well as advantages, which characterize the present invention, will be apparent from a reading of the following detailed description and a review of the associated drawings.

BRIEF DESCRIPTION OF THE DIFFERENT VIEWS OF THE DRAWINGS Figure 1 is an architecture diagram of the computer system illustrating a computer system provided by and used in the various embodiments of the invention; Figure 2 is a network architecture diagram illustrating an illustrative operating environment for embodiments of the invention; Figures 3-5 are screen diagrams illustrating presentation screens provided by the various aspects of the invention; and Figures 6-7 are procedural diagrams showing aspects of an illustrative procedure for providing key performance indicators based on spreadsheet.

DETAILED DESCRIPTION OF THE INVENTION Referring now to the drawings, in which similar numbers represent similar elements, various aspects of the present invention will be described. In particular, Figure 1 and the corresponding discussion are intended to provide a brief general description of a suitable computing environment in which embodiments of the invention can be implemented. While the invention will be described in the general context of program modules running on server and personal computer systems, those skilled in the art will recognize that the invention can also be implemented in combination with other types of computer systems and modules. Program. Generally, program modules include routines, programs, components, data structures, and other types of structures that perform particular tasks or implement particular abstract data types. In addition, those skilled in the art will appreciate that the invention can be practiced with subsequent computer system configurations, including portable devices, multiprocessor systems, microprocessor-based or programmable consumer electronics, minicomputers, macrocomputers, and the like. The invention can also be practiced in distributed computing environments where tasks are performed through remote processing devices that are linked through a communications network. In a distributed computing environment, program modules can be located in both local and remote memory storage devices. Referring now to Figure 1, an illustrative computer architecture for a computer 2 used in the various embodiments of the invention will be described. The computer architecture shown in Figure 1 illustrates a conventional desktop or laptop computer, including a central processing unit 5 ("CPU"), a system memory 7, including a random access memory 9 ("RAM") and a read-only memory ("ROM") 11, and a common system conductor 12 that couples the memory to the CPU 5. A basic input / output system contains routines that help transfer information between elements within the computer, such as during startup, it is stored in ROM11. The computer 2 further includes a mass storage device 14 for storing an operating system 16, application programs, and other program modules, which will be described in greater detail below. The mass storage device 14 is connected to the CPU 5 through a mass storage controller (not shown) connected to the common conductor 12. The mass storage device 14 and its associated computer-readable media provide non-volatile storage for the computer. Although the description of computer readable media contained herein refers to a mass storage device, such as a hard disk or CD-ROM drive, it should be appreciated by those skilled in the art that computer readable media can be any medium that can be accessed by the computer 2. As an example, and not limitation, computer readable media may comprise computer storage media and media. The computer storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information such as computer-readable instructions, data structures, program modules or other data. Computer storage media includes, but is not limited to, RAM, EP EEP flash memory or other solid state memory technology, CD- digital versatile discs ("DVD"), or other optical storage , magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other means that can be used to store the desired information and which can be accessed by the computer 2. According to various embodiments of the invention, the computer 2 can operate in a networked environment that uses logical connections to remote computers through a network 18, such as the Internet. The computer 2 can be connected to the network 18 through a network interface unit 20 connected to the common conductor 12. It should be appreciated that the network interface unit 20 can also be used to connect other types of networks and computer systems remote. The computer 2 may also include an input / output controller 22 for receiving and processing input fa number of other devices, including a keyboard, mouse, an electronic needle (not shown in Figure 1). Similarly, an input / output controller 22 may provide output to a display screen, a printer, or other type of output device. As briefly mentioned above, a number of program modules and data files can be stored in the mass storage device 14 and RAM 9 of the computer 2, including an operating system 16 suitable for controlling the operation of a computer network personnel, such as the WINDOWS XP operating system fMICROSOFT CORPORATION of Redmond, Washington. The mass storage device 14 and RAM 9 can also store one or more program modules. In particular, storage device 14 and RAM 9 can store a browser application program 10. As is known to those skilled in the art, the web browser application program 10 is operative to request, receive, submit, and provide interactivity with electronic documents, such as web pages 24 that have been formatted for use with the web browser 10. In addition, the web browser application program 10 may be operative to execute writings contained in web pages 24, such as writings expressed using the JAVASCRIPT language of SUN MICROSYSTEMS, INC. According to one embodiment of the invention, the browser application program 10 comprises the web browser application program of INTERNET EXPLORER of MICROSOFT CORPORATION. It should be noted, however, that other third-party browser application programs may be used to represent various aspects of the present invention, such as the web browser application of MOZILLA FOUNDATION's FIREFOX. As will be described in more detail below, web pages 24 may comprise pages for creating and viewing KPIs. In particular, the web pages 24 may include HTML and writings which, when presented by the web browser application 10, provide a user interface for creating spreadsheet-based KPIs and for presenting spreadsheet-based KPIs. Additional details will be provided later with respect to the format, construction, and use of the web pages 24. Referring now to Figure 2, a network architecture diagram illustrating an operating environment 200 for the various modes of operation will be described. the invention. As shown in Figure 2, the computer 2 is connected to a network 18. Also connected to the network 18 is a web server computer 30. The web server 30 comprises a server computer which may contain some or all of the conventional computing components described above with respect to Figure 1. Additionally, the web server computer 30 is operative to run a web server application 34 to receive and respond to requests for documents stored in or accessible to the web server computer 30. In addition, the web server 30 is operational to receive and respond to requests from web pages 24 created by a web application 35. It should be appreciated that web application 35 may comprise executable code in web server 30, executable code to communicate with other computers, and may include templates, graphics, audio files, and other content known to those skilled in the art. In accordance with one aspect of the invention, the web extension 35 is operative to provide an interface to a user of the computer 2 to create spreadsheet-based KPIs and to view pages showing the results of evaluated KPIs. In particular, the web application 35 can receive a request from the client computer 2 to create a new spreadsheet-based KPl. In response to the request, the web application 35 will return a web page 24 containing files to create a new KPl based spreadsheet. An illustrative web page 24 for creating a new KPl will be described later on below with respect to Figures 3 and 4. Once the user has provided the necessary information to create a new KPl, a definition of KPl 36 can be stored with The provided information. As will be described in more detail below, the definition of KPl is used when evaluating the KPl. A user of computer 2 may also request to see a balanced instrument board including one or more KPIs. When such a request is received, the web application 35 retrieves the appropriate KPl definitions 36 and evaluates the KPIs. In order to evaluate the KPl, the web application 35 uses a server application programming interface ("API") 38. The server API 38 is operative to allow communication with a spreadsheet calculation server computer 32. The spreadsheet calculation server computer 32 is operative to execute a spreadsheet calculation server program 40. The spreadsheet calculation server program 40 comprises an executable program for retrieving and calculating spreadsheets. calculation such as the spreadsheet 44 stored in the database 42. It should be noted that in the embodiments of the invention described herein, the spreadsheet calculation server program 40 comprises the Excel Calculation Server program of MICROSOFT CORPORATION. However, other programs can be used to calculate a spreadsheet on a server computer. It should be appreciated that the compute server computer 32 may include many of the conventional hardware and software components discussed above with respect to Figure 1. It should be appreciated that the spreadsheet cell 44 may contain references to data sources external, such as a database. In particular, one or more of the cells of the spreadsheet 44 may contain a reference to a database 50 connected to a database server computer 46. In order to retrieve the data referenced by such a sheet cell of calculation, the spreadsheet calculation server 40 can transmit a request for the data to an application of the database server 48 running on the database server computer 46. The base server application of data 48 is then operative to retrieve the requested data from the database 50 and return the data to the spreadsheet calculation server 40. It should also be noted that the particular data returned by both the spreadsheet calculation server 40 as per the database server application 48 are dependent on the credentials provided by the user of the computer 2. As a result, the value of a KPl can also be different depending on the particular user and their credentials. Referring now to Figure 3, an illustrative display 60A provided by one embodiment of the invention will be described. In particular, Figure 3 shows several KPIs generated by an embodiment of the invention together with user interface articles to edit the KPIs, to add additional KPIs to the list, and to perform other functions. As shown in Figure 3, each KPl includes a visual indicator 62 and explanatory text for each KPl. The text identifies each KPl and the goal and indicator values for each KPl. Both the target value and the indicator value can be retrieved from a spreadsheet. The visual indicators 62 provide a quick indication of the relationship between the target and indicator values and can comprise any type of computer icon, text, or other type of visual indicator from which the relationship between the target and the target values can be quickly determined. indicator. For example, the visual indicator 62 for a KPl can be presented in one color if the target value is greater than the indicator value (green, for example) and another color if the target value is less than the indicator value (red, for example). Alternatively, different icons can be used if the target value is less than or greater than the indicator value. As will be described in more detail below, a warning value can also be specified. The visual indicator 62 for a particular KPl may also be presented differently depending on whether the indicator value is less than, greater than, or equal to the warning value. As shown in Figure 3, the user interface objects are provided to edit a KPl and to add new KPIs to the list. In particular, button 64 allows a user to create a new KPl or edit the parameters for an existing KPl. An illustrative user interface will be described later with respect to Figures 4-5 to add a new KPl. Additional details regarding the definition, evaluation, and selection of the appropriate visual indicator will be described below with reference to Figures 4-7. Referring now to Figure 4, an illustrative user interface 70 will be described to create a new spreadsheet-based KPl. The user interface 70 is presented in response to the selection of button 64 shown in Figure 3. As illustrated in Figure 4, a number of fields is provided through which a user can enter information related to the new indicator. In particular, the field 72A is used to provide a name for the new indicator and the field 72B is used to provide a description for the new indicator indicating the purpose of the indicator. Field 72C can be used to provide comments for the new indicator that help explain the current value or status of the indicator. In field 72D, the user provides the location of a spreadsheet (also referred to as a "workbook") to be used to retrieve the indicator value. In field 72E, a cell address is provided indicating the cell within the spreadsheet identified in field 72D from which the indicator value must be retrieved. In fields 72F and 72G, the user specifies values for the target value and the warning value, respectively. Alternatively, fields 72F and 72G can be used to specify cell addresses within the worksheet from which meta and warning values should be retrieved. According to one embodiment, the user can be provided with a user interface to visually select the cells of a spreadsheet to be used as the meta, warning, and indicator heats. An illustrative user interface 70B for performing this function is shown in Figure 5. The user can also specify whether the new indicator should be calculated once only or must be calculated each time a user sees the list of indicators. Once the user has provided all the required information, the KPl can be saved by the web application 35 as a definition of KPl 36. Referring now to Figure 6, an illustrative routine 600 illustrating a procedure for creating a new spreadsheet-based KPl will be described. It should be appreciated that although the embodiments of the invention described herein are presented in the context of a web browser application program, the invention can be used in other types of application programs. When reading the discussion of the routines presented here, it should be appreciated that the logical operations of the various embodiments of the present invention are implemented (1) as a sequence of computer-implemented acts or program modules running a computer system and / or (2) as interconnected machine logic circuits or circuit modules within the computer system. The implementation is a matter of choice dependent on the performance requirements of the computer system that implements the invention. Accordingly, the logic operations illustrated in Figures 6 and 7, do not conform the embodiments of the present invention described herein are variously referred to as operations, structural devices, acts or modules. It will be recognized by one skilled in the art that these operations, structural devices, acts and modules can be implemented in software, firmware, special purpose digital logic, and any combination thereof without deviating from the spirit and scope of this. invention as mentioned within the claims appended thereto. The routine 600 starts at operation 602, where the web server computer 30 receives the name and description of the new flag. As described above with respect to Figure 4, this information may be provided in the fields 72A and 72B, respectively. The routine 600 then proceeds to step 604, where the web server computer 30 receives the location of the spreadsheet from which the indicator value must be retrieved. This information is provided in field 72C. The routine 600 then proceeds to step 606, wherein the web server computer 30 receives the cell address within the spreadsheet for the indicator value. This information is provided in field 72D. From operation 606, routine 600 continues operation 608. In operation 608, the web server computer receives a numerical value for the target value or a cell address within the worksheet from which the goal value.

This information can be provided in the 72E or 72G fields, respectively. The routine 600 then continues to operation 612, wherein a numerical value for the warning value or a cell address within the worksheet from which the warning value must be retrieved. This information can be provided in the 72F or 72H fields, respectively. The routine 600 then proceeds to step 612, where the user can specify options with respect to whether the KPl should be calculated only once or each time a request is made to see the KPl. The routine 600 then proceeds to the operation 614 where the data received in the operations 602-612 are stored as a definition of KPl 36. The routine 600 then proceeds to the operation 616, where it ends. Now changed to Figure 7, an illustrative routine 700 for processing a request from the computer 2 for a web page 24 which includes a spreadsheet-based KPl will be described. The routine 700 starts at operation 702, where the web server computer 30 receives a request for a web page 24 that includes a spreadsheet-based indicator, such as the page described above with respect to Figure 5. In In response to the request, the web server computer 30 retrieves the definition of KPl 36 corresponding to each of the indicators on the page. The routine 700 then proceeds to the operation 704, wherein the web server computer 30 transmits a request to the spreadsheet calculation server 40 for the value of the cell in the spreadsheet 44 that is to be used as the value of indicator. In response to the request, the spreadsheet calculation server 40 loads and recalculates the spreadsheet 44. This may include retrieving data from the database server application 48 if the spreadsheet 44 references external data. contained in the database 50. The spreadsheet calculation server 40 then retrieves the calculated indicator value to the web server computer 30. From step 704, routine 700 proceeds to step 706, where the computer Web server 30 determines whether the meta and warning values were expressed as cell references. If the meta and warning values were not expressed as cell references, routine 700 continues to operation 708, where the meta and warning values are retrieved from the KPl 36 definition. If the meta and warning values were expressed as cell references, routine 700 continues to operation 710, wherein a query is transmitted to the spreadsheet calculation server 40 for the values of the cells specified for the meta and warning values. In response to the request, the spreadsheet calculation server 40 returns the values of the identified cells. The routine 700 then proceeds from the operations 708 and 710 to the operation 712. In operation 712, the web server computer 30 compares the indicator value with the target value and the warning value to determine whether the indicator value is greater that, less than, or equal to the target and warning values. Once the comparison has been made, routine 700 continues to operation 714, where the visual indicator for the KPl is selected based on whether the indicator value is less than, greater than, or equal to the target and warning values . The routine 700 then proceeds to step 716, where a web page with the selected visual indicator is created, such as the web page described above with reference to Figure 5. The routine 700 then proceeds to step 718, where returns the web page to the client 2 computer in response to the initial request to see the KPl. The routine 700 then continues to step 720, where it ends. It should be appreciated that, in accordance with the embodiments of the invention, any number of values can be specified and compared with the indicator value. Additionally, any number of different visual or audible indicators may be provided to indicate the relationship between the specific values and the indicator value. In this way, any level of granularity can be provided to compare the indicator value with other specific values. Based on the foregoing, it should be appreciated that various embodiments of the invention include method, system, apparatus, and computer readable medium to provide key performance indicators based on spreadsheet. The above specification, examples and data provide a complete description of the manufacture and use of the composition of the invention. Since many embodiments of the invention can be made without departing from the spirit and scope of the invention, the invention resides in the claims appended thereto.

Claims (20)

1. - A method to provide a key performance indicator driven by spreadsheet, the method comprises: creating a key performance indicator driven by a spreadsheet comprising a location of a spreadsheet, a location of a cell within the squeeze calculation to be used as an indicator value, and a target value; and evaluating the key performance indicator to retrieve a value from the cell from the spreadsheet as the indicator value, comparing the indicator value with the target value, and providing a visual indication based on the comparison.

2. - The method according to claim 1, wherein the target value is expressed as the location of a cell within a spreadsheet for the target value, and where evaluating the key performance indicator also comprises recovering the value of the spreadsheet cell for the target value.

3. The method according to claim 2, wherein providing a visual indication based on the comparison comprises presenting a first visual indication if the indicator value is less than the target value and providing a second visual indication if the value of indicator is greater than or equal to the target value.

4. The method according to claim 3, wherein the cell within the spreadsheet to be used as an indicator value comprises a reference to a database and wherein the retrieve the cell value of the sheet calculation as the indicator value includes recovering a value from the database for the indicator value.

5. The method according to claim 4, wherein the cell within the spreadsheet for the target value comprises a reference to a database and where the retrieve the value of the spreadsheet cell for The goal value includes recovering the target value of the database.

6. The method according to claim 1, wherein the key performance indicator also comprises a warning value and wherein evaluating the key performance indicator further comprises comparing the value of the cell with the warning value and providing a visual indication based on the comparison.

7. The method according to claim 6, wherein the warning value is expressed as the location of a cell within a spreadsheet for the warning value, and wherein the evaluation of the key performance indicator also comprises Retrieve the value of the spreadsheet cell for the warning value.

8. The method according to claim 7, wherein the cell within the spreadsheet for the warning value comprises a reference to a database and wherein the retrieve the value of the spreadsheet cell for the warning value includes recovering the warning value of the database.

9. The method according to claim 8, wherein providing a visual indication based on the comparison comprises presenting a first visual indication if the indicator value is less than the warning value and providing a second visual indication if the value of indicator is greater than or equal to the warning value.

10. The method according to claim 9, wherein the target value is expressed as the location of a cell within a spreadsheet for the target value, and wherein the evaluation of the key performance indicator also comprises Retrieve the value of the spreadsheet cell for the target value.

11. A computer-readable medium that has computer executable instructions stored in it, when executed by a computer, will cause the computer to: receive a request to create a key performance indicator driven by a spreadsheet, the request including a location of a spreadsheet, a location of a cell within the spreadsheet to be used to obtain an indicator value, and data to obtain a target value; in response to the request, it keeps a definition of key performance indicator including the location of the spreadsheet, the location of the cell within the spreadsheet to be used to obtain an indicator value, and the data to obtain the goal value; receive a request to evaluate the key performance indicator driven by spreadsheet; and in response to the request evaluate the key performance indicator driven by spreadsheet, recovering the indicator value of the cell within the spreadsheet, comparing the target value with the indicator value, and presenting a visual indication based on in the results of the comparison.

12. The computer readable medium according to claim 11, wherein the data to obtain the target value comprises a number to be used as the target value.

13. The computer readable medium according to claim 11, wherein the location of the cell within the spreadsheet to be used to obtain an indicator value comprises a reference to a database and wherein the retrieval The indicator value of the cell within the worksheet includes retrieving the indicator value from the database.

14. The computer readable medium according to claim 11, wherein the data to obtain the target value comprises the location of a cell within a spreadsheet of what data can be retrieved and used as the target value. .

15. The computer readable medium according to claim 14, wherein the location of a cell within a spreadsheet from which the data can be retrieved and used as the target value comprises a reference to a base of data and wherein the computer readable medium further comprises computer executable instructions to recover the target value of the database.

16. The computer readable medium according to claim 11, wherein presenting a visual indication based on the results of the comparison comprises presenting a first visual indication if the indicator value is less than the target value and presenting a second visual indication if the indicator value is greater than or equal to the target value.

17. The computer readable medium according to claim 11, wherein the request to create a key performance indicator directed by spreadsheet further comprises data to obtain a warning value, and wherein the computer-readable medium comprises also computer-readable instructions that, when executed by a computer, cause the computer to cause the computer to save the data to obtain a warning value in the definition of key performance indicator, to compare the warning value with the value of indicator in response to the request to evaluate the key performance indicator, and to present a visual indication based on the results of the comparison between the indicator value and the warning value.

18. The computer readable medium according to claim 17, wherein the data to obtain the warning value comprises a number to be used as the warning value.

19. The computer readable medium according to claim 17, wherein the data to obtain the warning value comprises the location of a cell within a spreadsheet from which the data can be retrieved and used as the value of warning.

20. The computer readable medium according to claim 17, wherein presenting a visual indication based on the results of the comparison comprises presenting a first visual indication if the indicator value is less than the warning value and presenting a second one. visual indication if the indicator value is greater than or equal to the warning value.