Power BI developer for Commissions

Job Description:

I would like your assistance in developing a module to work with MS Excel and Power BI. The system would be required to provide a weekly calculation of employee overhead and employee revenue, and calculate the difference between those 2 components. The system needs to calculate commissions by Recruiter, for each employee assigned to that recruiter, for every week of the year.

The data necessary to calculate these components is as follows:

The basic data elements are maintained in Quickbooks. The QB system will provide export data in Excel format to provide the data elements below;

The key export table will contain Employee Name and Employee ID number; a 4 digit unique identifier for each employee.

The second export table would contain Employee ID, Employee Name, Type of hours, daily employee hours, and employee hourly pay rate, and the RECRUITER assigned to the employee. This table will be summarized into an Excel pivot table for productive hours by employee

The data elements provided by the accounting system would be as follows:

Hourly pay rate per Employee (PAYRATE);

Hourly bill rate per Employee (BILLRATE);

SCA rate per Employee (SCARATE);

Burden rate per Employee (BURDEN); expressed as a percentage;

Productive Hours per Employee per week (PHEW);

ZERO Weekly Commission (Currently at $1,000); ZERO

The formulas to determine commissions are:

PHEW x PAYRATE = Employee Weekly Pay (EWP);

EWP x SCARATE = Employee Burden$; in dollars and cents

PHEW x SCARATE = SCA Pay; in dollars and cents

PHEW x BILLRATE = Employee Weekly Revenue

EWP + Employee Burden$ + SCA Pay = Employee Overhead (OVERHEAD) in dollars and cents

Employee Weekly Revenue minus (OVERHEAD + ZERO) = MARGIN; (not less than zero)

WeeklyCOMMISSIONPAY = MARGIN x CommissionRate

The report needs to show, for each Recruiter, the following elements;

the recruiter the report is for;

the employee ID and employee name;

the week of the month;

the employee margin for the week;

the employee commission pay for the week;

the total commission pay for the recruiter for all the weeks shown on the report.

We are willing to negotiate price depending on our confidence in the solution and our ability to understand it.

Kemahiran: Perakaunan, Pemprosesan Data, Excel, SQL, Visual Basic, Power BI

ID Projek: #35886602