Release 12.1
Part Number E13481-03
Contents
Previous
Next
| Oracle Enterprise Planning and Budgeting User's Guide Release 12.1 Part Number E13481-03 | Contents | Previous | Next |
This chapter covers the following topics:
A worksheet is a document that is distributed for the purpose of collecting data for an Enterprise Budgeting and Planning business process. For example, worksheets might be distributed for budgeting and forecasting.
You may be expected to enter data into a worksheet, distribute the worksheet to subordinates who will enter the data, or both enter data and distribute to subordinates. You might also be required to enter target amounts for subordinates or approve worksheets that have been submitted to you.
The actual functions that you perform for a specific worksheet depend on your role in the organization and the data collection workflow specified by the owner of the business process.
Note: Worksheet information is relevant to users who will enter data and distribute worksheets to subordinates. If you are the business process owner, also see Overview: Data Collection Process.
The Worksheets subtab on the Documents tab is the starting point for data entry functions. Here you can view a list of worksheets, see the status of a worksheet, open a worksheet, and submit a worksheet. You may also be able to distribute a worksheet and set targets for subordinates.
For more information, see the following topics:
The flow of a worksheet through the organization is supported by notifications which are displayed on the Enterprise Planning and Budgeting Home page. You might receive the following notifications concerning a worksheet:
A worksheet has been distributed to you.
A worksheet that you submitted has been approved.
A worksheet that you submitted has been rejected.
A subordinate has submitted a worksheet for your approval.
Your worksheet has been frozen as a result of a higher level submission.
You open a worksheet from the Worksheets subtab on the Documents tab.
Navigate to the Worksheets page (Documents > Worksheets subtab).
Click the worksheet name, or click the Update icon for the worksheet.
Note: The Update icon is not available if the worksheet status is "Frozen," "Submitted," or "Submitted to Shared." If a worksheet has one of these statuses, you can click its name and open it in read-only mode.
Depending on how worksheet data is being distributed, the worksheet may open at once, or you will see the following message:
"Data from a new distribution needs to be loaded into your personal workspace. This action may take some time. If you have not loaded data before, you will not see any data in your worksheet. Do you want to load data now?"
If you see the message, click Yes to load the latest data into the worksheet. Click No to open the worksheet without refreshing data.
Note: If this is a newly distributed worksheet, choose Yes. Otherwise, the worksheet will open but no data will be displayed.
You may be prompted to specify the currency mode for data entry. For more information, see Choosing the Currency for Data Entry.
Navigate to the Worksheets page (Documents > Worksheets subtab).
Click the worksheet name.
When you open a worksheet for the first time, you may be prompted to specify the currency in which you want to enter data. You can choose Business Process Currency or Multiple Currencies.
If you choose Business Process Currency, you will be able to enter data in the currency specified for the business process. This may be the functional currency for your organization or another specified currency. Your worksheet will not include a Currency dimension but you can use the Currency Conversion calculation template to convert entered data to other currencies.
If you choose Multiple Currencies, you will be able to enter data in multiple currencies. Your worksheet will include a Currency dimension. At any time you will be able to change the currency that you are using. For more information see Data entry: Switching the currency view.
Alternatively, you might not be prompted to specify a currency. This is either because there is no currency specification for the business process (currency does not matter), or because worksheets are being distributed in batch and the default currency is being set by a System Profile option.
When you open a worksheet that includes currency, you can identify the currency in which data is expressed in one of three ways. The method depends on the situation.
Currency code in the View name — If the business process has been set up to use a specific currency and you are using that currency, a currency code is appended to the end of the worksheet view name.
Currency code in cell — If a business process has been set up to use local currency and you choose to enter data in that currency, a currency code is displayed within the cells.
Currency in Currency Dimension Label — Regardless of how currency has been set up for the business process, you can determine the currency from the label of each currency dimension member (for example, a currency dimension label "Japanese Yen.")
A worksheet is a data collection document that includes some specially marked cells, a toolbar, a legend, and navigational aids. It may also have instructions for completion.
For more information, see the following topics:
Input cells are the cells in which data entry is requested and the cells from which data will be submitted. Input cells to which you have write access are denoted by a double line border inside the cell. You may see these cells as blank, or they may be prepopulated with values from another view: for example, input cells in a Budget worksheet might initially display values from last year’s budget.
In addition to input cells, a worksheet might also include the following:
Editable cells not designated for input — These are cells to which you have write access, but from which data will not be submitted. An editable cell for which input is not requested is denoted by a single line border inside the cell. You can enter data into these cells and use Autofill functions to distribute the data to other cells. For example, input cells might be at the State level, but if you have write access to City, you could enter city values and let Enterprise Planning and Budgeting calculate State values.
Read only cells — These are cells to which you have read access.
Target cells — These are cells for which target amounts have been set. A cell for which a target has been set is denoted by a bar icon signifying the target type.
Annotations — These are cells into which you, your manager, or a subordinate have entered comment text. An annotated cell is denoted by a bubble icon. Annotations may be associated with input cells, editable cells, and read only cells.
When you open a worksheet in update mode, the document displays the following controls:
Edit — Enables you to add dimension member selections to a worksheet. For more information, see Personalizing a worksheet: Adding and refining dimension members and Using the Refine Selections Page.
Restore Layout — If you have personalized the worksheet, enables you to quickly revert to the layout that was originally distributed to you. For more information, see Personalizing a worksheet: Restoring the default layout.
Print — Enables you to print the worksheet. For more information, see Printing Worksheets.
Export — Enables you to export worksheet data in .csv, .htm (Excel HTML), or .txt format. The Excel export supports an option that allows you to enter budget data offline and import the budget back to Enterprise Planning and Budgeting. For more information, see Exporting Data from Worksheets and About Excel exports.
Import — Enables you to upload budget data that you have entered into a file that was exported to Excel. For more information, see Importing Budgets from Excel.
Save — Saves the current data, annotations, layout, dimension member selections, and format but does not close the worksheet. For more information, see Saving Worksheets.
Apply — Saves the current data, annotations, layout, dimension member selections, and format and closes the worksheet.
Cancel — Closes the worksheet without saving.
In addition, your worksheet might also display the following controls:
Submit — Submits data from cells for which data input is requested. Submit is enabled if you are required to submit data but have not yet done so. For more information, see Submitting Worksheet data.
Validate — Validates cell values against target amounts. Validate is visible if targets have been enabled for the worksheet. For more information, see Viewing target amounts.
The worksheet toolbar provides functions for populating cells, personalizing the appearance of the worksheet, annotating cells, and inserting or editing calculations. The toolbar includes the following tools:
Layout — Enables you to change worksheet layout. For more information, see Changing the layout.
Sort — Enables you to sort dimension members according to a specified order. For more information, see Sorting dimension members.
Annotation — Enables you to enter or edit comment text. For more information, see Data Entry: Annotating cells and Using annotations.
Calculation — Enables you to insert or edit a calculation. For more information, see Personalizing a worksheet: Inserting calculations, and Using calculations.
Format — Enables you to specify formats such as font style and color. For more information, see Personalizing a worksheet: Applying format options.
Autofill — Enables you to populate cells by growing data, increasing data, spreading data, or aggregating data. For more information, see Worksheet data entry: Growing data, Worksheet data entry: Increasing data, Worksheet data entry: Spreading data, Worksheet data entry: Advanced autofill spread based on another view, and Worksheet data entry: Aggregating data.
Recalculation — Calculates data in the worksheet based on the business process solve. For more information, see Worksheet data entry: Recalculating data.
Short instruction text may be displayed at the top of a worksheet. Additional detail may be available as plain text or hyperlinks.
Click the "More" hyperlink next to the short instruction text at the top of the worksheet.
A legend for the worksheet is displayed below the worksheet name. The legend illustrates and describes icons that are displayed in worksheet cells.
Click the Expand (+) icon for Legend.
You can move through a worksheet and change your view of the data.
Use the Tab key or the mouse pointer to move between cells.
Use the horizontal and vertical scrollbars to view visible rows and columns.
Use the settings for Page Items to change the page.
Use Right and Left to view additional columns. Use Up and Down to view additional rows.
Use the drill icons to view lower and higher level dimension members.
You can also use the Layout tool to change the worksheet layout. For example, you can exchange dimension positions or move specified dimensions or edges to different positions relative to one another.
For more information, see the following topics:
You can enter values manually and insert calculations. You can also use Autofill functions to populate cells. For example, you might enter data at one level and aggregate it up to a higher level, or you might enter data at a high level and spread the data down. You may also be able to switch the currency mode for the worksheet.
To expedite data entry you can also export the worksheet data to Excel, enter or edit data offline, and then import the data back into the worksheet.
For more information, see the following topics:
Worksheet data entry: Advance autofill spread based on another view
Worksheet data entry: Exporting data to work offline in Excel
Type a value into a cell. Use the Tab key or the mouse pointer to move between cells. Use the horizontal and vertical scrollbars to view visible rows and columns. Click Right and Left to view additional columns; click Left and Down to view additional rows.
Use the Page Items setting to change the page. Use the drill icons to expand and collapse dimension members. For more information, see Drilling through levels of data.
You can use the Aggregate function to aggregate data across worksheet columns or down worksheet rows. The aggregation method is Sum. NA values are treated as zero.
The last cell in a range is the cell on which the aggregation is performed. All other cells are sources, which are summed to compute the aggregate value.
Requirements for aggregating data are as follows:
The range of cells cannot span nested cells in a column in a left-right direction or nested cells in a row in an up-down direction.
A single row range of cells cannot use the up-down direction and a single column range of cells cannot use the left-right direction.
If the range of cells spans multiple rows and columns, the selection must be symmetric and continuous.
Cells on which aggregation is performed must be write accessible and must not contain calculated values.
From the toolbar, select Autofill.
In the Method box, select Aggregate.
Select the source cells and the cells to which you want to aggregate data.
In the Direction box, choose the direction in which to aggregate data. "Direction" refers to the destination. For example, if you highlight three cells in a row and select "Left," then the cell to which you aggregate data (the destination) will be the left cell; the source cells will be the two cells to the right of the destination cell.
Click Go.
You can use the Grow function to grow data across worksheet columns or down worksheet rows. You can grow data by an amount or percentage, and by either a positive or negative value.
The value in the first cell in a range is used as the base value upon which the calculation is performed, and that value remains unchanged. All subsequent values in the range grow by the amount or percentage as it relates to the previous cell's value.
Requirements for growing data are as follows:
The range of cells cannot span nested cells in a column in a left-right direction or nested cells in a row in an up-down direction.
A single row range of cells cannot use the up-down direction and a single column range of cells cannot use the left-right direction.
If the range of cells spans multiple rows and columns, the selection must be symmetric and continuous.
Cells in which you grow data must be write accessible and must not include calculated values.
From the toolbar, select Autofill.
In the Method box, select Grow Data.
Select the source cells and the cells to which you want to grow data.
In the Direction box, choose the direction in which to grow the data. "Direction" refers to the destination cell or cells. For example, if you highlight three cells in a row and select "Left," then the cells to which you grow data (the destination) will be the left cells; the source cell will be the cell on the right.
If the value is to be treated as a percentage, in the Value Type box select Percent.
In the Value box, enter the value by which you want to grow the data.
Note: To grow the data by a negative amount, type a minus sign (-) before the number.
If you want to treat NA values in the target cells as zero values, select Treat NA Values as Zeros.
Note: If you do not select this option and the value in a destination cell is NA, the cell will have no arithmetic value when you grow data. Only further NAs will result.
Click Go.
You can use the Increase function to increase data in selected cells. You can increase the data by a specific amount or by a percentage, and by a positive or negative value. The current value in each cell that receives the increase will be incremented by the specified amount or percentage.
The cells that you select must be write accessible and must not contain calculated values.
From the toolbar, select Autofill.
In the Method box, select Increase Data.
In the worksheet, select the cells in which you want to increase data.
If the increase value is to be treated as a percentage, in the Value Type box select Percent.
In the Value box, enter the value by which you want to increase data.
Note: To decrease data, type a minus sign (-) before the number.
If you want to treat NA values as zero values, select Treat NA Values as Zeros.
Note: If you do not select this option and the value for a cell is NA, the cell will have no arithmetic value when you increase data. Only further NAs will result.
Click Go.
You can use the Spread function to spread data from one or more cells to other cells.
Requirements for spreading data are as follows:
The range of cells that you select as the source cannot span nested cells in a column in a left-right direction or nested cells in a row in an up-down direction.
If the range of cells that you select as the source spans multiple rows and columns, the selection must be symmetric and continuous.
Cells to which data is spread must be write accessible and must not include calculated values.
If you base a spread on another view, do not choose a local currency view.
From the toolbar, select Autofill.
From the menu, select Spread Data.
In the worksheet, select the source cells and the cells to which you want to spread data.
In the Direction box, choose the direction in which to spread the data. "Direction" refers to the cell or cells to which you are spreading data (the destination). For example, if you highlight a group of cells and select "Left," then the cells to which you spread the data will be on the left; the source cells will be to the right of the destination. The first cell in the range is the cell from which the amount is spread (the source). All other cells in the range receive the data spread from the source.
In the Spread box, select a spread method from the following choices:
Evenly — Data from source cells will be spread equally among all destination cells.
Proportionally, Same Profile— Data from source cells will be divided among destination cells so that cells retain their relationships to the previous values.
Proportionally, Another Profile — Data from source cells will be divided among destination cells according to the percentages represented by another Line Item dimension member.
Proportionally, Another View — Data from source cells will be divided among destination cells according to the percentages represented by another view.
If you chose Proportionally, Another Profile in Step 5, in the Using Profile From box, specify the Line Dimension member on which to base the spread.
If you chose Proportionately, Another View in Step 5, select a view. If the dimensionality of the selected view differs from the current view or if you want to choose different members for one or more dimensions, click More.
The Advanced Autofill Spread Based on Another View page opens where you choose members from the specified view. For more information, see Data entry: Advanced autofill spread based on another view.
If you chose Evenly or Proportionally, Same Profile in Step 5, specify how to treat NA values in the destination cells. To treat NA values as zero values, select Treat NA Values as Zeros.
Note: If you do not select this option and a destination cell is NA, it will have no arithmetic value when you spread data. Only further NAs will result.
Click Go.
One option for spreading data is to base the spread on another view. By default, Enterprise Planning and Budgeting will use the dimension selections in that view. If the dimensionality of the current view and the selected view differ, Enterprise Planning and Budgeting will pin values for missing or extra dimensions on a default alternate member. You can modify this behavior by choosing different dimension members for the spread. You can also change the alternate member for a dimension.
In the Spread box, select the Proportionately: Another View option and select a view.
Important: Do not select a local currency view.
Scroll to the right and click More.
The Advance Autofill Spread Based on Another View page opens.
Identify a dimension for which you want to specify member selections. For this dimension, choose No in the Choose the Same box.
The Update icon becomes live for the dimension.
Click the Update icon.
The Refine Selections page opens where you select dimension members.
Repeat Steps 3 and 4 for each dimension for which you want to change member selections.
If the dimensions of the worksheet view and the selected view do not match, in the Alternate Member box choose a dimension member to hold the values for the missing or extra dimension.
Click Apply.
When you enter or modify data in a worksheet, you can recalculate the data so that values affected by your modifications are automatically updated.
If data has been entered in multiple currencies, recalculation will translate the data against each currency and sum them using the business process currency. It will then run the solve on the business process currency.
You can recalculate the current page or the entire worksheet.
Note: Oracle recommends that you generally recalculate the entire worksheet. Successful recalculation at the page level requires that input selections are page dimensions.
From the toolbar, select Recalculation.
Specify the extent of the recalculation:
To recalculate worksheet using values from the current page, select Page Only.
To recalculate the entire worksheet, select Entire Worksheet.
An annotation is a comment entered in a worksheet cell. You can enter, modify and delete annotations for any cell in your worksheet. You can also view annotations that have been entered by others.
A cell that has been annotated is denoted by a triangle in the left corner. When you pass your mouse pointer over the cell, the annotation text is displayed. When you click the triangle, the Comments Recorded page opens where you can view, delete, and add annotations.
Annotations are saved with a worksheet and are submitted with cells for which input is requested. When you submit or distribute a worksheet that includes annotations, users who have read access to the cells will be able to view your annotations.
For more information, see Using annotations.
You can export worksheet data to Excel and choose an option that allows you to enter and modify values in Excel and upload the results back to your Enterprise Planning and Budgeting worksheet. For more information, see the following topics:
If the business process has been set up to support data entry in more than one currency, you can switch from one currency view to another.
For information about currency options for data entry, see Choosing the Currency for Data Entry.
Enter data in one currency.
Save and close the worksheet.
Navigate to the Worksheets page (Documents > Worksheets subtab).
Click the Switch Currency View icon for the worksheet.
Choose a currency option.
Numeric values in a worksheet are always saved and submitted exactly as entered. Do not use the Format tool to set decimal places, scale, or round numbers. Although formats will appear to change, the original values will apply.
A worksheet that is distributed to you might include targets indicating maximum or minimum performance expected by management. For example, a worksheet for an expense budget might specify maximum target amounts. Targets may be advisory (deviance allowed), or absolute (deviance not allowed).
The values that you enter in cells for which target amounts have been specified will be automatically validated against the targets when you submit the worksheet. You can view the target amounts as you enter data. You can also validate your entries against the targets before you submit the worksheet.
For more information, see Viewing target amounts and Validating entries in target cells.
When a worksheet includes targets, the Target legend is enabled and each cell for which a target amount has been entered displays one of the following icons:
A blue bar with a dotted line below — Indicates an advisory minimum.
A blue bar with a solid line below — Indicates an absolute minimum.
A blue bar with a dotted line above — Indicates an advisory maximum.
A blue bar with a solid line above — Indicates an absolute maximum.
Click the Legend.
Click Edit.
The Refine Selections page opens.
For the View dimension, add the Target view.
Note: The view will have the same name as the worksheet, with the "target" designation appended. For example, if the name of the worksheet is "Expense Budget 2006 001," then the name of the Target view would be "Expense Budget 2006 001 - Target."
You may want to change the layout of the worksheet so that the Target view is on the column edge of the worksheet, next to the data view.
Note: The following procedure will not work if you have added the Target view to the worksheet.
Hover your mouse pointer over a cell that displays a Target icon.
Although target compliance is automatically validated when you submit your worksheet, you can optionally validate your entries against target amounts before submission.
Validation brings up the Validation Results page, which lists each cell for which a target has been specified, the target amount, the variance amount, the variance percent, and the target type.
In an open worksheet click Validate.
You can personalize a worksheet in ways that can help you to enter, review, or analyze data but has no impact on the actual data that will be submitted. For example, you might change the worksheet layout, apply formats, sort dimension members, add or refine dimension member selections, and insert calculations.
Important: Personalizations only apply to your view of the worksheet. If you distribute the worksheet to subordinates, your modifications will not be included. When you submit the worksheet, only data in cells for which input is requested will be submitted. If the business process owner subsequently redistributes the worksheet, personalizations may be overwritten.
For more information, see the following topics:
Use the Layout tool on the Worksheet toolbar to change the layout of an open worksheet. For example, you can exchange dimension positions or move specified dimensions or edges to different positions relative to one another.
For more information, see Changing layout.
Use the Sort tool on the Worksheet toolbar to modify the order in which members of a dimension are displayed in an open worksheet. You can sort dimension members by name or hierarchy. If the View dimension is in the row or column position, you can also sort by View.
For more information, see Sorting dimension members.
Click the Edit button to modify the dimension members that are displayed in an open worksheet. You can add members, delete members that you added, and refine current member selections.
For more information, see Using the Refine Selections Page.
A calculation is a formula that is based on one or more stored or previously calculated dimension members. Use the Calculation tool on the Worksheet toolbar to insert a calculation into a row or column of an open worksheet. You can also edit a calculation. Calculation results will be updated when you recalculate the worksheet. For more information, see About Calculations.
Note: If your layout is overwrittten by a redistribution of the worksheet, you can use the Edit function to add back previously inserted calculations.
Use the Format tool on the Worksheet toolbar to change the appearance of selected cells in an open worksheet. You can specify the following formats:
Font style — You can change font size and apply bold, italic, and underline formats.
Font color — You can set font color.
Background color — You can set cell background color. Do not choose red or blue as these colors are used by various system cell-level icons.
Borders — You can specify border formats.
Important: Do not use the Format tool to set number or date formats. Numeric values are always saved in the format in which they are entered.
For more information, see Specifying formatting through the format tool and Specifying formatting through the Format Cells page.
You can apply conditional formats to worksheet cells. Conditional formats enable you to easily identify cells that meet user-defined conditions. For more information, see Specifying conditional formatting.
Click Restore Layout to quickly restore the appearance of an open worksheet that you have personalized. All modifications to dimension member selections, calculations, format, and layout will be lost.
Important: Restoring the default layout does not clear data values. If this is your intention, clear the data manually or ask your manager to redistribute the worksheet to you using the Overwrite Worksheet Data option.
You can print an open worksheet and specify the page dimension members to include.
Click Print.
The Print Options page opens.
In the Page Items area, specify the dimension members in the page position to include. Choose one or more of the following:
Current selections for page items — Includes dimension members on the current page.
All n combinations of page items (where n is the number of dimensions in the page position) — Includes all combinations of dimension members in the page position.
Specified combinations of page items — Includes a user-specified combination of dimension members in the page position. Choose one or more dimension members in each box. Use the Ctrl key or the Shift key to select multiple members.
Tip: You may want to change the worksheet layout before you select page items. Use the Layout tool on the Worksheet toolbar. For more information, see Changing layout.
Select the paper size and orientation.
Specify the number of rows and columns per page to print.
Click Apply.
Saving a worksheet retains all values that you have entered as well as personal dimension member selections, formatting, and layout. The worksheet is saved in your personal Analytic Workspace.
You must save a worksheet before you can submit it.
Note: If the worksheet is subsequently redistributed, saved data values and layout may be overwritten.
Select one of the following:
Save — Saves the worksheet but does not close it.
Apply — Saves and closes the worksheet.
You can export data from an open worksheet and save the file locally in .csv, .txt, or Excel HTML (*.htm) format. The Microsoft Excel HTML (*.htm) export includes an option that enables you to link the export file to the source worksheet. You can then edit the data offline in Excel and upload the results back into your worksheet.
Click Export.
The Export Options page opens.
In the File Format box, choose an export format:
CSV (*.csv) — Values are exported in Comma Separated Values (comma delimited) format. The exported file is not linked to the source worksheet.
Microsoft Excel HTML (*.htm) — Values are exported in a format using the HTML specification supported by Microsoft Excel. You can link the exported file to the source worksheet by choosing the Enable import from Microsoft Excel option (see Step 3), or you can create an unlinked export file. Selecting the option will allow you to edit the data offline and upload the results back into your Enterprise Planning and Budgeting worksheet.
Text (*.txt) — Values are exported in text format in which the values are delimited by tabs. The exported file is not linked to the source worksheet.
For Excel HTML (*.htm) exports only:
In the Sheets box, choose a sheet option:
Separate sheet for each combination — Places each combination of dimension members within the export scope on a separate sheet.
Single sheet for all combinations — Places all members within the export scope on a single sheet.
Choose or clear Enable import from Microsoft Excel. This option indicates whether the exported file will be linked to the source Enterprise Planning and Budgeting worksheet.
If you choose Enable import from Microsoft Excel, Enterprise Planning and Budgeting will track offline edits made to input cells in Excel. When you upload (import) the budget back to Enterprise Planning and Budgeting, you will see the results in your Enterprise Planning and Budgeting worksheet.
If you do not choose Enable import from Microsoft Excel, you can work with the data in Excel but modifications made to input cells will not be tracked and you will not be able to upload the budget back into Enterprise Planning and Budgeting.
For more information, see Importing Budgets from Excel and About Excel exports.
In the Content Options area, specify the scope of the export file:
Current selections for page items — Exports the dimension members on the current page.
All n combinations of page items (where n is the number of dimensions in the page position) — Exports all combinations of dimension members in the page position.
Specified combinations of page items — Exports a user-specified combination of dimension members in the page position. Choose one or more members for each dimension. Use the Ctrl key or the Shift key to select multiple members.
Tip: You might want to change the worksheet layout before you select page items. Use the Layout tool on the Worksheet toolbar. .
Click Apply.
You are prompted to specify a file name and path for the export file. If you chose the Enable import from Microsoft Excel option in Step 3, you will also be prompted to save the worksheet.
The extent of a file that you export to Excel is based on the page items selected for export. When you open the file in Excel, a table of Contents page displays hyperlinks to each logical page. Clicking the hyperlinks displays each page as a separate worksheet. The exported data reflects the format, layout, sorting, and labels of the source worksheet. Calculations are displayed as static values. The export does not include target values or annotations.
If you chose the Enable import from Microsoft Excel option when you exported the worksheet, the Excel file is linked to the source Enterprise Planning and Budgeting worksheet. You can enter data offline in Excel and then import the data back into the Enterprise Planning and Budgeting worksheet. The import will update editable cells in the worksheet with values that have been entered offline in Excel. This feature requires the HTML plug-in for Excel.
If you do not choose the Enable import from Microsoft Excel option, the export file is not linked to the source Enterprise Planning and Budgeting worksheet. You can work with the data in Excel, but you will not be able to import your edits back to the source Enterprise Planning and Budgeting worksheet.
When you work with an export file that has been enabled for import from Excel, you can enter and modify data in Excel. When you upload the file, data in editable worksheet cells will be imported back into the worksheet. Results of any formulas entered in Excel will be imported as static values.
Note that is only the data that is imported to the worksheet. If you move cells, rows, and columns, insert rows and columns, enter annotations, or apply formatting in Excel, these modifications will not be imported.
The import process does not support moving or copying a worksheet to another workbook. This breaks the link between the export file and the worksheet.
If you delete an editable cell while working in Excel, the import process will generate a warning that some cells are invalid. Because this may be confusing, Oracle recommends that you not delete editable cells in Excel.
To import a budget from Excel
In Enterprise Planning and Budgeting, open the worksheet from which you exported data (Documents > Worksheet > Worksheet Name > Update).
Click Import.
The Import from Excel page opens.
In the File Location box, specify the location of the Excel file. Use the Browse button to search for the file.
In the Import Update Options area, choose an update option:
Editable cells changed in Excel — Uploads data from cells that have been changed in Excel.
All editable cells — Uploads data from all editable cells. This option is useful if you want to refresh the entire worksheet.
Click Apply.
If the data collection process supports target setting, you can enter targets for input or calculated cells that are subordinate to those that you own. When you distribute the worksheet, recipients who have access to these cells will be able to view target amounts and validate their entries against the targets.
Note: You will not be able to enter targets for loaded lines or for cells that you own.
You can designate a target as maximum or minimum; advisory or absolute. If you enter a target amount but do not specify other information, Enterprise Planning and Budgeting will enforce an advisory target. Whether the target is maximum or minimum depends on the "Better Flag" setting for the Line in the Enterprise Performance Foundation: for example, Revenue lines will default to minimum; Expense lines will default to maximum.
Before you enter target amounts, you can adjust your view of the Targets page to make it easier to enter values. Note however, that if the worksheet is redistributed to you, your layout will be overwritten.
Navigate to the Worksheets page (Documents > Worksheets tab).
Identify the worksheet for which you want to enter target amounts and click the Targets icon.
The Set Targets page opens.
Note: The Targets icon is not available if the worksheet status is "Frozen," "Submitted," or "Submitted to Shared."
You can use the Layout, Format, and Sort tools to adjust the page in ways that can help you to enter target amounts. For more information, see the following topics:
Enter target amounts into appropriate cells. You can also use Autofill functions (Grow, Increase, Spread, and Aggregate) to populate cells.
Note: By default, new targets are advisory. Proceed to Step 5 to change target type.
For more information, see the following topics:
Specify target type as follows:
Click in a cell that includes a target amount and click the Target tool.
Note: To specify the target type for multiple cells in a row or column, select the row or column before you click the tool.
Select an option in the Target Type box and click Go. You can select one of the following:
Minimum amount - Advisory
Minimum amount - Absolute
Maximum amount - Advisory
Maximum amount - Absolute
An arrow representing the target type is displayed.
To clear settings, select the cell or cells and click Clear.
To enter target amounts for another page, change one or more dimension selections, scroll to the right of all the dimension selection boxes and click Go.
Click Save on any page to save your settings and continue working.
Click Apply to save and exit.
If the Distribute icon is active for a worksheet when you view the worksheet list (Documents > Worksheets subtab > Worksheet Name), you can distribute the worksheet to subordinates.
You can also redistribute a worksheet that you have previously distributed. For example, you might redistribute a worksheet if you received an updated version from your manager or if a subordinate has asked that his or her worksheet be reset. You might also redistribute a worksheet if your submission was rejected by your manager and you want subordinates to adjust their entries and resubmit.
When you redistribute a worksheet, you specify whether or not to overwrite the previous distribution.
Note: If you want to provide baseline data or targets that will be visible to recipients, populate your worksheet prior to distribution. If this is a redistribution, ensure that you select the Overwrite option.
Navigate to the Worksheets page (Documents > Worksheets subtab).
Identify the worksheet that you want to distribute and click the Distribute icon.
The Distribute Worksheet: Worksheet Name page opens.
In the Worksheet Recipients area, specify the users who will receive the worksheet. Click Add to add a user.
In the Distributions Options area, you can specify an optional deadline by which recipients must submit their worksheets. Enter a date or click the Calendar icon to select a date.
Recipients will see this deadline on the Worksheets subtab.
In the Message box, enter text for the notification that recipients will see when the worksheet is distributed to them.
Use the Overwrite Options area to specify how distribution will affect recipients' existing data.
Select Overwrite to overwrite data and annotations in recipients' worksheets.
Select Do not Overwrite to preserve data and annotations in recipients' worksheet.
Note: Target amounts and the layout of the Target view will always be overwritten.
The submission process automatically recalculates data and, if targets are enabled, validates compliance with the target amounts. If approval is required, submission also generates a notification for the approver.
Note that only data and annotations in cells for which input is requested gets submitted; data outside the scope of the data collection template or objects such as personal or shared dimension members and calculations are not submitted.
Once you submit a worksheet, you will not be able to edit or resubmit it unless the worksheet is rejected by the approver or redistributed to you.
You can submit from the open worksheet or from the Worksheet list.
Note: It is possible that when you attempt to submit a worksheet, the same worksheet might be in the process of being redistributed. In this case, a message will be displayed and you will be prevented from completing your submission.
For more information, see the following topics:
Click Submit.
The Submit Worksheet: Worksheet Name page opens.
Enter text in the Comments box. If the worksheet requires approval, comment text will be displayed in the notification sent to the approver.
Click Submit.
You are prompted to confirm the submission.
Navigate to the Worksheet list page (Documents > Worksheets subtab).
Identify the worksheet that you want to submit.
Click Submit.
The Submit Worksheet: Worksheet Name page opens.
Enter text in the Comments box. If the worksheet requires approval, comment text will be displayed in the notification sent to the approver.
Click Submit.
You are prompted to confirm the submission.
When you submit a worksheet for which targets have been specified, your submission is automatically validated for compliance against target amounts. If there is non-compliance with any absolute target, the submission will fail and a Validation Results page will be displayed showing all non-compliant values. If there is non-compliance with an advisory target, the Validation Results page will be displayed and you will be prompted to specify whether you want to proceed with the submission.
When you submit a worksheet that requires approval, a notification is sent to the approver.
If the worksheet is approved, data and annotations in cells for which input is requested will be merged into the approver's worksheet. You will receive an approval notification, and the status of the worksheet will be set to "Approved." Your worksheet and any subordinate worksheets will be frozen.
If your worksheet is rejected, you will receive a rejection notification and the status of the worksheet will be set to "Rejected." You can then open the worksheet, update it, and resubmit. Alternatively, you can redistribute the worksheet to subordinates who can modify their entries.
When you submit a worksheet that does not require approval, data is copied directly to the shared Analytic Workspace and a notification is sent to the owner of the business process.
The business process owner can view the submitted worksheet. However, he or she can neither approve nor reject it.
Note: If you find that you need to revise data, you can ask the owner of the business process to redistribute the worksheet to you. He or she will be able to do this if the Manage Submission task which copies data to the shared Analytic Workspace has not yet run.
If you have distributed a worksheet to subordinates, you will be notified when each recipient to whom you distributed submits his or her worksheet.
You can open the submitted worksheet and see the data entered by the submitter. If the worksheet requires approval and you approve the submission, data in cells for which input is requested (including any annotations that have been entered) will be merged into your worksheet and recalculated as specified in the business process solve. The status of the subordinate worksheet will be set to "Approved." If you reject the submission, a notification will be sent to the submitter. The status of the subordinate worksheet will be set to "Rejected." In this case, data will not be copied into your worksheet.
When you approve a worksheet, you cannot edit data. You can only approve or reject the submission.
Navigate to the Worksheets page (Documents > Worksheets subtab).
The list of worksheets that you have received or distributed is displayed.
Identify the worksheet for which you want to review data and click the Status hypertext.
The Status page for the worksheet opens.
Select All to see the status of subordinate worksheets. Alternatively, you can enter a user name, search for a user, or limit the display by status.
Identify the user whose worksheet you want to review and click the Review icon.
The Review Worksheet: Worksheet Name page opens.
Click Approve to approve the worksheet. Click Reject to reject it.
If you chose Reject in Step 5, enter a comment. Comment text will be displayed in the rejection notification that will appear on the user's Home page.
You can view status information about a worksheet that you have received. You can also view status information about a worksheet that you have distributed.
For worksheets that you have distributed, the initial display is limited to information about your worksheet and the worksheets of those individuals who received the distribution. If recipients have further distributed the worksheet, you can also view the status of subordinate worksheets.
Navigate to the Worksheets page (Documents >Worksheets subtab).
The list of worksheets that you have received or distributed is displayed. The Status column displays the worksheet status. Status designations are as follows:
Approved — The worksheet has been approved.
Distributed — The worksheet has been distributed.
Distribution Pending — The worksheet has been distributed, but the recipient has not yet opened it or has not accepted the distributed data into his or her personal worksheet.
Frozen — The worksheet has been frozen because higher level worksheets have been submitted.
Rejected — The worksheet has been submitted but was rejected. (This status only appears if the worksheet requires approval.)
Submitted — The worksheet has been submitted.
If your system has been set up to distribute worksheets in batch and you are expecting a distribution, you can use the Request tab to view the status of the Worksheet Data Distribution concurrent request
Log into Enterprise Planning and Budgeting.
Navigate to Schedule Requests (Requests tab > Schedule Requests).
In the View box, search for the request by name: Worksheet Data Distribution.
The grid displays requests that meet your criteria. The icon in the Status column displays the request status.
Click Details to view detailed information for a request. On the Details page, click View Log to open the request log.
Copyright © 2004, 2010, Oracle and/or its affiliates. All rights reserved.