Column: Spend by Project by Quarter

[CalvinFold]

First, I freely admit to not being a wizard at the financial gymnastics involved in project management at the corporate level. But math and programming I can do, reporting I at least have an intermediate grasp of, I'm learning fast, and I'm a WF Sysadmin, so it's my job to figure this out... I'm tasked building a custom burn report that needs to list among other things: SCOPED BUDGET // Q1 // Q2 // Q3 // Q4 // TOTAL BURN // BALANCE ...where Q1 Spend + Q2 Spend + Q3 Spend + Q4 Spend = Total Burn ...Balance = Scoped Budget - Total Burn Now Scoped Budget is easy, and once I have the spend for each quarter, Burn and Balance are easily calculated columns. I started this as a Project report due to a bunch of other fields required that are not listed above. However, I can't really suss-out how to do "total spend in Q1" where Q1 = a range defined in Preferences under Custom Quarters called "Q1 - FY19". I'm not seeing these constructs in API explorer (how do I access the date range of a custom quarter I've defined?), but maybe I haven't poked around the right places? Nor do I honestly know how to calculate "spend by quarter" except that: It needs to include the dollar amount of timesheet hours logged to the project at the proper rate for that user/project. It needs to include all logged Expenses. It needs to do these calculations without relying on Billing Records...we need more "live, up-to-the-moment" fiscal amounts rather than the periodic aggregates that result from billing records I'd appreciate any help I can get. :-) Kevin Quosig

[CoryAn]

Hi Kevin, I believe you can get Actual Revenue and Actual Expense Cost at the project level. You could even do a calculated field to add the two together so you can report on the total cost. These can be summarized in the grouping on your report to give you a total. For Actual Revenue to fill in on the task and roll up to the project the task Revenue Type needs to be set to either Role Hourly or User Hourly. Also, the task needs to be assigned accordingly. Users need to have Job Roles assigned and Job Roles need to have a Bill/Hr amount set. To get the data by fiscal quarter, you will need to do separate reports for Hours and Expenses. The reason is they use different date fields than what the project has. For example, if you want to see the cost of hours logged this week you would have to rely on the Hour Entry Date rather than a project level date. Same for expenses. If you want to see expenses paid this week you would have to use the Expense Date Paid. Using the Project Actual Completion Date would give you all the revenue and expenses for projects that completed in the date range, rather than only getting revenue and expenses that were actually incurred during the period. Short answer is that if you want actual expenses and time logged during a specific period regardless of the project completion date you would need to have separate reports. We do this effectively across our organization by utilizing AtApp Store's Uber Calc to filter the needed values in specific portfolios & time periods and sum them to a custom field on a special project that we can easily report on. We have a dashboard with a summary report and supporting charts. The summary report shows planned and actual revenue, expenses, billed amounts, etc and calculates a financial projection for the quarter based on all that. The supporting charts allow leadership to drill into all the numbers in the summary chart. But it does require the Uber Calc addon. Hope that helps! Cory Cory Anderson Cisco Systems- Communication Services