Contractor Forecast Report

Overview

Updated 05/16/2023

The report forecasts the total revenue, cost of service, and gross profit through a specified date based off your current contractors’ intake. Additionally, you can drill down to view the jobs used to calculate the totals.

• Click here to go to the Operational Category (list of Standard Reports)

Click on the links below for more information: List of Standard Reports List of BOLD Analyze Reports List of INSIGHTS Dashboards List of Business Intelligence (BI) Reports

CHAPTERS Legend Locating the Report Logic Filter Options Field Details     Details     Summary

Legend

Locating the Report

1. Click on Analyze Tab
   The Reports Sub Tab will automatically be selected
2. In the Search Reports field, type CONTRACTOR FORECAST
3. Click on the Contractor Forecast Report

Logic

The report forecasts the total revenue, cost of service, and gross profit through a specified date based off your current contractors’ intake. Additionally, you can drill down to view the jobs used to calculate the totals.

• Click here to go to the Operational Category (list of Standard Reports)

Filter Options

	Filter Name	Options	Description
	Division	All available Employer list	Use this filter to refine Employer of Job
	Region	All available Region list	Use this filter to refine Region of Job
	Office	All available Branch list	Use this filter to refine Branch of Job
	Date End		Filters placement that has StartDate before or on the week the report is run on and has EndDate after or on the week of the Date End parameter of the report.

Field Details

Details

• Click here to go to the Operational Category (list of Standard Reports)

Field Name	Description	Notes
Requisition ID	Job's Requisition ID
Company	Hiring Manager's Company Name
Consultant Name	Placement's Talent Name
Start Date	Placement's Start Date
End Date	Placement's End Date
Est. Weekly Hours
Bill Rate	Placement's Reg Bill Rate
Pay Rate	Placement's Reg Pay Rate
Total Bill	Total Billed Amount	CONVERT(decimal(19,2),((f.req_billrate-(f.req_billrate * (f.vms_deduct/100))) * sy.weekly_hours))
Total Pay	Total Paid Amount	CONVERT(decimal(19,2),(f.req_payrate  *  sy.weekly_hours))
Burden	Burden Amount	CASE WHEN (ISNULL(f.burden,'') = '' AND (f.emp_type = 'C2C' OR f.emp_type = 'W-2' OR f.emp_type = '1099')) THEN 0 ELSE f.burden
Cost of Service	Cost of Service Amount	CONVERT(decimal(19,2),((f.req_payrate  + ( f.req_payrate * ((CASE WHEN (ISNULL(f.burden,'') = '' AND (f.emp_type = 'C2C' OR f.emp_type = 'W-2' OR f.emp_type = '1099')) THEN 0 ELSE f.burden END)/100)))  * sy.weekly_hours))
Gross Profit	Gross Profit Amount	CONVERT(decimal(19,2),((((f.req_billrate-(f.req_billrate * (f.vms_deduct/100))) * sy.weekly_hours)) - ((f.req_payrate  + ( f.req_payrate * ((CASE WHEN (ISNULL(f.burden,'') = '' AND (f.emp_type = 'C2C' OR f.emp_type = 'W-2' OR f.emp_type = '1099')) THEN 0 ELSE f.burden END)/100)))  * sy.weekly_hours)))
Job Title	Job's Title
Job Status	Job's Status
Req Eor	System's Job Type	If ReqEor is blank Permanent is passed
Req Type	Job's Type	If ReqEor is Permanent or placement activity id = 11, W-2 is displayed else Job Employment Type's Tax
Sales Rep	Job's Sales Representative
Contractor Rep	Placement's Recruiter Representative
Hired Date	Placement's Hired Date
Total Est Hours	Placememt's Estimated Hours	If ReqEor is Permanent or placement activity id = 11(Converted to Permanent, 0 is displayed else Placement's Estimated Hours
Placement Fee	Placement's Fee	If ReqEor is Permanent or placement activity id = 11(Converted to Permanent), Placement's Fee is displayed else 0
Total Est Revenue	Total Estimated Revenue	CONVERT(decimal(19,2),((f.req_billrate * IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, ISNULL(f.est_hours,0)) + IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11,ISNULL(f.placement_fee,0), 0) - ISNULL(f.discounts,0) + ISNULL(a.flat_fees,0))))
Total Est Cost	Total Estimated Cost	CONVERT(decimal(19,2),(f.req_payrate * ((ISNULL(IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, f.burden /100),0)) + 1) * IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, ISNULL(f.est_hours,0))))
Total Est GP	Total Estimated Gross Profit Amount	CONVERT(decimal(19,2),(((f.req_billrate * IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, ISNULL(f.est_hours,0)) + IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11,ISNULL(f.placement_fee,0), 0)) - ISNULL(f.discounts,0) + ISNULL(a.flat_fees,0) - (f.req_payrate * ((ISNULL(IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, f.burden /100),0)) + 1) * IIF(a.req_eor = 'Permanent' OR f.act_type_id = 11, 0, ISNULL(f.est_hours,0))))))

Summary

Field Name	Description	Formula
WeekStart	Week Start Date If the Week Start Date (Monday's Date) is less or equals to date end parameter, the Week Start date is added by 7 (Next Week's Monday)	SELECT  WeekStart [@CurrentWeek] = CASE DATEPART(dw, GETDATE()) WHEN 1 THEN DATEADD(DAY, -6, GETDATE()) WHEN 2 THEN DATEADD(DAY, 0, GETDATE()) WHEN 3 THEN DATEADD(DAY, -1, GETDATE()) WHEN 4 THEN DATEADD(DAY, -2, GETDATE())  WHEN 5 THEN DATEADD(DAY, -3, GETDATE()) WHEN 6 THEN DATEADD(DAY, -4, GETDATE()) WHEN 7 THEN DATEADD(DAY, -5, GETDATE())                                END
TotalRevenue	Total Revenue is calculated as per jobs bill rate, vms deduct(%) and weekly hours.	SUM(( c.req_billrate - ( c.req_billrate * ISNULL(c.vms_deduct,0) / 100 ) ) * sy.weekly_hours)
CostOfService	Cost Of Service is calculated as per jobs payrate, burden (%) and weekly hours. If burden is not available then 15% is taken into consideration.	SUM(CASE WHEN c.req_payrate IS NOT NULL  THEN ( c.req_payrate + ( c.req_payrate  * ( CASE WHEN c.burden IS NOT NULL  THEN ( c.burden  / 100 ) ELSE 0.15  END ) ) )        * sy.weekly_hours      END)
GrossProfit	Difference of Total Revenue and Cost Of Service	Total Revenue - Cost Of Service
Details	Hard coded value 'Details'