I’m a project manager who disdains repetitive, manual processes. If there’s a way to do something more efficiently, I’m going to find it. That drive hardly makes me unique in my field. Global staffing firm Robert Half says a project manager “focuses on completing a project as efficiently and promptly as possible.” Many of us have developer backgrounds. So, if you give us the tools and the freedom, there’s a good chance we’re going to automate everything that needs automating.
A project manager’s automation objective is to spend more time absorbing info than amassing it. We want to focus on the client, understanding their issues and finding ways to resolve them. It would be great to have a team of people collecting data to feed our analysis efforts, but if that’s just not in the cards (or the budget), automation enables us to create digital data collectors.
I joined Mind Over Machines in March 2020. Yep, right before lockdown. Now that I’ve got my feet under me and learned the organizational ropes, I’m building automations that increase our efficiency and capacity. I’m drawing on old skills and learning new ones. In this post, I share my recent automation adventures and offer tips to aid you in yours.
“It would be great to have a team of people collecting data to feed our analysis efforts, but if that’s just not in the cards (or the budget), automation enables us to create digital data collectors.”
A Template for Every Spreadsheet
Lately, we’ve been working to build up our suite of project management tools. Excel spreadsheets are still a go-to workhorse for tracking project progress and budgets, but you can lose a lot of time to formatting and fiddling with data. If you are hunting for repetitive, manual, relatively mindless work to automate, look no further than average users interacting with their favorite spreadsheets. The best way to combat this particular timesuck is the OG of old-school automation tools: macros. I use Visual Basic for Applications (VBA) to build and automate all kinds of formulas on steroids. Macros go way beyond summing values to referencing other worksheets, accessing databases, and even inserting if-then logic.
It can be a challenge to create spreadsheet templates that are flexible enough to accommodate widely varying projects (Will it last 3 weeks or 30? Are there 2 resources billing or 22?) yet maintain their functional integrity. But investing in solid template design is far better than requiring PMs to create all new assets for each project that comes along. My top tips? Avoid tables; they are notoriously rigid. Always use relative references. And make your templates intuitive for everyone by creating clearly labeled buttons that run your custom macros.
I’m also a big fan of building in stoplight status indicators. What’s more obvious than green, yellow and red color-coding propagating to various forms to show how close you’re getting to deadlines and target spends? These are the easily automated notifications Frank Shin mentioned in his recent post.
Sidenote: Well-built macros can save your whole team time, but you can use them to automate personal workflows too. As the new guy, I just couldn’t acclimate to the quirks of our internal time-tracking software, so I wrote a macro to deal with it. Automating your way around legacy weirdness is a great sanity-saver!
Bots to Track Budgets
At Mind Over Machines, we make a point to practice what we preach. So, earlier this year, I took a deep dive into how robotic process automation (RPA) might benefit my project management. I started by taking advantage of the courses offered by UiPath Academy. If you have a basic programming background and a handful of hours, you can become very productive with UiPath’s platforms.
Thus far, I’ve built two bots to help me track project budgets. The first pulls consultants’ hours worked out of that infamous time-tracking system and into my project spreadsheets so my macros can manipulate the data. The second bot tallies the hours our MINDs spend helping clients’ automation Centers of Excellence achieve self-sufficiency. This regular reporting enables clients to track their evolving needs and buy only the hours they will use.
The swivel chair task of finding and copying data out of one system and pasting it into another used to take me minutes each day. If a consultant updated or corrected their hours, I’d have to go back in and capture the new information manually. Now, I use UiPath Orchestrator to manage my digital assistants from my desktop (or mobile device). What used to take a bunch of pointing, clicking, and keyboard shortcuts in various apps now takes 2 clicks.
UiPath has a robust selection of libraries you can pull into your automations to access a variety of resources, tools and applications. Like Visual Studio Code’s IntelliSense, once you have a library, it will walk you through your build, pulling you along step-by-step. You reference an object, the system tells you, “Oh, that object has these methods.” You choose the method you want, and it prompts you to set the parameters that particular method requires.
Automate to Expand Organizational Capacity
Once you’ve built up your automation toolbox, you can use all or none of the tools at your disposal to start working smarter. As a relative newcomer, I’m currently at the sweet spot where my project manager efficiency instincts blend with fresh eyes on the operation to find opportunities for growth and improvement.
I recently built a set of spreadsheets to power our new ballpark estimator. Prospective clients answer roughly a dozen questions about their proposed project. Behind the scenes, the spreadsheets crunch that data and spit out a project cost range. No macros, no bots needed, but this still qualifies as automation. It is something humans used to have to do, scheduling time to meet and trade information, albeit at a preliminary, cursory level. Now, those humans can focus on more fleshed-out proposals further along the pipeline.
At the other end of the spectrum, I created a simple automation combining all the tools discussed above. After 30+ years of business, Mind Over Machines has built up a massive CRM. We’ve been cleaning it up a little lately and found 1,400 woefully incomplete records, like company name with one other scrap of info. I built a bot to run Google searches on the company name and then hand off the retrieved addresses, phone numbers and URLs to macros that put the info in the correct fields within our CRM. I set it up to run overnight, and it updated 800 records while we slept.
Tips for Getting Started with Automation
We project managers have always been powerhouses of optimization. Depending on your background, you may be ready to jump right into automating or need some extra training/practice “thinking like a programmer.” In either case, here are 3 tips to get you going:
Start Simple:
A small macro, a basic bot. Don’t bite off more than you can chew because small wins are essential to building digital confidence. As you hone your skills, you will start to feel more comfortable. And this citizen developer trend is still new enough that you’re bound to catch the attention of your organization, pulling in more people and resources to aid your cause.
Start Static:
If you’re new to automation, you don’t want to be poking around in dynamic systems that are updating constantly. Look at your workload and select a process that requires consistent manual effort, but is stable and predictable.
First, Look for an API:
The goal of automation is often to access and share data between disparate systems. If the application you want to connect with offers an API, that is usually the easier, more dependable way to pull data. RPA is essentially screen scraping, which is notoriously brittle. You program your bot to grab data from certain places. Whenever a UX designer rearranges the page, your bot breaks. APIs change far less frequently than user interfaces.
You know which aspects of your operations are inefficient. Automation is efficiency, and the tools to do it are getting more useful and user-friendly all the time. Combine the productivity hacks you have been using for years with today’s new automation methods to supercharge your efficiency efforts.
About Stuart
Stuart Keyes’ Baltimore roots run deep, though he was born in New Mexico where his engineer dad was building and testing missiles for the U.S. military. When the family returned to Maryland, Stuart spent a lot of his childhood in art studios with his painter mom. He enjoyed the artistic life, but could never quite figure out how to make a living at it. So, he earned a master’s in computer science, blending creativity and technology to become a software engineer.
Over a 20+-year tenure at UPS, Stuart worked on the teams that built ups.com and ORION, the driver-routing system that saves the company hundreds of millions every year and is still going strong. Managing application development segued naturally into project management, but in retrospect, process automation was at the heart of just about everything he did at UPS. “At this point, automation is in my blood.”
Stuart and his wife celebrate raising two well-adjusted and productive members of society by spending as much time as possible on their sailboat and cooking recreationally. In these pandemic times, rolling sushi and upping their French culinary game have taken the place of dining out.
