https://community.ibm.com/community/user/apptio/discussion/ace-case-study-ppt-link#bm94b1fe77-aeeb-4f70-89f3-5893e1e2dfb9

Rebuild Financial Analysis Report by adding in extra metrics, logic and filters

Background:

Cost Transparency > Financial Analysis Reporting is widely used by entire organization frequently to manage cost vs budget vs forecast to make critical and agile business decisions.  

However, the “out of box” Financial Analysis Report do not meet all our requirement and as TBMO lead in this, I was asked to build some new models, metrics (in IT Planning, TBM Studio) and add filters and column pickers in this report/dashboard for all end users.

Result: Successfully implemented a new IT Financial Analysis Report in Cost Transparency, based on business requirements by adding over 10 features that are tested and well-received by management and used by all business units daily today.

There features include but not limited to:

• Create a new metrics “Total Spend” based on logic that OPEX + CAPEX= Total Spend in the metrics and reporting “Total Spend” as well as OPEX and CAPEX separately.
• Create a new metrics “Total Annual Forecast” based on logic that Actual Cost of past months + Forecast Cost of future months = Total Annual Forecast
• A filter on funding stream that requires the change of cost source model, so a project can switch between “funded” and “not funded”
• A filter to separate Cost from Revenue so user can choose to see Cost, Revenue or Both. And this is set up as global filter.

Procedure:

Phase 1: Conducting Interviews with key stakeholders and identify top features

Considering there are many stakeholders and opinions in this report, we followed the Agile development principles eg. 80/20 rule and Minimum Variable Product (MVP) to deliver the project.

We conducted a workshop and collected features that business would like to see and ask people to rate the importance, then we prioritized the top 5, then Top 10, then we ranked them on their difficulty level. When it comes to development, we will first focus on the high importance and low difficulty level ones to check, to give users some features while we keep on develop the other harder ones.

Below is an example of the matrix of the Top 5 features users would like to see.

Phase 2:  Building Metrics in TBM Studio. I will use funding stream as example in the case study.

Background: We plan our financials on a project-by-project level. Since we are government, not all projects are funded by the same funding stream and at the same time. We therefore need a dynamic funding stream filter in IT Financial Analysis reporting to change a project from “Not Funded yet” to “Funded” and this change needs to be captured live in reporting.

Technical solution: We add a mapping table called “Funding Stream Table”, then add a LookUp function to “Cost Source Pre-Master and Master Table”. The challenge is to make sure all actual, budget and forecast follow the same logic.

Challenge: We went on changing 3 tables, aka “eHealth Actual” Table, “ITPF Budget Transform” Table and “ITPF Forecast Transform” Table but the result doesn’t show what we want.

Solution:  We figured out all these 3 tables are Appended in “Cost Source Pre-Master Table”, so we only needed to do one final LookUp after the Append step instead of LookUp 3 times before the Append. Then we map “Funding Stream” to “Cost Source Pre-Master and Master Table”.

Phase 3:

UAT and Iteration

We build the Mock-Up first - after we ensure the logic is correct in TBM Studio, we checked in all the changed, then did a reconciliation on the numbers. During reconciliation, we identified some abnormalities, and then we did the internal trouble shooting within TBMO to make sure it all ties.

We asked for a small group meeting with key stakeholders to show case the build. Some of them pointed out certain change needed, so we went back on a few small iterations. Then we engaged a large group to do a proper demo. After receiving the good feedback, we checked in the final Financial Analysis report and send notification to the entire organization. We continue to send update monthly if any feature changed and held a couple of trainings. The feedback is report is intuitive and addressed the key financial numbers.

Below is the example of the Financial Analysis Report we built (removed the key numbers)

Key Learnings:

1. Through this project, we further deepened our understanding on the metrics, tables, reports in TBM Studio and how to change them.
2. We learned how to design report – using the global and local filter, column picker, adding tables, making certain selection as default landing page.
3. We learned how to introduce a new table and change the model in TBM Studio by mapping the Pre-Master and Master Cost Source table.
4. We learned the development process: how to collect information and avoid the noise, how to build and test the model, how to communicate with stakeholders and provide effective trainings etc.

 
@Sabeeha Kutty @Debbie Hagen @Zivile Girnyte@Amith Suresh@Zahid Arain
@Arun Kumar@Glenn Thompson@Laurent Alter@Zahid Arain@@Sambit Mohanty
@Sudevya Sharma@Reza Javidi@Reza Jogiastra@Ahmad Farman
@Mina Hornby