Zoho Projects Advanced Analytics: Projected Revenue Report

We announced Advanced Analytics for Zoho Projects around a month back, and it has been well received. Project managers now tap into their projects data and get good insights of what’s happening in their projects.

The Advanced Analytics package came with a wide range of ready made reports and dashboards. One such report is the Projected Revenue report. We will see how this useful report is derived.

• The projected revenue report is based mainly on the number of hours that are to be billed in the coming months.
• Each project member has a hourly billing rate which is to be filled in the ‘User Hourly Rates – Template‘ table.
• The query table ‘Projected Revenue Calculation Table‘ calculates the projected revenue by taking the total number of days of all upcoming tasks in the coming months (incomplete tasks that are to be completed in future) and multiplying that with the hourly rates. The query assumes an 8-hour workday and an uncertainity factor of 0.6 to take into account the holidays, leave etc. You can change this factor according to your projects.
• The SQL query that is being used:
  SELECT
  “Tasks”.”Project Name” “Project Name”,
  “Tasks”.”Milestone Name” “Milestone Name”,
  “Tasks”.”Task Name” “Task Name”,
  “Tasks”.”Owner” “Owner”,
  “Tasks”.”Start” “Task Start Date”,
  “Tasks”.”Due” “Task Due Date”,
  “Tasks”.”Status” “Status”,case
  when “Tasks”.”Status” = ‘Upcoming’ then ((datediff(“Tasks”.”Due”, “Tasks”.”Start”) + 1) * 8 * 0.6)
  else ((datediff(“Tasks”.”Due”, currentdate()) + 1) * 8 * 0.6)
  end “Projected Billable Hours”,
  “User Hourly Rates – Template”.”Hourly Rate” “Hourly Rate”
  FROM “Tasks” JOIN “User Hourly Rates – Template” ON “Tasks”.”Project Name” = “User Hourly Rates – Template”.”Project Name”
  AND “User Hourly Rates – Template”.”Member” = “Tasks”.”Owner”
  WHERE “Tasks”.”Status” = ‘Upcoming’
  OR “Tasks”.”Status” = ‘In Progress’
• The pivot table report that calculates the projected revenue is named ‘Projected Revenue‘ and is found under the ‘Billing Reports‘ folder.

We also have a slide show that explains the steps involved.

Try the Projected Revenue Report, which would give you a very good idea of how your revenue is going to be in the coming months.

If you haven’t tried Advanced Analytics for Zoho Projects yet, login as an Administrator to your Zoho Projects portal, click Settings -> Zoho Analytics -> Configure, and you can get started right away.

Related Links

• Zoho Projects Advanced Analytics Add-on
• Sample reporting database