Use Excel to create a Balance Sheet view

A balance sheet view in Excel


The basic accounting equation, also called the balance sheet equation, represents the relationship between the assetsliabilities, and owner’s equity of a business. It is the foundation for the double-entry bookkeeping system. For each transaction, the total debits equal the total credits. It can be expressed as

 \text{Assets} = \text{Liabilities} + \text{Capital}  [1]
 a = l + c

In a corporation, capital represents the stockholders’ equity. Since every business transaction affects at least two of a company’s accounts, the accounting equation will always be “in balance,” meaning the left side should always equal the right side. Thus, the accounting formula essentially shows that what the firm owns (its assets) is purchased by either what it owes (its liabilities) or by what its owners invest (its shareholders equity or capital).

(from Wikipedia)

And because we are plotting a corporate balance sheet, we state the formula:

Assets = Liabilities + Equity

, which in turn could be expressed as


This basic and simple view of the balance sheet (BS) allows managers to grasp the full picture very quickly.

As a way of example, we have translated into Excel the BS of an imaginary legal entity that holds a portfolio of funds. This entity is being financed by investors in the equity tranche in 3 different currencies: US dollars, Swiss Francs and Euros. It also uses a credit line to boost its performance. On the assets side, besides the portfolio, there is unencumbered cash, cash reserved solely to cure any margin call, and a margin deposited in a bank as a collateral of the FX hedging needed.

In a blink, we can asses how far away the entity is from being distressed. We can also elaborate a visual understanding of how much the portfolio is actually levered. And we can asses what parts of the equity tranche (net asset value or NAV) are being funded in each currency.

The invested portfolio is the blue area that is further subdivided into lighter blues indicating funds grouped by different liquidity. Assets are presented sorted by liquidity, therefore the first lighter blue area indicates funds with weekly and monthly liquidity, the second with quarterly and the rest are less liquid funds.

We have done all this using a set of Store Procedures and qUtopic functions connected to a Webfolio’s database running on SQL Server .

Connecting qUtopic templates to Azure

All templates’ sample data are stored in a database in Azure so you can quickly check the functionality of the Excel Add-In.

If you are behind a Firewall you need to make sure that your port 1433 is not blocked.
note Note

In addition to configuring the Azure SQL Database firewall, you may also need to configure the firewall on your network and local computer. To access a Azure SQL Database database from your computer, ensure that the firewall on your network and local computer allows outgoing TCP communication on TCP port 1433. (The Microsoft Azure SQL Database service is only available with TCP port 1433.)

Further reading can be found here: http://msdn.microsoft.com/library/azure/ee621782.aspx

Monthly report template “Blue”


If you are a fund management company in need to communicate your investment products’ periodic performance your most cost-effective answer is Excel®. You can produce a high-quality eye-catching report with a professional look for your clients and prospects.

The report format should be stable enough to convey a branding message, but with certain flexibility to be a real useful communication tool.

You would probably like to add your comments, news, awards, and even cutting dates for new subscriptions, but most of the things will remain constant. Some data is repeated exactly every period. We call that static data. And some other data changes as time passes by. We call this dynamic data.

The great part of this Excel template is that uses “dynamic data” from an SQL Server database.  In this case, we have posted data in the cloud so you can test the report.

If you open this report, with qUtopic.xll installed, you will see only one sheet in your workbook. On the left how this report will look like in the classic WISWIG way and on the right all the data used.

We used blue to mark static input data, green locally calculated data, and orange data obtained from a database with qUtopic functions.

Take a look to the template! It’s free.

Cross-correlation template


Up to 250 x 250 instruments in a single correlation matrix. Each cell takes the value of the correlation between the instrument in the row and in the column displaying a numbers between -1 and 1. Then the matrix is coloured using red for 1 or high correlation, yellow for 0 or no-correlation, and green for -1 or uncorrelation.


Try Adventure Works with Azure


In this example you can test how fast Excel could be dealing with 20,000 contact names coming from Azure. The spreadsheet uses a list of names obtained using the statement SELECT FirstName, LastName AS Name FROM Person.Person ORDER BY FirstName directly from then AdventureWorks2012 database.

Then using a ComboBox, which is a standard ActiveX control, you can choose one of those names to, in turn, feed another formula that gets details of that contact.