Multiple Currencies Receive Payment & Make Deposit Excel 1325

QuickBooks Online 2021 multiple currencies receive payment and make deposit in Excel. Let’s get into it with Intuit QuickBooks Online 2021. Here we are in our QuickBooks Online multiple currencies practice file in prior presentations, we set up the free 30 day trial so that we can practice with the foreign currencies turning on the foreign currencies, then what you can find in the cog up top, and then going into the advanced settings for the foreign currencies, we’re going to be opening up our balance sheet and income statement by going to the tab up top right clicking on it, duplicate it, we’re going to duplicate it again,

00:38

go into the tab up top right click on it and duplicate again, we’re going to go to the reports on the left hand side, then open up the PnL the profit and loss range change up top for the date range from a one a one to zero to 1231 to zero, we’re gonna run that report, close up the burger hold down Control, scroll up just a bit, we’re at the one to 5% up top next tab on over to the left, back down to the reports on the left hand side. Now open up the BS balance sheet report date range change up top from a one a one to zero to 1231 to zero, run it run nine closing up the hamburger.

 

01:22

This is what we have thus far. So we put on the books a sale in Canadian dollars even though the US dollar is our home currency invoice, then going on the books here. If we go into this transaction, we can see that the invoice went on the books that we’re going to be receiving in the future 42,000 Canadian dollars, which at the point in time of the invoice was equal to 27,300 US dollars, therefore it goes on our books for the US dollar equivalent at the point in time of the sale. Even though we didn’t get any money in either Canadian or US dollars.

 

01:55

Back then to our report, we then reported a period end adjustment imagine it as of the end of 530. So we should probably make this date end date here. As of Oh 53020. Let’s make it Oh 53120. Run that report that’s like the cut off date, where then we enter this adjusting entry. So we didn’t receive the money. But we wanted to make our financial statements correct with regards to the foreign currency as close as possible with the current exchange rate as of the period end of 530 the end of the month. And therefore we recorded this adjusting entry to do so.

 

02:33

So if we add these together, these would be the US dollar equivalent as of the point in time that the adjusting entry was put in place, which is the cutoff date. Now we’re going to say that we’re going to receive the payment, we’re going to receive the money. So what we will typically do here, the easiest thing to do is reverse the the adjusting entry as the first day after the cutoff period first day after the month, which will make it wrong as of that point in time.

 

02:59

But then after recording the transaction that will take place the receiving of the money, then it’ll it’ll play out it’ll those two things will tie out. And we’ll be correct. As of this point after. As of that point in time, that’s the easiest thing to do, because QuickBooks will automatically record the gain or loss between the point in time of the invoice in the point in time of the collection of the currency. And this adjusting entry. If we have to do it will kind of muddy up the process in the middle.

 

03:28

So once we reverse this, we’ll be back basically to a normal transaction that was we can imagine as if it happened in the same period where we didn’t have to deal with a period in the adjusting entry and then just simply do the normal accounting process. Let’s first think about it over here in Excel. So in Excel, we did a similar type of transaction here, we did the adjusting entry, but in Excel, we did it in order to basically get our our accounts receivable up to the 28 560, because that’s the amount that we’re actually going to be receiving.

 

04:03

In other words, we started out putting it on putting the 42,000 Canadian dollars on the books for 42,000 times the exchange rate as of three one the date that the invoice was put in place, and then we added to it another 1200 which is the takes us up to the point in time where then where we get the money at this point in time for this example problem, which is going to be the 531 rate. So now we are at if we if we take a look at it, we’re at the 42,000 times the point six eight the current exchange rate, and that’s where we stand here and with a journal entry format, then that allows me then to do the next step, which is to collect the money, the money is going to be collected in Canadian dollars.

 

04:48

So in the Excel worksheet, I’m not going to I’m not going to do a reversing entry, because we’re just going to record the collection at this point in time. This entry made it easy from a from a journal entry standpoint to Record the collection. in QuickBooks, we have to do a reversing entry because QuickBooks will automatically record the difference between between you know these two dates automatically. So we’re going to reverse it. So QuickBooks can do that transaction, the way QuickBooks wants to do it. So that’ll kind of make sense, hopefully, once we get there.

 

05:17

So what we’re going to do then, is once we collect the money, we’re going to get foreign currency. So I can’t put it into cash because cash is in US dollars, I got to put it into foreign currency money. This is a little bit confusing. Because once again, we’re gonna we’re got cash, it’s cash, but it’s not in our home currency. So it’s still liquid asset, it’s still a very liquid acid, it’s cash, we could spend it and whatnot, but it’s foreign currency. So therefore, when we put it on our books, we still have to measure it in terms of US dollars.

 

05:45

So this account is going to indicate that the cash is in foreign currency units, we have so many foreign currency units, in this case, 42,000 units of foreign currency, which are valued in US dollars as of the current exchange rate. So we’ll do this calculation, that’s going to be then the 42,000 Canadian dollars at the current exchange rate of 68.68. And that’s going to be the debit and the credit. And of course, that matches what’s currently in the accounts receivable for the Canadian dollars. That’s why we revalued it for Excel purposes here.

 

06:15

Now we’ll record this transaction increasing our cash that goes up by the 28 516. Notice this is a cash account, we have 28 560 in it, which isn’t the amount of units that we have in it, we have 42,000 Canadian units in it, which have a US equivalent based on the exchange rate at that point in time of 2560. And then we’re going to reduce the accounts receivable double clicking on that plus, and we’ll take the accounts receivable down. So now the accounts receivable has gone back down, and we’ve recorded that foreign currency.

 

06:48

Now in QuickBooks, we’re going to do another step, because we’re basically going to reverse this step here. Because we’re thinking of it in QuickBooks as basically an adjusting entry as of the end of the period. And then we’ll we’ll do the transaction. If this step wasn’t here, if we didn’t need an adjusting entry, then then this entry in QuickBooks would be a little bit different, because we would simply record the gain at the point in time we record this transaction, in other words, we would include kind of this gain in the transaction with the normal process of QuickBooks.

 

07:18

So let me see if that makes sense. We go back to QuickBooks, and I’m gonna say let’s reverse of this transaction, that’ll get us back to where we want to be. Normally, this would be done like the first day of the next time period, which would be June. So let’s imagine we’re going to do it in June 1. But we’ll we’ll imagine that the exchange rate hasn’t changed in that day as well. So let’s go ahead and reverse that, that’ll be the typical process. So I’m going to, what we would do is, is look at that journal entry in this reverse that exact so I’m going to go in here, I’m going to say plus, and I’m going to go then into the the journal entry journal entry.

 

07:53

And I’m going to make this as of the first day of the following period, it’s going to be in US dollars. So I’m going to make this on six, one standard reversing entry kind of process, we do reversing entries in order to make the difference or, or accounting of the cut off, not mess up or affect the normal journal entries of the accounting department. And then I’m just going to reverse it exactly. So basically, I need this to go down to zero. So that’s on the books as a debit, so I’m going to credit it. So I’m going to say all right, this is going to be accounts receivable, adjusting entry, I’m going to be picking up this item, and we’re going to credit it for the 12601260.

 

08:34

I’m missing a six there 121260. This is going to be a DJ entry. And then the other side, I’m going to say is going to go to that gain account the gain account. What did I call it, I know what it was it was foreign currency gain loss. That’s the one and I spelled it wrong GYN. Sorry about that, I might fix that. But I’ll keep keep it for now, I’m going to reverse this exactly, we’re going to save and close that, save and close that.

 

09:07

And then if I go back on to my financial so this is now correct, I’m going to hold down Control scroll up just a bit one to 5%. This is where we are as of the cutoff making the financial statements as of the first day of the next time period. Bringing us up to six one, we have now reversed this back down so that’s back down to zero. And we are at the same point as if we didn’t do an adjusting entry meaning this is still on the books as of the exchange rate that we first put the invoice on the books for and now we can do kind of like the normal thing for QuickBooks if I go back to quick to the to the profit and loss and I bring this up or if I run this again.

 

09:48

So now the gain and loss has been down to zero. You can also think of it as if I had it as of Oh 531 to zero then we See the gain on the books here. And then of course, if I go to the next time period, the next month, if I run just the next month, oh 601 to 0206, let’s say 30 to zero, run it. Now we have a negative amount there. And that negative amount is kind of like wrong right now. But it will be correct once we basically record the transaction of us receiving the Canadian dollars.

 

10:26

And that’s kind of what the how the reversing entries will work. So now we can just basically if we think about this, this receivable, we’re in the normal process, now we can just record the receive payment, and QuickBooks will record the transaction basically, for us recording the gain or loss properly. So let’s do that, we’re going to go back to the first tab, and now we’re just going to imagine we receive the Canadian dollars, straightforward type of transaction, we’re going to say new, and receive payment. And then I’m going to be choosing the customer which is Canada one, that means we must be receiving in Canadian dollars,

 

11:00

it will pick the current exchange rate at the point in time that we’re getting the money, we’re going to say as of six, one. So that would be the exchange rate we would use. But this being a practice problem, we’re going to use the book exchange rate, which was given at the point six, eight, I’m using the same one for 531 as six one, so I’m imagining it’s the same amount here. So point six, eight, we’re gonna use that point six, eight, and then I’m just gonna use that for this transaction only. And there we have that I’m going to check this off.

 

11:30

And so that’s the amount that that we are receiving in any kind of form of payment that we’re going to be getting. And then it’s going to be going into the checking account. Now notice here, we have the the US checking account, if we’re receiving the Canadian dollars, we might then be putting this into Canadian, you know, dollar checking account. So I might have to be careful with that if I’m receiving Canadian dollars, I’m going to put it into a Canadian dollar checking account. So I’m going to make a new one of these bank accounts, I want to make it a checking account. And then I want to change the currency type to Canadian dollars.

 

12:06

So this checking account will be holding Canadian dollars, but we’ll still have to represent it in US dollar equivalents. So I’m going to say save it and close it. So there we have it. So now we’re going to be getting the $42,000, I’m going to put it directly into the checking account, we may need to go through undeposited funds and so on in a similar process as with normal accounting, but I’m just going to go directly into the checking account here.

 

12:29

And it’s going to be in the new checking account holding Canadian dollars, the US dollar equivalent is that 28 560, which is the same amount that we recorded here, because we use the same exchange rate. So this will then be increasing the checking account by the 28 560. US dollar equivalent even though the checking account will be holding 42,000 units of foreign currency Canadian dollars in the other side decrease in the accounts receivable. Let’s check it out and see if that is true. Save it and close it. And then we’ll go to the currency to the balance sheet.

 

13:06

Balancing the sheets, I can balance a lot of sheets at one time on my nose love sheets, okay, so then we’re going to go down and the accounts receivable is back down now. So if I select the accounts receivable, and then we’re going to go over here, there’s going to be our our payment that was received bringing this back down to zero. So it brought it back down to zero. And notice it brought it back down to zero. For the amount of that it was on the books for meaning it recorded exactly the amount of netted out to zero 27 380 27 300, not 28 560.

 

13:43

So it kind of worked out that difference for us the other side, then going into the checking account, it went in there for 28 560. So if I go into this transaction, there’s the 28 560. The difference between the two, QuickBooks is recording automatically to the income statement. So then if I go to the income statement, run this report, then it recorded down here in exchange gain or loss, this difference and it did it automatically. So there there did that transaction scrolling back up, closing this out. So so that’s why QuickBooks does it for you.

 

14:21

And it’s it does a really nice job. And it’s nice and easy unless of course you have to do that period and adjustment. So if I go back to excel then then basically, you know, QuickBooks recorded a transaction that would look something like this right it said it said that it got foreign currency units of the 28, the 28 and then it took off the the accounts receivable took the accounts receivable off for what it was on the books for which was the 27 three and then the difference. It recorded to the gain the gain or loss foreign current Again, a lots of did that and kind of like one transaction.

 

15:02

And when we did it up here, we did it kind of like in two transactions, right, we recorded, we adjusted our receivable up to the 28. And then we basically recorded the receiving of the of the currency for the value at the exchange rate at that point and then taken off the receivable. So we did this, this kind of makes sense to do this in excel in two separate journal entries, even if you don’t have a cut off period adjusting entry. But this kind of format you have up here is useful to look at in the case that you have a cut off adjusting entry, because this works.

 

15:35

Kind of like an adjusting entry you might have at the end of the month or year end, if you do that adjusting entry, then it’s best as we did in QuickBooks to reverse it. Because QuickBooks wants to do this, it wants to record the three accounts, it doesn’t want to do this double step kind of thing. And so to allow QuickBooks to do that allow the accounting department to do the easiest thing possible. If you need to do an adjusting entry at the end of the month or a year, best to reverse it so that the accounting department can just record the receive payment.

 

16:04

And QuickBooks will do the magic for us. So if I go back on over here, that’s, that’s what’s going to be on the income statement. And let’s go to the income statement and run this again, if I run the income statement, then also note that these two items here are netting out against each other. So we should probably record them to the same account, I could go into this one. And they call the exchange gain and loss. Let’s see if I can adjust this adjusting entry I’m going to put into if we put it into exchange, gain and loss here, instead of making another account, so we’ll call it exchange, gain and loss. And then I’ll save it and close it.

 

16:47

And if I go back up, then those two accounts net out in the current time period. And because it shouldn’t be recorded, no income should be recorded in just simply the month of June, even though we’re saying we got the payment, basically in June, because because basically, the exchange rate was what it was, as of the end of May, let’s bring this back to two may Oh 101, to zero. And then we could see the full thing here for the full time period. And now we’ve got the foreign currency recorded in the in the proper area.

 

17:20

But again, we might want to put it all down here to this exchange, gain and loss stead of this foreign currency. So let’s just do that I’m going to I’m going to reset this one, this transaction, and we’ll put it to the same account. So this one has to have two of them, I’m going to record this one and change it to exchange gain loss. In this one I’ll change to exchange gain loss. So they’re all going to exchange gain loss instead of foreign currency, save it and close it. And then I’m going to go back up top and back to our income statement.

 

17:52

And so therefore, for the entire time period, we’ve recorded that 1002 60. And it was recorded basically in the month of May, because that’s kind of when it was generated. Okay, so now let’s take a look at the trial balance mean I hit the right click up top, let’s just open it here. I’m just going to open up the go to the reports, hamburger reports. Then I’m going to type in trial balance trustee TB trial balance. And we’re gonna say there it is, let’s run it from Oh 101 to zero to 1231 to zero, run it like jenai told us to Okay,

 

18:28

so then this is going to be the checking account here so we can kind of tie this out then we can kind of tie this out over here so that we got the 100,000 and the 28 560. So 100,000, the 28 560 retained earnings 100,027 27 three and 1260. there it’ll try to print out the trial balances. So if you’re following along, you could check your work against the trial balance, which is probably the easiest place to do so.

 

Leave a Reply

Your email address will not be published. Required fields are marked *