Lately I have been talking to people. That's not news, right? We've been talking about processes for translation agencies. That's also not news, everybody has processes. But then we talked about the tools they are using... AND THAT MADE THE NEWS:
The amount of those who told me they're still using "beautifully customized" Excel sheets to track their translation agencies work is just mind-blowing. You know what I tell them?
"Great. Welcome to 1989!"
They say it is enough. Excel is powerful. They can pull all the data they need from that one huge file with 16 spreadsheets. Or from 16 different Excel files. Or from, wherever, dude...
I mean, seriously, in the time when your mobile device is capable of predicting where you'll go next (if you use Waze you know what I am talking about), you are putting your whole business in a "hand" (eh!) of a software that creates spreadsheets and graphs for mangers.
As much as I love those graphs, and spreadsheets, Spreadsheets are only covering 5% of data we - as translation agency owners or project managers - need.
Don't get me wrong, sometimes, Excel can save your ass.
Spreadsheets can sometime save your #t9n agency, but they are definitely not even close to a #TMS #l10n #minitpms
It sure did it for my company a couple of times when we were out of TMS. So if you're not ready to invest in a powerful TMS to have all the sexy features, don't go crying just yet. You can still use Excel to track your projects and not miss anything. Or at least almost anything.
I'm gonna show you the easiest way to do that.
First of all, let's take a list of
the basic data you need
if you have a translation project at your hands. It goes something like this:
Client data: the obvious is the name, maybe PM's name who contacted you, e-mail addresses, telephone numbers etc. You can enter all these in your Sheet but it really won't help you much, except that you'll be able to find these data if you need them.
Now we can move on to the project part: ​
At least three different dates: project start, project end (when it is really finished), project due (your client's due)
You need client "numbers", that is, the amount of work they sent you. Units. It's not the same if they send you 3 pages, or 1 hour or 11,000 words for translation.​
Finally we are at Finances, prices, and a ton of other important data out of which we are only taking the crucial ones into our sheet. We work with it, but never really see the whole picture. Unless you are some superhuman, there's no way to do so.
And we're not even half finished.​
I mean there's Vendor (Freelancers) data too: again, the standard data like name, e-mail addresses, language pairs etc along with the price (note that you can only have one price here), and for the project it's pretty much the same like we had at the Client side.
Whoa, I'm not even sure if everything is listed there, but it is a long list, isn't it? And it is far from ideal, but it could work. In the beginning, when you are not having a lot of business at your hand, maybe it is even enough.
You need to put all that in an Excel spreadsheet
I suggest the following:
Have separate tabs called "All Projects", "Vendors", "Clients" and maybe some kind of "Monthly Overall" (to show you how much money did you get, and spend in a month... )
This is what you put in the Client tab:
​Client code, client name, address, contract date, price per word, etc. whatever else you might want or need there. For the purpose of making the Excel sheet work it is important to have a Client Code and a price established.
Next, this is what you need on your Vendors tab:
Vendor code (not 100% necessary, but cool to have), vendor name, language pairs, price, and also the usual data, address, e-mails, skype and other contacts like whatsapp or viber...
Finally we are at the projects tab:
Project number (not necessary but helpful), project status (must have), date started, client code (must be the same as the code in Client tab), project name, description, source language, target language, customer word count, vendor word count and finally, vendor code (or name if you didn't use codes with vendors).
After that, you are just partially set, you still need:
Client price (you'll se below how to auto-fill this), Vendor price (also auto-filled), and then probably you want some profit row (client price - vendor price), and maybe a profit margin too... ​
Awesome right? Now you get these three tabs, and all you need to do is combine them with Excel search functions:
On the "All Projects" tab You need to use the VLOOKUP forumla ​to look up the client price from the Client tab (check for Client Code on All Projects tab and the same Client Code on Clients Tab), as well as Vendor price from the Vendor tab (same philosophy for search).
So in a cell, when you enter the formula it looks like something like this: =IF(ISBLANK(N72);VLOOKUP(F72;Clients!$A$2:$L$266;6;FALSE);VLOOKUP(F72;Clients!$A$2:$L$266;5;FALSE))​
When you enter a Client Code that exists, and Vendor Code that exists, the VLOOKUP will find these data on other Tabs and auto-fill your prices for clients and vendors. ​You multiply them with the amount of words you have been given from your customer, and with what you gave to vendors, and it all fills in nicely.
Not really easy on the eye, when you have several hundreds of projects but it is still better then just relying on your e-mail.
What comes next is the so called conditional highlighting in the All Projects tab.
It's a pretty awesome Excel feature in which you can set, for example, highlighting of an entire row based on data you enter in one of the cells. In our case, we enter only one character (you can set up more) into the Status row so that our complete row is color coded.
You need to create come color codes in order to immediately know the status of each and every project.
What we used mostly, are these codes: ​
- A - light yellow - for Announced projets and tasks
- W - yellow - Work in progress
- P - magenta - proofreading (or Editing, call it how you like it)
- F - green - finished
- C - red - cancelled (or you know, when something was wrong with it)
- I - gray - invoiced (which means all the parts of it were done, client finance, vendor finance, etc...)
Here is how it would look like in real life. Just ignore that the colors in the picture are off. And of course, the data on the right side is not visible due to, well, due to the fact that I don't have a monitor wide enough.
The good thing about this small spreadsheet helper ​is that it's simple, and easy to use. You have your list of projects to do, and with the color codes you also will have the status of your project-to-project works.
Trust me, you can manage a million words a month with this Excel Sheet. We did it in 2015. However, it mostly only works for projects that are really straight-forward and have not more than 2 steps in their workflow (say, translation and editing). Even then, it's not ideal but it is pretty much manageable.
What about Monthly Overall?​
For overall look you can create another tab with monthly searches, using formulas in Excel that would pull data from your All Projects tab separately month by month.​
These monthly finance tables​ are awesome if you only track "project money in" and "vendor money out" (you can calculate some kind of - a little bit erroneous - ROI with that), and it's a good indicator of the amount of work in your company.
However, when you are doing more complex projects and need to set up more than two steps in a workflow, unfortunately it all falls apart and becomes hard to follow.
Final Problem: there is a huge amount of things that
Excel spreadsheets cannot do​
Actually there is a lot of pitfalls, where the whole thing can be so frustrating that you wish you could just throw your whole damn computer, laptop, business, mobile phone and your dog out of the window. Except for the dog. Who throws dogs out of windows? I mean, come on!
Anyway, trouble in Excel paradise? There's a lot of it. For example: ​
- When you need a partial delivery, with separate due dates - how do you track that in spreadsheets? How do you know when to send, what to send, to whom to send it?
- When you need CAT data entered into your projects easily and fast - there are ways to do it in Spreadsheets, but it's a nightmare just to think about it.
- When the Client pays you differently for project A and project B. Or when you have ten different units you get jobs in, such as pages, words, hours, lines, characters... - do I really need to tell you this?
- Finally, when it comes to invoicing and more complex stuff... you want to create Client reports for approval, or
- Client invoices, or
- Comprehensive income and expense reports either for yourself, for your Clients or Vendors,
- What if you need data for the last 3 years and your spreadsheet has only data for one month...​
This list is endless...
Bottom line? Excel - or any other spreadsheet tool - can be a powerful one if you know how to use it, and you are ready for huge compromises. But it is by far not capable of doing everything even a smallest translation agency, heck, even what a freelancer really needs.
Good news:
There is a solution for you. If you want "the Kraft", the Power, the automation, the sexy features, the even sexier reports and all that Jazz... then Click here to find about the today's special! (subject to some sheriff criteria matters, like sheriffness, and cowboyship. And many other non existent words.)
