Financials: Smart View Account Inquiry and Analysis



Fusion Smart View Account Inquiry and Analysis

Highlighted Features

▪ Online analysis of account balances in a familiar spreadsheet environment.

o You get better user-friendliness and greater productivity in manipulating and analyzing financial data in any ad hoc way that suits you.

▪ Drill down from parent-level account balances to lower level account balances and to the underlying journals.

o You get instant access to the details you need to investigate and resolve any issues.

▪ Refresh the spreadsheet to update the latest balance information, for example if a new journal gets posted.

o You get up-to-date data instantly, so your decisions are always based on current, correct information.

Login Details

Login id/password: finuser20/Welcome1 or finuser21/Welcome1

Be sure you are logged into Fusion GL using same login so that you can demo drilldown from Smart view to GL.



Demo Script

This script walks you through the process for using Smart View to view and analyze your account balances in Excel. This introductory flow will show you how straightforward it is to inquire upon and manipulate account balances using familiar desktop tools, in order to get to the information you want in an efficient, user-friendly way.

1. Launch Microsoft Excel and open the VFUS_OOW.xls file. Note that your Smart View queries can be saved and reused, saving time if you are regularly investigating similar account balance information. You can refresh your query to update it with the latest live GL balances (more on this later).

2. Enter your login details to connect to the database - finuser20/Welcome1 or finuser21/Welcome1.

3. Using the Point of View window

An additional window appears once you are connected to the database. This Point of View window allows you to select values for your data dimensions[1], so you can pinpoint the information you want in your Excel analysis. This window can be floating or docked at the top / side of the screen to suit you. Try it by grabbing the title bar and dragging to move or dock the window.

The Smart View Data Source Manager window also appears, but as your database connection is already setup for this exercise, you can close this window.

a. The query is currently set of cost center 311-Sales & Marketing US. Note that 311 is the cost center value, and Sales & Marketing US denotes its description.

Find the Cost_Center dimension in the Point of View (POV) window, and click the list of values arrow to select a new value.

Note: If you hover over the pull-down arrows beside each dimension, you will see the dimension names.

From the list, click […] to open the Member Selection window. The Members region shows all the hierarchy (tree) versions that have been created for this dimension.

b. Click [+] to expand ALL VF COST CENTERS-V2. Then expand [ALL VF COST CENTERS-V2].[999] and again [ALL VF COST CENTERS-V2].[999].[500].

Note: When a value appears in more than one tree version of the same dimension, Smart View always uses the unique member path to identify each value; for example in Tree-v1 cost center 999 might have child values 100, 200 and 300 but following a re-organization structure Tree-v2 is created where cost center 999 has child values 100 and 200, so Smart View must clarify which version of cost center 999 to use. In this chart of accounts, the cost center values 999 and 500 appear in more than one tree version and so they are identified by their full path name. The fully qualified member name is also used when the same value is used in more than one dimension, for example if 100 is a valid value for both cost center and product it will need a fully-qualified member name.

c. Check the box next to cost center 500 and click the [pic] icon to move it across to the Selection region, and also click the [pic] icon to take 311 out of the Selection region, and then click OK to close the window.

d. Click the Refresh button. The Smart View query will refresh the account balances for cost center 500. As this is a parent value, the amounts retrieved will be the sum of accounts balances for its children (cost centers 510, 520, 550 and all their children).

[pic]

e. Investigate some of the other dimensions available in the POV window. Remember to click Refresh after changing any member values in order for the changes to be reflected in the balances.

▪ Currency

The query is currently based on USD. You can change the currency to GBP to view entered amounts in GBP, but also remember to change the Currency Type in the next step.

▪ Currency Type

Use the Member Selection window to change the currency type from Total to Entered. Refresh the balances to see entered currency amounts for GBP journals or transactions.

Change the currency code back to USD, and you will only see balances that derive from journals or transactions originally entered in USD.

Use the Member Selection window again to change the currency type from Entered to Converted from GBP, leaving the currency code as USD. You will now see the portion of the total USD balances that was converted from transactions entered in GBP.

Guidelines:

If Currency Type = Total, then pick the ledger currency

If Currency Type = Entered, then pick an entered currency

If Currency Type = Converted from […], then pick the ledger currency

In order to see total balances (both foreign amounts converted to USD, and those originally entered in USD), change the Currency Type back to Total and the Currency back to USD.

Note: Some of the dimensions do not have a specific member value specified by default. In those cases, they will take the value of the top of the hierarchy as the default. For instance, the default for program will be All Program Values which is the top of the hierarchy, and the default for Amount Type is Base, which is the first value.

As demonstrated here, the Point of View window acts like a set of parameters to refine the data in your spreadsheet. When you change values in the point of view, they can be refreshed instantly against the latest General Ledger data; you do not need to run any batch process to load in new data.

4. Working within the spreadsheet

The POV is a convenient way of changing your view of the data, but you can also place multiple dimensions within your Excel sheet in order to see multiple member values at once in a report-style layout. Dimensions can be dragged-and-dropped from the POV window onto the spreadsheet, and you can have multiple dimensions in the rows or the columns.

The example spreadsheet shows the Account dimension in the rows, and the Accounting Period and Balance Amount dimensions in the columns.

a. Drag cost center 500-Corporate Office from the POV window over to the left-hand side of your spreadsheet (over the top of one of the Account cells in Column A). When you drop it into place, it will become a new row dimension.

Note: If your dimension lands in the wrong place, right-click-and-drag to move it back to where you started and try again – it becomes easier with practice. You also have an Undo button in the Smart View menu (when working in Smart View, use this instead of the standard Undo function in Excel).

[pic]

b. Parent values can be expanded within the spreadsheet to drill down and view the account balances of the child values. Either use Zoom from the Smart View toolbar, or double-click on the parent value to expand it – the children of 500 (cost centers 510, 520 and 550) will now be displayed, allowing you to analyze them in more detail. 520 and 550 are parent values themselves, and you can expand them to see their children. Within cost center 520, you can expand cost center 530, which is also a parent. Each child level in the hierarchy is indented from its parent.

Note: At the moment, child values are expanded above their parent rather than below.

c. From the Smart View toolbar, click Options and select the Member Options tab. Under the Zoom In grouping, you can specify how members of a hierarchy are displayed; for example, you can control whether you zoom in by one level at a time, expand all child levels, or zoom straight o the lowest level of the hierarchy.

Under the Data Options tab, you can control the suppression of data under the Suppress Rows grouping. Check the box to suppress the No Data / Missing, click OK to close the Options window, and refresh your spreadsheet. The cost centers with no balances (corresponding cells have #Missing) will be removed from your analysis. (If this box was already checked, see what happens when you uncheck it).

d. You can zoom in and out on any dimension that parent/child values – not just segments within your chart of accounts, but also accounting periods. Zoom in on the cell containing Qtr3-09. It will expand to show the accounting period months that are child values for that quarter.

e. The original data for Qtr2-09, Qtr3-09 and Qtr4-09 is still displayed, in addition to the new child data for months within Qtr3. Use the Essbase toolbar to keep only the monthly data and remove the quarterly data from the Spreadsheet. Use CTRL + Click to select the cells corresponding to Apr-10, May-10 and Jun-10. Click [pic] in the Essbase toolbar and these will not be the only accounting periods retained in the spreadsheet. The [pic] function works in a similar way.

f. You can also pivot the data in the spreadsheet if you want to change the orientation of your view. Click to select the cells that reads Beginning Balance (cell C2), and use the Essbase toolbar to pivot the Balance Amount dimension from the columns to the rows using the [pic] button. The spreadsheet is now laid out with 3 dimensions – Balance Amount, Cost Center and Account – in the rows of the report.

The selected dimension to be pivoted is always moved to the outermost row/column of the opposite axis. This might not always be the most helpful place for your analysis, so the dimensions can also be rearranged in the spreadsheet by dragging and dropping.

To analyze the data by cost center first then balance amounts within each account, right-click and drag the cell for Beginning Balance from the left of the spreadsheet and position it over column C – i.e. the cell that says 50000 – Total Cost of Goods Sold – and the spreadsheet’s layout will change accordingly.

[pic]

g. Dimensions can also be dragged back to the POV window if you would like to remove them from the spreadsheet. Right-click and drag the cell for cost center 520-Finance and Accounting and drop it back to the POV window. The cost center dimension is removed from the spreadsheet.

h. Right-clicking on a member also gives you the ability to manipulate data in the spreadsheet. Right-click on the cell containing Jun-10, and choose Smart View > Member Selection… Find Jul-10 and Aug-10 and add them to the selection region (they are in Qtr4-09). Click Refresh to populate data for those periods.

[pic]

If you know the unique member name of a dimension member, you can type it into the desired position, and Smart View will look up the data. Type ‘Sep-10 into cell H1 to the right of Aug-09 (the apostrophe is required so Excel treats this as a character cell). Click Refresh.

i. You can use Excel’s capabilities for calculations on the data in your spreadsheet to calculate totals, averages, variances, etc. The recommended practice is to add calculations after you have finalized your report layout, since adding a new dimension to your Smart View report might mess up your calculations or cause data in them to be lost. You can also use Excel formatting and conditional formatting to change fonts, or the way cells display currencies, percentages, etc.

5. Refreshing the query to see real-time balance information

Having the flexibility to manipulate your balances in Excel without the need to import and export the data is a key feature of General Ledger and Smart View integration.

One additional problem with traditional import/export to Excel is that data can become “slate” – your analysis would be based on out-of-date information as new journals are posted and balances change. Smart View has a Refresh button on the POV window and on the Essbase toolbar which will instantly update your data with the most current balances posted in General Ledger, so you can reuse your query time and again, knowing that it is always based on accurate account balances.

6. Drilldown to GL journals

You can drill down on an account balance in Smart View directly into Fusion General Ledger to view the journal lines that make up the account balance. Expand on a value in Smart View until to arrive at an account combination that has child values for all the segments. Then select the Drill Through icon on the Essbase toolbar to drill into the GL Inquiry page. (If drilldown does not work, go to Essbase menu in the spreadsheet, select Change Alias, and make sure it is set to NONE.)

-----------------------

[1] In Smart View terminology, dimensions are each of the segments of your chart accounts plus other accounting elements such as ledger, currency, accounting period, balance type. The values for each dimension are known as members.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download