Embedded Files
Manpower billing based on Day rates or Hourly rates are part & parcel of engineering companies.
To proceed, the elements required are:
an official data source of the actual Hours / Days worked (time sheets or systems extract)
Billing logic (Rates: flat , variable, mixed etc)
Hourly Billing:
If there isn't a system for extracting, one perhaps could consider using a standardized template like this ~ so that one can consolidate the data easily later on by using add-ins like RDBmerge. With the Summary, one can then proceed to multiply the hours by rates (derived by a price lookup)
Billing of Day-rates
For day-rates, as a rather common requirement, the customer mostly would require a supporting schedule something as per below ~ which triggers the need for some data processing of the source data.
The source required could usually be extracted from the company's crew management/HR systems: the format of data normally falls into one of the 2 structures to the left.
For Data of a structure of DATTYPE2, a direct summary by pivot-table should be sufficient to produce the required output.
For Data of a DATATYPE1, a template summary could be done up by using array formulas & conditional formatting as per illustrated below.
A sample file illustrating the 2 scenarios could be downloaded here as a reference.
Hope it helps.
Example of using formulas and conditional formatting as per below screenshot
Page updated
Google Sites
Report abuse