Pipeline tracking spreadsheet for staffing

How to create outlook signature

[Music] foreign let's say you want to create a visual representation of how your Salesforce sales pipeline changes over the course of a year in this tutorial we'll be using the coefficient app to import your Salesforce data into Google Sheets and create that visualization first let's import our Salesforce data using the coefficient app launch the coefficient app from the extensions menu on the top ribbon coefficient launch choose import from Salesforce from objects and Fields choose opportunity select your Fields I'll choose forecast category amount and close date done selecting Fields engage pivot mode I want a forecast category as my row and then I want closed date as my columns going across the pivot table Group by month with amount as my value and I'll change this to be sum I'll name my import click import now that I've imported my sales pipeline by forecast category let's start building the analysis on a separate tab I'll build a table that references my Salesforce import where forecast category is in column A and then my months go across the first row now I want to use index in conjunction with match to find the months dollar amount by forecast category make sure to add extra rows to your index range so that your formulas will automatically catch the new months as they're added to the import so here I'm selecting the existing range for the current Salesforce import but I'm selecting additional columns as the import expands and I am using index in conjunction with match to find the appropriate forecast category and then an additional match function to determine the column which will return the specific months dollar amount for each forecast category make sure to lock your column and row references as appropriate so I want to make sure that as I click and drag the index and match formula down and across that the column locking is correct and then I'm wrapping my entire index match formula in an if error to return a null value if the month and year does not exist so to copy this formula over I do need to make a change starting in February because I want to calculate the cumulative dollar amount across the year so that I can see how the pipeline changes as we go through the year so I'll add a plus here and add the previous month's dollar amount to my index match formula to create a cumulative value as we move across the year and now I want to add a Target line that will be the same across the entire year that will indicate to me how close I am to achieving my yearly sales Target now we are ready to insert a chart so go ahead and highlight the entire range and go to the top ribbon and go to insert chart now I'm going to make some changes to the Chart I'm going to change it to a combo chart so that I can showcase the sales pipeline Evolution as an area chart in conjunction with a line for my target now that I have my pipeline forecast categories as a area chart and my target as a line I'm going through each one of the forecast categories to change the area opacity to differentiate between the dollar amounts between each forecast category with my closed one forecast category being the heaviest area opacity so now I'm just going to make this graph a bit bigger and I'm going to show you how my pipeline Evolution graph changes over time as my Salesforce import grows with the number of funds that now I have in my data set and now as my Salesforce import grows throughout the year I can also see how my sales pipeline evolves throughout the year one last cool feature that I'm going to add to this chart is a dynamically changing chart label for my closed One deals let's go ahead and add another row for my closed one label this will dynamically change depending on the last month that I have in my Salesforce data set I'm going to use an if statement here I'm going to check to see if the current column that I'm under is actually the max month of my Salesforce data set if it is then I'm going to return that closed one dollar amount otherwise I'm going to return a null value make sure here to lock your column and row references as required and then click and drag that formula across the columns through the end of the year December happens to be the last month in the data set so you can see the label appearing correctly under December 2022. here now I'm going to add a special formatting parameter to my closed One deals label because I don't want the full seven or eight digit number appearing on the graph that will take up too much space so here I'm wrapping my label in a text formula and adding some special formatting to make sure the number is compressed and easy to read now we're ready to add my chart labels so I'm going to edit my chart and add the close one label to my closed one series now it's time to see this Dynamic label in action so you can see as my data set expands across the year how am I closed one label dynamically updates based on the dollar amount that I have for that month and in just a few minutes we have brought Salesforce data to life using the coefficient app and Google Sheets and Google charts if you're interested in downloading this sales pipeline Evolution template check out the link in the description below