Report S105 - Project profit and loss - By service

This report lists one line per project with the total client invoice amounts and the total supplier invoice amounts. With this report you can immediately see profits and loss for each of your projects.

Report content

This report combines client and supplier cost details from the respective invoices and attempts to align supplier and client amounts by document, task and language combination.

When creating the report you select a date range and by which date you want to filter:

The system starts with selecting all projects that pass the filter (excluding cancelled ones). For each of these projects, we then load all the jobs, client invoices, supplier invoices and word count details.

All this information is then aggregated by client, project, document name, task type and language couples. It is thus easy to see, per document, how much is invoiced to the client and how much is paid to vendors.

Special notes:

Cancelled projects, invoices and jobs are disregarded
Aggregated invoices are disregarded, the original details invoices are used instead
The grouping works best if you create (a) a single invoice/quote per job and (b) You do not manually edit Codyt supplier invoices.
If multiple invoices are created for a Codyt job then the report prints the extra cost in a separate line with a special indication.
If a client invoice includes details lines not referring to a document then the corresponding cost is printed in a separate line with a special indication.

Also read Languages, Dates and Currencies for important information.

Template

Screenshot of default template:

The default template includes:

Basic project information
Document, task and languages
Client invoice totals: The number and total sum excl. VAT of all client invoices or quotations in this project.
Supplier invoice totals: The number and total sum excl. VAT of all supplier invoices or quotations in this project.
Profit and loss as the difference of client totals minus supplier totals.
Full project, job, supplier details
Full word count details
And more.

Global placeholders

These can be placed in any location in the Excel and are general properties applicable to all the report.

Placeholder name	Description
<#StartDate>	The start date selected when creating the report. The date is adjusted to the time zone you have configured.
<#EndDate>	The end date selected when creating the report. The date is adjusted to the time zone you have configured.
<#GeneratedDate>	The date when the report was generated.
<#MasterCurrency>	Your default currency as 3 letter ISO code (such as EUR or USD). This is taken from the default price list you configured in the My Company page of your platform. Read more.

Data rows and placeholders

Each row in the report corresponds to either a client invoice or a supplier invoice. In other words, all information of a specific invoice will be contained in one single row.

The placeholders of the row must be placed within the Excel table range named "___Lines___" (3 leading and trailing underscore characters). Click here for more details on table ranges.

A report prints the raw data in rows. Each row, you can use any of the following placeholders:

Placeholder name	Description
Project fields	The project to which an invoice refers to.
<#Lines.ProjectFirst>	Prints a * (star) if this is the first line for the project. The report contains as many lines per project as there are client or supplier invoices.
<#Lines.ProjectReference>	The project reference.
<#Lines.ProjectClient>	The client company name.
<#Lines.ProjectStatus>	The project status title.
<#Lines.ProjectManager>	First and last name of the project manager (if any).
<#Lines.ProjectInProgressDate>	The date when the project status was last changed to In Progress. This is a native Excel date.
<#Lines.ProjectCompletionDate>	The date when the project status was last changed to Completed. This is a native Excel date.
<#Lines.ProjectComments>	The internal comments saved with the project.
<#Lines.ProjectType>	The type of project. Prints either "Standard" or "Codyt". The former is for standard projects and the latter for Codyt projects. This title is not localized.
<#Lines.ProjectCreationDate>	The date when the project was created. This is a native Excel date.

Client details	Client fields.
<#Lines.ClientCompany>	The client company name
<#Lines.ClientCode>	The optional "Code" field that can be supplied in the client details page.
<#Lines.ClientAccCode>	The optional "Accounting code" field that can be supplied in the client details page.
<#Lines.ClientDebtorNo>	The optional "Debtor no" field that can be supplied in the client details page.
<#Lines.ClientCountry>	The country code of the client.

Document details	Each report line refers to a specific document
<#Lines.DocumentId>	Internal ID that can be used to distinguish between documents having the same name.
<#Lines.DocumentName>	The name of the document.
<#Lines.DocumentParagraphFrom>	If the job is for a specific section of the document, then this is the first paragraph included (starting at 1).
<#Lines.DocumentParagraphTo>	If the job is for a specific section of the document, then this is the last paragraph included.
<#Lines.DocumentCreated>	The date when the document was uploaded to the system. Typically, this is the date when the project manager marked the document for translation.
<#Lines.DocumentVersion>	An optional version number of the document. Used with Codyt where the project manager can replace a document with a newer version and type a version number.

Task and languages	Each report line refers to a specific task type and language combination
<#Lines.JobTaskCode>	The task code such as TR for translation or PROOF for proofreading. These codes can be viewed in Wordbee under Settings > Task categories.
<#Lines.JobTaskName>	The task. Tasks are "Translation", "Revision", etc. Note that the task name is localized. For example, if an invoice contains details for translation then this field will be "Translation".
<#Lines.JobSourceLocale>	The source language ISO code. Note that thelanguage names are localized.
<#Lines.JobSourceLocaleName>	The source language name. Note that the language names are localized.
<#Lines.JobTargetLocale>	The target language ISO code. Note that thelanguage names are localized.
<#Lines.JobTargetLocaleName>	The target language name. Note that the language names are localized.

Client / Supplier cost	Indicates the cost of the services contained in a report line. These are the amounts of the invoice details that correspond to the line.
<#Lines.ClientExVAT>	The client invoice sub-total for the row's services, excluding VAT.
<#Lines.ClientExVATBase>	The client invoice sub-total before discounts for the row's services, excluding VAT.
<#Lines.ClientExVATConv>	The client invoice sub-total for the row's services, excluding VAT and converted to your currency.
<#Lines.ClientExVATBaseConv>	The client invoice sub-total before discounts for the row's services, excluding VAT and converted to your currency.
<#Lines.ClientInvoiceCurrency>	The currency code of the client invoice, such as EUR, USD... Formatted as 3 letter ISO code.

<#Lines.CostExVAT>	The supplier invoice sub-total for the row's services, excluding VAT. Note: With Standard jobs, this placeholder will contain the total cost for all documents in the job. It is printed in the first line printed for each job. If you need to print the cost individually per each document, please use placeholder <#Lines.CostExVAT2>.
<#Lines.CostExVAT2>	Contains the cost per each document of all jobs. With Codyt jobs this is identical to <#Lines.CostExVAT>. With Standard jobs, see above for the difference. This value is left blank if no word count was performed. Use this placeholder if you need to see the cost individually per each document.
<#Lines.CostExVATConv>	The supplier invoice sub-total for the row's services, excluding VAT and converted to your currency.
<#Lines.CostExVATBase>	The supplier invoice sub-total before discounts for the row's services, excluding VAT.
<#Lines.CostExVATBaseConv>	The supplier invoice sub-total before discounts for the row's services, excluding VAT and converted to your currency.
<#Lines.CostCurrency>	The currency code of the supplier invoice, such as EUR, USD... Formatted as 3 letter ISO code.

Client invoice	These describe each invoice. For sub totals per report line see further down on this page.
<#Lines.ClientInvoiceReference>	The invoice reference.
<#Lines.ClientInvoiceStatus>	The localized status of the invoice. The status also indicates if the invoice document is still a quotation or already an "invoice". Note that cancelled invoices are not included in this report.
<#Lines.ClientInvoiceNo>	The accounting invoice number. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted.
<#Lines.ClientInvoiceEffectiveDate>	The effective date of the invoice. he financial period to which an invoice is attributed. Assigned when an invoice status moves from quotation to "Draft invoice".
<#Lines.ClientInvoiceChangeDate>	The last status change of the invoice.
<#Lines.ClientInvoiceDate>	The accounting date of the invoice. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted.
<#Lines.ClientTotalExVAT>	The total invoice amount excluding VAT.
<#Lines.ClientTotalExVATConv>	The total ex VAT amount converted to your currency.
<#Lines.ClientVATRate>	The VAT rate percentage. Provided as a string with percentage sign: "10%", "22.5%"...
<#Lines.ClientInvoiceCurrency>	The currency code of the invoice, such as EUR, USD... Formatted as 3 letter ISO code.

Supplier Invoice	Contains details of all supplier invoices in the project.
<#Lines.InvoiceReference>	The invoice reference.
<#Lines.InvoiceStatus>	The localized status of the invoice. The status also indicates if the invoice document is still a quotation or already an "invoice". Note that cancelled invoices are not included in this report.
<#Lines.InvoiceNo>	The accounting invoice number. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted.
<#Lines.InvoiceEffectiveDate>	The effective date of the invoice. he financial period to which an invoice is attributed. Assigned when an invoice status moves from quotation to "Draft invoice".
<#Lines.InvoiceChangeDate>	The last status change of the invoice.
<#Lines.InvoiceDate>	The accounting date of the invoice. Assigned when the invoice status us set to "Approved". Inside Wordbee you can configure if this number is automatically assigned and how it is formatted.
<#Lines.CostCurrency>	The currency code of the supplier invoice, such as EUR, USD... Formatted as 3 letter ISO code.

Supplier job details	Shows all the job details for this line.
<#Lines.JobReference>	The reference of the job. With Codyt jobs this is equivalent to the document name of the job.
<#Lines.JobStatus>	The status title of the job. Note that status titles are localized.
<#Lines.JobAllocMode>	The mode of allocation of the job. Either one of Fixed assignment, Job opening or Crowd. In the case of job openings, the mode is changed to fixed assignment once a supplier is assigned.
<#Lines.JobTaskCode>	The task code such as TR for translation or PROOF for proofreading. These codes can be viewed in Wordbee under Settings > Task categories.
<#Lines.JobTaskName>	The task. Tasks are "Translation", "Revision", etc. Note that the task name is localized. For example, if an invoice contains details for translation then this field will be "Translation".
<#Lines.JobSourceLocale>	The list of all target language ISO codes contained in the invoice and separated by commas. Note that the language names are localized.
<#Lines.JobSourceLocale>	The source language ISO code. Note that thelanguage names are localized.
<#Lines.JobSourceLocaleName>	The source language name. Note that the language names are localized.
<#Lines.JobTargetLocale>	The target language ISO code. Note that thelanguage names are localized.
<#Lines.JobTargetLocaleName>	The target language name. Note that the language names are localized.
<#Lines.JobCompany>	If assigned, the company name of the supplier.
<#Lines.JobPerson>	If assigned, the person first and last name of the supplier.
<#Lines.JobCreationDate>	The date when the job was created in the system. This is a native Excel date.
<#Lines.JobDeadline>	The job deadline, if any. This is a native Excel date.
<#Lines.JobCompanyAssignDate>	The date when the job was assigned to a supplier. Empty if not assigned.
<#Lines.JobPersonAssignDate>	The date when the job was assigned to a specific person at the supplier company. Empty if the job is not assigned to a supplier at all or not to a specific person.
<#Lines.JobStartDate>	The date when the job status moves to In Progress.
<#Lines.JobEndDate>	The date when the job status moves to Completed.
<#Lines.JobRatingsAvg>	The average rating given to this job (typically by the project manager). The rating is a value between 1 and 5 (best). This field is empty if the job was not rated.
<#Lines.JobRatingsDate>	The last time the job was rated. This field is empty if the job was not rated.
<#Lines.JobDeadlineYM>	The job deadline formatted as "year-month" such as in 2016-10 or 2014-02.
<#Lines.JobCreationDateYM>	The job creation date formatted as "year-month" such as in 2016-10 or 2014-02.
<#Lines.JobStartDateYM>	The job in progress date formatted as "year-month" such as in 2016-10 or 2014-02.
<#Lines.JobEndDateYM>	The job completion date formatted as "year-month" such as in 2016-10 or 2014-02.

Supplier job metrics	Delays in handling the job.
<#Lines.JobCreatedToCompletion>	Total minutes from job creation to completion (status Completed). This is the delay from<#Lines.JobEndDate> minus <#Lines.JobCreationDate>. The field is empty if either one of the dates are not yet set.
<#Lines.JobStartedToCompletion>	Total minutes from job start (status In Progress) to completion (status Completed). This is the delay from<#Lines.JobEndDate> minus <#Lines.JobStartDate>. The field is empty if either one of the dates are not yet set.
<#Lines.JobAssignedToStart>	Total minutes from job assignment to start of work (status In Progress). This is the delay from<#Lines.JobStartDate> minus <#Lines.JobCompanyAssignDate>. The field is empty if either one of the dates are not yet set.
<#Lines.JobAssignedToCompletion>	Total minutes from job assignment to completion (status In Progress). This is the delay from<#Lines.JobEndDate> minus <#Lines.JobCompanyAssignDate>. The field is empty if either one of the dates are not yet set.

Word counts	The word count details for the document above. If the scope of the job is for a portion of the document only, then word counts are for that portion. Please note that word counts are optional and any of the below fields may be empty.
<#Lines.WCSegments>	Total segments.
<#Lines.WCWords>	Total words.
<#Lines.WCCharacters>	Total characters.
<#Lines.WCMinutes>	Total minutes worked. This optional value is filled in by the supplier or the project manager in the jobs page - it is not calculated by the system.
<#Lines.WCPages>	Total pages. This optional value is filled in by the project manager in the jobs page - it is not calculated by the system.

<#Lines.WCNoMatchWords>	Total words that were not leveraged.
<#Lines.WCWordsExcluded>	Total number of words in the original text that are excluded from translation (do not require translation).
<#Lines.WCWordsPretransFuzzy>	Total words of fuzzy (< 100%) pre-translations.
<#Lines.WCWordsPretransIdentical>	Total words that are pre-translated at 100% or above.
<#Lines.WCWordsPretransIdenticalCtx>	Total words that were pre-translated at 110% (perfect pre-translations) from memories or termbases.
<#Lines.WCWordsPretransIdenticalPrevCtx>	Total words that were pre-translated at 110% (perfect pre-translations) from a previous document version.
<#Lines.WCWordsPretransIdenticalPrev>	Total words that were pre-translated at 100% (identical pre-translations) from a previous document version.
<#Lines.WCWordsPretransIdenticalMT>	Total words that were machine translated.
<#Lines.WCPretrans110>	Total words of perfect pre-translations (110%). This is equivalent to the sum of <#Lines.WCWordsPretransIdenticalCtx> and <#Lines.WCWordsPretransIdenticalPrevCtx>.
<#Lines.WCPretrans100>	Total words of identical pre-translations (100%).
<#Lines.WCPretrans100> - <#Lines.WCWordsPretransIdenticalMT>	Total words that were pre-translated at 100% (identical pre-translations) from memories or termbases, including previous file versions. This doesn't include machine translations
<#Lines.WCWordsFR100>	Total not-translated matches or repetitions at 100% or above. Includes words from 100% to 110%.
<#Lines.WCWordsFR95>	Total not-translated matches or repetitions at 95% or above. Includes words from 95% to 110%.
<#Lines.WCWordsFR90>	Total not-translated matches or repetitions at 90% or above. Includes words from 90% to 110%.
<#Lines.WCWordsFR85>	Total not-translated matches or repetitions at 85% or above. Includes words from 85% to 110%.
<#Lines.WCWordsFR80>	Total not-translated matches or repetitions at 85% or above. Includes words from 80% to 110%.
<#Lines.WCWordsFR75>	Total not-translated matches or repetitions at 75% or above. Includes words from 75% to 110%.
<#Lines.WCWordsFR70>	Total not-translated matches or repetitions at 70% or above. Includes words from 70% to 110%.

<#Lines.WCTransWords>	Total number of words in the translated texts. This value can be calculated by the system with a button press once translations are done.