I need an easy way for ~25 staff to regularly submit their allocation of time (as a % of their total time) across ~15 project objectives. The ideal form would show them how they have allocated their time historically and have them input their current allocation. When they're done, the new allocation should be saved to a spreadsheet that maintains a running list of all the staff allocations over time. Ideally this spreadsheet would reside on sharepoint worksite. This spreadsheet would have the following columns:
Date covered, staff name, project objective, % of FTE, Notes about particular tasks
Time not allocated by a staff member to a specific project objective should be automatically allocated to "other." Staff should not be able to allocation more than 100% of their time. In addition to picking objectives and allocating a percentage to each of those, staff should be able to add note on particular tasks they will be doing/have done with each given percentage of their time. This should just be a text field no more than 250 characters.
The staff names and the project objectives must be able to be updated easily. Ideally they would draw from lists saved on the same sharepoint worksite as the spreadsheet.
The historical allocation shown on the form should pull from the spreadsheet. It would be nice to have staff able to specify whether they are shown just their most recent allocation or date range of they specify. If that's not possible they should be shown just the most recent. It would also be nice if the historical allocation was presented as pie chart, but a simple table would suffice.
Most staff will allocate their time across 5 or fewer objectives, but they should be able to choose up to 10 if a limit is important.
The form must be easily distributed by email or be able to reside on the sharepoint worksite where staff could be directed to visit it.