Working in consulting there are many times you're inspired to eat your own dog food. By that I mean to take a look at your own processes and see if you could be doing things more efficiently. Tasked with managing multiple projects, I saw a need for a quicker way to spin up a project plan and to gain better visibility on if my projects would come in on time and under budget. Although I'd built simple macros before, this was a deep dive into creating a full tool. It was a great learning experience but took about six months to learn how to write in VBA and to refine the tool to what it is today.
Generator Output
What does it do?
Basically it creates the image you see above. A better Gantt chart in Excel that generates with just a few inputs. This allows you to spend less time planning by simplifying some of the assumptions you're making anyway as part of project planning. Additionally, the Gantt chart isn't static, everything is meant to be modifiable allowing for clear visibility into project progress and quick identification of project risks (past schedule, over budget). The idea is that on a weekly basis you're checking how many hours are left on your project, inputting the data in the "hours left" cell, updating the "% complete" cells for each task, and inputting the "Actual hours" each task took as they complete. Essentially, everything in Purple is what you'll be modifying on a weekly basis. This again was intentionally simplified over time to make it easy to quickly jot a few items and get an at a glance view at project progress.
How does it work?
When it comes to planning a project, there are countless variables, most of which you can't control. Despite this, we have to work with the most concrete data we have so we can get moving. This inspired me to create a tool that takes as few inputs as possible to plan out a project week by week. These variables are:
A client or project name. In this case, Bill's Tires
An engagement type. We'll talk later on about how this works but it's essentially pulling the set tasks and hour estimates for those tasks from a separate sheet
How many hours assigned you have to spend on the entire project
Start date and end date. When the project kicks off and when it will end
The Backend
So what powers the backend, how does the program know the tasks and the amount of hours it should assign for tasks? The key is backend sheets which we'll have to pre-populate at first but which will save us huge amounts of time later on:
Backend Project Sheet
For each project type, you'll need to copy and paste a template I've created, input your tasks, hour estimates, and day estimates. Although this seems to fly in the face of simplifying things, this is meant to be a do it once and save a lot of time later on type exercise. Within the sheet you'll simply need to break out the tasks required to accomplish the project and then input how many hours the task will take assuming everything went perfectly (Opportunistic), assuming everything went as planned (Realistic), and assuming things went off the rails (Worst Case). These three inputs feed into a PERT result. PERT is a "Program evaluation and review technique", essentially it gives a weight to your estimates for each task to arrive at a realistic estimate of how long the task might take. The backend is using this PERT result to calculate the percentage of total hours it can assign to each task. So if you divide 100 hours across 10 tasks with one task taking 20 hours, that task will get 20% of the total hours inputted as a variable. Finally, you'll need to input the amount of days you estimate each task will take. The generator will use this input to figure out how to allocate the set amount of time you've given it as an initial input (Start Date and End Date). So if you tell the generator you have 90 days to accomplish a project, it will calculate the percentage of time given to a task to calculate how many hours it can give that task and across what timeframe.
Although this can seem daunting, all these steps are summarized and available within the input sheet of the generator. This makes it easy for even an inexperienced user to pick up the workbook, add in a new project, and quickly generate a project plan. I've tested this by providing the workbook to other consultants and checking in a few days later to see if they had any issues, happily I've got none to speak on.
Summary
The Project Generator is something I'm proud of, not just because it works but because I was able to stick with it for such a long period of wanting to just give up on it. I use my own tool for all my projects and I've started to show others its' benefits. This has led to additional iterations as I take that feedback to further improve the generator. Like most software it likely won't ever truly be done but that's the point isn't it? To be truly useful it has to grow and adapt with the needs of its consumers. I'm not proposing I invented an IPhone but I saw a problem, learned how to solve it, and after probably a hundred or more iterations, it works, and it's useful.
If you're interested in trying the Project Generator for yourself or if you have some feedback on how the tool could be further improved please reach out via Contact in the header.
