Objective

In Apptio R12, create a report that computes the specific amounts by your key Cost Source Accounts (i.e. Account and IT Cost Center) that are over/under allocating to the end point/s of your model. In our case, these model end points are Applications and Business Cost Centers.

By generating this report we are able to better pinpoint and troubleshoot our model at the Cost Source Level from both monthly and full-year perspectives. Since it enables you to directly compare amounts by key accounts from the Cost Source against those allocated to the End Point object/s, this report shows the proportion of the Cost Source amounts that are being over/under allocated at-a-glance within a single view. This will help you troubleshoot dollars falling out between your Cost Source and your selected model endpoint.

In a nutshell, this report will answer the following question:

"By how much am I over-allocating/under-allocating from the Cost Source to the Model End Point Object/s?"

Example:

Background

The need to generate this computation stemmed from a request from our organization's Finance Department who will be consuming the data output of our Cost Transparency Model for their own analysis. Per their request, our team will be providing them with the following Data Sets: 

1) Amounts Allocated to Applications by Account and IT Cost Center (X%)

2) Amounts Allocated to Business Cost Centers by Account and IT Cost Center (Y%)

3) Amounts Over/Under Allocated by Account and IT Cost Center (Z%)

While fallout can already be identified through different views within the TBM Studio (e.g. Model Configuration Wizard; Custom Sankey Diagram Model Reports etc.), these are limited to monthly time periods, data, and amounts. Thus, I came up with the methodology detailed in this blog to compute the Over/Under Allocated Amounts across a longer time frame.
In my opinion, it's easier to conduct fall-out analysis at an aggregate level than month-to-month.

Initially, this was something I used to compute using MS Excel (c/o PowerQuery) at an ad-hoc basis, but I later realized that the data transformation processes can also be translated into Apptio R12's Data and Report Studios. Having the report computed within Apptio R12 rather than MS Excel eases & speeds up the report preparation process and also encourages usage.  (Finance Department Users be like: "wow - a self-checking system!")

Required Data

The data required to generate this report table is as follows:

Report Table Name	Description
A. Cost Source Table (100%)	·         This is the base of your Finance Layer and source of your allocated amounts. ·         Report Table should contain the 100% amount you're attempting to allocate. ·         Must be at the level of detail of the specific key accounts you're trying to compare against (e.g. Account and IT Cost Center). ·         Contains the following fields and dimensions: o    Account (from Cost Source) o    IT Cost Center (from Cost Source) o    Values = Cost Metric (from Metrics) o    Columns = Months (Yr) (from Time) o    Source = "01 - Cost Source" Example: Best if identifier rows are published to "Show All Rows in Time Based Query"
B. Applications to Cost Source Drill-down (X%)	·         This is an output of the Cost Transparency Model. ·         Report Table locked to Applications and Cost Source (2-Object Drill) ·         Contains the following fields and dimensions: o    Application Name (from Applications) o    Account (from Cost Source) o    IT Cost Center (from Cost Source) o    Values = Cost Metric (from Metrics) o    Columns = Months (Yr) (from Time) o    Source = "02 - Applications" Example: Best if identifier rows are published to "Show All Rows in Time Based Query"
C. Business Cost Centers to Cost Source Drill-down (Y%)	·         This is an output of the Cost Transparency Model. ·         Report Table locked to Business Cost Centers (Custom Object) and Cost Source (2-Object Drill) ·         Contains the following fields and dimensions: o    Business Cost Center (from Business Cost Centers (Custom Object)) o    Account (from Cost Source) o    IT Cost Center (from Cost Source) o    Values = Cost Metric (from Metrics) o    Columns = Months (Yr) (from Time) o    Source = "03 - Business Cost Centers" Example: Best if identifier rows are published to "Show All Rows in Time Based Query"
D. Allocations Merge - Column Headers File	·         Column Headers that will align the data from the three tables above. ·         Given the examples above, recommended fields are as follows: o    Account (Label) o    IT Cost Center (Label) o    Source (Label) o    Target Cost Object (Label - Optional) o    Month Columns (i.e. 01 - Jan, 02 - Feb, 03 - Mar etc.) (Numeric) o    Total Column (Numeric) Example:

Configuration Steps

Step 1: Create the Merged Data Table ("Allocations Merge")

Assuming that the required data is already available, all you need to do is merge Report Tables A, B, and C - aligning their corresponding columns under table D.

In our case - as not to impact the already lengthy calculation time of our Cost Transparency Project - we created a new Custom Project called "Unallocated Report". Using DataLink, we use the copy table function to send Report Tables A, B, and C into the data studio, where we append these three tables to Report Table D (Allocations Merge - Column Headers).

Here's a sample layout of this table after the tables have been appended:

Example - Allocations Merge Table

Once your table is prepared, remember to add the model step and assign an Object Identifier to your table, so that you can create custom reports from it in the Report Studio.

Step 2: Create Formula Metrics to Enable and Simplify Calculating & Reporting

The following metrics are helpful for creating your Unallocated Computation Table - for calculations and for easy referencing when creating other custom views. Assuming you've used the same names used in the examples, sample metric formulas for the month of January are as follows:

Example - Monthly Formula Metrics:

• 01 Cost Source Amount Jan =if(Allocations Merge.Source="01 - Cost Source",{Allocations Merge.01 - Jan},0)
• 01 Applications Amount Jan =if(Allocations Merge.Source="02 - Applications",{Allocations Merge.01 - Jan},0)
• 01 Business Cost Centers Jan =if(Allocations Merge.Source="03 - Business Cost Centers",{Allocations Merge.01 - Jan},0)

Tip: Using numbers in naming your formula metrics ease the process of typing them in once it's time to create your report table in later steps (e.g. 01 - Jan; 02 - Feb etc.)

Create these metrics for all months (including the Total column, if you wish). This might be the most tedious step here, but if you type the first three formulas in MS Word - you can simply keep using Find & Replace on the month value in the formula (i.e. 01 - Jan) to speed up generating the formulas for the succeeding months (i.e. replace it with 02 - Feb etc.)

Step 3: Create the Report Table in Report Studio

On your report studio, create a report with a report table locked to Allocations Merge.

For the Rows, use your key account fields from Allocations Merge (i.e. Account and IT Cost Center).

For the Values, create formula columns and reference the metrics you've created in Step 2.

For instance, if you want to show the amounts for Jan and the Total, see the examples below:

Example - Jan Amounts (Formula Columns):

• Jan Cost Source Amount =01 Cost Source Amount Jan
• Jan Applications Amount =01 Applications Amount Jan
• Jan Business Cost Center Amount =01 Business Cost Centers Jan
• Jan Unallocated Amount =01 Cost Source Amount Jan - (01 Applications Amount Jan+01 Business Cost Centers Jan)

Example - Total Amounts (Formula Columns):

• Total Cost Source Amount =13 Cost Source Amount Total
• Total Applications Amount =13Applications Amount Total
• Total Business Cost Center Amount =13Business Cost Centers Total
• Total Unallocated Amount =13 Cost Source Amount Total - (13 Applications Amount Total+13 Business Cost Centers Total)

Cost Source Amounts (100%) less [Applications Amounts (X%) + Business Cost Center Amounts (Y%)] = Unallocated Amount (Z%)

***BONUS*** Example - Other Potential Report Layouts:

• Adding a few more data points to the Allocations Merge table, we were also able to use a Tree Diagram Table Viewto group the data into high-level views and switch them around in different perspectives with QuickPivot (e.g. by IT Resource Tower, by Cost Pool, and other categories/sub-categories)

Note: this layout was made possible by including another layer of data from our Cost Source and using metadata tables to group the data

Conclusion

The approach illustrated above is my attempt to compute the over/under allocated amounts from our Cost Source.

Since the target audience of our Unallocated Report are users from the Finance Department, we figured that the reports we're providing them should be at the level of our key accounts (i.e. Account and IT Cost Center). After all, these accounts are what give a shared perspective between the IT Finance Team (us) & the Finance Department users (them).

Beyond fulfilling our Finance Department's reporting needs, I believe that other insights can also be derived from this report. For example, this can be beneficial to the Technical TBMA in identifying allocation gaps and improvement areas within the Cost Transparency model from the Cost Source Level at both Monthly and Annual Perspectives. Being able to present your model's allocated amounts and computing its limitations (i.e. in terms of over/under allocated amounts) can help boost confidence in your Cost Transparency Project.

As Peter Drucker once said, "you can't manage what you don't measure."

I hope you find this insightful or even useful. Good luck to every one in reaching that 100%!

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

NOTES:

• Accounts and Amounts shown in examples are samples and dummy data only, shown for illustrative purposes
• The computed Unallocated Amount (Z%) represents Cost Source Amounts that are falling-out within your model, between the Cost Source until the end point object
  • Unallocated Amount < 0 = Over allocation of Positive Amounts OR Under allocation of Negative Amounts (if applicable)
  • Unallocated Amount > 0 = Under allocation of Positive Amounts OR Over allocation of Negative Amounts (if applicable)
  • Unallocated Amount = 0 = Fully Allocated (Congratulations!)
• The "inspiration" for this method is MS Excel's Pivot Table functionality to group rows together, while segregating its aggregated amounts by columns (i.e. "Source" column) - the same data transform concept is being used in this methodology, but with Formula Columns & Metrics instead
  • Think of it as creating a pivot table using the "Allocations Merge Table", then using the "Source" column to split its numeric columns into different columns - and afterwards, using a formula in the adjacent cells to compute the over/under allocated amounts
  • g. =Sum of 01 - Cost Source -(Sum of 02 - Applications + Sum of 03 - Business Cost Centers)
• Our model is designed with data-driven "allocation paths" in mind. We have an "Application-Infra Path" and a "Business Cost Centers Support Path"
  • Application-Infra Path allocates towards the Applications Object and is for computing Application TCO (Direct App Spend + Indirect Infra)
  • Business Cost Centers Support Path allocates towards the Business Cost Centers Object is for computing non-app-related Business Support Costs (e.g. Desktop Equipment; Mobile Devices etc.). The Y% data referred to in this blog is from a custom staging object that helps us segregate the amounts that should go directly to Business Units (i.e. non-application related amounts)
  • As our data improves, the allocated amounts from both paths will eventually further roll-upinto either the standard "Business Services" object OR directly into "Business Unit Allocations" object.
    • At the end of the day, Business Unit Allocationswill serve as the true single end-point of our model
  • I will be glad to answer any questions regarding the particulars of creating this report in the comments section or via Private Message

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