I have to admit it…as a SharePoint practitioner I’m often guilty of overthinking. It’s easy to get drawn into designing complex solutions while overlooking simple features that generate a lot of user interest. I think this is a predictable problem, though. As we gain expertise in understanding how a platform like SharePoint works, we often gravitate to looking at complex, highly detailed solutions rather than focusing on the quick wins using “simple” functionality.

This blog post is all about the quick win, though. If you’ve seen me present at conferences or SharePoint user groups before, you know I’m passionate about documenting ROI for SharePoint projects. I follow up and formalize the business value of all my implementations (see my previous blog post I need some ROI…but I have no idea where to begin! for details). But I use ROI as more than just a means to summarize the value of projects I’ve completed. I start capturing ROI data during requirements gathering and rely on ROI estimates to prioritize my SharePoint project queue. This blog post walks through the simple SharePoint solution I built to use ROI estimates to auto-prioritize my projects.

First, let’s look at the completed solution. In the screen shot below, you can see a “SharePoint Requests” web part that displays my SharePoint project queue. Notice the Priority column. This is a calculated column that automatically assigns a low/medium/high priority to each project based on an automated ROI calculation. I use this priority rating to manage my queue of projects and determine my build/deploy schedule. 

Here are the steps to build out this project prioritizer:

1. Create a new custom list in SharePoint.
2. Add in the fields you need to track your Sharepoint requests. My list contains the following fields:
   • Project name – text field
   • Requestor(s) – person/group field
   • Request type – choice field
   • Team – text field
   • Request date – date field
   • Status – choice field
   • Collaboration owner – person/group field
   • Estimated ROI per year – currency field
   • Estimated hours to implement – number field
   • Start date – date field
   • Due date – date field
   • High-level goals – multiple lines of text field
   • Detailed requirements – multiple lines of text field

   NOTE: The Estimated ROI per year and the Estimated hours to implement fields must be set up in your new list. They will be essential for the priority calculation.

3. Now that your base list is ready, you can set up your automated priority calculation. Go into your List Settings and create a new Calculated column named Priority (raw). Copy and paste the following text into the column’s Formula field:
   

   =IF(ISERROR([Estimated ROI per year]/[Estimated hours to implement]),”-“,[Estimated ROI per year]/[Estimated hours to implement])

   Specify that the data type returned by this formula be a number and press OK to save the settings for this new field.

4.  Now you have an automatic calculation for your project priorities. But as you start entering data into your list, you’ll see that these numeric results aren’t terribly helpful. Depending on the values you enter into the Estimated ROI per year and the Estimated hours to implementfields, you may end up with raw priority numbers like 6,250 or 71.428571428571.

   I’m no math whiz, but I know I can’t make sense of these numbers. In fact, their only inherent value is their position relative to each other. Fortunately, we’re not done yet! There’s still one more calculated column to set up…

5. Go into List Settings and create a second Calculated column named Priority. Copy and paste the following text into the column’s Formula field:
   

   =IF([Priority (raw)]=”-“,”-“,(IF([Priority (raw)]<650,(IF([Priority (raw)]<300,”Low”,”Medium”)),”High”)))

   Specify the data type returned by this formula to be a Single line of text and press OK to save the settings for this new field.

6. View your results. As you enter new projects and specify their estimated annual ROI and estimated hours to implement, you will see a raw numerical priority value and a subsequent priority ranking of Low, Medium or High.

If you like, you can even change the default numerical thresholds that translate your Priority (raw) values into Low, Medium or High rankings. Just tweak the “650” and “300” numerals in the Priority field’s formula to alter the default thresholds and find the specific differentiators that will best meet your needs.

Next steps I’ve been meaning to try include color-coding my prioritizations and building a fancier dashboard. But that’s for a later blog post 🙂