Skip to main content

Annual Toggl Plan v Actuals Report

Purpose

This SOP outlines the procedures for creating and maintaining the annual Toggl Report, which tracks time and costs across all DORIS Research projects. The report compares planned hours to actual hours worked, calculates project costs, and provides phase-level analysis to inform project management decisions and identify patterns in how DORIS allocates time across project phases.

Responsible Party

Operations Manager

Interdependencies

This workbook relies on and feeds data to:

  • Toggl Track time tracking system: Source of all time entry data
  • The Black Box: Used to populate Plan and Plan to Date columns
  • Cost to Run DORIS file: Cost data
Directions

Part 1: Creating the All time entries this year Tab

  1. Export data from Toggl Track: Log in to Toggl Track and navigate to the Reports section. Set the date range to the full year (e.g., 01/01/2025 - 12/31/2025). Export the detailed time entries report, which should include: Project, Task, Description, Member, Duration, Start date, Stop date, Start time, Stop time.
  2. Set up the worksheet: Create a new worksheet named 'All time entries this year'. Add a title in cell B2: 'All time entries this year'. Add the period in cell B4 using the format: 'Period: MM/DD/YYYY - MM/DD/YYYY'.
  3. Create column headers in row 6: Starting in column B, create headers: Project | Task | Description | Member | Duration | Start date | Stop date | Start time | Stop time | Hours | Hourly Rate | Cost
  4.  Paste Toggl data: Starting in row 7, paste the exported Toggl data into columns B through J (Project through Stop time). Ensure dates are formatted consistently and time durations are in H:MM:SS format.
  5. Add the Hours formula: In column K (Hours), starting at K7, enter the formula: =F7*24. This converts the duration from Excel's time format to decimal hours. Copy this formula down for all time entries.
  6. Add hourly rate: In column L (Hourly Rate), enter the standard hourly rate for each team member. Based on the data, DORIS uses $39.90 per hour. Enter this value for all rows.
  7. Add the Cost formula: In column M (Cost), starting at M7, enter the formula: =K7*L7. This calculates the cost for each time entry by multiplying hours by hourly rate. Copy this formula down for all entries.

Part 2: Creating the Report Tab

8.     Set up the header: In cell A1, enter the title: '2025 Toggl YTD (through [date])' where [date] is the date through which the report is current.

9.     Create project summary column headers in row 3: Create headers: Project | Plan | Plan to Date | Actual | Actual Cost | Remaining Hours | % to Plan to Date | Notes

10. List all active projects: Starting in row 4, column A, list each active project by name. Include both standard research projects and specialized usability projects. List projects alphabetically or by priority.

11. Enter Plan hours: In column B (Plan), enter the total contracted or budgeted hours for each project. For projects with multiple phases, you may use formulas that reference other projects or calculate remainders (e.g., '=1162-B7' for Citizens Energy which has a usability component).

12. Enter Plan to Date hours: In column C (Plan to Date), enter the hours that should have been completed by the report date based on the project timeline. For completed projects, this equals the Plan. For in-progress projects, calculate based on milestones reached.

13. Add Actual hours formula: In column D (Actual), enter this SUMIF formula: =SUMIF('All time entries this year'!$B$6:$B$3222,Report!$A4,'All time entries this year'!$K$6:$K$3222). This sums all hours from the time entries sheet where the project name matches. Adjust the row numbers based on your data size.

14. Add Actual Cost formula: In column E (Actual Cost), enter: =SUMIF('All time entries this year'!$B$6:$B$3222,Report!$A4,'All time entries this year'!$M$6:$M$3222). This sums the cost column for each project.

15. Calculate Remaining Hours: In column F (Remaining Hours), enter the formula: =C4-D4 (where 4 is your first data row). This calculates how many hours remain from the Plan to Date.

16. Calculate percentage to plan: In column G (% to Plan to Date), enter: =D4/C4. Format this column as a percentage to show what proportion of planned hours have been used.

17. Add notes: In column H (Notes), manually enter any relevant context about variances, scope changes, or project-specific considerations that explain why actual hours differ from planned hours.

Part 3: Creating the Phase Analysis Section

18. Add section header: After all project rows, skip a few rows and add a section header (e.g., around row 32): 'Phase totals excl PCRHS and PFR Regional Wabash Valley'. Adjust the exclusions based on which projects have unique structures that would skew averages.

19. Create phase column headers: In the next row, create headers: Phase | Cost / Hr | Plan to Date | Actual | Actual Cost

20. List all phases: List each DORIS research phase in order: 0 :: PM & Logistics, 0 :: R&D, 1 :: Project Launch, 2 :: Orientation, 3a :: Challenge Seeking Prep, 3b :: Challenge Seeking Execution, 4a :: Challenge Defining A/S, 4b :: Challenge Defining Writing, 4c :: Challenge Defining Making, 4d :: Challenge Defining Meeting Prep, 4e :: Challenge Defining Meeting Execution, 5a :: Solutioning Session Prep, 5b :: Solutioning Session Execution, 6a :: Solution Defining A/S, 6b :: Insight Report Writing, 6c :: Insight Report Making, 6d :: Solution Defining Meeting Prep, 6e :: Solution Defining Meeting Execution, 7 :: Project Conclusion, 8 :: Post Findings Action Planning, 8 :: Travel Time, 8a :: Buffer Box, and any usability-specific phases like 3a :: Usability Prep, 3c :: Usability Prep, 3b :: Usability Execution, 3d :: Usability Execution.

21. Add Plan to Date manually: In the Plan to Date column, manually enter the budgeted or expected hours for each phase across all projects. Leave blank for phases not yet reached.

22. Add Actual hours formula for phases: In the Actual column, use SUMIF to sum hours by phase (Task column in the time entries sheet): =SUMIF('All time entries this year'!$C$6:$C$3222,Report!$A34,'All time entries this year'!$K$6:$K$3222) where A34 contains the phase name. This sums all hours logged to that specific phase across all projects.

23. Add Actual Cost formula for phases: In the Actual Cost column, use: =SUMIF('All time entries this year'!$C$6:$C$3222,Report!$A34,'All time entries this year'!$M$6:$M$3222)

24. Calculate Cost per Hour: In the Cost / Hr column, enter the formula: =E34/D34 where E is Actual Cost and D is Actual hours. This calculates the effective cost per hour for each phase.

Part 4: Adding Key Takeaways

25. Create a Key Takeaways section: After the phase analysis, skip a few rows and add a 'Key Takeaways:' header. In the rows below, document patterns, insights, or learnings from the data. For example: 'Hours could shift from Project Launch to Orientation' or 'Hours could shift from Usability Execution to Usability Prep'.

26. Update regularly: As projects progress, update the Key Takeaways section with new observations about hour allocation, phase timing, or resource utilization that can inform future project planning and estimating.

Resources
  • Toggl Track time tracking system and API/export functionality
  • The Black Box (for Plan hours)
  • Cash to Run DORIS

No comments to display

Back to top