The Assumptions Worksheet

You can set up a series of Excel Reports in a workbook that refers to an Assumptions tab in your workbook. The Excel Reports can use absolute or relative cell addresses in the Design Manager to specify where to find one or more dimension members. Cell addresses can point to dimension or member names in the row axis, column axis, or point of view (POV).

For example, if you are designing reports for each department entity or company where each group’s data is available in a worksheet, you can create an assumptions worksheet where you can input global drivers like Scenario and Fiscal Year. Point the Excel Cell References to the assumptions worksheet from each department, entity or company worksheet. Then you can update the assumptions worksheet with the required scenario and fiscal year and refresh the data in all worksheets by using the Refresh All option.

Consider the following Assumptions tab in a workbook.

Each cell in the Assumption tab is a simple text label. Member names must be spelled correctly because the data retrieval will be based on the text in the cell. Additionally, member names should not include the display label; but only use the code for the member. Here is a Summary report in the workbook that uses the Assumption tab for the page heading, column headings, and row headings.

The data cells in the Summary report are pointing to the page, row, and column headings on the worksheet, as follows:

Example of Inserting a Relative Cell Address for the Row Axis

For the Department cell reference, select all the SpotlightXL data cells in row 6 then click Design Manager.

Select the Department dimension. Click the Member Select icon > Select Cell Reference > Relative.

A box appears, asking you to select the relative cell address. Click cell A6.

After you click OK, you will see cell address A6 as the new value for the Department dimension. If you make a mistake, you can double-click the New Value column and correct.

Click Update. Click Refresh to ensure that the data is retrieved correctly. A valid department name must be in cell A6.

Example of Inserting an Absolute Cell Address for the Column Axis

For the Month cell address, select all the SpotlightXL data cells in column C then click Design Manager.

1. Select the Time dimension. Click the Member Select icon, Select Cell Reference > Absolute.
   
   
2. A box appears, asking you to select the absolute cell address. Click cell C5.
3. After you click OK, you will see cell address $C$5 as the new value for the Time dimension. If you make a mistake, you can double-click the New Value column and correct.
4. Click Update. Then click Refresh to ensure that the data is retrieved correctly. A valid time dimension name must be in cell C5.

Example of Inserting a Custom Cell Address for the Point of View (POV)

For the Account cell address, select all the SpotlightXL data cells in the table then click Design Manager.

Select the Account dimension. Click the Member Select icon > Select Cell Reference > Custom.

The New Value area in the Design Manager displays a text box for you to type the cell address.

This text box gives you the flexibility to specify any cell in any worksheet, so long as you use the correct syntax. You can mix absolute and relative in the address, as you can in Excel. Here are several examples:

Type Assumptions!$B$2.

After typing in the new value, you must press Enter (or use the mouse or arrow keys to select a different row in the Design Manager box) before the Update button is enabled. You will see cell address Assumptions!$B$2 as the new value for the Account dimension. If you make a mistake, you can double-click the New Value column and make a correction.

Click Exclude POV. The SpotlightXL Cell Addressing feature is intended to be used for row and column axis members. If you use the Relative, Absolute, or Custom Cell Addresses to specify a POV dimension, you must click Exclude POV. This ensures that SpotlightXL will give precedence to the Excel Cell Address rather than the member that was originally selected for the data.

Click Update. Then click Refresh to ensure that the data is retrieved correctly. A valid account dimension name must be in cell B2 of the Assumptions worksheet.

Additional Information When Specifying Member Names for Excel Cell References

• When specifying an Excel cell address as a SpotlightXL dimension member, that cell can contain a SpotlightMetadata formula or plain text. SpotlightXL prevents you from accidentally erasing a Spotlight formula by disallowing you to delete one cell at a time. However, you can erase Spotlight formulas by selecting 2 or more cells and pressing Del. (Note that the drop-down arrow still appears on any cells that previously contained Spotlight formulas but the drop-down is not functional.)
• If an invalid member name is used, SpotlightXL displays the data as #INVALID. In the example below, the Sales department is spelled incorrectly.

Cross-Workbook Excel Cell References

If you have an Assumptions list that is in a separate workbook, you must use the Custom Cell Reference option. The separate workbook must be open, but you cannot click into it to insert the cell reference. You are responsible for typing the correct syntax of the cell address.

Excel Reporting Income Statement Example

This topic provides an example of creating an Income Statement based on a view using Excel Reporting.

1. Select a View that contains the pertinent accounting data for the income statement.
2. Click the required sheet in the workbook, and create the headings and look of the Income Statement that you want.
   
3. Click back to the Analyze tab, and select Design With > Excel Report.
   
   
4. Click Refresh.
5. Copy the appropriate data cells to the Income Statement tab to fill in the contents of the Income Statement. Note that Gross Profit is not a member in the model so it will need its own Excel formula.
   Here is the result.
   
   Now, if you want to display your numeric values on a smaller scale, such as in millions, here are the steps:
6. Insert a heading indicating values in millions.
7. Use custom Cell Formatting ($#,,;) to display the value of the cells in millions.
   
   
   Here is the resulting report.