Canopy Financial Statements Module

Disclaimer: Canopy is an Analytics company and does not specialize in fields of accounting and taxation. These calculations are intended to give a customer a sense of how their financial statements would look like. They are offered as a convenience to our customers and are not intended to replace your accounting process or be used for reporting purposes. You should consult your accountant and tax advisor for any accounting, regulatory reporting or taxation matters.

Introduction

Canopy Financial Statements Module (CFSM) can be used to create basic Financial Statements. This module takes all the data inside Canopy Analytics and creates journal entries, ledgers and financial statements. See example output attached.

Setup

CFSM will create the following tables (if they do not exist already)

• chart_of_accounts
• journal_entries
• ledger
• balance_sheet
• profit_loss

Chart of Accounts

These are the backbone of CSFM. CSFM will automatically create a complete set of accounts as follows:

A sample chart of accounts is here

Journal Entries for Purchase and Sale of Stock in Foreign Currency

Journal entries are created by going through the flash_blotter in chronological order. Let's take the following transactions for NTT (a JPY denominated stock). These are for the canopy_demo account (where the base currency is USD)

Background

The flash_blotter entries are as follows

As we can see this stock was purchased on 31 Dec 2013 and then sold in 2 different lots and also earned a dividend. The market prices and fxrates moved for each transaction. The journal entries are as follows

Purchase of Stock in Foreign Currency

We record the purchase as

(a) Credit the Cash Account with the equivalent amount in base currency i.e. 3100 * 5660 * 0.009497 = 166,634.36 .. please remember that all debits and credits are ALWAYS in the accounting currency (i.e. the base currency)

(b) We also record the actual amount in contract currency (i.e. 3100 * 5660 = 17,546,000) for reconciliation purposes in column cash_quantity

(c) Debit Equity (being the investment group dictated by "user_asset_class") for the same amount i,e 166,634.36

(d) We also track the outstanding amount of each purchase in a separate column called security_quantity_outstanding

The journal entries look as follows

Sale of Stock in Foreign Currency (Lot 1)

Even though the sale price of 5648.953 was lower than our purchase price of 5660, a small gain was made on this sale due to a favourable fx rate movement (from 0.009497 to 0.009837). Even though the total sale consideration was USD 88,910 we need to split it up into a reversal of the original purchase and record the difference as a trading gain. The entries are as follows

(a) Credit Equity with a proportional amount i.e. 166,634.36 x (1,600 / 3,100) = 86,004.83

(b) Debit the Cash Account with the same amount 86,004.83

TradingGainLoss is calculated from the balance amount i.e. 88,910 - 86,004.83 = 2,905.17

Credit(+)/Debit(–) the gain from difference between sale price and average purchase price i.e. Transaction Quantity X (sale price – average purchase price) X (local ccy fx rate / base ccy fx rate) or 1,600 X (5,648.953 – 5,660) X (0.009837 / 1) = –173.87 (Debit)

(c) Debit the PriceTradingGainLoss Account with the amount calculated above i.e. 173.87

(d) Credit the Cash Account for the same amount

(e) Credit the balance amount i.e. 2,905.17 – (–173.87) = 3,079.04 to FxTradingGainLoss

(f) Debit the Cash Account for the same amount

(g) Update the security_quantity_outstanding to 3,100 - 1,600 = 1,500

Sale of Stock in Foreign Currency (Lot 2)

We now sold the remaining quantity of stock at a different price and exchange rate. The accounting entries are similar to the ones above. We also update the security outstanding quantity (which is now zero). 

Receipt of Dividend in Foreign Currency

(a) Credit Distribution for the equivalent amount in base currency (i.e. 236,271 x 0.009862 = 2,330.10)

(b) Debit the Cash account for the same amount

Conclusion

At the end of this exercise we have

(a) All credits to Equity (i.e. USD 166,634.36) equal all debit to Equity (USD 86,004.83 + USD 80,629.53 = USD 166,634.36) thereby squaring off the entries.

(b) We have a net FX gain from sale of (–USD 173.87) + USD 2,776.23 = USD 2602.36 

(c) We also have trading gains from price changes of USD 3,079.04 + USD 4,861.89 = USD 7,940.93

(d) Finally, we have dividend income of USD 2,330.10

Journal Entries for a Spot Fx Transaction

Let us look at a scenario where the customer bought USD 1mm against JPY on 24 Mar 2018 at a spot rate of 104.72. The market moved in favour of the customer who then took profit on the trade on 19 Jul 2018 at a spot rate of 112.79.

Background

The flash_blotter entries look as follows:

Purchase of USD against a Foreign Currency (SpotFx Purchase)

We use an account called SpotFxTrade to record all the gains and losses from Fx translation. On 24 Mar 2018 the closing as per Bloomberg was a little higher at 104.7450 (i.e. 0.0250 JPY per 1 USD higher than the transacted rate of 104.72). This will mean a small gain for the customer on their purchase of USD at a rate of 104..72. This gain of 0.0250 JPY per 1 USD is approx USD 238.67 (because  1,000,000 x 0.0250 = 25000 JPY, which is equivalent to 25,000 / 107.7450 = USD 238.67). We should see this gain in the booking. The journal entries are as follows

(a) USD leg: Debit USD cash account and credit SpotFxTrade (both legs for USD 1,000,000)

(b) JPY leg: Credit JPY cash account and credit SpotFXTrade. This leg translates into 104,720,000 / 104.7450 = 999,761.33 for each leg

This means that SpotFxTrade sees a credit of 1,000,000 and a debit of 999,761.84 = gain of USD 238.67 (as expected)

Sale of USD against a Foreign Currency (SpotFx Sale)

Now let us look at the take profit transaction where the customer bought back JPY 104,720,000 against USD at a rate of 112.79 (i.e. USD 928,451). The USDJPY SpotFX rate on that day closed at 112.8668. The entries are as follows:

(a) USD leg: Credit USD cash account and debit SpotFxTrade (both legs for USD 928,451.10)

(b) JPY leg: Debit JPY cash account and credit SpotFxTrade (both legs for JPY 104,720,000 divided by closing rate of 112.8668 = USD 927,819.34)

This results in a small translation loss of 927,819.34 - 928,451.10 = - USD 631.76

Conclusion

(a) Translation Gains: At inception the customer had a translation gain of USD 238.67 and on closing had a translation loss of (USD 631.76) for a total translation loss of (-USD 393.60)

(b) Trading Gains: The customers JPY closing balance is back to zero but the USD cash balance have increased by USD 1,000,000 - USD 928,451.10 = USD 71,548.90. This gain will result in an increase in the networth of the account.

Journal Entries for Derivatives

Introduction

Derivatives can be tricky for the following reason

(a) They are easy to short (and most positions are initiated via a short sale)

(b) Bank Reporting on derivatives is usually sketchy and expiring derivatives often just 'disappear' from the following months closing statements. These are then booked as Transfer Out (or Transfer In if the original position was a short sale) in Canopy and need special treatment.

The following paragraphs summarizes our derivative bookings.

New Positions or Adding to Existing Positions (i.e. same direction as existing trades)

Unwind of Existing Positions (full or partial unwind)

Summary of Journal Entries for all Trade Types

Journal Entries depending on the Trade Types are given below: