Clinical Trial Site Budgets: Per-Patient Costs, Visit Schedules, Screen Failures, Invoiceable Items & Payment Holdbacks

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Value the staff minutes of four visit types with each role's loaded hourly cost, add the site's overhead, round the result to a quotable visit fee and see how much of a visit's cost the investigator's time drives.
• Turn the sponsor's visit schedule into a payment for every visit with SUMPRODUCT, total the payment per completed patient, compare it with the site's cost visit by visit and find the margin per patient.
• Build the training and archiving costs from their drivers, compare every fixed start-up and close-out line with the sponsor's offer, and separate the net gap from the shortfall the site should negotiate.
• Work backwards from the completed-patient target through the completion and screen pass rates, round up at every stage, and turn the screenings needed into months of screening and an end date.
• Apply a fictional cap on paid screen failures with MIN and INT, pay withdrawn patients only for the visits they attended with SUMIFS, measure the revenue lost through withdrawals and total what the site has really earned.
• Total the extras that are still to be invoiced, isolate the ones older than 60 days, find the age of the oldest open item with MINIFS and report one month's items with two date criteria and EOMONTH.
• Split each quarter's earnings into the holdback and the amount payable now, date the payments with WORKDAY, measure what the site has earned but not yet received, and date the holdback release with EDATE.
• Build the study P&L from per-patient and fixed lines, calculate the total margin and margin %, find the break-even number of completed patients and chart revenue against cost by category.