Every 2 weeks, Natalie has to spend 2 hours after her workday on Wednesday to prepare for a 30-minutes meeting with her colleagues from other parts of the world. With our help, 2 hours became 10 minutes. The 1 hour 50 minutes saved was then spent bonding with her 3 children, helping them with their homework, or even enjoying a short movie with them on Disney Plus.
Natalie is the director of APAC region in a global consultancy company, managing consultants assigned to projects billed in USD, EUR, AUD, and SGD. Every biweekly, Natalie has to prepare a presentation for the HQ on the regional performance.
Natalie used to have a excel sheet with 13 tabs, 1 tab for each month, and 1 tab for “presentation calculations” where she would copy the total revenue for the past 2 weeks, or the whole month, and then converted them to USD, EUR, and SGD for easier comprehension for the C-suite leaders, as well as the directors of the other regions.
Her table looked something like this, let’s call this the “Revenue table”:
| Project Name | Currency | Amount |
| Project A | USD | 15,000 |
| Project B | EUR | 9,000 |
| Project C | USD | 4,500 |
| Project D | AUD | 900 |
| Project E | SGD | 3000 |
| … | ||
| Project L | EUR | 6,500 |
After 2 hours of work, Natalie usually ends up with a table that is something like this, let’s call this the “Presentation table”:
| USD | EUR | SGD | |
| Total Revenue | 55,122.00 | 48,286.40 | 71,374.57 |
Natalie’s process:
- Since the currency of every row is not the same, not all rows needs to have the amount converted. Natalie started by grouping the rows by the currency.
- She calculated the subtotal in each currency
- Converted them into one currency (USD/EUR/SGD) by Googling for the real-time exchange rate and then multiplying the amount.
- Summed them up in to one grand total.
- After getting the grand total, she has to converted the grand total to 3 currencies again.
- Check through everything and make sure everything is accurate.
The process was quite straightforward and Natalie tried to minimize mistakes by using the excel SUM function to reduce manual calculation. As these are sensitive figures to the company, the data presented has to be accurate. Therefore, a strigent check by Natalie usually follows after the process. The biggest headaches were when the numbers don’t seem to tally or when it was last discovered that there was a project that was left out from the data entry.
Natalie often wonders:
- Did I accidentally exclude a row from the SUM function?
- Did I group a row in the wrong currency?
- Did I multiply incorrectly for the currency conversion?
- Where did I go wrong?
- There must be a faster, more efficient way to do this.
With error carried forward, it is usually faster for Natalie to redo than to pinpoint and fix the problem.
Even though working late is a norm for Natalie, she still wishes to reduce the hours of overtime work to spend more time with the people who mattered most to her: her 3 children.
When she shared her list of routine tasks with TN Tech Consultancy, this meeting preparation was among the first things we identified that could be greatly sped up.
Natalie’s requirement was simple:
“I want to minimize manual involvement, and if possible, I want as little change to the format and structure of things.”
Once her process was understood by our team, we got to work. The final solution, from 6-steps that took 2 hours, to 3-steps that took an average of 10 minutes.
- Googling for the real-time exchange rate
- Copy and paste the exchange rate to the following table, let’s call this the “Currency Exchange table”:
Currency USD to: 1 USD SGD 1.30 EUR 0.88 - Check through everything and make sure everything is accurate.
We automated the calculation by currency. In laymen’s term, we added conditions. “Add this row to the sum if currency is ‘USD’”
All Natalie has to do is just enter how much 1 USD is equals to in the respective currency, and the excel sheet will do the conversion automatically.
Since the rows are automatically categorised and all calculations are now automated, Natalie has less worries for human error.
But that’s not all, even though at this current moment, the final table that Natalie has to present looks the same, our team planned further and designed the excel sheet to be able to adapt to the following scenarios:
- No problem if a project was left out. New row(s) can be injected anywhere in the revenue table, it will be included into the calculation in real time.
- The presentation table can be scaled for more currencies. Simply add a new to the currency conversion table, and a new column will be added to the presentation table.