Forecasting with Workdays

Software Engineering

R/Shiny proof of concept for WEHI budget forecasting. Uploads a master spreadsheet, allocates funds to expenses with an MIP solver, and exports Excel plus a dashboard.

Visit project

R/Shiny proof of concept built at WEHI (Walter and Eliza Hall Institute of Medical Research) during a Nov 2025-Mar 2026 research software internship. The app reads a master spreadsheet of funding sources and expenses, allocates money under category and date rules, and writes Excel plus dashboard charts.

What it does

Upload a Master Spreadsheet, or add, edit, and delete funding and expense rows in the UI
Set expense priority by column sort or by dragging a manual order
Run a forecast: a mixed-integer program fully funds the highest-priority expenses first, then a greedy pass fills leftovers (including overdue payments)
Review the result on a dashboard (allocation table, shortfall plot, chord diagram) and download Excel

How it works

Three pages: Input & Review, Forecast, and Dashboard.

1. Input

readxl loads the spreadsheet; data_processing.R cleans and validates rows
Funding fields include amount, allowed categories, valid_from, and valid_to
Expense fields include category, planned amount, and latest payment date

2. Priority

Column-based sort, or a drag-and-drop order via sortable

3. Allocation (allocation_algorithm.R)

Compatibility matrix: expense category must sit in the source's allowed list, and the payment date must fall inside the source window
MIP (ompr + HiGHS): maximize sum(2^(n-j) * y_j) so higher-priority expenses are fully funded first. x[i,j] is dollars from source i to expense j
Constraints: source capacity, all-or-nothing full payment when y[j]=1, incompatible pairs forced to 0
Greedy fill: leftover capacity onto unfunded expenses, with a looser date check (payment before valid_to)

4. Output

Excel via openxlsx; shortfall chart via Plotly; allocation chord diagram via chorddiag

app.R                         # Package setup and shinyApp()
src/
  main_ui.R / main_server.R   # Page wiring
  ui/                         # Input, forecast, dashboard layouts
  server/
    components/
      allocation_algorithm.R  # Compatibility matrix, MIP, greedy fill
      data_processing.R       # Excel read/clean/validate
      graph.R / output.R      # Plots and Excel builders
www/                          # WEHI logo and CSS

Results

Internship write-up: a forecasting pass that previously took up to 14 hours finished in minutes
Live demo: forecasting-budget.shinyapps.io/forecasting-budget-demo

Tech stack

Language: R 4.4.1 (the version on WEHI's Linux server)
App: Shiny, bslib, shinyjs, shinyWidgets, DT, sortable
Data: dplyr, tidyr, readxl, openxlsx, lubridate
Optimisation: ompr, ROI, ROI.plugin.highs
Charts: Plotly, chorddiag
License: GPL-3.0

Project link: github.com/narolinelim/forecasting-with-workday-project