Johnny Ho
Menu
All projects

UNSW Finance AutomationData Analyst InternJan 2024 - Mar 2024

Finance automation dashboards for the UNSW CFO

ROI, payback and bot health in Power BI, presented to the CFO

Role: Designed and built the Power BI dashboards (Data Analyst Intern)

  • Power BI
  • UiPath
  • Microsoft Excel
Power BI dashboard "Automated Workflow Performance": 4 active workflows, 8,136 estimated minutes saved, 399 transactions, with transactions per month and per workflow
Animated overview of the steps below (an illustration, not recorded footage).

Dashboard figures

  • 4active automated workflows
  • 8,136estimated minutes saved
  • 399transactions
  • Jun 2022 to Jan 2024date range

From the Automated Workflow Performance report. Time saved is an estimate: estimated minutes before minus after automation, times the transaction count.

Before vs after

Before

The CFO question

  • Which automation projects pay off?
  • Why do some bot runs fail?
  • Insight scattered across reports

After

The dashboards

8,136estimated minutes saved

  • ROI and payback period per project
  • Bot success, failure and exception reasons
  • 8,136 estimated minutes saved, in one view

Continuous Improvement Dashboard

  1. 01Step 1 of 4, Microsoft Excel

    Project register

    Each automation project: cost, saving, category, customer, owner.

  2. 02Step 2 of 4, Power BI

    ROI and payback model

    ROI = saving minus cost. Payback in weeks = cost / weekly saving.

  3. Layout of the Continuous Improvement Dashboard: estimated annual ROI by project, cost vs saving, cumulative ROI and payback period per project

    03Step 3 of 4, Power BI

    ROI by project

    Estimated annual ROI, cost vs saving, cumulative ROI, payback per project.

  4. Layout of the Continuous Improvement Dashboard page 2: ROI by category and customer, saving vs cost, filters by process owner

    04Step 4 of 4, Power BI

    Slice and present

    Filter by category, customer and process owner. Presented to the CFO.

Automation dashboard redesign

  1. Power BI dashboard "Automated Workflow Performance": 4 active workflows, 8,136 estimated minutes saved, 399 transactions, with transactions per month and per workflow

    01Step 1 of 3, Power BI

    Workflow performance

    Active workflows, transactions per month, estimated time saved.

  2. Power BI dashboard "Automated Workflow Performance (UiPath)": bot transactions by status category and over time

    02Step 2 of 3, UiPath

    Bot health

    UiPath bot runs by status category over time.

  3. Layout of the bot failure page: status donut, decomposition tree and exception reasons table

    03Step 3 of 3, Power BI

    Failure reasons

    Drill from status to exception reason to see why bots fail.

Tech stack in this project

  • Power BIData model, DAX measures (ROI, payback, averages), dashboards, tooltips with formulas
  • UiPathBot run logs: status, exception and execution time
  • Microsoft ExcelProject register with costs, savings and owners

Why it matters for supply chain

  • ROI and payback = business cases for WMS, TMS and automation investments
  • Exception analysis = root cause of order and shipment failures
  • Before vs after time = continuous improvement in warehouse and transport ops

Linked to my Master of Logistics & SCM, University of Sydney

  • Power BI dashboard "Automated Workflow Performance": 4 active workflows, 8,136 estimated minutes saved, 399 transactions, with transactions per month and per workflow
  • Power BI dashboard "Automated Workflow Performance (UiPath)": bot transactions by status category and over time
  • Layout of the Continuous Improvement Dashboard: estimated annual ROI by project, cost vs saving, cumulative ROI and payback period per project
  • Layout of the Continuous Improvement Dashboard page 2: ROI by category and customer, saving vs cost, filters by process owner
  • Layout of Johnny's redesigned Automated Workflow Performance report: KPI cards, transactions per month, per-workflow table and transactions by workflow
  • Layout of the UiPath bot page: active bots, execution time, status decomposition tree and status donut
  • Layout of the bot failure page: status donut, decomposition tree and exception reasons table
Read the full case study

Problem

The UNSW CFO and finance managers needed to see which automation projects were profitable, how the automated workflows were performing and why some bot runs failed.

Approach

  1. Designed and built a Continuous Improvement Dashboard in Power BI to assess each project over time, with ROI analysis and payback period calculations to identify profitable initiatives, and presented the findings to the CFO.
  2. Redesigned the existing automation dashboard so the CFO and finance managers could read current automation performance: transactions per workflow and month, estimated time saved, and UiPath bot success and failure.
  3. Analysed bot success rates and drilled into exception reasons to investigate failures.
  4. Layout images marked as layouts are drawn from Johnny's own .pbix files with data values hidden.

Figures are the confirmed dashboard figures or Johnny's own estimates, labelled as such.