Roy Pan CA

• Back

Reporting & Governance

^ Right click and open the image in a new tab for the live, interactive dashboardDashboards:This Tableau dashboard demonstrates business performance reporting in an FP&A context. It integrates key metrics such as budget-to-actuals/forecast variances and milestone tracking, allowing users to quickly assess financial and operational performance. The dashboard showcases skills in chart selection, backend data structuring, calculation logic setup, and user-focused design, highlighting the ability to translate complex datasets into clear, decision-ready visualizations.You can toggle between year-to-date and full-year figures, and also between two dummy data ledgers to see how this dashboard dynamically adjusts based on ledger period. This, accompanied with system/environment variables, can delivered as a fully automated solution, with figures self-refreshing on a periodic basis.Figures used have been randomly generated in Excel via RANDBETWEEN().

^ Right click and open the image in a new tab for further detailsVBA Scripting:This is a dynamic PowerPoint-to-PDF automation tool using VBA to streamline financial reporting for multiple stakeholder groups. The script reads a configurable Excel file that specifies which slides are visible to different audiences - such as executives, management teams, or operational staff - and automatically generates tailored presentations and PDF reports. By programmatically copying the master presentation, filtering slides based on audience permissions, and exporting the results to PDF, the tool eliminates manual combing of slides and ensures consistency and accuracy in reporting. No unintended viewers will see confidential information. This solution significantly reduces preparation time for recurring financial reports and allows flexible, secure distribution of sensitive content.

• Back

Modelling & Analysis

^ Right click and open the image in a new tab for the model templateImpairment Testing via DCF:This model automates the impairment testing process by integrating a discounted cash flow (DCF) directly within TechOne XLOne. By leveraging the queried forecast data (removed for confidentiality), the model dynamically ingests multi-year cash flow projections across a complex organizational hierarchy. XLOne's REPEAT row command ensures new items dynamically flow through. It calculates the net realizable value through an NPV-based approach, with the discounted balance compared against inventory, thus triggering either a reversible impairment or permanent write-off. This integration eliminates manual data entry risks, ensuring that impairment assessments are based on 'single source of truth' ledger data, while also ensuring COGS is not materially misstated as at time of revenue recognition.

^ Right click and open the image in a new tab for the model templateLedger Variance Analysis:This ledger variance model is designed to dynamically compare two user-defined financial ledgers via XLOne - such as a current forecast versus a baseline budget or prior forecast. The model achieves its goal through a parameter-driven architecture, allowing end-users to seamlessly toggle inputs like specific ledgers, date ranges, and numeric scaling factors at runtime. Under the hood, the logic retrieves period-by-period balances across the financial calendar and structures the output hierarchically, drilling down from the divisional group level to specific divisions and then individual projects.

• Back

Systems & Analytics Engineering

^ Right click and open the image in a new tab for the full imageThis TechnologyOne ETL workflow was designed to replace manual Excel forecasting and automate the complex interdependency between debt and cash balances. Forecast debt is managed via the following logic:- IF debt is present, THEN cash must be used to pay off debt;- IF there are net cash outflows for the month that exceed cash on hand, THEN debt must be incurred to cover the gap, thus increasing debt balance and reducing cash to zero;- Cash can only be positive IF debt has been fully repaidYou can find the logic in Excel format here.The primary technical challenge was translating recursive spreadsheet logic - specifically comparing prior period closing cash plus current net operating cashflow against the opening debt balance - into T1’s rigid, database-driven columnar environment. I engineered a solution that leverages T1's local variables as dynamic calculation placeholders to evaluate the core business rules: repaying debt until cash or debt is nil, and borrowing if the cash position turns negative. The ETL loop stores these intermediate mid-calculation results, passing parameters to accurately derive movement values. This effectively rolls current period closing balances into subsequent opening balances across the forecast horizon. Designed for high scalability, this ETL focuses on a single project but acts as a modular component that can be called by master ETLs to iterate through multiple divisions and projects, delivering a fully automated, enterprise-grade debt forecasting engine.Note that in absence of tools like TechnologyOne, this can be rebuilt solely within SQL databases using stored procedures, declared variables, temporary tables and while true loops to mimic T1 ETL capabilities. In other words, this solution can be tool-agnostic.

^ Right click and open the image in a new tab for a clearer imageTo redefine the efficiency of the month-end close, I engineered a T1 ETL workflow that operates on a dynamic 'Workday Decision Tree.' Unlike static schedules, this intelligent process evaluates the current business workday (e.g., WD-2 vs. WD+3) to trigger specific, time-sensitive financial operations. The engine autonomously handles high-to-low value tasks, ranging from complex data wrangling, scheduled reports and automated journal preparation to the execution of month-end actuals and forecast calculations. Sub-ETLs are also called here to ensure all workflows are consolidated under one area.By bridging the gap between raw ERP data and stakeholder-ready insights, workflows like this can help eliminate manual bottlenecks, significantly reclaiming team capacity, and allowing the FP&A function to focus on high-level strategic variance analysis rather than administrative data processing.

Other Projects:Pulling Trial Balances via Xero API (click here for details):
I attempted replacing manual accounting data extracts with an automated API-driven workflow by connecting directly to Xero and programmatically retrieving financial data using Python. The objective was to establish a repeatable and controlled data ingestion process suitable for FP&A and finance systems environments, where reporting models depend on timely and consistent inputs. The solution demonstrates end-to-end setup of authentication, token management, and scripted API calls to pull reporting data (trial balances) as structured JSON outputs, removing reliance on ad-hoc downloads and reducing operational friction across multi-system reporting processes.Automating Month-end Rollover via System Variable Design (click here for details):
I developed a lightweight governance framework to automate month-end reporting rollover by introducing a centralized system-variable table backed by a SQL database. Instead of manually updating periods, versions, or filters across multiple reports, dashboards, and workpapers, the solution uses a single-row “reporting calendar” control table that can be joined to transactional datasets, allowing reporting outputs to dynamically shift based on one controlled parameter. The design demonstrates how FP&A and finance systems environments can implement centralized version control for reporting, reducing operational risk, eliminating repetitive rollover tasks, and synchronizing updates across large reporting ecosystems through a single point of control.Overnight Scripting in Virtual Private Servers (click here for details):
I attempted to replicate TechnologyOne's scheduled background jobs functionality (e.g. ETLs, Playlists) via virtual private servers hosted on third-party services such as DigitalOcean, and setting up cron jobs in Ubuntu Linux for overnight scripting. In absence of TechnologyOne, such solutions could still provide finance teams scheduling capabilities.