Partager via

What-If Analysis in CRM Online with Excel Online (Immersive Excel)

  • Article

Microsoft Excel is the king of business intelligence (BI) tools. Millions of Excel users use it for reporting and analysis of corporate data. Users of CRM migrate data to Excel just to perform analyses or entity management. Examples include pipeline analysis, pipeline management, territory planning, leads filtering and generation, etc. With CRM Online 2015 Update 1 we are enriching our strong connection between CRM and Excel with an immersive experience

A new view for Excel Online is introduced in CRM Online – Embedded View. This gives you a great opportunity to seamlessly integrate CRM data and scenarios with Excel on the web. You can take along relevant and contextual information to do your tasks with the benefits of Excel features and visualization and perform quick analysis such as what-if, filter, search, contextual to CRM information and UI.

In the example below I'll take a look at my Open Opportunities - to see how the sum of weighted estimated revenue looks, and how it will look if I up some of the numbers

I open my Open Opportunities


   Fig1.: My Opportunities View

I click Export to Excel and then Open in Excel Online


    Fig 2.: Export the records to Excel Online

The records open in Excel Online right within CRM Online


    Fig 3.: Records now opened in Excel Online inside CRM Online (Immersive)

I add a new columns (call it "WhatIf") and add a formula multiplying the Estimated Revenue and Probability


    Fig 4.: Add formula to calc weighted estimated revenue (probability times estimated revenue)

I expand the formula to the cells below using Excels drag and drop feature


    Fig 5.: Add formula to all rows using Excel drag and drop

I add a SUM formula via the ribbon


    Fig 6.: Add formula to sum all rows using Insert tab / Function dialog

I'm now able to see the weighted estimated revenue.


    Fig 7.: Sum of all rows

Lets assume I'm interested to see a higher figure there - so I'm now applying a few changes. I think I'm able to upsell the last opportunity a bit - so I raise the estimated revenue from 25k to 35k (1)


    Fig 8.: Increasing Estimated Revenue of last opportunity to 35k

 

Further, I think the probability for the third opportunity is set a bit to low, so I raise it from 50% to 60% (1). I now see that my weighted estimated revenue has gone up to an acceptable figure (2). Whats left is to commit the changes back to CRM Online.


    Fig 9.: Increasing Probability of third opportunity to 60% - now my weighted sum is acceptable

Before I commit the changes back I need to erase the column I created for my what-if analysis


    Fig 10.: Clearing WhatIf calculations before committing changes back

To save the changes back I click Save Changes to CRM


    Fig 11.: Saving changes to records back to CRM

A dialog pops up telling me that the data has been submitted for import, and I can click the link to see the progress of the import job


    Fig 12.: Import dialog displayed

Data import is progressing


    Fig 13.: Parsing

Data import is completed


    Fig 14.: Completed

And I can verify that the changes has been commited


    Fig 15: Changes committed

I hope this new feature will be helpful to you.

Comments

  • Anonymous
    August 01, 2015
    Immersive Excel Experience! CRM Online (7.1) and Excel Online
  • Anonymous
    May 31, 2017
    Is there any way to embed these formulas into excel online feature prior to exporting so you don't have to keep adding them every time? I tried doing this via an excel template and uploading in CRM. The template allows you to save formulas and export to excel online, but does not allow you to save the changes back to CRM after adding your new ros. Unless I'm missing something this looks like a limitation to the system.