Usage & Billing Summary Dashboard

What you’re building

At the end of every month, someone in the core facility opens a spreadsheet of instrument usage logs, builds a pivot table grouped by PI, cross-references the rate schedule, adds up the totals, and produces a billing summary. It takes an hour or more, and one wrong drag in the pivot table means a PI gets charged for someone else’s microscope time.

You are going to build a tool that does this in five seconds.

💬This replaces the worst spreadsheet in your facility

Every core facility has one: the billing spreadsheet. It has 14 tabs, three people know how it works, and nobody trusts the formulas. A standalone dashboard that reads the raw usage log and produces grouped totals from scratch every time is more reliable than any shared spreadsheet because there is no accumulated state to corrupt.

By the end of this lesson you will have a standalone billing summary dashboard that runs entirely in the browser. Upload an instrument usage CSV, and it instantly groups hours and costs by PI and grant number, renders bar and pie charts with Chart.js, and produces a sortable billing table you can print or export. No server, no database, no Excel — just one HTML file.

ℹSoftware pattern: Upload, aggregate, visualize

Upload → parse → group by keys → aggregate → chart + table. This is the classic business intelligence pattern. It works for sales data, project hours, expense reports — any dataset where you need totals grouped by categories.

🔍Domain Primer: Key billing terms you'll see in this lesson

New to core facility billing? Here are the terms you’ll encounter:

Instrument usage log — A record of who used which instrument, when, and for how long. Most facilities track this through a booking system (iLab, SUMS, or a shared calendar) and export it as a CSV at month-end.
Rate schedule — The price list for each instrument, usually expressed as dollars per hour. Rates may differ for internal vs. external users. Example: a confocal microscope might be $75/hr for campus users and $150/hr for industry.
Charge-back — The process of billing a PI’s grant for services used. The core facility is a service center that recovers its costs by charging the grants that use its instruments.
Indirect costs (F&A rate) — Facilities & Administrative costs that the university adds on top of direct charges. A 55% F&A rate means a $100 instrument charge actually costs the grant $155. Core facilities typically bill direct costs only.
Service center — The university’s accounting classification for a unit that provides services and recovers costs through charge-backs. Core facilities are service centers.
iLab / SUMS — Common lab management and billing software platforms used by university core facilities to track reservations, usage, and invoicing.

You don’t need to be a billing expert — the tool handles the math. You just need to know what the columns in the usage log represent.

Who this is for

Core facility directors who need monthly billing summaries for each PI and grant.
Financial administrators who reconcile usage charges against grant budgets.
Lab managers who want to see which instruments are most heavily used and by whom.

ℹCore Facility Context

UW-Madison core facilities collectively process thousands of instrument hours per month across sequencers, microscopes, mass spectrometers, flow cytometers, and more. Each facility produces its own usage log format, but the billing aggregation problem is identical everywhere: group by PI, group by grant, multiply hours by rate, sum it up.

The showcase

Here is what the finished dashboard looks like once you open the HTML file in a browser:

Upload zone at the top for dragging or selecting a usage log CSV.
Summary cards showing total hours, total billable charges, number of unique PIs, and number of unique instruments.
Bar chart showing total usage hours per PI (horizontal bars, sorted descending).
Pie chart showing cost distribution across instruments (which instruments account for the most charges).
Billing summary table with columns for PI, grant number, instrument, total hours, rate, and total cost — sortable by any column, with subtotals per PI.
Export button that opens a print-friendly billing report in a new window.

Everything runs client-side with Chart.js loaded from a CDN. Your usage data never leaves the browser.

The prompt

Open your terminal , navigate to a project folder , start your AI CLI tool (e.g., by typing claude), and paste this prompt:

Build a single self-contained HTML file called billing-dashboard.html that summarizes
core facility instrument usage and billing. Requirements:

1. FILE INPUT
   - A drag-and-drop zone for CSV upload (dashed border, visual feedback on dragover)
   - Click-to-browse fallback
   - Parse CSV client-side, handle quoted fields

2. SAMPLE DATA (embed as a "Load Example" button)
   Include this instrument usage log with realistic data:
   Date,Instrument,PI_Name,Lab,Grant_Number,Hours_Used,Rate_Per_Hour,Notes
   2026-03-01,Confocal Microscope,Dr. Sarah Chen,Chen Lab,R01-GM134522,3.5,75,Live cell imaging
   2026-03-01,Flow Cytometer,Dr. James Rivera,Rivera Lab,R01-HG009876,2.0,60,Cell sorting CD4+
   2026-03-02,Illumina NovaSeq,Dr. Anika Patel,Patel Lab,P30-CA014520,8.0,200,Whole genome sequencing
   2026-03-02,Confocal Microscope,Dr. Sarah Chen,Chen Lab,R01-GM134522,4.0,75,Z-stack acquisition
   2026-03-03,Mass Spectrometer,Dr. Lisa Yamamoto,Yamamoto Lab,U54-AI170856,6.0,150,TMT proteomics
   2026-03-03,Flow Cytometer,Dr. Marcus Brown,Brown Lab,R21-NS112340,1.5,60,Compensation panel
   2026-03-04,Confocal Microscope,Dr. James Rivera,Rivera Lab,R01-HG009876,2.5,75,Fixed tissue imaging
   2026-03-04,Illumina NovaSeq,Dr. Sarah Chen,Chen Lab,R01-GM134522,12.0,200,RNA-seq 150PE
   2026-03-05,qPCR System,Dr. Emily Foster,Foster Lab,T32-GM008349,1.0,50,Gene expression panel
   2026-03-05,Mass Spectrometer,Dr. Anika Patel,Patel Lab,P30-CA014520,4.5,150,Metabolomics
   2026-03-06,Confocal Microscope,Dr. Lisa Yamamoto,Yamamoto Lab,U54-AI170856,3.0,75,Calcium imaging
   2026-03-06,Flow Cytometer,Dr. Sarah Chen,Chen Lab,R01-GM134522,2.0,60,Apoptosis assay
   2026-03-07,Illumina NovaSeq,Dr. Marcus Brown,Brown Lab,R21-NS112340,10.0,200,Exome sequencing
   2026-03-07,qPCR System,Dr. James Rivera,Rivera Lab,R01-HG009876,1.5,50,Validation qPCR
   2026-03-08,Mass Spectrometer,Dr. Sarah Chen,Chen Lab,R01-GM134522,5.0,150,Phosphoproteomics
   2026-03-08,Confocal Microscope,Dr. Emily Foster,Foster Lab,T32-GM008349,2.0,75,Immunofluorescence
   2026-03-09,Flow Cytometer,Dr. Anika Patel,Patel Lab,P30-CA014520,3.0,60,Immune panel
   2026-03-09,Illumina NovaSeq,Dr. Lisa Yamamoto,Yamamoto Lab,U54-AI170856,6.0,200,16S amplicon
   2026-03-10,Confocal Microscope,Dr. Marcus Brown,Brown Lab,R21-NS112340,1.5,75,Brain slice imaging
   2026-03-10,qPCR System,Dr. Sarah Chen,Chen Lab,R01-GM134522,2.0,50,Knockdown validation
   2026-03-11,Mass Spectrometer,Dr. James Rivera,Rivera Lab,R01-HG009876,7.0,150,Intact protein MS
   2026-03-11,Flow Cytometer,Dr. Emily Foster,Foster Lab,T32-GM008349,1.0,60,Training session
   2026-03-12,Illumina NovaSeq,Dr. Anika Patel,Patel Lab,P30-CA014520,8.0,200,ATAC-seq
   2026-03-12,Confocal Microscope,Dr. Sarah Chen,Chen Lab,R01-GM134522,3.0,75,FRAP experiment
   2026-03-13,Mass Spectrometer,Dr. Marcus Brown,Brown Lab,R21-NS112340,3.5,150,Drug metabolite screen
   2026-03-13,Flow Cytometer,Dr. Lisa Yamamoto,Yamamoto Lab,U54-AI170856,2.5,60,Yeast cell cycle
   2026-03-14,qPCR System,Dr. Anika Patel,Patel Lab,P30-CA014520,1.5,50,Copy number assay
   2026-03-14,Confocal Microscope,Dr. James Rivera,Rivera Lab,R01-HG009876,4.0,75,Cleared tissue
   2026-03-15,Illumina NovaSeq,Dr. Sarah Chen,Chen Lab,R01-GM134522,6.0,200,ChIP-seq
   2026-03-15,Mass Spectrometer,Dr. Emily Foster,Foster Lab,T32-GM008349,2.0,150,Student project
   2026-03-15,Flow Cytometer,Dr. Marcus Brown,Brown Lab,R21-NS112340,2.0,60,GFP reporter assay
   2026-03-15,Confocal Microscope,Dr. Anika Patel,Patel Lab,P30-CA014520,5.0,75,Super-resolution

3. SUMMARY CARDS (top of page, update on data load)
   - Total hours used (sum of all Hours_Used)
   - Total revenue (sum of Hours_Used * Rate_Per_Hour for each row)
   - Unique PIs
   - Unique instruments

4. CHARTS (use Chart.js from CDN: https://cdn.jsdelivr.net/npm/chart.js)
   - Horizontal bar chart: total usage hours per PI, sorted descending by hours
   - Pie/doughnut chart: total cost distribution by instrument (show instrument name and
     dollar amount in tooltips, percentage labels on slices)
   - Use these colors for instruments: #38bdf8, #10b981, #f59e0b, #ef4444, #8b5cf6

5. BILLING TABLE
   - Columns: PI_Name, Grant_Number, Instrument, Total_Hours, Rate_Per_Hour, Total_Cost
   - Group rows by PI with a subtotal row for each PI (bold, darker background)
   - Grand total row at the bottom
   - Sortable by clicking any column header (toggle ascending/descending)
   - Format costs as USD with commas ($1,200.00)

6. EXPORT
   - "Export Billing Report" button that opens a new window with a print-friendly layout
   - Include: report title, date range (min/max dates from data), all summary stats,
     the billing table with subtotals, and a signature line at the bottom
   - White background, black text, no charts (tables only for print)

7. DESIGN
   - Dark theme: background #0f172a, cards #1e293b, text #e2e8f0, accent #10b981
   - Clean sans-serif font (Inter from Google Fonts CDN)
   - Responsive layout: summary cards in a 2x2 grid, charts side by side on desktop
     and stacked on mobile, full-width table below
   - Chart containers should have a subtle card background

8. TECHNICAL
   - Pure HTML/CSS/JS in one file, no build step
   - Chart.js loaded from CDN
   - All calculations done in JavaScript: group by PI, group by grant, multiply and sum
   - No rounding errors: use whole cents for currency math

💡Copy-paste ready

That entire block is the prompt. Paste it as-is. The embedded sample data has 32 rows across 5 PIs and 5 instruments with varied rates, giving you enough diversity to verify that grouping, charting, and totals all work correctly.

What you get

After the LLM finishes (typically 60-90 seconds), you will have a single file: billing-dashboard.html. Open it in any browser.

Expected output structure

billing-dashboard.html    (~700-900 lines)

Click Load Example and you should see:

Summary cards showing approximately 127 total hours, ~$17,000-18,000 in total charges, 6 PIs, and 5 instruments.
Bar chart with Dr. Sarah Chen at the top (she has the most hours across confocal, NovaSeq, flow cytometer, mass spec, and qPCR).
Pie chart showing the Illumina NovaSeq dominating cost share (at $200/hr, sequencing accounts for the most total charges even with fewer total hours than the confocal).
Billing table with rows grouped under each PI, subtotals per PI, and a grand total at the bottom.
All costs formatted as USD with dollar signs and commas.

ℹWhat about Excel files?

Usage logs often come from iLab or SUMS as Excel files (.xlsx). Ask your staff to export to CSV before uploading, or add native Excel support as a customization (see the SheetJS extension prompt below in the Customize section).

If something is off

LLMs occasionally produce code with small bugs. Here are the most common issues and one-line fix prompts:

Problem	Follow-up prompt
Charts show but are too small	`The Chart.js charts are rendering at about 50px tall. Set the chart container height to 400px and make sure the canvas elements have responsive sizing with maintainAspectRatio set to false.`
Subtotals are wrong	`The PI subtotal rows are showing the sum of Rate_Per_Hour instead of the sum of Total_Cost. The subtotal should sum (Hours_Used * Rate_Per_Hour) for all rows belonging to that PI.`
Sorting breaks the grouped layout	`When I click a column header to sort, the PI grouping breaks and subtotal rows end up in the wrong place. Sort only within the grouped structure, or re-group after sorting.`

🔧

When Things Go Wrong

Use the Symptom → Evidence → Request pattern: describe what you see, paste the error, then ask for a fix.

Symptom

The pie chart shows every row as a separate slice instead of grouping by instrument

Evidence

The doughnut chart has 32 tiny slices instead of 5 (one per instrument). Each slice shows a single usage session rather than the instrument total.

What to ask the AI

"The pie chart data is not being aggregated before rendering. Group the data by Instrument and sum (Hours_Used * Rate_Per_Hour) for each instrument before passing it to Chart.js. There should be 5 slices: Confocal Microscope, Flow Cytometer, Illumina NovaSeq, Mass Spectrometer, qPCR System."

Symptom

Currency values show as 1200 instead of $1,200.00

Evidence

The billing table and summary cards show raw numbers like 1200 and 750 with no dollar sign, commas, or decimal places

What to ask the AI

"Add a currency formatter using Intl.NumberFormat('en-US', { style: 'currency', currency: 'USD' }). Apply it to all cost displays: summary cards, table cells, subtotals, and the grand total."

Symptom

Chart.js charts are blank white rectangles

Evidence

The page loads with cards and the table, but both chart areas are empty white boxes. Browser console shows 'Chart is not defined'.

What to ask the AI

"Chart.js is not loaded before the chart code runs. Wrap all chart creation code in a window.onload handler or put the script tag for Chart.js in the head with a load callback. Also add an onerror handler on the CDN script tag."

Symptom

The bar chart shows PIs in alphabetical order instead of sorted by hours

Evidence

Dr. Anika Patel is at the top of the bar chart even though Dr. Sarah Chen has more total hours. The bars are in A-Z order.

What to ask the AI

"Sort the PI data by total hours descending before passing it to the bar chart. The PI with the most hours should be at the top of the horizontal bar chart."

How it works (the 2-minute explanation)

You do not need to read every line of the generated code, but here is the mental model:

CSV parsing reads each line, splits by commas (respecting quoted fields), and maps each row to an object with named properties. Hours_Used and Rate_Per_Hour are converted to numbers.
Aggregation uses JavaScript objects as accumulators. For the PI bar chart, it iterates through all rows and sums Hours_Used by PI_Name. For the instrument pie chart, it sums Hours_Used * Rate_Per_Hour by Instrument.
Chart.js takes the aggregated arrays and renders them. The horizontal bar chart uses type: 'bar' with indexAxis: 'y'. The doughnut chart uses type: 'doughnut' with percentage labels.
The billing table groups rows by PI, calculates subtotals, and appends a grand total. Sorting re-groups the data and re-renders the table.

🔍For Financial Admins: Reconciliation and audit trails

This dashboard produces a summary, not an invoice. It is a tool for checking your numbers before they go into iLab or the university billing system. If the dashboard total does not match your booking system total, you know something was logged incorrectly. Use it as a reconciliation step: export the print report, attach it to your monthly billing submission, and you have a secondary audit trail that took zero extra effort.

Customize it

The base dashboard covers monthly billing. Here are extensions that make it more powerful:

Add a date range filter

Add a date range picker at the top of the dashboard with "Start Date" and "End Date"
inputs. When the user selects a range, filter the data to only include rows within
that range and recalculate all summary stats, charts, and the billing table. Default
to the full range of dates in the uploaded data. Add "This Week", "This Month", and
"All" quick-select buttons.

Add an editable rate schedule

Add a "Rate Schedule" panel that shows a table of instruments and their rates per hour.
Let the user edit the rates inline (click a rate to change it). When a rate is changed,
recalculate all costs in the billing table and charts using the new rate. This is useful
for comparing internal vs. external rates or modeling a rate increase.

Add Excel (.xlsx) support

Add support for uploading Excel files (.xlsx) in addition to CSV. Use SheetJS from
CDN (https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js) to parse
the workbook. Read the first sheet, convert it to an array of arrays, and feed it
into the same parsing pipeline. If the file has multiple sheets, show a dropdown
to select which sheet to use. Keep CSV support working as before.

Generate per-PI invoices

Add a "Generate Invoices" button that creates a separate printable invoice page for
each PI. Each invoice should include: PI name, lab, grant number, a table of their
individual usage sessions (date, instrument, hours, rate, cost), and a total amount
due. Format it like a professional invoice with your facility name at the top, an
invoice number (auto-generated from PI initials + month), and payment terms. Open
each invoice in a new tab.

ℹInternal vs. external rates

Most core facilities charge different rates depending on the user’s affiliation. A confocal microscope might be $75/hr for internal (campus) users, $112/hr for affiliated institutions, and $150/hr for external/industry users. The sample data above uses a single rate column for simplicity, but real billing requires a user classification. The extension prompt below adds this.

Add internal/external rate classification

Add a User_Classification column to the data model with three values: Internal,
Affiliated, and External. Create a rate schedule table that shows different rates
per instrument for each classification. When calculating costs, use the rate that
matches the user's classification instead of the flat Rate_Per_Hour column. Add a
validation highlight for any rows where the logged rate doesn't match the expected
rate for that classification — flag those in yellow so the billing admin can review
whether it was a courtesy rate, a data entry error, or a negotiated exception.

ℹThe customization loop

Start with the working dashboard, then add features one prompt at a time. Date filtering is usually the highest-value addition because it lets you run mid-month checks without waiting for the full export. Rate editing is second — it lets you model “what if we raise the NovaSeq rate to $225/hr?”

Worked example: Finding billing discrepancies

Here is a real-world scenario. Your booking system (iLab) shows $14,200 in charges for March, but the accounting office says they received $13,800 in charge-back requests. Where is the $400 discrepancy?

Step 1. Export the usage log from iLab as a CSV.

Step 2. Drop it on your billing dashboard. Check the grand total — it should match the iLab report.

Step 3. Sort the billing table by PI. Compare each PI subtotal against the charge-back requests the accounting office has on file.

Step 4. When you find the PI whose subtotal is $400 higher than what was submitted, check their individual sessions. Look for sessions that might have been logged but not charged — training sessions, courtesy hours, or sessions that were cancelled but not removed from the log.

This is exactly the reconciliation task that the dashboard was built for. Without it, you would be scrolling through a 500-row spreadsheet trying to find a $400 needle in a $17,000 haystack.

ℹGrouping by grant vs. grouping by PI

Grouping by PI tells you who used the most resources. Grouping by grant tells you which funding sources are being spent. Both matter. If one grant is consuming 80% of the facility’s time, that is useful information for the PI (they may be burning through their budget faster than expected) and for the facility director (capacity planning).

Try it yourself

Open your CLI tool in an empty folder.
Paste the main prompt from above.
Open the generated billing-dashboard.html in your browser.
Click Load Example to see the charts and billing table populate.
Check that the grand total matches a manual sum of a few rows (spot-check the math).
If your facility exports usage data as CSV, drop a real file on the dashboard and see if the grouping works.
Pick one customization from the list above and add it.

If the dashboard works with your real data, save it. Bookmark it on the billing workstation. Share it with your financial admin. You just replaced the worst spreadsheet in your facility with something you can trust.

Key takeaways

One prompt, one tool: a detailed prompt with 32 rows of embedded sample data produces a working billing dashboard in under 2 minutes.
Aggregation is the core operation — grouping by PI and grant, multiplying hours by rates, and summing. Getting this right matters more than any visual polish.
Chart.js handles the visualization with minimal configuration. A horizontal bar chart and a doughnut chart cover 90% of billing reporting needs.
The billing table with subtotals is the most important output. Charts catch trends, but the table is what gets printed and sent to accounting.
Currency formatting matters — $1,200.00 is professional, 1200 is ambiguous. Using Intl.NumberFormat handles this correctly and consistently.

KNOWLEDGE CHECK

Your dashboard shows Dr. Chen's total cost as $4,870 but your booking system shows $4,820. What is the most productive way to find the discrepancy?

KNOWLEDGE CHECK

The pie chart shows the Illumina NovaSeq represents 55% of total facility charges despite only accounting for 30% of total usage hours. Why?

What’s next

In the next lesson, you will build a Mouse Colony Breeding Planner — a different kind of core facility tool that tracks litters, weaning dates, and Mendelian inheritance expectations. Same pattern (showcase, prompt, output, customize) but focused on animal husbandry data instead of billing.

Usage & Billing Summary Dashboard

What you'll learn

What you’re building

Who this is for

The showcase

The prompt

What you get

Expected output structure

If something is off

When Things Go Wrong

How it works (the 2-minute explanation)

Customize it

Add a date range filter

Add an editable rate schedule

Add Excel (.xlsx) support

Generate per-PI invoices

Add internal/external rate classification

Worked example: Finding billing discrepancies

Try it yourself

Key takeaways

What’s next