Investigate strategies for weekly and monthly planning automation

[hancush]

Background

Ported from https://github.com/datamade/devops/issues/126.

Proposal

tl;dr - We'd like to automate one or more of several components that require manual data entry from project managers:

1. Listing projects
2. Aggregating time for projects
3. Copying spreadsheets over from week to week

Deliverables

Per @derekeder in https://github.com/datamade/devops/issues/126#issuecomment-678540844:

The first task will be to get an accurate listing of projects. In working with freshbooks data, we have some stale and duplicate projects that we would need to clean up. I think @fgregg's project_budget repo would be a good place to start and build on top of it since it has a lot of the scaffolding with the Freshbooks API done already.

I'd start by modifying that script and replicate the list of projects we have active in our weekly planning spreadsheet, merging and archiving projects in Freshbooks as needed.

Timeline

tk.

[hancush]

IMO, we're in an ok situation with weekly planning. Some recent SNAFUs include formulas not updating when we add/remove people from the spreadsheet, and of course, adding time from the prior week requires a couple of minutes of manual data entry, which is annoying but not too hard thanks to the time entry report in FreshBooks.

Monthly planning is a bigger challenge.

Meeting outcomes:

• Revenue
  • Estimate billable time for the month (@derekeder uses these estimates at the end of the month to compare estimates vs. actual amounts invoiced)
  • Schedule and pace project work such that we're using / billing as much of our weekly capacity as possible while also meeting deadlines
  • Identify and mitigate when projects are at risk of running over budget
  • Ensure we have enough, but not too many, projects in the pipeline
• Staffing
  • Ensure all roles are filled on projects in progress
  • Ensure developers are staffed to enough projects
  • Ensure we have enough, but not too many, projects in progress

Some frictions:

• Summing remaining hours on our projects is a pain in the ass because we have a practice of creating a project for each scope item. This is useful during development, but it makes it a lot harder to get a bird's eye view of the project.
• We have a loose color code for projects we're proceeding with vs. pausing on, but it treats major development the same as small hours investments (slower moving projects or small follow-on work), so it is a pretty noisy indicator of how many projects we're juggling at once.
• The monthly planning spreadsheet gives us a good indication of whether we have enough time scheduled overall, but not necessarily on a per-person basis.
• The staffing spreadsheet is brittle: The project list, but not the staff assignments, updates with the Gantt chart, which means they can get out of whack. The calculated values don't auto-update with the staff assignments.

@derekeder, @fgregg – Anything to add about either meeting? Also interested in what about monthly planning is working well, from your perspective.

[derekeder]

@hancush thanks for writing this up. I think its a good summary.

I agree with your overall assessment that the monthly planning spreadsheet generally does what we need it to for staffing allocation, prioritizing projects and predicting our monthly revenue.

I think the points of friction you listed are right, though i don't expect any system we use to be perfect. It does reinforce the idea that there would be some benefit in completing the script we discuss in this thread to help out with some of those frictions.

I also would like to start the practice of reviewing the hours/invoicing for the previous month and review if we met our projection or not (and if not why). We have stopped doing this at weekly planning due to similar frictions described here, but it's something I already collect as part of the invoicing process now, and so is an opportunity for refining our estimates.

[hancush]

Project budget has been revised to give us instructive views of both project cost and hours for planning purposes. Will work with partners to determine next area of automation focus.

[hancush]

• Derek opened issue on project budget re: estimates
• Possibly add ability to automate summing of Parserator sales per month.

[hancush]

Average rate added to project budget in https://github.com/datamade/project_budget/pull/7.

[hancush]

Invoice summaries added in https://github.com/datamade/project_budget/pull/8.