I will take a look at this because this is my area of expertise but it's going to take a little time. You are asking for a lot here.
You are going to need to be much more specific here. Where do you want to add tasks, and what do you want auto-populated?
Are you saying that you want to automatically update graphical diagrams based on some data inputs? I wouldn't say it's impossible, but that is an enormous effort in Excel and I would go so far as to say it's prohibitive. It's like developing a whole application.
Honestly, you are trying to do something by cobbling it together in Excel when there are professional applications out there to do specifically this, such as Microsoft Project, Primavera, and Deltek.
Also I see nothing in here that tries to implement EVMS, only record raw inputs such as task data. There is no reporting.
Also in Budget, column K, your calculation of % complete is not correct. You cannot take an average of several percentages to get an overall percentage, unless each percentage has equal weight. That is, suppose you have an activity with two subtasks. If Joe is 10% done with a subtask that is 100 hours, and Frances is 90% done with a subtask that is 5 hours, your task is not 50% done.
You seem to be using % complete as the method to earn value for every task. However, this is rarely a realistic way to measure completion as it almost always involves subjective judgement by the person who is doing the task, and that person is almost always going to overestimate completion. Methods for earning value are:
- Milestones
- Percent complete
- 50/50
- 0/100
- Equivalent Units
- Apportioned effort
A method that gives credit only when some atomic task can be objectively judged as complete is the closest to reality. For example, let's the say the task is that a piece of software must be tested. If you ask the tester for a % complete they might count test cases executed, but it's more reliable to carry it as 0% until all test cases are complete then go to 100%. This is because the tester may not have an objective way to measure how much work was done and how much remains. Even if they count test cases, different test cases require a different amount of effort. Also encountering bugs in the code will slow down testing, which cannot be predicted before running the tests.
I have managed projects for contracts for the U.S. federal government that use EVMS over the last 35 years. You are biting off an awful lot to chew here.
Bookmarks