The following case was developed by the Ernst & Young Academic Resource Center, which is sponsored by the Ernest & Young Foundation. Robotic process automation is growing in importance in the accounting profession. Several RPA vendors provide free trial software including Automation Anywhere (https://www.automationanywhere.com/lp/communityedition)

and UIPath (https://www.uipath.com/freetrial-or-community). Download one of these software packages and complete the following case.*

Robotic Process Automation – Wood’s Amazing Woods Inc.

Overview

Wood’s Amazing Woods Inc. (Wood’s), a small company in Idaho, sells wood products to customers. The founder, Jason Woodworth, has focused most of his attention on providing excellent service and products, but not on keeping his accounting system up-to-date.
Wood’s currently has a manual billing process to bill customers for the wood products sold. That work is performed by a billing specialist, Emily Young, a recent accounting graduate. Jason would like to completely automate his currently manual billing process to drive productivity and cost savings. Jason also wants to provide Emily with the opportunity to do some data analytics on their financial information and reduce her overtime during month-end for better work life balance.
You will build a bot for each case part starting with a simple billing scenario in part I. The bot you will build in each subsequent case builds off the first bot and is more complex than the previous bot as more complexity will be added to the billing process. Recall that the basic function of a bot is to recreate the steps that a human would do in a process.
This case uses a simplified automation process that is modified from what would happen in an actual accounting firm or business in several ways. For example, companies typically would use more sophisticated software than the excel files used in this case; however, the process of creating bots is the same for simple and more sophisticated software. In addition, companies typically have solutions to provide automated billings. This case uses this simplified scenario to teach the introductory principles of building bots and does not necessarily demonstrate a typical process that would be automated in practice. Although the case simplifies the software and the process, the basic skills of building an RPA are applicable to all automation settings.

Before you begin

► Save all of the provided Excel files for the case (go to the student download page at http://www.pearsonhighered.com/romney and download the files) into the same folder on your hard drive. You should build the bot so that the first sequence allows the user to input the file path for the folder where the files are located. Choose a file path that does not have too long of a name. Store this file path as a variable and then use this variable as a reference to load/save/move/etc. any files. This way the bot can be moved to other computers and still function (i.e., be graded by your professor).

– Innovation_mindset_case_studies_RPA_Billing_MasterInvoice.xlsx – Innovation_mindset_case_studies_RPA_Billing_InvoiceData1.xlsx – Innovation_mindset_case_studies_RPA_Billing_InvoiceData2.xlsx – Innovation_mindset_case_studies_RPA_Billing_InvoiceData3.xlsx ► It is recommended that you organize your thinking in a flowchart before you begin programming your bot. This makes it more likely that you do not forget key parts of the process. It also allows you the ability to develop your bot in segments or sections.
This can make troubleshooting much easier.

Objective

Your task is to build a bot that automates the billing process. Ultimately, a billing specialist should be able to open the RPA software and run the bot, which should do all the steps that are described in the manual process (following) without any human interaction, with one exception. When the bot is run, it should require the user to enter a file folder location where the files are currently and will be saved when done. This allows the bot to be transferable to other computers and still run successfully.

Part I

Wood’s Amazing Woods Inc. currently uses the following manual process to bill its customers:
► The billing specialist, Emily Young, opens the Excel file with the invoice data, Innovation_mindset_case_studies_RPA_Billing_InvoiceData1.xlsx.
► She then reviews the data and opens the Excel invoice template, Innovation_mindset_case_studies_RPA_Billing_MasterInvoice.xlsx.
► Emily creates a unique invoice for each record in the invoice data.
► She then saves the updated invoice template file for each customer as “Innovation_mindset_case_studies_RPA_Billing_MasterInvoice_InvoiceNumber.xlsx”
(where InvoiceNumber is replaced by the invoice number being billed).
In the invoice data file, there is only a single record.

Required
► Your bot is required to create an invoice for the one invoice.

Part II

Wood’s Amazing Woods Inc. currently uses the same billing process as in Part I but uses the invoice data Excel file, Innovation_mindset_case_studies_RPA_Billing_InvoiceData2.xlsx. In this invoice data, there are multiple records for billing.

Required

Your bot is required to create an invoice for each customer that should be billed. The bot should be programmed so it can complete the task for any number of records that are included in the invoice data. In other words, you should not hard code all the values. Instead, you should use programming to loop through all of the rows and create invoices accordingly.

Part III

Wood’s Amazing Woods Inc. currently uses the following manual process to bill its customers.
► The billing specialist, Emily Young, opens the Excel file with the invoice data, Innovation_mindset_case_studies_RPA_Billing_InvoiceData3.xlsx. There are multiple records in this data.
► She then reviews the data and opens the Excel invoice template, Innovation_mindset_case_studies_RPA_Billing_MasterInvoice.xlsx.
► Emily creates a unique invoice for each record in the invoice data if the customer has not already been billed.
– Customers who have been billed have dates in the column labeled “Billed.” If the field is empty, then the customer needs to be billed.
– If a customer has already been billed, Emily skips this customer and goes on to the next customer.
► She then saves the updated invoice template file for each customer as “Innovation_mindset_case_studies_RPA_Billing_MasterInvoice_InvoiceNumber.xlsx”
(where InvoiceNumber is replaced by the invoice number being billed).
► Once she is finished creating the invoices for each customer, she opens the invoice data Excel file, Innovation_mindset_case_studies_RPA_Billing_InvoiceData3.xlsx. and updates the “Billed” column with the current billing date.
► Finally, she saves the file as Innovation_mindset_case_studies_RPA_Billing_InvoiceData3_Complete.xlsx.

Required
► Your bot is required to create an invoice for each customer that has not been previously billed. The bot should be programmed so it can complete the task for any number of records that are included in invoice data. In other words, you should not hard code all the values. Instead, you should use programming to loop through all of the files and create invoices accordingly.