Connect R&D metrics from Microsoft Excel to strategy in Cascade

Bring your research and development (R&D) metrics closer to your strategy to help you drive better results with the R&D projects, and achieve new dimensions of your products and services with innovations.

Integrate Microsoft Excel with Cascade to bring visibility on your key metrics like project progress, on-time completion rate, R&D costs, R&D budget, time to market, and so on. With this integration, you can automatically update your metrics and measures in Cascade, and report accurate progress on your strategic objectives.

Eliminate the need to go back and forth between Cascade and one hundred spreadsheets. Keep your data up-to-date, accurate, and right where you need it. Save time spent on reporting and monitoring your strategy.

Being that one source of truth with centralized access to all company data, Cascade provides the ability to transform statistical information into actionable insights and a compelling narrative with effective data storytelling.

Prerequisites

Before you establish the connection, it is important that these prerequisites are met:

Cascade:

• A valid account and workspace in Cascade.

Microsoft Excel:

• A Microsoft 365 business account synced with OneDrive or SharePoint.

• A .xlsx spreadsheet with the dates and metric data.

Please note that you cannot pinpoint to a single cell, but rather to columns or rows, where you're updating progress.

Integrate Microsoft Excel with Cascade:

Step 1: Connect your Microsoft Excel with Cascade

It is important to establish a connection between Microsoft Excel and Cascade to start importing your metrics. This connection can then be used by all the other metrics in that workspace which have their progress in Microsoft Excel.

You can establish the connection in either of these ways. Click Connect in Microsoft Excel

• either from the Integrations > Metrics & measures tab,

• or, from the Metrics > Connect metrics tab.

Give the connection a name, and set it up as a private or public connection, and click Connect.

Follow Microsoft's instructions to connect your Microsoft account with Cascade. Now, select the folder or file from your OneDrive or SharePoint that you want the connection to access, and click Finish.

Step 2: Connect your metrics from Microsoft Excel to Cascade

Now that you've successfully established the connection between Microsoft Excel and Cascade, you can start importing the metrics by providing the spreadsheet details.

1. Choose the spreadsheet from your OneDrive or SharePoint by specifying the file and the sheet details.

2. Specify the spreadsheet layout by selecting the format in which your spreadsheet is set-up - either horizontal or vertical.

3. Choose the column in which your metrics are available, and select the metrics that you wish to import.

   1. Specify the unit for each metric. If you do not find the unit that you want from the list, then you can add your own custom units. Just type the text, click + Add "custom unit" as a new option, and it'll get added to the list.

4. Select the date format. Without dates, we'll not be able to read or import metrics. Only based on the dates, the values are imported and displayed.

5. Choose the format of data set-up and timeframe.

   1. Choose Sum of values if you wish to display the summed up value of the metrics, Avg of values if you wish to display the average of values of the metrics in the chosen time frame, or choose Last value if you wish to display just the latest value available in the spreadsheet for the metric.

   2. Select the time frame or frequency by which you want to display the metrics information. Note that, selecting a time frame that is smaller than what is in the spreadsheet will show the same value over and over (i.e., selecting "daily" when the data is set up "monthly", will display the same value every day until the month changes, and so on).

   3. Trend - depending on the trend that you choose, the color will change, i.e., if you choose "Higher is better" or "Lower is better", then when the metric value matches this criteria, the color will be green. If the metric value is opposite to the trend that's set, then the color will be red.

6. Click Connect metrics to finish the import.

The successfully imported metrics will now be displayed in the Metrics > Library tab. The sync happens every one hour, and you can click Refresh sync now to poll the data immediately. You can now connect these metrics to measures, and report accurate progress on your strategic objectives associated with these measures.

Refer Import your Metrics from Microsoft Excel article for detailed information on this import function.

Step 3: Connect the imported metrics to measures to sync the progress of metrics in Microsoft Excel to the relevant measures in Cascade

You can now connect these metrics to measures, and report accurate progress on your strategic objectives associated with these measures. You can either create a new measure to connect to the imported metrics or add the metrics to an existing measure in your workspace.

Hover the mouse over the metric tile, and click the measure icon, or from the three dots in the metric tile or metric Sidebar, click Create measure from metric.

To connect a new measure to a metric:

Before you begin, make sure you have the plan, focus area, and the relevant objective already created in the workspace.

1. Select the relevant plan, focus area, and the objective from the drop-down list.

2. Select New measure, and give it a title, set due dates, and assign ownership.

3. Enter the initial and target value to track the progress of a measure. This helps in the expected progress and health calculations. If you wish to customize the tracking option of the measure, toggle the Maintain button and choose the relevant option. Set the target limits and the critical limits to track and visualize the measure’s progress.

To connect an existing measure to a metric:

Before you begin, make sure you have the plan, focus area, objective, and the relevant measure already created in the workspace.

1. Select the relevant plan, focus area, and the objective from the drop-down list.

2. Select Existing measure, and choose the measure from the list.

3. Enter the initial and target value to track the progress of a measure. This helps in the expected progress and health calculations. If you wish to customize the tracking option of the measure, toggle the Maintain button and choose the relevant option. Set the target limits and the critical limits to track and visualize the measure’s progress.

You can also connect an existing measure to a metric in this way - Navigate to the relevant measure, and edit it from its Sidebar:

1. Under the Tracking section, select the Metrics library from the drop-down list.

2. In the Metric name field, choose the metrics from the list.

3. Enter the initial and target values, and if you wish to customize the tracking, toggle the Maintain button, and enter the target and critical limits.

Refer Connect your Imported Metrics to Measures article for detailed information.

You have now successfully enabled the tracking. All you need to do now is, just change the values in the sheets, and see the progress getting updated in Cascade. The sync happens every one hour, and you can click Refresh sync now to poll the data immediately.

Some tips:

• We recommend setting up connection as "private", unless you use shared drive accounts where everyone accesses the files or updates progress in which case you can set up the connection as "public".

• The cells in the date column or row in your spreadsheet must be set to "date format", i.e., choose the date column or row, right-click and select Number Format > Date. Also, if you've specified mm/dd/yyyy format, make sure you're entering the dates in the same format too.

• Though it is not mandatory to connect a metric to a measure, we recommend you to connect them so that the progress can be tracked alongside its strategic objectives. From the metric, you can understand its historical trend, and then connect it to a measure with deadlines and targets to track it more effectively. This measure can then be grouped with projects to improvise the metric's performance and be associated with a strategic objective or an outcome that you wish for.

• Choose the options under “maintain metric” when you need to customize the health of your measure.

Benefits

This integration provides you with all the elements for an effective “data storytelling”.

• Bring precise and up-to-date “data”: Track the progress precisely and on time with automatic sync between the spreadsheet and the measures in Cascade.

• Build engaging “visuals”: Visualize the progress with the various measure widgets and custom charts in the dashboards.

• Create compelling “narratives”: Build reports to make guided decisions based on the progress made in these measures and thereby the objectives in the plans.

FAQs

I have connected my measures to Microsoft Excel and tracking it already, but now this functionality seems interesting, how do I transition to this? Will this mean, I'll lose the data and progress that was made till date?

You can simply import the metrics using the same spreadsheet, and then hook the measures up to those metrics. And, from the tracking section, choose the tracking type as Metric library, and select the metric. The spreadsheet is the source of truth, so if you've the historical data there, it'll translate to the measure once connected.

I have metrics in different files, will that mean I need to do separate imports every time for all these files?

Yes, but the best way is to unify them in a single spreadsheet and import in one go.

I do not find my unit in the drop-down during import, what needs to be done?

You can add your own custom units. Just type the text, click + Add "custom unit" as a new option, and it'll get added to the list.

What happens if I change the Microsoft Password used in a public connection or how can I change/adapt the Cascade - Excel connection in case of a password update?

Navigate to Integrations > Metrics & measures > Microsoft Excel, and click Manage. It'll list all the connections with MS Excel in your workspace. From the three dots against your connection, click Refresh connection, and follow Microsoft Office's instructions to reconnect your Microsoft account with Cascade.

Will I see expected progress graph and numbers if I connect to metrics?

Yes, when metrics are hooked up to measures, they'll provide the historical and current values to track the measures. The initial and target values that you set while connecting a metric to a measure will define its expected progress.

Who can do this import workflow?

Anyone that has a Cascade account and workspace, and also a google drive account and a spreadsheet with metrics in it.

Why can’t I update the values of a metric from Cascade?

We consider the data coming from Microsoft Excel as the source of truth to avoid having misaligned information in the metric. This is a one-way connection: from Microsoft Excel to Cascade. Nothing you do in Cascade will change spreadsheet values.

Why aren’t my metrics being updated automatically?

Metrics bring in whatever is there in the spreadsheet. If you do not update or add anything there, then no new values will be added in the metric.

I've formulas in my spreadsheet cells, how is this handled?

The import fetches only the values from the spreadsheet however it is set-up. Be it a pivoted value, or obtained from formula does not make any difference as long as the value is valid.

What happens if the data is set up "weekly", but I choose "monthly" in the time frame on import?

This'll not display any error, however, it'll show the same value over and over every week until the month changes.

I chose the wrong unit for my metric while importing.

This'll just show the incorrect unit, but it won’t affect the actual number.

Can I use the same Excel file for updating multiple data?

Yes, you can set up the file with different data (ARR, Pipeline, Churn, NPS, etc.) in your sheets. If you have these information across sheets in the same file, you can collate them all in one sheet, and then import them in one go into Cascade. The imported metrics can then be connected with different measures for tracking each data. Once the data is updated in the sheets, it'll automatically update all the relevant metrics and associated measures.

I'm getting a "Connection does not work" error message.

Check if the connection is a private or shared connection. If a connection is private, then you might see this error. If it's a shared connection, and you still see this error, then the best option is to delete the connection and reconnect. Go to Integrations and use the trash icon to delete the connection. Follow the preceding instructions to set up a new connection.

I am a collaborator for the measure, can I download the spreadsheet from Cascade?

No, as mentioned, it is a one-way connection. You need to request the owner to give you shared permissions on the spreadsheet.

Can I connect the same metric to different measures?

Yes, that's the main advantage of using metrics. However, be careful when you connect the same metrics to different measures and not get confused by them.

What happens if I accidentally delete the data in the pinpointed column or  junk characters are displayed?

You must either see the last shown value or an error message. If either of this is not seen please Contact Support, and we will get it resolved for you.

I use the desktop application of Microsoft Excel. Will the data still sync?

It is important that you sync this file in your local to OneDrive or SharePoint, else this will not work.

My data is not showing up.

The column or row that you mention during import can have only numbers and related characters, like percent and currency signs. Check them to make sure there are no letters or special characters.

Please note:

• Cells for the date column/row need to have a date format in the spreadsheet (Number Format > Date).

• Make sure commas or full stops are used correctly, especially in currency, according to the spreadsheet format. Some countries use “,” for decimals and others “.”. Depending on the spreadsheet set up, this might cause wrong data coming in as it will consider it as “text” and not “currency”.  Cascade will detect the spreadsheet’s set-up and use that as a guide.

• Columns/rows selected for metrics will only bring in data that is in number format.