Robotic process automation, or RPA, can save organizations considerable time and money. Where appropriate, RPA can complete manual processes much faster and more accurately than a human. With many low-cost RPA tools available to accountants, RPA is also incredibly cost-efficient, especially in areas where the benefit of a professional thirdparty solution may not justify the cost.
However, learning RPA can be tricky. To get started, one useful way to think of RPA is in terms of building blocks. Your first RPA bot or process may seem simple and not too useful. However, think of what you learned from making the bot. What baby steps did you notice that you could automate? How could you use those in different situations? What is the next building block you need to solve your next problem? It is critical to keep this mindset when learning RPA.
In this article, I’ll show you, step by step, how to create a simple RPA bot with Microsoft Power Automate (PA) and then test it. This is a simplified version of a bot I built and use on the job at the Capital Area Food Bank (CAFB) that helped solve a real business problem.
As we go through the steps, consider what each individual component can do and how you can use it as a building block to start your RPA journey. You can also create the simple bot demonstrated here as you read along.
HOW TO START CREATING A BOT
Setting up your first RPA bot with PA is easy. If your organization has an Office 365 environment or Windows 11, you already have access to PA. (Note that you may have to ask your IT department to grant you access to PA.) You can also use a personal Microsoft account with Windows 11 to experiment.
First, navigate to the Power Automate website and log in using your Microsoft account. On the left side of your window, click on + Create.
We can select three types of “triggers” (see the screenshot “3 Ways to Make a Flow Dialog Box,” below). Triggers are how an RPA process is started. The three types of PA triggers are:
■ Automated cloud flow: The RPA process will start when another event happens (e.g., when an email with specific wording arrives).
■ Instant cloud flow: The RPA process will start when something tells it to. This can be when you manually click the button. Other RPA processes can also start instant cloud flows.
■ Scheduled cloud flow: The RPA process will run on a given schedule. Select Scheduled cloud flow.
3 ways to make a flow dialog box
STEPS TO BUILD A SIMPLE RPA BOT
Now, I’ll show you how to create a simple RPA bot — an automated reminder to submit credit card reports. I originally built this bot because CAFB has several corporate cards in use by its staff and getting credit card expense reports submitted on time was a challenge. One of the reasons reports were submitted late was the lack of consistent notifications from accounting. While the card expense reporting software did send notifications, they were unclear and many card users filtered these to spam. To help alleviate this problem, I developed a simple RPA process to send out a monthly reminder to all card holders, reminding them of due dates for expense reports.
SETTING UP THE BOT
Create a new “Scheduled cloud flow” in PA. Say we want the bot to remind users on the second day of each month to submit reports by the fifth day of the month. Accordingly, we would select the RPA process schedule as shown in the screenshot “Build a Scheduled Cloud Flow Dialog Box,” below, (you can also name your bot here).
Build a scheduled cloud flow dialog box
Notice the other frequencies available, such as weekly, daily, or hourly. Be careful when selecting highfrequency repetition, and make sure it is what you need.
Click Create. Don’t worry. You can always edit the frequency later. This will bring you to the PA editor. Here, we have the start of our RPA process. (See the “Recurrence Trigger Step” screenshot, below.)
Recurrence trigger step
First, determine the name of the month for which expense reports are due. For example, if we are sending this notification on Feb. 2, we know the expense report month must be January. We can obtain this information automatically and store it in a variable for later. Click + New step. Search for variable in the search bar and select Initialize variable. (See the screenshot “Choose Operation Initialize Variable,” below.)
Choose operation initialize variable
Name your variable something descriptive like “varCurrentMonth.” You can name the variable whatever you want, but be sure your naming convention is consistent and reflects the value the variable holds. Select variable type String from the dropdown menu. (“String” indicates a text value; see the sidebar, “Variable Types.”) Click into the blank Value box. You will notice a window pops up (ensure pop-ups are allowed on this website in your browser). Click on Expression. (See the screenshot “Setting Up a Variable,” below.)
Setting up a variable
Now we are in the PA expression bar. Think of this exactly like Excel formulas: We have a variety of functions that give us values or calculate values, based on several given arguments. Here we need to calculate the string value of the previous month. In Excel, the formula =TEXT(NOW()-5,”mmmm”) returns the string value of the month for the day that is five days ago. The PA formula is similar: formatDateTime(addDays(utcnow(),-5),’MMMM’).
Let’s break down the formula:
- utcnow(): This returns the current date. Because we have set the RPA process to run on the second day of each month, this will always be equal to the second day of each month (e.g., 1/2/2023, 2/2/2023, 3/2/2023, etc.).
- addDays(date_value, days): This adds or subtracts days from a given date value. Here, we are subtracting five days (-5) from utcnow(). Following this logic, we are guaranteed to be returned a date value in the previous month (e.g., for the same dates above, we would get: 12/27/2022, 1/27/2023, 2/25/2023).
- formatDateTime(date_value,format_string): This formats our given date in the way we specify. In this case, we provide the format string MMMM, which returns the entire name of the month (search on the web for other valid format strings). For the same dates above, we would get: December, January, February.
Type this formula into the expression bar. Notice the formula helper pop ups as you write, which is useful to help understand what is going on. Click OK. (See the screenshot “Formula in Expression Bar” below)
Formula in expression bar
Congratulations! If you are following along, you have made your first, albeit very simple, RPA process.
TESTING YOUR SIMPLE BOT
A key part of RPA design is testing as you go. Let’s save our bot and test to make sure everything is working as intended. Click Save in the upper-right corner. If everything is correct, you should see a green popup bar, indicating your flow is ready to go. If you have a red bar indicating something is wrong, double-check the instructions above.
Next, click Test in the upper-right corner. Select Manually in the screen that pops up, then Test, and then Run Flow. (See the screenshot “Run Test Flow Box,” below.)
Run test flow box
If your flow ran successfully, you will get a pop-up stating, “Your flow ran successfully.” If your flow failed, double-check the instructions above.
Now let’s see if our variable calculated the previous month string correctly. Staying on the same screen, click on the Initialize variable flow step to expand.
This will provide information about the variable and what value it held during the run process. As we can see, our formula above successfully returned the string value of the month five days ago. (See the screenshot “See Results of Initialize Variable Box,” below)
See results of initialize variable box
Note: You may want to change the -5 argument to -45 to see how the formula works. If you are testing this in the middle of the month, it will return the same value as the month you are in.
As a best practice, you should also rename the PA flow step to describe what it is doing. The description “Initialize variable” is not helpful. Instead, name it something like “Get string of previous month and store in varCurrentMonth.”
You can rename PA flow steps by clicking the ellipse on the “Initialize variable” step (while editing the flow) and then Rename.
Next, add another “Initialize variable” flow step, which creates a string variable that calculates the due date of the expense reports. Knowing reports are due the fifth day of the month, we should add three days to the current date since this flow runs on the second day of each month. The formula would be as follows (note the different date format string): addDays(utcnow(),3,’MM/dd/yy’).
Name your variable something descriptive, such as “varExpenseReportDueDate.”
Now, we want to get a list of all our credit card user email addresses. For simplicity, make a new initialize string variable step with all emails separated by a semicolon (;). If you are testing, put your email address into this variable (and change it when testing is complete). Name your variable something descriptive, such as “varCardUserEmails.” (See the screenshot “Initialize Variable With Card User Emails,” below.) Note that multiple email addresses must be separated by semicolons (;) in most modern email clients.
Initialize variable with card user emails
Note: In this example, we manually type out the email addresses for simplicity. However, PA can query existing datasets, such as a SQL table, SharePoint list, or Excel table, and get information such as email addresses dynamically. If, for example, you implement this bot and later add a new credit card user, you can program the bot to automatically add that user’s email address. Search the web for how to access and format your organization’s data using RPA.
Now, we have our basic information stored in variables: the expense report month, the due date, and the email addresses we want to notify. Let’s add a new step to send an email. Click + New Step and search for send an email in the search box. Select Send an email (V2) for the Office 365 Outlook connection. (See the screenshot “Choose ‘Send an Email via Outlook’ Step” below.) Note that if you are logged in with your Microsoft account, your account should automatically be linked.
Choose ‘send an email via Outlook’ step
Now, we need to provide some basic values: the email recipients, the email subject, and the email body. (See the screenshot “Send an Email (V2) Dialog Box,” below.)
Let’s start with recipients. We’ve already calculated this and stored our email addresses in the variable “varCardUserEmails,” so let’s reuse our work. Click in the To box and select Add dynamic content.
Dynamic content includes variables and information from previous flow steps we can reuse. You will see a box with the variable “varCardUserEmails” pop up. Go ahead and select this. (See the screenshot “Dynamic Content Dialog Box.”)
Dynamic content dialog box
Now in our “To” line, we have the variable shown in the screenshot “varCardUserEmails String Assigned to ‘To’ Box,” below.
varCardUserEmails string assigned to ‘To’ box
Let’s fill in the Subject portion of the email. We would like the subject to read “REMINDER: Expense reports for month due .” Fortunately, we have already calculated the expense report and in our variables above. Using a combination of text and variables, we can arrive at the subject line seen in the screenshot “Subject Box,” below, (remember to click Dynamic content to add variables).
Subject box
Also fill in the body of the email. You can add whatever you want here — helpful tips, whom to reach out to for help, or a link to an SOP document. (See the screenshot “Filling in the Body of an Email,” below.)
Filling in the body of an email
Done! We have made the simple body of our first RPA process. Let’s test it out. Click Save then Test, following through until you run the flow (see instructions above). If your flow runs correctly, you should see the message “Your flow ran successfully” in a green bar.
In your Outlook, you should see an email sent that looks like the one shown in the screenshot “Sent Email,” below.
Your automated flow will run in the cloud in the frequency you specify — in this use case on the second day of every month. You don’t need to run the process yourself.
Sent email
SIMPLE BOTS AS BUILDING BLOCKS
You may wonder why we went to the trouble of creating a bot for this task. After all, it can easily be done manually in a few minutes each month. Several non-RPA features can do the same basic task. Why go through the trouble? While this RPA process solved a small problem for CAFB accounting, it also provided us with the building blocks to solve larger problems. These building blocks include:
- How to create and store values in variables;
- How to send an automated email;
- How to use PA expressions; and
- How to use dynamic content in future flow steps.
When you’re starting with RPA, start small: Think of any simple manual process you do and try to automate it via RPA. It may take a lot of time and end up being less efficient than doing the task manually, and you may even discover the process is not possible using RPA. However, from this experimentation, you gain an understanding of how RPA can make different systems, datasets, and processes talk to one another.
How does one gain building blocks? Extensive web searching (such as Google) and watching videos (such as YouTube) are the answer. When I am developing RPA processes, I have about 20-plus web tabs with searches open at any given time.
CONCLUSION
Among other modern technology solutions, RPA is a powerful tool that can help streamline and automate manual processes in any accounting department. However, as with any new technology, understanding how RPA can be useful can be difficult. The building block approach is an excellent way to frame your RPA education.
Variable types
The following are some of the basic variable types available for use in PA:
Boolean: True or false.
String: A text value (e.g., “Hello World” or “1234 Stice Bros St”).
Integer: A whole number with a value between -2,147,483,647 and 2,147,483,657 including 0.
Float: Any number, including decimals.
Object: Stores a “THING” (e.g., a reference to an email object).
Array: A list of any of the variables above (e.g., a list of integers: [-12, 6, 0, 1, 2, 2, 3, 6], a list of strings: [“hello,” “1,” “CPAs rock,” “Capital Area Food Bank”]).
About the author
Karl Spanbauer, CPA, is the controller for Capital Area Food Bank in Washington, D.C. To comment on this article or to suggest an idea for another article, email joaed@aicpa.org.
LEARNING RESOURCES
Robotic Process Automation Fundamentals for Accounting and Finance Professionals Certificate Program
Automation can ensure a competitive advantage. This three-part certificate program focuses on robotic process automation (RPA) benefits and how the use of software robots can help your business or clients become more competitive and agile.
Robotic Process Automation Strategy for Business Leaders
Get an overview of robotic process automation in this three-course self-study online program.
For more information or to make a purchase, go to aicpa.org/cpe-learning or call the Institute at 888-777-7077.
AICPA RESOURCES
Articles
“Why CPAs Should Care About RPA,” AICPA Insights, Aug. 31, 2020
“3 Tips for Introducing RPA,” FM magazine, Feb. 27, 2020
“A Beginner’s Guide to Robotic Process Automation,” AICPA.org, Nov. 15, 2019
“Implementing RPA Is Easier Than You Think,” AICPA Insights, May 16, 2019
Podcast episodes
“The Payoffs of RPA for One Accounting Firm,” JofA, Aug. 25, 2022
“Why Robotic Process Automation Is for Everyone,” FLP, the Finance Leadership Podcast, Nov. 27, 2018
Video
“The Benefits of RPA,” JofA, Oct. 11, 2019
