This article provides a few basic tips and tricks for getting starting using Diligent data cubes in Excel.
This article contains the following topics:
How to Access a Cube:
The cubes can be accessed/modified using various approaches.
Modifying an Existing Cube:
Typically, upon kicking off a project your will receive a preliminary databook from your Blue Ops representatives. This file will have a number of cubes that have been formatted and built out to fit the need state of the project and team. Upon opening the file you should enable editing and enable content, at which point you will be prompted to login using your Diligent credentials. If you have not yet set up a Diligent account, please reach out to your Blue Ops representative to set this up for you.NOTE: If you are not prompted with the login screen or are experiencing other issues upon initial access of the cube, links to additional troubleshooting resources have been included at the bottom of the article.
If you find a schedule with formatting and/or fields similar to what you are trying to create you can simply make a copy of the tab and begin modifying the pivot table field list to fit your desired objective. If you are trying to drill-down into a line item without impacting existing formatting or presentation this is also a helpful method.
Hypothetical Use Case: Lets say there is a data cut that shows revenue and margin by channel and you would like to create an additional schedule to show top customers or products within that channel as of a certain time period. To do so I could make a copy of the tab and then underneath channel I would add customer or product and sort/filter to the desired groupings.
Starting with a Blank Cube (Not Formatted):
- Option A: Insert a blank cube into a pre-existing Diligent/Blue Ops file
- Navigate to the EBM office bridge ribbon > Insert cube(s)
- Upon doing so may be prompted to select from a list of cube options, depending on what/how many sources of data are available in your Diligent project instance.
- The two most common are
- Profitability: This is utilized for invoice level customer and product/service data
- Financial: This is utilized for trial balance detail, summarized up to the various financial statement line items (think Financial Statements - Balance Sheet or Income Statement)
- If you are unsure what to select, you can always open up a file received from your Blue Ops Representatives, navigate to the data ribbon > queries & connections > connections and check what query was utilized to build out the databook or other relevant analysis.
- Once selected - this will insert a new tab with a blank, unformatted cube that is ready to be modified
- Option B: Download a blank cube via the Diligent User Interface
- Navigate to the diligent website using the link provided by your Blue Ops representatives.
- Select your project in the top left (if you have access to multiple projects)
- Within the analyze section - select the cube you are looking to download (defined above).
- This will download a blank, unformatted cube that is connected to your project instance
Getting Started: Essential Fields
To get started using a Diligent data cube there are 3 key attributes that will need to be defined in every schedule:
1. AMOUNT: Drag Amount to Values - this will summarize all numeric values available.
2. ACCOUNT: Navigate to the Profitability Account section within your pivot table field list. By selecting an account, this will give context to your numbers. e.g., Revenue, Cost of Goods Sold, Gross Profit, etc.
3. TIME PERIOD: The next step is to define the time period. Time periods are defined using scenarios in Diligent. A scenario is typically a 12-month block of time (actuals, TTM, pro-forma, etc.) but may also be a partial block of time as well for year-to-date actuals. Every schedule will need to have a time period defined. This can be done by dropping the 'Scenario' or 'Scenario Short' field into your filters, columns or rows and selecting the time periods for which you want to measure the above variable(s). The only difference between 'Scenario' and 'Scenario Short' is that 'Scenario Short' is utilized to manage column size. (e.g. showing '2020A' instead of '2020 Actuals').
Understanding of common Diligent terminology can be found here
Sample Data Cuts: Step-by-Step Instructions
Below are examples of basic data cuts that may be relevant to your project with step-by-step instructions on how to build out:
Relevant for each example below:
1. Drop in Amount (will result in an undefined, meaningless number to start)
2. Define your time period - drop 'scenario short' into columns, rows or filters. For the examples below are are going to put scenario short into our columns. You can also right click on scenarios you do not wish to see in your table (e.g. current year YTD actuals), select filter > hide selected.
3. Define your account(s) - Within the examples below we are going to be focused on revenue (sales) and/or Gross Profit. Typically an account will either go in the filter section or columns. In this example the profitability account field is filtered to 'Sales' and put in the columns.
**At this time, remove grand totals and sub totals as appropriate and format to preference. Pivot table formatting can be modified by right clicking anywhere within the table and selecting pivot table options OR through the pivot table design ribbon.
CHECK: Once you have defined the 3 attributes above, your table should look something like this:
Sample Data Cut - Time Series Revenue Trends by Channel
To show revenue by channel or end market you would next add the relevant field (from the pivot table field list) into your rows. Note: as you can see below, the default sorting for the table will be alphabetical. Often times, you may want this to be sorted by highest to lowest revenue on the most recent period which you can do by right clicking on one of the data values in the year you wish to sort, Sort > Largest to smallest.
Default Sorting: Alphabetical
Resorted: Revenue Largest to Smallest in TTM period
Sample Data Cut - Time Series Revenue & Margin Trends by Channel
Lets say now you want to add both revenue and gross profit or gross margin (%) to your view. To do so, modify the account filter to include gross profit and then manually calculate gross margin utilizing the two fields.
Note: Pivot tables do have some limitations where it may be more efficient for formatting and presentation purposes to add in a manual calculation alongside the cube output. This is typically a one time set-up in your schedule and as long as its driven off of the live cube data, the calculations will refresh alongside any cube refreshes.
Sample Data Cut - Calculating a YoY, QoQ or MoM % Change
To calculate a YoY, QoQ, or MoM change within the pivot table, simply drop a second 'Amount' in the values section. Then right click on the field in the pivot table field list > value field settings and the below window should pop up. In the show values window select the calculation to be performed. Once this screen is up, you can rename the field (as shown within the 'custom name' section) and you will need to select the related field for which you want the calculation to be performed on. In this case we want to compare the current year to the prior year so we would select our scenario (short description) as our base and then 'previous' as our base item.
A shortened version of this window can also be found by right clicking on a value in the Amount2 section of the pivot table > show values as > % difference from.
Sample Data Cut - Calculating revenue concentration
Using the same concept as the example above, select show values as '% of Parent Row Total' and now your data table will show the channel revenue mix.
Sample Data Cut - How to drill down into revenue trends to understand drivers
In the example above, you can see that revenue for all channels drops off from 2019 to the TTM period. Using the 'Retail' channel as an example (i.e. now filtered to only retail), we will walk through how to drill down and understand key drivers. The important thing to note here is that the data cube is a dynamic, relational database. That means additional fields can be dropped under channel to achieve more granularity. First, we may look to see if we've lost any significant customers. This can be achieved by dropping 'parent customer' or 'customer' beneath channel and sorting the YoY Change % from low to high. As you can see below - there are a group of customers that are either dormant or lost in the TTM period that are driving a portion of this revenue decline.
It also appears that some of the existing larger customers (e.g. Parent Customer 161) appear to have YoY revenue declines as well. If you wanted to deep dive into the product level purchasing habits of Parent Customer 161, right click > filter > keep only selected on this customer and now drop product in rows. In this view, we can now see that they have significantly reduced purchases of items 103 & 370.
Sample Data Cut - Top 20 products or customers
When wanting to show top customer or top product schedules this can be achieved in the cube in a number of ways. One way is to work with your Blue Ops representatives to add a hierarchy grouping that layers over customers (designating a customer as a top customer or all other). The second way is to use filtering functionality directly in the cube as outlined below.
Comments
0 comments
Article is closed for comments.