Hiding in Plain Sight: The Strategic Advantage of Microsoft Tools in Organizational Change Management

Unleashing hidden potential:

For change management teams, the universally recognized Microsoft Excel contains remarkable potential that is often left untapped. This article aims to show how common tools can be elevated to sophisticated systems for change analytics and strategic decision-making.

The journey from straightforward spreadsheets to comprehensive analytics is currently achieved with macro-enabled templates in Excel, automated workflows in Power Automate, specialized SQL coding, and interactive dashboards in Power BI, all within the safe confines of your organization’s Microsoft environment.

Looking ahead, advancements like Copilot may streamline these processes further or remove the need for them completely. Until then, we hope this guide serves as a resource for those eager to harness these capabilities themselves now.

Data-Informed Change Management: A Practical Guide

This guide, while technical in nature, is designed to help change management teams to fully harness the power of their data using Microsoft tools. From initial data entry in Excel to creating dynamic visualizations in Power BI, we provide guidance and best practices.

A Step-by-Step Guide:

We've outlined a pragmatic approach, grounded in best practices and user-centric design, that uses a series of automated workflows for data transformation, storage and integrity alongside the intuitive visualizations of Power BI.

1. Building the Foundation with Excel:

Designing the Template:

o?? Create an Excel template with pre-defined logic and dropdowns to standardize data collection. Use data validation to enforce consistency. If we look at the Change Impact Assessment example again (See Edition #3) we would have the global stakeholder model hard coded in. Data validation and drop-down menus save change managers on the ground significant time in completing assessments. The Excel should have formulas that check the logic of the stakeholder list in the input stage.

o?? Ensure that the drop-down menus are context-sensitive and display only relevant data corresponding to the choices made within the Stakeholder Model. For example, when a 'Region' is selected, the 'Country' drop-down menu should dynamically update to list only the countries that are within the selected region.

A note on Data Management:?Excel struggles with version control, data integrity, and access management can be solved without significant technical investment by using SharePoint. This common solution offers centralized data storage, version history, and controlled access, streamlining collaboration without the complexity of full-scale system overhauls. This makes it an accessible and practical choice for many organizations.

Custom VBA-code:

o?? To create the best user-experience in the data input-stage, especially if, as in this example, the master stakeholder-list is large, we recommend using custom VBA. We use custom VBA code to populate cells with ie. “All Regions” . When a user selects “All regions” in the template, the Excel VBA automatically fills that cell with the possible Regions for that change.

2. Creating Workflows with Power Automate:

• Data Integration: Set up an automated flow to import, synchronize and store data from Excel in a datawarehouse (we use Dataverse, and store the data in Dataverse tables). This ensures that every time an Excel file is updated, the data in Dataverse reflects these changes. You can do this with automated triggers, or you can use scheduled flows to automate data imports at regular intervals.

• Notification Systems: Create notification flows that alert stakeholders when new data is available or when certain conditions are met (e.g., a project is behind schedule or over budget). Customize email templates within Power Automate to include key information and direct links to the updated reports.
• Data Integrity Checks: Implement flows that monitor data quality and integrity, checking for anomalies or outliers in the data set. Create a system to flag these issues and route them to the appropriate team member for review and correction.

3. Data Enrichment and formatting with SQL:

• Establish a Secure Connection to Dataverse: Use SQL Server Management Studio, or any other SQL software, to establish a secure connection to your Dataverse tables. Ensure proper credentials and permissions are set up to access the data while maintaining security and compliance standards.
• Write and Optimize SQL Queries: Develop SQL queries to extract, transform, and structure your data for analysis. Start with basic SELECT statements and progress to more complex queries using JOIN, WHERE, and aggregate functions like SUM() and AVG()w where needed. Optimize your SQL queries for performance by using best practices such as proper indexing and avoiding SELECT *. As this will slow down the reports when we come into Power BI and the analytics.
• Test and Refine Your Queries: Execute your queries within SQL Server Management Studio and review the results for accuracy. Refine your queries based on the results, and iterate this process until the output aligns perfectly with your reporting needs. Test and optimize your queries for efficiency and speed.

4. Visual Analytics with Power BI:

• Connect and Transform Data: Use Power BI Desktop to connect to the data in the Dataverse tables using your SQL queries created in the previous step. Utilize Power Query within Power BI to perform any additional data shaping required before loading it into your model. Use something called “Direct Query” in the data import stage. This will make your Power BI report automatically update if the underlying data in your tables change. This will allow your power BI to display new data in real-time. If Direct Query is enabled, most of the data must be formatted properly in the SQL stage, because Direct Query restricts the functionality in the Power Query editor in Power BI.
• Build Interactive Dashboards: Design reports with interactive visualizations that highlight key change management metrics and insights. Use slicers, filters, and drill-throughs to make your dashboards user-friendly, allowing stakeholders to explore the data from different angles.
• Publish and Share Reports: Publish your reports to the Power BI Service, configuring scheduled refreshes to ensure the data is always current (if you don’t have the Direct Query enabled). Share the reports with stakeholders through Power BI apps or by embedding them in SharePoint or Teams for easy access.

5. Continuous Improvement and Iteration:

• Feedback Loops: Establish feedback loops with users to gather insights on dashboard usability and relevance, continuously refining your visualizations.
• Adopting New Technologies: Look ahead to incorporating emerging technologies such as AI to improve the solution for change managers on the ground and enhance predictive analytics capabilities within your practice.

?

Each of these steps should be revisited regularly as part of a continuous improvement cycle. As you build and refine your change analytics platform, keep in mind that the most effective systems are those that can evolve with the changing needs of the organization.

By following this approach change management leaders can help their teams:

1. Enhance Decision-Making: Leverage precise, data-driven insights to make informed strategic decisions.
2. Increase Efficiency: Streamline processes and reduce manual effort through automation and integrated data flows.
3. Plan More Effectively: Ensure that change management is planned and provided in an integrated way based on shared data insights and a whole org view.
4. Cultivate a Data-Driven Culture: Foster an environment where data is at the heart of every change plan and strategy discussion.

?

Case Study: Tackling Change Fatigue with Microsoft Tools at a Major Pharmaceutical Company

Introduction: The head of change management joined a leading pharmaceutical company that faced a common challenge - change fatigue due to numerous uncoordinated major projects. To better understand and manage these impacts over the next year, the team planned to pilot analytical tools within their portfolio of transformation programs.

Implementation: Having reviewed third-party software options, the company opted instead to work with Issoria and within the Microsoft suite of tools. Initial challenges included the complex organization structure needing a unified stakeholder model, ensuring widespread staff adoption, and safeguarding sensitive data.

Features and Usage

1. Excel: Custom templates with built-in macros automate and standardize data collection, improving accuracy across different teams.
2. Power Automate: This tool automates the syncing of data in real time, boosting both the accuracy and the accessibility of crucial information.
3. Power BI: Dashboards visually display change impacts, facilitating better-informed decisions through interactive and accessible data presentations.

Pilot Outcomes

The pilot streamlined the change impact assessment process with a new dynamic dashboard and heatmap, making the evaluation of organizational change impacts more efficient and insightful.

Key Benefits

• Enhanced Visibility: Provides a comprehensive view of change impacts from individual roles to the entire organization, enhancing decision-making at all levels.
• Increased Efficiency: Automation significantly reduces manual tasks and errors, allowing teams to focus on more strategic initiatives.
• Tailored Insights: Data-driven insights are customized for key personas, enhancing the organization's ability to support and make informed decisions regarding its stakeholders.

Future Steps

Plans are in place to extend these tools to change teams within other global functions and explore expanding analytics to adoption and benefits realization.

Conclusion

This strategic adoption of Microsoft tools has supported the change management practice to level up planning, offering a low cost, scalable approach for other global change practices with similar ambitions.

Conclusion: Insights from Developing Enterprise Change Management Analytics

Our experience in developing Microsoft change analytics software for global corporations has taught us a lot. One major lesson has been the importance of simplifying the data input process, saving time and effort for change managers. By making their tasks easier and more efficient, you collect more data and ensure it is of the right quality.

Many of our clients have struggled to use off-the-shelf change analytics tools for various reasons that could be discussed in depth separately. Microsoft's tools offer a valuable alternative. These tools are straightforward to adopt, cost-effective, and highly adaptable, all while being securely managed within your company's own IT environment (if you so wish).

Leave a Comment: Have thoughts or questions? Leave a comment below. We’d love to hear your experiences and discuss how Microsoft tools can be tailored to your needs.

Schedule a Demo: Interested in seeing this in action? Schedule a personalized demo to see how you can optimize your change toolkit for actionable insights at scale.

#ChangeManagement #MicrosoftTools #Innovation #PowerBI #PowerAutomate #AI