- Prepare a budget for clinical trials based on a specific protocol, for example, drug will be used 1mg per day per patient. The protocol may be changed in the future so make sure your worksheet is flexible enough to be easily modified.
- Begin your budget in a new workbook named TrialsBudget_Your name.xls.
- Prepare three identically formatted sheets, one for each year. Name your sheets Year1, Year2, and Year3.
- Each sheet needs to contain the expenses for each of the items listed in the table below. Expenses should be calculated for each of the twelve months and the yearly total. All expense values should be rounded to the nearest dollar.
| Expense Item | Cost |
| Manufacturing and Distribution | $0.25 per milligram |
| Participant Fees | $25 per month per patient |
| Physician Fees | $1200 per patient per year |
| Blood Testing | $35 per participant per test (quarterly: January, April, July, October) |
| Urine Analysis | $5 per participant (bimonthly: January, March, May, etc.) |
| Administration and Overhead | $10,000 per month |
- To create this budget, you also make the following assumptions:
- Assume that there will initially be 1000 participants with an attrition rate of 5% per year (thus, year two’s budget is based on 950 participants). This value should be explicitly listed on the top portion of each year’s budget.
- Assume that each year has 365 days, and each month contains the corresponding number of days (e.g., January 31, February 28, etc).
- Assume that physician fees and blood and urine test fees increase approximately 5% per year.
- Assume all other fees will remain the same.
- For calculation purposes, assume that the average daily dosages per person per day match those of your selected protocol.
- Generate monthly and yearly totals for each expense item. Include a grand total for all expenses in each year.
- After the budget for each year is complete, add a fourth worksheet title Summary. This worksheet needs to contain the yearly total columns from each of the three years. Create 3-year totals by expense item. Include a grand total for the three years of expenses. Reference the values on the yearly worksheets, so any updates to the expenses for a given year are immediately reflected on the Summary worksheet.
