[SOLVED] Project Summary (1-2 sentences):

Project Summary (1-2 sentences):
Develop an automated process solution that allows managers and team members to manage and forecast staff availability (supply) against work demand.
Solution is simple and easy to understand and use.
Solution adjusts as supply, demand, parameters/key factors, and supply and demand inputs change.
Based on the supply and demand inputs, the solution should be able to produce visualization (preferably a dashboard) that allows management and team members to make decisions around scheduling resources and work activities.
Questions we are trying to answer through quick and easy visuals:
What work is due in a specified timeframe?
What does each staff member have due in a specified timeframe?
Who is assigned to a particular project/work task?
What work is on a persons plate?
What assignments are assigned by role in a specified time period?
What projects/work is in progress for a specified period?
Who is under allocated (i.e. more work can be assigned)?
Who is over allocated (i.e. has too much work assigned)?
When will a specified staff member have availability to take on additional work?
Who is available to work on a given work unit (project, operational, or ad hoc) with the desired skillset in a specified timeframe?
Are we on track to finish a project/work task on time (taking into account all that is on the designed plates)?
What phase are we in for a project (i.e. project status?)

Supply Staff availability (Resources):
Solution should allow a designated number of weekly working hours to be assigned to each staff member.
Solution should allow identifying roles/skillsets for each team member so that specified skillsets needed can be identified to contribute to suppling a body of work.
Solution should calculate the hours each staff member has available to be assigned work based on key factors that contribute to staff availability and key factors that utilize (reduce) staff availability.
Solution should accommodate staff working on multiple and simultaneous work activities and to unique projects.
The solution should allow for sourcing a team to work on a body of work.(Teams working on a project consist of 1-6 members but should be flexible enough to allow for more team members.)
Solution should accommodate providing resources/staff members who are partially dedicated to project(s).
Work should be able to be assigned based on skillsets.
A manager should be able to easily view their staffs resource availability.
A team (could be multiple manager) should be able to easily view their staffs availability.
Details Supply:
Key Factors that contribute to supply (resource availability):
When calculating the hours available by resource, the solution should take into account the following time that is available for project, operational, or ad hoc work:
Working hours:
The number of hours a staff member normally works must be changeable (not hard coded).[Most everyone is 40 hours a week (default) but some staff members work an alternate schedule, such as 30.]
Skillsets
The solution should allow skillsets to be associated with each staff member.
The skillset list should be flexible so that skillsets can easily be added, deleted, and modified.
Multiple skillsets should be easily associated with each staff member.

Key Factors that reduce supply (staff availability):

When calculating the hours available by resource, the solution should take into account the following time that reduces the availability for a staff member to be available for project or ad hoc work:

Paid Time Off (PTO):
PTO Expected for the year The solution should allow each person to enter the number of their specific annual PTO that they will generally be unavailable to work.(Note:Staff has an annual allotment of hours and can carry-over a maximum of 120 hours a year.)
Scheduled PTO The solution should allow for PTO to be designated by date(s) to show specific dates (or date range) when a staff member has scheduled time off and they are specifically unavailable to work.
Dates specified for scheduled PTO should be unavailable for work assignments (8 hours unless specified otherwise).
Scheduled PTO should be easily changed.
Unscheduled PTO
Unscheduled PTO is calculated by the annual PTO (minus) the scheduled PTO.
Unscheduled PTO anticipated to be taken for the year should be unavailable for work assignments.
For scheduling purposes, the unscheduled PTO hours should be reflected at the end of the year. Decide:Is there a more effective way to accommodate unscheduled PTO so that realistic analytics can be performed (such as splitting unscheduled PTO amongst the remaining months of the year)?
Holidays:The following holidays should reduce available resource time by 8 hours (unless specified otherwise below):
New Years Day
Martin Luther King, Jr. Day
Presidents Day
Memorial Day
Independence Day
Labor Day
Columbus Day
Veterans Day
Thanksgiving Day
Christmas Eve (only 4 hour work day)
Christmas Day
New Years Eve (only 4 hour work day)

Admin time A percentage of the work week designated for administrative type activities (reading email, etc.) should reduce available resource hours.
There should be a field for management to set the expected administrative percent of the weekly working hours (such as 20% or 25%), that is not available to work on projects, operational, and/or ad hoc work.

Other time out
The solution should allow for days that a staff member is out for various reasons other than the above (training, FMLA leave, military leave, death in family, etc.)
The solution should adjust the schedule as any other time out is specified.

Parameters/Key Factors Staff availability:
Name
Manager
Team (There are currently 4 teams but the tool should be flexible to allow for more teams (current teams can be called teams E, N, P, A)
Each staff members # working hours work a week
Roles/skillsets (each staff member may have multiple roles/skillsets)
Paid time off (PTO) input per staff member) hours
PTO for the year
PTO scheduled on specific days
PTO unscheduled can be used throughout the year (calculated:PTO for year PTO scheduled for specific days = unscheduled PTO )
Holidays (8 hours a day unless otherwise noted above)
Admin time percentage
Other time out (training, FMLA leave, military leave, death in family, and other.) (8 hours a day)

Calculation of supply
Staff availability for a week = # working hours work a week holiday PTO scheduled training scheduled other time out (admin% x # working hours work a week )

Demand on staffs time
High level
Types of Demand on staffs time include the below.
Types of demand should be easily changed (i.e. not hard coded)
The solution should allow users to assign tasks to people
The solution should allow users to assign people to various types of work.
The solution should allow the ability to schedule tasks based on parameters and key factors.
As staff members change, the solution should easily allow for changing the team members and projects/work tasks.
The solution should easily allow for adjusting the schedule.
Staff includes teams who are assigned to multiple and simultaneous work activities and to projects.
Projects may vary from approximately 8 hours to approximately 2000 hours with a duration of a day to many months.
Teams The solution should be able to help managers and staff to manage and forecast staff availability given the following:
Accommodate multiple teams.(Currently 4 teams:E, N, P, & A)
Each team should be allowed to have more than one manager associated with the team.
Each manager should be allowed to have designated staff associated with the team.
When deciding on supply for a particular body of work (project, ongoing, etc. as noted in the demand section), the available supply should default (or be easily viewed) to the staff working for the manager.
When a manager is reviewing resource supply to meet demand, the list should default (or be easily selected) to a filtered view of their own staff.
The available resource supply to meet demand should be easily viewed by the team assigned the type of work.
Supply for a particular body of work (project, operational, ad hoc, etc.) as noted in the demand section) should be able to be staffed from multiple teams, if needed.
Teams working on a project may consist of 1-6 members who are partially dedicated to a project but the solution should be flexible to allow for larger teams.
The list of types of work should be flexible so that the list can easily be changed.

Detail level by type of work
Project (A temporary endeavor undertaken to create a unique product or service.Has beginning and end.)
The solution should provide the ability to specify a project start date and end date.
Solution should provide the ability to specify a project due date.
Solution should allow people to be assigned to projects.
The solution needs to allow for projects to be added, modified, and deleted.
Projects typically have phases.The solution should allow for the input of the phases in order to track the project status.
Audit projects have planning, fieldwork, reporting, and follow-up phases.
Team A projects have requirements elicitation, create a solution, QA testing phases, UAT testing, training, publish solution, and lessons learned.
The solution should allow for other types of phases to be added. (i.e. not hardcoded)
The solution should allow an hour estimated/budgeted for the project work.(Projects may vary from 8 hours to approximately 2000 hours with a duration of a day to many months.)
The solution should use the estimated hours for project work to reduce a staffs available time in the specified time period [month(s), quarter, year] by the estimated hours.
Should have the ability to prioritize projects
Priorities are:high, moderately high, moderate, or low
For ease of use, should have ability to easily change the prioritization names]
A projects priority can change
Not required to use prioritization.
Solution should allow for selecting the types of roles needed in each role.
Roles should be easily added, modified, or deleted (i.e. not hard coded)

Parameters/Key Factors Project:
Team (E,N,P, A)
Manager
Person assigned
Type of work (project)
Prioritization
start date
end date
due date
effort (hours) to complete a project/task
Estimated hours (or percentage of time) per week to work on project (allow data entry option either by month or specific dates)
Duration of time (difference between start date and end date and number of hours to complete).
Phase (see above)
Audit phases are planning, fieldwork, reporting, follow-up phases. (Should be able to add other phases, as needed.)
Team As projects are requirements elicitation, create a solution, QA testing phases, UAT testing, training, publish solution, and lessons learned phases.(Should be able to add other phases, as needed.)
Estimated hours (audits should be able to estimate hours by phase and role)
Roles needed for the project :
Audit roles (team E, N, & P) are EM, AIC, staff, and others noted above.Roles should be flexible to add other roles (such as staff- IT, etc.)
# of staff needed to fulfill each role
Estimated hours for each audit phase (or would percentage of time work?)
Team A roles are BA and BIA and should be flexible to add other roles (such as BIA SQL, etc.)
# of staff needed to fulfill each role
Estimated hours for each phase [not required to be provided by the user]
Dependency Project:
Staff availability of roles identified as needed on a project

Operational activities (Standing meetings and ongoing work.The work can be performed at various frequencies (daily, weekly, monthly, quarterly, yearly, etc.)(Examples include reporting, workgroups, QAIP, etc.)
The solution should allow the user to add the frequency of the operational work (weekly, monthly, quarterly, semi-annually, or annually).
The solution should use the estimated hours for operational work to reduce a staffs available time in the specified time period [month(s), quarter, year] by the hours input for operational/recurring work/meetings.
The solution should allow operational work to be added, modified, and deleted.
Solution should allow the ability to designate a specified number of hours in a specified time period that the staff member is estimating to dedicate to operational work.(Staff are assigned to multiple and simultaneous work activities that need to be taken into account in scheduling.)[not required to be completed by the user assess impact to questions cant answer without this information]
Recurring standing meetings (Each team member should be able to account for the number of hours planned for standing meetings that occur on a regular basis.) (weekly, bi-weekly, monthly, quarterly, semi-annually, and annually). [not required to be completed by the user assess impact to questions cant answer without this information]

Parameters/Key Factors Operational work demand:
Team
Manager
Person assigned
Type of work (operational)
Prioritization (high, moderately high, moderate, or low) [to make easy to use, should have ability to easily change the prioritization names]
start date
end date
due date
effort (hours) to complete
Estimated hours (or percentage of time) per week to work on project (allow data entry option either by month or specific dates)
Frequency operational activity takes place (daily, weekly, bi-weekly, monthly, quarterly, semi-annual, yearly, etc.)[should be able to add other frequency options]

Ad hoc work (A special request to accomplish an objective.Ad hoc work could have a short or longer duration.Examples include preparing presentations, SCAD work, etc.)
The solution should allow ad hoc work to be added to the schedule.
The solution should provide the ability to designate a start date and end date.
The solution should allow ad hoc work to be added, modified, and deleted.
Solution should allow the ability to designate a specified number of hours in a week that the staff member is estimating to dedicate to ad hoc work.(Staff are assigned to multiple and simultaneous work activities and to unique projects that need to be taken into account in scheduling.) [not required to be completed by the user assess impact to questions cant answer without this information]
The solution should use the estimated hours for ad hoc work to reduce a staffs available time in the specified time period [month(s), quarter, year] by the estimated ad hoc hours.

Parameters/Key Factors Ad hoc work demand:
Team
Manager
Person assigned
Type of work (ad hoc)
Prioritization (high, moderately high, moderate, or low) [to make easy to use, should have ability to easily change the prioritization names]
start date
end date
due date
effort (hours) to complete
Estimated hours (or percentage of time) per week to work on project (allow data entry option either by month or specific dates)

Background information for demand:
Can use historical time to get an idea past demand.
Project demand is captured in Microsoft Excel spreadsheets.

Visualization (preferably a dashboard):
Demand Due:Display work with due dates in a specified period
Allow views by:
Team (E,N,P,A)
Manager
Person
Role & skillset
Type of work
Due date
Time period Allow user to select month(s), quarter, year

Demand in Progress:Display work expected to be progress in a specified period
Allow views by:
Team
Manager
Person
Project/work
Project phase
Role/skillset
Time period Allow user to select month(s), quarter, year