Release 11i
Part Number A96138-01
 Contents |
 Index |
| Oracle Financial Analyzer User's Guide Release 11i Part Number A96138-01 |
|
This chapter describes how to create and use worksheets.
Read this chapter if you are an Administrator, Budget or Analyst workstation user who is responsible for creating or maintaining worksheets.
This chapter includes the following topics:
A worksheet is a tool you can use to:
Worksheets allow you to view multiple slices of financial data from different perspectives. In addition, they allow you to change the values in data cells. You can enter data manually or have the software populate worksheet cells based on instructions that you provide.
Since worksheets enable you to enter, drill down, and perform calculations on data, they are essential for developing budgets, forecasts, and what-if analyses.
Worksheets let you view and manipulate financial data based on the financial data items, dimensions, and dimension values that are currently defined in your personal database. Administrators typically define these and other database objects and distribute them to users.
The worksheet window contains various elements that enable you to manipulate a worksheet's layout and format.
The following illustration shows a worksheet and its elements.
This table describes the elements of the worksheet window.
|
Element |
Description |
|---|---|
|
Dimension tiles |
Enable you to rearrange the layout of a worksheet and access the Selector dialog box, where you can choose a different set of values for the dimension. There are three types of dimension tiles:
|
|
Edit bar |
Enables you to edit text (such as titles, footnotes, and dimension labels) and individual data values. Select the text or data value you want to edit to make it appear in the edit bar. |
|
Edit bar buttons |
The Cancel button clears text from the edit bar.
The Enter button moves text from the edit bar into the currently selected area.
|
|
Page controls |
Enable you to move between pages in a multiple-page worksheet. |
|
Title, subtitle, and footnote panes |
Enable you to specify a title, subtitle, and footnote. |
|
Row and column labels |
Identifies currently selected dimension values. |
|
Highlighter buttons |
Enables you to select data cells for performing calculations or formatting. You can select a single row or column, a series of rows or columns, or the entire body of the worksheet. |
|
Redisplay button |
Enables you to refresh a worksheet manually after making format or selection changes. |
|
Status bar |
Displays information about currently selected models or hierarchies. |
When you create a new worksheet or open a worksheet, the Worksheet menu becomes available on the menu bar.
The following table describes each item on the Worksheet menu.
|
Menu item |
Description |
|---|---|
|
Worksheet Options |
Enables you to perform the following functions:
|
|
Dimension Labels |
Opens the Dimension Label Setup dialog box, where you can specify the text that the system will use when it creates row, column, and page labels. |
|
Drill |
When checked, enables drilling in worksheets. |
|
Auto Redisplay |
When checked, specifies that worksheets are to be automatically redisplayed when you make changes to them. |
|
Worksheet Tools |
Displays a cascading menu, from which you can select options that enable you to perform calculations on data. You can also access this menu by clicking the right mouse button with the cursor positioned anywhere in the body of the worksheet.
|
When you create a worksheet, current financial data from your personal database is automatically included in the worksheet. You can base a worksheet on the default worksheet template or on an open worksheet.
Follow these general steps to create a new worksheet:
You specify the slices of data that you want to include in the worksheet. A data slice is defined by a financial data item and a set of dimension values.
For more information on the tools and techniques that are available for selecting data, see Chapter 7.
Unlike reports and graphs, which allow you to include multiple financial data items in the same document, worksheets allow you to work with only one financial data item at a time.
Worksheets permit you to work with stored financial data items only. You cannot include formula financial data items in worksheets.
Suppose you want to work with Actuals data. Using the Selector, you would:
For example, if Actuals is dimensioned by Time, Line Item, and Organization, you would select a set of time periods, line items, and organizations.
Once you have created a worksheet and selected the data that you want to work with, you can arrange the dimensions according to how you want to view the data. You rearrange dimensions in a worksheet by changing the positions of the dimension tiles in the worksheet window.
In the sample worksheet shown following:
Each page of the worksheet displays data associated with multiple line items and time periods for a single organization and product line.
To view data for multiple product lines on the same page, you could drag the Product tile and drop it on the Time tile to have the corresponding dimensions swap positions, as shown in the following illustration.
If you wanted to change your view of the data to reflect a single line item and time period and multiple products and sales organizations, you could swap the Organization and Line tiles. The following illustration shows the results.
You could continue to rearrange tiles to yield as many different views of the data as you needed.
To quickly page through an open worksheet, select the paging dimension by clicking on it and press Alt+Page Up or Alt+Page Down.
For more information about creating worksheets, search for the following topics in the Financial Analyzer Help system:
You can use a variety of formatting tools to make a worksheet attractive and easy to read.
The following table describes tasks and related Help topics for using Worksheet Options to change the appearance of a worksheet.
The following table describes tasks and related Help topics for changing the look of rows and columns in a worksheet.
The following table describes other formatting tasks.
As an alternative to manually editing labels for a series of dimension values, you can choose from a set of labels that are already defined for those values. To do this, choose Dimension Labels from the Worksheet menu, then select a label type.
You can use the Highlight Special feature to apply formatting characteristics to all dimension values associated with a dimension in a worksheet. Formatting characteristics applied using Highlight Special:
For more information, search for the following topics in the Financial Analyzer Help system:
You can open any worksheet that you have previously created, or any worksheet that your administrator has defined and distributed to you. You can open only one worksheet at a time. When you finish using a worksheet, it is best to close it to conserve system resources and save space in the Financial Analyzer window.
You can print the worksheets that you create. You can print an entire worksheet, the current page, or a set of pages you specify. You can also print multiple worksheets.
Choose Print from the File menu to print individual worksheets. Choose Print Multiple to print multiple worksheets in a single print session. If you choose to do so, you can sequentially number all the pages across all the worksheets that you print together.
Both Financial Analyzer and Microsoft Windows provide a wide range of printing options that let you adjust margins, create headers and footers, preview a document before printing, and so on.
You can organize worksheets within folders so that users can locate them quickly and easily.
Choose Maintain Folders from the Tools menu to add a worksheet to a folder. This opens the Maintain Folders dialog box, where you can choose a folder and select one or more worksheets to add.
You can delete worksheets that you have created from your personal database.
Choose Delete from the File menu to delete a worksheet. This opens the Delete dialog box, where you can select a worksheet to delete.
As an administrator, you can also delete a worksheet from the shared database so that users no longer have access to it. You delete the worksheet from your personal database, and then distribute the structure with a Delete from System action, which deletes the structure from the shared database and from the personal databases of all of your users, including any administrators immediately below you in the reporting hierarchy.
You can export data from a worksheet to a file that can be used in Microsoft Excel and other applications. You can select from three export formats (.csv, .xls, and .txt), and choose the pages or values to export. If you choose to export to the .xls file format, you can specify whether the data will be displayed in a single worksheet or in multiple worksheets.
Note: The .xls export uses the most recently installed version of Excel. See the Release Notes for a list of supported versions.
To export data from a worksheet, follow these steps.
Result: The Export to File dialog box opens.
If you are exporting to the .xls file format and the size of the file that you are exporting exceeds the maximum size for your version of Excel, a warning message will appear.
For more information about managing worksheets, search for the following topics in the Financial Analyzer Help system:
After you create a worksheet, you can save it for use in future work sessions.
When you save a worksheet, only its shell, or form, is saved to disk. The next time you open the worksheet, current data is incorporated into the worksheet's data cells.
When you save a new worksheet, you assign it a unique name and, optionally, one or more keywords that can be used to access the document at a later time.
When you save a worksheet under a new name, the system gives you the option of saving it as your default worksheet. This enables you to use the saved worksheet as a starting point the next time you create a new worksheet.
When you save changes to an existing worksheet, you can overwrite the original worksheet (if it belongs to your personal library), or you can save the modified worksheet under a new name and preserve the original.
You choose Save to overwrite the original worksheet and Save As to save your changes as a new worksheet with a unique name.
When you save a worksheet from a Budget workstation, both the worksheet specification and any new data that you entered into the worksheet are saved to your personal database. If you want to save the data without saving the worksheet specification (for example, if the worksheet belongs to the administrator's library and you cannot resave it), from the toolbar choose
Alternatively, you can choose Worksheet Tools from the Worksheet menu and then choose Save Data.
When you save a worksheet from an Analyst workstation, Financial Analyzer automatically submits any data that you have entered or changed to the shared database.
For more information about saving worksheets, search for the following topics in the Financial Analyzer Help system:
If you need to create a series of worksheets that are related in some way (such as multiple versions of a budget or forecast), you can link the worksheets so that selections and label formatting changes that you make in one worksheet are propagated to all the linked worksheets.
For example, suppose that you need to prepare a series of worksheets based on the same set of line items. By creating a series of new worksheets and linking their Line Item dimensions, you need to select the set of line items only once; the choices that you make are automatically applied to all the linked worksheets. As long as the dimensions are linked, any time you add or remove a line item or change a label associated with a line item, your changes are applied to all the linked worksheets.
When linking worksheets, the following rules apply:
Links that you create in a worksheet are preserved until you overtly remove them. That is, you can create a link and save and close the worksheet. When you reopen the worksheet, the link remains intact.
For more information about linking worksheets, search for the following topic in the Financial Analyzer Help system: "Linking Dimensions in Worksheets."
The drill feature enables you to view worksheet data at different levels of detail. You can use the drill feature on any dimension that uses a hierarchy to structure dimension values.
In order to use the drill feature, you select a hierarchy and place its corresponding dimension in the down position of the worksheet. Note that you can only use the drill feature when there is no more than one dimension in the down position.
In the following illustration, the plus symbol beside Consulting indicates that drilling is activated, and that Consulting has descendants.
When you click on the plus symbol, the dimension value list expands to show that Consulting has three children: Consulting East, Consulting Central, and Consulting West.
For more information about drilling, search for the following topic in the Financial Analyzer Help system: "Drilling Through Levels of Worksheet Data."
You enter financial data by opening a worksheet, inputting individual data values into the worksheet's cells, and saving the data to your personal database. You can then submit the data to the shared database, making your work available to other users.
Zero values entered into budget worksheets are stored in the database as NA values. Zero values entered into worksheets through a data reader are stored as zeros.
If you enter new data in a worksheet or change data used to calculate other values, you can use the recalculate feature to refresh all the data in the worksheet.
If you are recalculating data along a hierarchy or according to a model, you need to specify the appropriate recalculation options before you use the recalculate feature. The options that you specify are retained in the worksheet until you change them.
When you recalculate data in a worksheet, the recalculation affects only the data slice that is currently selected for display in the worksheet. To recalculate data beyond the boundaries of the worksheet, use a solve definition or a group solve definition. For information about solving data, refer to Chapter 9.
If you want to save data without saving the worksheet, from the toolbar choose:
or choose Worksheet Tools from the Worksheet menu nd then choose Save Data.
If you decide not to keep changes that you make to data in a worksheet, but you want to continue using the worksheet, you can reset its data. You can only reset data that has not been saved. Once data is saved in a worksheet, you cannot reset it.
To reset data, choose Reset Data from the Worksheet Tools menu.
For more information about entering data in worksheets, search for the following topics in the Financial Analyzer Help system:
You can use worksheets to perform calculations on data in your database. The following sections describe the types of calculations that you can perform.
You can grow data in a worksheet by an amount or percentage over a range of cells. The first data value in the range remains unchanged and is used as the base value upon which the calculation is performed. Each subsequent value in the range grows by the specified amount or percentage as compared to the base value.
Suppose that in each month of the last quarter of 2000, Volume Sales for Product A are reforecast to be 5% higher than the month before. By orienting a worksheet to reflect 2000 forecast data with months in the across position, you could select three cells representing Product A Volume Sales for October through December.
If you grew the data values across columns by 5%:
You can increase data in a worksheet by a constant amount or percentage. You can increase values in individual cells or over a range of cells spanning rows, columns, or both.
Suppose that, for each month of the fourth quarter of 2000, Volume Sales for Product A are reforecast to be 5% higher than the corresponding figures in the previous forecast. By orienting a worksheet to reflect 2000 forecast data with months in the across position, you could select the three cells representing Volume Sales for Product A for November through December. You could then increase each of these values by 5%. Each new value would, therefore, be 5% higher than its original value.
You can use the Spread Data tool to spread data in a worksheet from a parent value in a hierarchy to its descendants. There are three different ways to spread data:
You can spread data either down columns or across rows in a worksheet.
Suppose that Expenses for the fourth quarter of 2000 are initially forecast to be $60,000 and is then reforecast to be $120,000. Suppose, too, that you want to determine the effect of the reforecast on the figures for each month in the quarter, and you want the change to be evenly spread to each month. You could use an even spreading method to generate the figures shown in italics.
|
|
Oct 00 |
Nov 00 |
Dec 00 |
Q4 00 |
|---|---|---|---|---|
|
Expenses (forecast 1) |
$10,000 |
$20,000 |
$30,000 |
$60,000 |
|
Expenses (forecast 2) |
$40,000 |
$40,000 |
$40,000 |
$120,000 |
Suppose that Expenses for the fourth quarter of 2000 are reforecast from $60,000 to $120,000. Suppose, too, that you want to determine the effect of the reforecast on the figure for each month in the quarter, and that you want each month's figure to retain its original proportion to the total for the quarter. You could use a proportional spreading method to generate the figures shown in italics.
|
|
Oct 00 |
Nov 00 |
Dec 00 |
Q4 00 |
|---|---|---|---|---|
|
Expenses (forecast 1) |
$10,000 |
$20,000 |
$30,000 |
$60,000 |
|
Expenses (forecast 2) |
$20,000 |
$40,000 |
$60,000 |
$120,000 |
Suppose that Expenses for the fourth quarter of 2000 are reforecast to be $120,000 and that you want to determine the effect of the reforecast on the figures for each month in the quarter so that monthly figures adopt the proportions in another dimension value (in this case, Sales). You could use the spread data tool to generate the figures shown in italics.
|
|
Oct 00 |
Nov 00 |
Dec 00 |
Q4 00 |
|---|---|---|---|---|
|
Sales |
$60,000 |
$90,000 |
$30,000 |
$180,000 |
|
Expenses |
$40,000 |
$60,000 |
$20,000 |
$120,000 |
In this example:
In the worksheet, the months of Nov 00 and Dec 00 are visible, but Oct 00 is not. In the following scenario, there is no value for October, so the $60,000 is distributed evenly between Nov 00 and Dec 00.
|
|
Oct 00 |
Nov 00 |
Dec 00 |
Q4 00 |
|---|---|---|---|---|
|
Expenses (forecast 1) |
|
$30,000 |
$30,000 |
$60,000 |
In the next scenario, Oct 00 has a value of $10,000. Spread deducts this value from the $60,000 leaving $50,000 to spread evenly between Nov 00 and Dec 00.
|
|
Oct 00 |
Nov 00 |
Dec 00 |
Q4 00 |
|---|---|---|---|---|
|
Expenses (forecast 1) |
|
$30,000 |
$30,000 |
$60,000 |
|
Expenses (forecast 2) |
$10,000 |
$25,000 |
$25,000 |
$60,000 |
For more information about calculating data in worksheets, search for the following topics in the Financial Analyzer Help system:
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
