Innovation Mindset

Pure Guitar Inc. (PG) is a small online retailer of high-end electric guitars located in Southern California. PG offers 11 models of electric guitars, 2 bass models, various sizes of electric amplifiers and a few electric guitar accessories. PG uses an Access database to store relevant business information, including 28 months’ worth of transactions. Management at PG monitors the number of sales each employee makes on a quarterly basis and creates an employee sales report that uses a bar chart to compare employees’ sales to one another each quarter. Management would like to simplify the process of creating this sales report. Your task is to automate the creation of the sales report by programming a bot that will prepare the sales report from the data stored in the Access database.

This case uses a simplified automation process that is modified in several ways from a typical process used in an actual company. For example, companies are likely to use more sophisticated software than the Microsoft Access files used in this case (e.g., an ERP system); however, the process of programming bots is the same for simple and more sophisticated software. This case uses this simplified scenario to teach the introductory principles of bot programming and, thus, may not necessarily demonstrate a typical process that would be automated in practice (see the RPA overview case for discussion of the characteristics of processes for which RPA is most beneficial). Although this case simplifies the software used and the automated process, the basic skills of building an RPA tool are applicable to all automation settings.

Before you begin

• It is a good practice to change the titles of activities to something descriptive so you, and others, can easily review your work and understand what the bot is doing.
• 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 in UiPath that automates the process of creating the employee sales report. Ultimately, management should be able to open UiPath and run the bot, which should do all of the steps described in the manual process without any human interaction, with one exception. When the bot runs, the first step should require the user to enter a file folder location where the files are currently stored and will be saved when done. This allows the bot to be transferred to other computers and still run successfully.

Employee sales report creation: current state

PG currently performs the following process to create the Employee Sales Report:

• A sales analyst, Ryan Sanders, loads new sales data into the Access database. The .csv files contain information about orders and the order detail that must be uploaded into the database before running the report.
• Once the data is uploaded, Ryan runs the query qry_EmployeeSalesReport.
• Ryan copies the information from the executed query and pastes it into an Excel file labeled Innovation_mindset_case_studies_RPA_SalesReporting_EmployeeReportData.xlsx.
• Ryan creates a bar graph for employee sales by quarter that looks like the one below (this is what your bar chart should look like when finished).
• Ryan creates a Microsoft Word file labeled, EmployeeSalesReport.docx. This file contains the bold, underlined and centered title, Employee Report, and then the above graph

Required

• Program a bot to create the Employee Sales Report. The bot should be programmed so it does all of the steps described above on its own.
• Note that the bot also must detect if the order and the order detail information have already been imported into Access (to avoid making duplicate entries) and, if so, continue running without tripping an error.
• Please submit the reportingBot.xaml UiPath file automatically to the virtualTA.

Innovation mindset case studies – Robotic Process Automation – Sales Reporting

© 2019 Ernst & Young Foundation (US). All Rights Reserved.

SCORE No. 05578-191US