Tips & Tricks: Tempering FX Volatility With Currency Analysis with Will Leonard

Will: Hi, everybody. Welcome to currency analysis in Planful. My name is Will Leonard. I'm a managing solution architect here at Planful. Today we're going to talk about currency analysis or FX impact for short and how it can help FP& A. So we're going to go through a brief example, just laying the groundwork here. What is FX impact, and how is it calculated? Starting very simply here, we can see in the top left we have an asset as of a prior year in euros, and the same asset has the same value as of the current year in euros, and there's no change in local currency. So there was no movement or activity at all for that asset or for that asset account. Whereas once that is translated to a common currency, for example, USD, we can see that the asset in the prior year was 30 million, in the current year, now it's 33 million, and so that change in common currency is actually 3 million for that same account or that same asset year over year. So this instance there was no activity whatsoever, a hundred percent of that change and that consolidated balance sheet is due to FX impact. So that's a 10% change in the value of the asset and that's all due to currency fluctuations. Believe it or not, that's actually a realistic number. We can also take this same concept and we can look at it at actual versus budget, for example. So in the actuals, we have 27 million in euros and in our budget we have the same amount, so we're not planning any activity of that calendar in that asset, but because the exchange rates differ between your actual exchange rates and then what we used for a budget exchange rate, there's a variance there. So we can look at actual versus budget or actual versus forecast type analysis and carve out the FX impact, just using the same methodology. Budget versus forecast is yet another example. So it's just like actual versus forecast. In this case, we're comparing the budget FX rates and the forecast FX rates to derive the variance in common currency. And this is a little more complicated, but it's definitely more realistic. So we have a prior year amount in euros, is some number. The current year amount in euros is some other number. So there was some activity, there was a year over year variance or movement in that account, so this could be any type of asset or anything at all on your balance sheet. And then we can see in USD that those are translated at different rates. Now we have a year over year variance in common currency and in local currency. And so the trick is, how do we carve out just the FX piece of that? What component of that change in common currency was all due to FX fluctuations? Some other key concepts. So to derive that and figure out exactly what part of that movement is just from FX, we have to figure out what is the FX impact just on the opening balance. So with no movement at all, what's the impact? And then on the exchange rate fluctuations within the period, whether you're reporting on a full year, full quarter, or just a single month, what is the FX impact on that activity? And then the total is just the opening FX impact, plus the FX impact on activity. So for FX impact reporting, how to do currency analysis in Planful. First off, we are finance, we're FP& A, we're accounting, of course it has to be right. And there has been a lot of due diligence on this type of reporting in Planful to make sure that we're getting consistent, correct results. So a little levity there, but it's only half kidding, right? The numbers definitely have to be right. We have to come up with the right answer in the end. This is an example of an FX impact report. We can see going from the top, so the assets down into the liability section, we're just looking at a section of the balance sheet here, and then going from left to right. We can see the opening balance, we can see the FX impact on opening. We can see the movements and the FX impact on movement, the closing balance, and total FX impact. So the building blocks for FX impact on opening are of course the opening balance in local currency. This is really basically back to slide one. Opening balance at the opening rate. So what did it look like in the last period? Last year, last quarter? And then what does it look like today using that same opening balance? So of course we're retranslating that opening balance using today's closing rate or the end- of- month rate for the current period. And then we just do some subtraction and we find out at that point what the FX impact is on opening. For the movement or activity, we also determine what is the periodic and month- to- date activity for any given timeframe, again, a month, a quarter, a fiscal period, a year. And we determine, what is that periodic activity? We translate that at the average rate, we translate that at the closing rate, we do a little bit more subtraction and we come up with the FX impact on movement. And then finally, to get to the total FX impact, we are just taking the FX impact on opening plus the FX impact on movement to derive the total FX impact. So there's one other component with FX impact. A lot of times people will refer to FX impact as CTA, or they will use those terms interchangeably. For the purpose of this demonstration, they are two different things. CTA is your cumulative translation adjustment, and that has a lot to do with your balance sheet accounts that are not translated using an end- of- month rate. So for example, Goodwill current year income, historic retained earnings, to name a few. The CTA is going to subtract the balance using the end- of- month rate. And then the balance as is. In other words, if you're using a historic or a spot rate to translate to common currency for these accounts, and they don't retranslate month to month. We have to determine what that cumulative translation adjustment is for those specific accounts. So now that we've determined how to calculate FX impact and the difference between FX impact and CTA, how do we do this in Planful? The solution we're going to be looking at today really encompasses just the following artifacts. So we've got one new reporting currency. This is a custom reporting currency. You will need the consolidations module to consolidate your numbers, whether they're actuals, budget forecast, and translate using the custom reporting currency. One new substitution variable, very simple to add a new column set. That's actually optional. You can also use custom members, we'll show that today. Consolidate, and then report. So that's the solution in a nutshell. Let's go ahead and take a look at what that looks like in Planful. So we mentioned a custom reporting currency for FX impact and CTA calculations. We actually only need this one, this movement at average. We can also, to make some things a little bit easier, we can use a movement at end. We're very sensitive to the fact that when we add reporting currencies, sometimes you might have other ones. You might have add budget rates, or plan rates, or add a constant currency rates. And you might have a prior year rate. Things like that that you already have implemented, and you don't want to take up more space and have more calculations to do in your consolidations module. That's totally fine. For this purpose, we only need the one. We are using this movement at end for cashflow FX impact. But in this case, we're just going to focus on the movement at average here. You can see that the rate that it uses is just the monthly average rate. So there's not another rate to maintain. It'll just leverage what you already have for your average rates. You'll translate to your common currency. In this case, I'm using USD. And the calculation type here. I know it's very light text, but that's a flow type of calculation. So it's not account- based, it's always going to be a periodic calculation for this custom reporting number. So we also have some custom members here. I'll show you guys this a little bit later. We have a guide that we can share out at your request that actually has all of the setup steps for this. So don't worry about some of the complexity in these members. There is some MDX you don't have to know MDX. You just have to be able to copy and paste, and in a couple of cases, do a find and replace in Excel to get the MDX just right for your application. With that said, each of these members can be used as columns in any dynamic report. So you can use the custom members that have been defined here. Some of them are very simple. For example, this movement at average, all this is doing is just looking at the movement at average custom reporting member. Whereas we'll go to the most complicated one here, and you'll see for the FX impact in total, we've got quite a bit of MDX. This has been... It's gone through quite a few iterations. I mentioned it has to be right. Well, this has been tested in several different environments and it's definitely a little bit more complicated than your average MDX. The next step that the guide is going to walk you through is just to consolidate your actuals. Whenever you have custom reporting numbers, and you go to run your consolidation process, you'll notice that there's this box here for reporting currencies. All we technically need to generate FX impact is just this single box checked right here, movement at average. And you can run that for any period or periods in a financial year. I have the actual scenario selected here. We can also run this on forecast scenarios and budget scenarios as well. So for a consolidated company for example, this is our timeframe for this 2021 forecast. And as you can see, we can translate the movement at average for any scenario, not just actuals. We also have one new substitution variable. So what we're calling this is the balance sheet FX account. This is a good way for the custom MDX and those custom members to derive what the balance sheet and the month rate is, company by company. What we do is we define an account or a roll- up member and account. In this example, we're using current assets, just from the balance sheet. Current assets is a great example because that's going to have your cash and your AR and any sort of operational activity is usually going to drive movements or activity within the accounts and your current assets. So this is a great one to use. The criteria for this is that there has to be activity in every month for every company that's going to leverage this. And every account underneath the roll- up needs to be translated at the end- of- month rate. That's so that we can systematically derive that end- of- month rate and do some of those FX impact calculations on the fly. And then we have a report. So reporting on FX impact is probably one of the easier things you could do with this setup. Now, in this example, we are not using a column set, although we certainly can. I'll show you a column set in just a moment. What we're using instead is just the same custom members that we've set up using the guide. Just copy, paste, set up the substitution variable. Consolidate your numbers. And then the values that are calculated in each of these custom members will be automatically available in any report that you want to run on. You can see here, we have the same concepts that we went through, the opening balance and local currency. That balance translated at the opening rate, that balance translated at the closing rate, and then calculating FX on opening. If we look at that in the report, we can see that those are just going across the columns for any given account. We've got the FX on opening. We have the movement here in local at average, and then at closing, or the end- of- month rate, and our FX on movement. And finally we have our closing balance and then our total FX impact. And you can see also that the CTA here is also a custom member that we've added just to calculate CTA by account for the balance sheet. So the column set. If we wanted to put this in a column set, this is just a very simple example. We have the same categories here. Each of these is an advanced formula. So if you're going to edit and go into advanced... I'm sorry, I picked the one that's not advanced. This is just looking at the movement at average, custom reporting member, but everything else is using the advanced rule here. This is the same exact, just copy paste in the X available in the guide. You can use it in the custom members, or you can optionally use it in a column set. And again, just more copy- paste once that's set up, then it's available for reporting. I mentioned cashflow briefly, so we can also calculate the FX impact on cash. With cashflow statements, they have a little bit different cash FX requirements or currency requirements. In this case, I'm looking at a Canadian local currency translated to USD, and we can see that the Canadian dollars column here on the left, the US dollars column here on the right, and so we have the translated amounts. All of the cash flow activity is actually translated at the average rate. So everything except for the opening balance or the beginning of period cash and then the ending cash, which those are translated at the ending rate, or end- of- month rate for their respective periods is calculated at the average rate here. And then the difference that bridges that average to end is actually FX impact on cash. So we're not using this. This is not a plug. We're not taking our ending cash, beginning plus activity, and just kind of plugging the amount. This is actually using the exchange rates on the fly at the time that the report runs, using in this case a calculated account, to figure out the FX impact on cash. So we can actually see that account here. It's just a calculated account. We can also create a custom member. We can create a row set that uses that same formula within it, and we'll get the same answer using any of those approaches. So let's take a quick peek at that guide that I've mentioned. I'll just pull that up here. Couple of words about the guide. So this is definitely a work in progress. We've tried this out in a few places. We haven't tried it out everywhere, so you may have different requirements. Your data may be different. Perhaps you might need a non- gap reporting or non US gap requirement, or your data might be a little bit different. Different data requirements could cause some nuances that we hadn't thought of for this example. So just be aware of that, know that we're here to help, but this guide is presented as is, right? There's no warranty associated with it. And this is here to help you out and enable 80, 90% of our customers that'll have the same requirement, but we anticipate there will be a few that it doesn't quite fit, and we're here to help you out on that. You can see everything that we've just gone through live in the demo is actually demonstrated here with setup steps. So exactly how to set everything up as we go through the reporting dimension, as we go through consolidating, setting up the substitution variable, and we have some narrative about what I spoke about. For example, it needs to be a roll- up number. It needs to have monthly activity, everything underneath it needs to be translated at the end- of month- rate. Here's the custom member set up the way that we've set this up for you is we've put the MDX here. There is one step on this, which is simply that the IDX, that's this red number, the IDX code might be different for you. Probably will be different, in fact, for your tenant. And so you'll just need to find the IDX code. This tells you how. You would do a full sheet, find and replace here. So it would basically look like for the entire sheet, you would just find what we have. Let's say your IDX code is actually 161 for that custom reporting member. And you'd do a replace all. Your MDX is then ready for copying and pasting. And each of these has their own MDX. You scroll down and copy this, paste it into the custom members as you create them. And it's ready to go. Optionally, you can set up a column set. So this just goes through how to do that. We also have the full report set definition for that column set. Again, find and replace that IDX code for your custom movement at average member. But once you do that, the MDX is just right here, ready for copying and pasting into your column set. And then this also guides you through how to set up the report. This will show you how to set up the report either using the custom members or with the column set as well. All right. So as far as the implementation guide, again, it's a work in progress. It's presented as- is, so don't expect it to be perfect. It's not something you could reach out to our support team on. It's something you could reach out to customer success. And then we can help you with some of the nuances if you're having any issues implementing using this guide. For access to the guide, please do reach out to your customer success partner. And again, it includes all the necessary steps that you need to implement FX impact reporting in Planful. It's a simple copy, paste. And just to reiterate, all that MDX, it's not something you necessarily need to know or understand. It's just something you would need to understand that it's there and how it works, right? How the custom members are set up and how they leverage the custom reporting member, but that's about it. All right. And of course, if you need any help, if you have any questions, please reach out to your customer success partner or the Planful Engage forum, which is our newly launched forum that has a lot of knowledge sharing, tips and tricks. We have customers like you on the Planful Engage forum, as well as members of our customer success, professional services team and people across Planful. So thank you very much, and happy planning everybody. Bye.