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 here.

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:

Name	Account Type	Details
Bank Accounts	Current Assets	Canopy will automatically create one account for every currency account in Canopy. e.g. canopy_demo332-ibkr-01-hkd-01. We create a separate account for each currency account (even though it ends up being a lot of accounts) because we also track the account balances and reconcile them back with Canopy Analytics as a check to see if all cash entries were processed
Asset Accounts	Current Assets	Canopy will automatically create one account for every currency account in Canopy. e.g. canopy_demo332-ibkr-01-hkd-01. We create a separate account for each currency account (even though it ends up being a lot of accounts) because we also track the account balances and reconcile them back with Canopy Analytics as a check to see if all cash entries were processed
Loan	Current Liabilities	One Account for all the Loans taken by the customer
Various	Revenue	Currently these are Distributions, TradingGainLoss, MiscIncome and Interest
Various	Expense	One for each type of expense that needs to be tracked. Currently these are Loan Cost and MiscExpense
ShareCapital	Equity	Represents the shareholders equity in the account. All Inflow, Outflow, Transfer In and Transfer Out transactions hit this account

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.

flash_blotter_id	ticketref	traded_on	settled_on	ticker	quantity	trade_price	trade_type	contract_currency	ccy_account_code	related_security_ticker	user_asset_class	day_1_fxrate_trade_ccy	base_ccy
1805	DEMO1-012568	31-Dec-13	31-Dec-13	9432_JP	3100	5660	Buy	JPY	demo_equity_managed_cssg01_jpy_01		Equity	0.009497	USD
1953	DEMO1-012183	8-May-14	12-May-14	9432_JP	-1600	5648.953	Sell	JPY	demo_equity_managed_cssg01_jpy_01		Equity	0.009837	USD
3144	DEMO1-012187	29-May-14	3-Jun-14	9432_JP	-1500	5989.933	Sell	JPY	demo_equity_managed_cssg01_jpy_01		Equity	0.009824	USD
3152	DEMO1-012287	27-Jun-14	27-Jun-14	Cash	236271	0	Dividend	JPY	demo_equity_managed_cssg01_jpy_01	9432_JP	Cash	0.009862	USD

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

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

The journal entries look as follows

entry_date	ticketred	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amount	debit_account_code	security_ticker	security_quantity	security_quantity_outstanding	cash_quantity	security_trade_price
12/31/2013	DEMO1-012568	Buy	JPY	0.009497	1	demo_equity_managed_cssg01_jpy_01	166,634.36	Equity	166,634.36	3100	3100	-17,546,000	5660

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)

(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

entrydate	ticketref	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amount	debit_account_code	debit_amount	security_ticker	security_quantity	security_quantity_outstanding	cash_quantity	security_trade_price
12/31/2013	DEMO1-012568	Buy	JPY	0.009497	1	demo_equity_managed_cssg01_jpy_01	166,634.36	Equity	166,634.36	9432_JP	3100	1500	-17,546,000	5660
5/8/2014	DEMO1-012568	Sell	JPY	0.009837	1	Equity	86,004.83	demo_equity_managed_cssg01_jpy_01	86,004.83	9432_JP	-1600		9,038,325	5648.953
5/8/2014	DEMO1-012568	Sell	JPY	0.009837	1	demo_equity_managed_cssg01_jpy_01	173.87	PriceTradingGainLoss	173.87	9432_JP	0		0	0
5/8/2014	DEMO1-012568	Sell	JPY	0.009837	1	FxTradingGainLoss	3,079.04	demo_equity_managed_cssg01_jpy_01	3,079.04	9432_JP	0		0	0

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). Look at the last three rows for the Sell entries:

entrydate	ticketref	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amount	debit_account_code	debit_amount	security_ticker	security_quantity	security_quantity_outstanding	cash_quantity	security_trade_price
12/31/2013	DEMO1-012568	Buy	JPY	0.009497	1	demo_equity_managed_cssg01_jpy_01	166,634.36	Equity	166,634.36	9432_JP	3100	0	-17,546,000	5660
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	Equity	86,004.83	demo_equity_managed_cssg01_jpy_01	86,004.83	9432_JP	-1600		9,038,325	5648.953
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	demo_equity_managed_cssg01_jpy_01	173.87	PriceTradingGainLoss	173.87	9432_JP	0		0	0
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	FxTradingGainLoss	3,079.04	demo_equity_managed_cssg01_jpy_01	3,079.04	9432_JP	0		0	0
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	Equity	80,629.53	demo_equity_managed_cssg01_jpy_01	80,629.53	9432_JP	-1500		8,984,899	5989.933
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	PriceTradingGainLoss	4,861.89	demo_equity_managed_cssg01_jpy_01	4,861.89	9432_JP	0		0	0
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	FxTradingGainLoss	2,776.23	demo_equity_managed_cssg01_jpy_01	2,776.23	9432_JP	0		0	0

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

entrydate	ticketref	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amount	debit_account_code	debit_amount	security_ticker	security_quantity	security_quantity_outstanding	cash_quantity	security_trade_price
12/31/2013	DEMO1-012568	Buy	JPY	0.009497	1	demo_equity_managed_cssg01_jpy_01	166,634.36	Equity	166,634.36	9432_JP	3100	0	-17,546,000	5660
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	Equity	86,004.83	demo_equity_managed_cssg01_jpy_01	86,004.83	9432_JP	-1600		9,038,325	5648.953
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	demo_equity_managed_cssg01_jpy_01	173.87	PriceTradingGainLoss	173.87	9432_JP	0		0	0
5/8/2014	DEMO1-012183	Sell	JPY	0.009837	1	FxTradingGainLoss	3,079.04	demo_equity_managed_cssg01_jpy_01	3,079.04	9432_JP	0		0	0
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	Equity	80,629.53	demo_equity_managed_cssg01_jpy_01	80,629.53	9432_JP	-1500		8,984,899	5989.933
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	PriceTradingGainLoss	4,861.89	demo_equity_managed_cssg01_jpy_01	4,861.89	9432_JP	0		0	0
5/29/2014	DEMO1-012187	Sell	JPY	0.009824	1	FxTradingGainLoss	2,776.23	demo_equity_managed_cssg01_jpy_01	2,776.23	9432_JP	0		0	0
6/27/2014	DEMO1-012287	Dividend	JPY	0.009862	1	Distribution	2,330.10	demo_equity_managed_cssg01_jpy_01	2,330.10	9432_JP			236,271

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

(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:

flash_blotter_id	ticketref	traded_on	settled_on	ticker	quantity	trade_type	contract_currency	ccy_account_code	canopy_asset_class	base_ccy
3515	TRX-000153996	24-Mar-18	24-Mar-18	Cash	1,000,000.00	FX Buy	USD	demo_equity_managed_cssg01_usd_01	Cash	USD
3516	TRX-000153997	24-Mar-18	24-Mar-18	Cash	-104,720,000.00	FX Sell	JPY	demo_equity_managed_cssg01_jpy_01	Cash	USD
3517	TRX-000153998	19-Jul-18	19-Jul-18	Cash	-928,451.10	FX Sell	USD	demo_equity_managed_cssg01_usd_01	Cash	USD
3518	TRX-000153999	19-Jul-18	19-Jul-18	Cash	104,720,000	FX Buy	JPY	demo_equity_managed_cssg01_jpy_01	Cash	USD

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)

entry_date	ticketred	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amt_base_ccy	debit_account_code	debit_amt_base_ccy	security_quantity	security_quantity_outstanding	cash_quantity	security_trade_price
24-Mar-18	TRX-000153996	FX Buy	USD	1	1	SpotFXTrade	1,000,000.00	demo_equity_managed_cssg01_usd_01	1,000,000.00				1,000,000.00
24-Mar-18	TRX-000153996	FX Sell	JPY	0.009547	1	demo_equity_managed_cssg01_jpy_01	999,761.84	SpotFXTrade	999,761.84				(104,720,000.00)

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

entrydate	ticketref	trade_type	ccy_of_entry	entry_ccy_fxrate	base_ccy_fxrate	credit_account_code	credit_amount	debit_account_code	debit_amt_base_ccy	security_trade_price
24-Mar-18	TRX-000153996	FX Buy	USD	1	1	SpotFXTrade	1,000,000.00	demo_equity_managed_cssg01_usd_01	1,000,000.00	1,000,000.00
24-Mar-18	TRX-000153997	FX Sell	JPY	0.009547	1	demo_equity_managed_cssg01_jpy_01	999,761.84	SpotFXTrade	999,761.84	(104,720,000.00)
19-Jul-18	TRX-000153999	FX Buy	JPY	0.00886	1	SpotFXTrade	927,819.34	demo_equity_managed_cssg01_jpy_01	927,819.34	112,790,000.00
19-Jul-18	TRX-000153998	FX Sell	USD	1	1	demo_equity_managed_cssg01_usd_01	928,451.10	SpotFXTrade	928,451.10	(1,000,000.00)

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)

Derivative Trade Type	Further Breakdown	Amount	Credit	Debit
Buy		Purchase Consideration	Cash Account	Security Account
Sell		Sale Consideration	Security Account	Cash Account
Transfer In
	Buy Leg of Transfer In
		Zero	Cash Account	Security Account
	Cash Inflow Leg of Transfer In
		Zero	Shareholder Equity in the Company	Cash Account
Transfer Out
	Sell Leg of Transfer Out (i.e. short sale)
		Zero	Security Account	Cash Account
	Cash Outflow leg of Transfer Out
		Zero	Cash Account	Shareholder Equity in the Company

Unwind of Existing Positions (full or partial unwind)

Trade Type		Amount	Credit	Debit
Buy to Unwind a Short Sale
	Security Account
		Cost Basis Amount	Cash Account	Security Account
	Gain/Loss if Profit
		Gain/Loss Amount	Trading Gain/Loss	Cash Account
	Gain/Loss if Loss
		abs(Gain/Loss Amount)	Cash Account	Trading Gain/Loss
Sell to unwind a long position
	Security Account
		Cost Basis Amount	Security Account	Cash Account
	Gain/Loss if Profit
		Gain/Loss Amount	Trading Gain/Loss	Cash Account
	Gain/Loss if Loss
		abs(Gain/Loss Amount)	Cash Account	Trading Gain/Loss
Transfer In to unwind a Short Position	This is booked as a Buy to Unwind a Short sale where Purchase consideration Amount is Zero (i.e Cost basis is equal to acquisition cost of derivative)
Transfer Out to unwind a Long Position	This is booked as a Sell to Unwind a Long Position where Sale consideration Amount is Zero (i.e Cost basis is equal to acquisition cost of derivative)

T-Account Example for a Call Option. View Excel file here.

Summary of Journal Entries for all Trade Types

Journal Entries depending on the Trade Types are given below:

Trade Type	Credit Account	Debit Account	Treatment
Buy	Cash Account	Asset Account	As shown in above example. Entries passed in equivalent base currency as of Trade Date. A track is kept of outstanding amount and that is adjusted for any Sale
Contribution	Cash Account	Asset Account
Coupon	Distribution	Cash Account	Same for Coupon, Dividend and Distribution
Deposit Drawdown	Cash Account	Asset Account	Accounting similar to a Buy
Deposit Interest	Interest	Cash Account
Deposit Repayment	Asset Account	Cash Account	Similar to a Sale. Reverse out the security_quantity_outstanding for the Investment leg
Distribution	Distribution	Cash Account	Same for Coupon, Dividend and Distribution
Dividend	Distribution	Cash Account	Same for Coupon, Dividend and Distribution
Drawdown
Expense Incurred	Cash Account	MiscExpense
FX Buy	SpotFxTrade	Cash Account	As Above
FX Sell	Cash Account	SpotFxTrade
Income Received	MiscIncome	Cash Account
Inflow	ShareCapital	Cash Account
Loan Drawdown	Liability Account	Cash Account
Loan Interest	Cash Account	LoanCost
Loan Repayment	Cash Account	Liability Account
Outflow	Cash Account	ShareCapital
Reinvestment	Distribution	Cash Account	This is split into two transactions (a) Dividend Received in Cash
Reinvestment	Cash Account	Asset Account	(b) Purchase of Stock
Sell	Asset Account	Cash Account	This is split into two transactions (a) Reversal of the original Buy transaction (for the proportional amount of original purchase consideration in base currency)
Sell	Cash Account	GainLoss	(b) GainLoss: For the difference between actual cash received versus the original amount paid
Transfer In	ShareCapital	Cash Account	This is split into two transactions (a) Inflow
Transfer In	Cash Account	Asset Account	(b) Buy of Security
Transfer Out	Cash Account	ShareCapital	This is split into two transactions (a) Sale of Security
Transfer Out	Asset Account	Cash Account	(b) Outflow

Categories

Categories