Easy Value with FDMEE Reports

Strolling into work sipping coffee, the realization soon hits that information is needed out of Financial Data Quality Management Enterprise Edition (FDMEE) for internal audit.  After logging in to Data Management, what happens?? We freeze!  And the questions begin swirling in our heads:  How do we get data out of FDMEE?  What are the drivers needed to do that?  What tools are needed to write an FDMEE report and from where do we get them?

At this point, it is often easier to evaluate existing reports within the application for what they lack rather than start creating a report from scratch and then modify and/or update them to meet our specific needs.

A Variety of Report Options

FDMEE Reports does not equal Financial Reports. From within the application, there are numerous options available to choose from for reports.  Most of these are updated reports from FDM Classic.  These groups help to focus on and categorize common reports together and provide information on the following:

  1. Audit Reports display all transactions for all locations that compose the balance of a target account
  2. Check Reports provide information on the issues encountered when data load rules are run
  3. Base Trial Balance Reports provide detail on how source data is processed
  4. Listing Reports summarize metadata and settings (such as the import format, or check rule) by the current location
  5. Location Analysis reports provide dimension mapping by the current location
  6. Process Monitor Reports shows locations and their positions within the data conversion process
  7. Variance Reports display source and trial balance accounts for one target account, showing data over two periods or categories
  8. Intersection Reports identify invalid HFM data load intersections

Below is a screen shot of the default FDMEE report groups:FDMEE Reports 1

 

 

 

 

 

 

 

 

Getting Started

While the canned reports are a great start, creating custom reports allows more creativity and only requires the following:

  1. Microsoft Word (2010+)
  2. Oracle BI Publisher 11.1.1.7 or 11.1.1.9
  3. Working knowledge of SQL
  4. Working knowledge of the FDMEE database tables

First, if you do not currently have Microsoft Word installed, this process isn’t going to work.  After confirming your version of Word, navigate to Oracle to download the BI Publisher software. (http://www.oracle.com/technetwork/middleware/bi-publisher/downloads/index-101746.html).

After installing the software, an access toolbar will become available:

FDMEE Reports 2

This is where the good nerdy stuff happens!  You need to write a query, via SQL*Developer or SMSS that can then be dropped into FDMEE to produce an XML.  In FDMEE, the query will produce an XML that contains the first 100 rows when you test/validate.  This XML file is what you can bring into BI Publisher (via Word) to produce your report.  Below is a screen shot of FDMEE-generated download for Word:

FDMEE Reports 3

And YES! FDMEE CAN Accept Inputs

FDMEE has the ability to have many prompts.  The information can be user input or a selection from a drop-down.  This information can be gathered/compiled in multiple smaller report queries or from out-of-the-box drop-downs.  Below is a sample FDMEE report with input parameters:

FDMEE Reports 4

Ample Value

Custom FDMEE Reports can be valuable in many ways.  For example, reports can be written to:

  1. Provide Data Compare analysis for data validation activities
  2. Track how many times an end user has exported data for a specific period
  3. Download the maps for a location to Excel
  4. List all the Journals posted by period and category
  5. List all the maps modification activity by date range
  6. List all the location and category and provides the status of each POV

Each of the report styles listed above has provided valuable information to both auditors as well as the administrators of the FDMEE application.   One of the most valuable reports is the one that permits quick data validations and reconciliations because it helps with COA conversions as well as upgrades to the EPM suite.  Here is a sample of a custom journal listing report:

FDMEE Reports 5

…and a custom FDMEE process monitor report:

FDMEE Reports 6

The Verdict

The possibilities and use of FDMEE for supplemental reporting is not limited to trial-balance analysis, trending, or variance reports. Reports are often created to provide additional valuable information for auditors, data workflow analysis, or external and downstream systems.  In many cases, they are used to provide additional and supplemental detail to IT or Financial auditors.  The verdict:  there is easy value added with variety and simplicity with FDMEE Reports.

Contact us at info@ranzal.com with questions about this product.

Using Hyperion Essbase to Report Comparable Store Sales

One of the commonly used measures in the retail industry is “comps” – comparisons of actual sales for this year versus last year.  The goal of reporting comparable store is to provide information on what portion of a company’s sales comes from increasing sales growth in existing stores versus opening new stores.   This metric is used to measure whether a company’s sales will continue to grow when store base reaches a saturation point, or the company slows expansion.

What are the considerations in defining comp store calculation?

  • Definition of comp store. In addition to having a store open for at least 1 year, it’s important to compare stores that have not changed significantly.  In this case, we are using square footage in the store to identify significant changes to a store.  In our example, if square footage changes by more than 25%, sales are no longer comparable to prior periods.  Also, if the status of a store changes (i.e. opening, closing, moving, temporarily closing), comp store sales are not comparable with prior periods.
  • Definition of applicable time periods. In this case, we used month to date, quarter to date, and year to date.  Each applicable time period is calculated monthly.  The applicable time period amount is calculated only for stores open during the applicable period.  For example, the June YTD amount for 2010 is only calculated for stores in existence from Jan 2009.
  • Calculation of comp sales. Most clients prefer to remove the effects of currency translation on this calculation.  In this case, only net sales are used for comp store analysis.

Implementation

The database outline for the comp sales database contains the following 10 dimensions:

  • An individual store is uniquely identified as a member in the stores dimension.
  • Comp store amounts are only calculated for the comp stores scenario.  Actual data is loaded to the comp store scenario.

Below are sections of the accounts dimensions used for the comp store calculation.

The comp store control stats are used to calculate the comp status counter, which is the first determinant of whether a store is a comp store.

The comp store metrics hierarchy stores the applicable comp store amounts in local currency and USD.  Local currency comp store metrics show amounts for current year and prior year for MTD, QTD, and YTD.  USD comp store metrics show amounts at a constant exchange rate.

Approach

There are 2 different calculations for the comp store process:

  • The calculation of the comp store sales counter determines whether a store qualifies for comp store status based on square footage and store status.
  • The calculation of comp store metrics is dependent on the calculation of the comp store sales counter.  The metrics calculation determines comp store amounts.

The key processes for the comp store sales counter calculation are as follows:

  • Calculate monthly square footage amounts.  Set beginning balance equal to prior December.  Accounts calculated are: square footage, store status, and comp store counter.
  • Calculate monthly square footage change percent.
  • Calculate ending store status and comp store status counter based on inputs for square footage and change type (open, close, move).  The comp store status counter is used to identify qualification for comparable periods.

The following is an example of how the comp store status counter logic would be applied to a store.  Note that the store comp counter is incremented monthly once a store is open, but a change in square feet of the store resets the counter.  This is to assure that sales from the 2000 square foot store are not compared with the 3000 square foot store.

After calculating the store comp counter, the key processes for the comp store sales metrics are as follows:

  • Copy actual (a rollup scenario including general ledger amounts and adjustments) to CompStoreAnalysis  (another scenario).  This allows reporting comp store results in a single scenario.
  • Create blocks for every year based on prior year gross sales.
  • Calculate net sales current year and net sales prior year in local currency for each appropriate time period, based on comp store status counter and the applicable comp time period (MTD, QTD, and YTD).
  • To be included in QTD comps, a store must have a store status counter of 13 and have been in existence since the beginning of the current quarter last year.  For YTD comps, the store must have been in existence since the beginning of last year.
  • Calculate comp store sales in USD using the prior year rate.
  • Aggregate comp store metric amounts in the comp store analysis scenario by stores, products, geographies, and legal organization.

Note in the sample store shown above, comparable net sales on a MTD basis would be calculated for December 2008.  Amounts would be calculated both in local currency and USD.  The USD accounts are for current and prior year would use the same rate (last year’s).