The spreadsheet can calculate what a job will cost you with a given vendor, and write that figure into the Payables column when you assign the vendor. The calculation uses three sheets together: Fuzzy schemes, then Price lists, then Vendors. Fill them in that order.
This setup is optional. If you do not fill in these sheets, you can still assign vendors, you simply will not get a cost.
Fuzzy schemes: what each TM band is worth
Each row is one band of one scheme.
| Name | Band name | Price factor |
|---|---|---|
| Standard | No match | 1.00 |
| Standard | 95-99% | 0.30 |
| Standard | 85-94% | 0.50 |
| Standard | 75-84% | 0.60 |
| Standard | Repetitions | 0.10 |
- Name groups the rows into one scheme. You refer to that name from a price list.
- Band name must match the band names in the Analysis data sheet exactly. The Fuzzy types sheet lists the names that actually occur in your data, so copy them from there.
- Price factor multiplies the base price for that band.
Warning: If a job's analysis contains a band your scheme does not define, the calculation stops for that vendor and the dialog shows the error instead of a price. Cover every band listed in Fuzzy types.
Price lists: the rate cards
A price list is a group of rows sharing the same Name. One price list can contain:
- one Fuzzy scheme row per work type, which is the main rate;
- optional Minimal charge rows;
- optional Additional price rows, reserved for future use and not calculated yet.
| Field | Meaning |
|---|---|
| Name | The price list name; vendors point at it |
| Source language / Target languages | What the rate covers. This is documentation for you; matching is done on the vendor's capabilities |
| Work type | Must match the job's Work type, for example Translation or Proofreading |
| Specialization | Optional. A row with a specialization wins for projects with that specialization; the row without one is the fallback |
| Fuzzy scheme | Which scheme from Fuzzy schemes applies |
| Price type | Fuzzy scheme, Minimal charge or Additional price |
| Price / Currency | The base rate |
| Unit | Words, Characters, Pages or Lines |
Example:
| Name | Work type | Fuzzy scheme | Price type | Price | Currency | Unit |
|---|---|---|---|---|---|---|
| Freelance EUR | Translation | Standard | Fuzzy scheme | 0.08 | EUR | Words |
| Freelance EUR | Proofreading | Standard | Fuzzy scheme | 0.03 | EUR | Words |
| Freelance EUR | Translation | Minimal charge | 15 | EUR | Flat |
To create different rates for different languages, follow the same approach and create individual rows with the same Name.
Vendors: who can do what, and at what price
For every vendor, fill in:
- Supported source languages and Supported target languages, using the language names as spelled in the Languages sheet;
- Supported work types, as spelled in the Work types sheet;
- Price list, picked from the dropdown, whose source is the Price lists sheet;
- Notes, free text for you.
These four columns are the entire matching logic: a vendor is offered for a job only when the job's source language, target language and work type are all found in that vendor's columns.
How the cost is calculated
For the chosen vendor:
- Take the vendor's Price list.
- Find its Fuzzy scheme row whose Work type equals the job's work type. If the project has a specialization and a row matches it, that row wins, otherwise the row without a specialization is used.
- Load that row's Fuzzy scheme from the Fuzzy schemes sheet.
- For every band in the job's Analysis data, calculate volume × base price × band price factor, round to two decimals, and add it up. Volume depends on the price list Unit: words for Words, characters for Characters, characters ÷ 300 for Pages, characters ÷ 55 for Lines.
- If the price list has a Minimal charge row and the total came out below it, the minimal charge is added.
So a job with 1000 no-match words and 500 repetition words at €0.08, with factors 1.00 and 0.10, costs 1000 × 0.08 × 1.00 + 500 × 0.08 × 0.10 = 80.00 + 4.00 = €84.00.
The Payables value written by the vendor selection dialog is a local calculation for your planning. It lives in the spreadsheet only and is not sent to BeLazy.
When the cost is 0 or an error is shown
| Symptom | Cause |
|---|---|
| Cost 0 for every vendor | The job has no rows in Analysis data |
| One vendor shows an error | Their price list has no Fuzzy scheme row for this work type |
| "Fuzzy scheme not found" | The price list points at a scheme name that does not exist |
| "No fuzzy band for ..." | The analysis contains a band the scheme does not define |
| A vendor is missing from the list | Their languages or work types do not cover the job |