Pure Reformed Oils

Swindler and Simpson: The path to redemption case

An in-class automation case across four sessions, culminating in a video demonstration of a bot that automates a basic order-to-cash cycle for a small business.

The Case Brief

Skylar Swindler, once known for running fraudulent ventures, is attempting to rebuild his reputation by launching a legitimate business—Pure Reformed Oils. Based in Seattle, Washington, the company specializes in selling essential oils with PNW themes. Although the product is ethically sourced and marketed with transparency, Skylar lacks formal infrastructure to run the business and operates the business entirely manually with his business partner, Victoria Simpson. Victoria Simpson, another redemption-seeker, has joined Skylar to help build out internal processes and the accounting systems. She hopes to ensure transparency in reporting and operations, and help grow the business by helping Skylar understand the financial implications of various business decisions.

This case has students act as consultant or advisors with Skylar and Victoria as their client in order to help them automate their order-to-cash process using Robotic Process Automation (RPA). Students will use UIPath to help develop a functional prototype that simulates automation tasks for intake, invoicing, recordkeeping, and potentially error handling, and generating rela-time insight into the business process. The case is split into four in-class sessions with an individual deliverable of a video submission where individuals present their automation along with discussion of the benefits (e.g., potential hours saved, human errors mitigated, and the improvement of insights into operations). Although the process is simplified, the goal is to foster deep thinking about process design, automation feasibility, and building and explaining bots developed using automation software.

I

Part I: Introduction and Mapping the Process

Students will be introduced to Skylar and Victoria's small business and its challenges in order processing, invoicing, and accounting. We will use LucidChart to map a suggested order-to-cash workflow, as a human-in-the-loop RPA solution. Students will document what tasks are rule-based and repetitive, making them good candidates for automation.

Required:

  • Read Case Brief and the Table which lists the existing process to be automated (see end of document).
  • Discuss the current manual order-to-cash process and identify pain points and inefficiencies that could be solved through automation.
  • Using LucidChart, outline a proposed RPA solution to the workflow and make notes of the inefficiencies that are solved via automation.
  • Finally, outline the outputs that the bot automates and the remaining responsibilities of Skylar and Victoria. As part of this step, pay attention to the client's wish to be able to identify problems in real-time (i.e., they appear to want a dashboard, see EOM in the table below).
II

Part II: Building an RPA solution proto-type/use case

The second part of the case is to build an RPA solution using UIPath that takes order data from a structured csv file and updates various excel files with triggers around certain criteria (i.e., orders, shipped, delivered). To build the prototype or use case bot, students will use simulated data to generate the desired outputs from Part 1 of the case. Students will use input/output folders and learn to manage variables, flowcharts, and file activities.

Required:

  • Create a bot that automates the order-to-cash workflow described by the clients.
  • Optional case 1: Extend the bot generate a dashboard that allows the client to achieve real-time monitoring of their business.
III

Part III: Finalizing and Documenting the Automation for Submission

The submittable component of this case is a short video demonstrating their working bot. The video should describe the business context, walk through the process flow, show the bot in action, and estimate efficiency gains in hours saved or error reduction. This project will help students prepare for the RPA Individual Challenge.

Required:

  • Prepare a video (3–5 min) explaining and demonstrating your automation (or partial automation) of the process.
  • Highlight steps automated and challenges addressed.
  • Quantify (or estimate) hours saved and risk mitigated.
  • Describe and show the inputs and outputs in the automation, and places where human interaction is required.
  • Please note that bots and supporting material will not be submitted in this case, and that a working bot is not required.

Overview of the existing process to be automated

Step Task Name Currently performed by Software and System(s) utilized Notes
1 Monitor for New Order Skylar Etsy Seller Portal (online/browser) Skylar collects new orders from Etsy dashboard, if an order is received, move to next step.
2 Extract Order Details for shipping Skylar Etsy Seller Portal: Order Page (online/browser) Information retrieved includes item(s), address, timestamps and inventory amounts, shares inventory requested from order with Victoria. Downloads orders in csv and saves information to orders.xlsx.
3a Generate Shipping Label and Send Confirmation Email Skylar excel: Orders.xlsx and email system: e.g., Gmail Skylar emails customer to confirm order with expected shipping time.
3b Update Inventory File Victoria and Skylar Excel (or Google Sheets) When Vicotria receives and email from Skylar, she updates the inventory_level.xlsx file by reducing the item quantity based on orders received.
4 Prints and ships physical product Skylar Etsy / USPS API Skylar can use either Etsy's integrated label services or a service by the delivery company of his choice (e.g., USPS API). He manually inputs customer address information and prints. One he has shipped the item(s) he confirms with Victoria item has been shipped.
5 Mark Order as Shipped in systems Victoria and Skylar Etsy Dashboard and excel: shipping.xlxs Skylar updates Etsy dashboard to record shipping of item, Victoria updates shipping.xlsx assigning "shipped" for each order and includes tracking number entry obtained from Skylar.
6 Initial Post to Accounting System Victoria QuickBooks (we will use Excel) Victoria posts initial JEs to reflect A/R, Unearned Revenue, and Inventory/COGs JEs.
7 Receive Shipping Confirmation Skylar and Victoria Gmail Skylar monitors email for tracking information confirming delivery to customer, Skylar forwards email to Victoria. Victoria updates shipping confirmations log ("shipped" items to "delivered").
8 Final Post to Accounting System Victoria QuickBooks (we will use Excel) Victoria posts revenue recognition JEs. This completes the cycle, and the cycle starts again at item #1 (above) during the month and moves to item EOM (below) at the end of the month.
EOM Reconciliations Victoria and Skylar QuickBooks (we will use Excel), all local Excel Files, Etsy Dashboards At the end of each month Victoria and Skylar Reconcile all of the excel files in their system with both QuickBooks and the Etsy Dashboard. They also examine any returns, customer complaints/feedback, and missing order information at this point. They update their accounting system at this point to recognize any returns with reversing JEs and recognizing shipping expenses for returned items. This is when they also spend time discussing how to grow their business, and minimize returns, but they wish that they could do this in real-time, using a much more automated system.