IDSB Banner

Converting Spreadsheets to Quantrix Models


Business modeling professionals often come to Quantrix having invested a lot of time and energy in creating and maintaining complex spreadsheets. Eventually, the question arises as to what would be the best way to get all this work into Quantrix quickly and accurately. This Modeler Note provides tips and techniques on how to do this. In this example, we will take a basic balance sheet from a spreadsheet and quickly convert it into a Quantrix model. You will learn techniques for:
  • Cutting and pasting items and cell data
  • Item grouping
  • Using Quantrix automatic summary items


Copy Accounts from Spreadsheet
First, the current assets portion of the balance sheet will be brought into Quantrix. Therefore we will want to start by building the items. This is done by selecting just the cells containing the current assets accounts in the spreadsheet, right click and then choose copy in the menu.
Note: Instead of using the right click menu of the mouse one can also select Edit from the menu bar and then choose copy/paste etc. from the drop-down menu.



Paste Accounts into Quantrix as Items
To get these item names into Quantrix, open up a new Quantrix Model, hold the CTRL key down and select item B1, right click and select paste from the menu. The names you copied from the spreadsheet will be copied directly into the item names underneath B1 thus saving you manual re-keying of the account names. To complete the process, delete the B1 item (as it is not needed) by selecting B1 in the matrix and pressing the delete key.



Group Current Asset Items in Quantrix
In this example we are working with a group of like accounts called current assets. Therefore it can be helpful to group these accounts in Quantrix. Do this by selecting all of the current asset items in Quantrix, then click the group icon in the Quantrix toolbar. Once the items are grouped, the group just created can be renamed to Current Assets.



Copy Cell Data from Spreadsheet
Now that the Quantrix structure is set up for current assets, it is time to get the account values from the spreadsheet into Quantrix. To do this, select the current asset value cells in the spreadsheet, right click and then click copy from the menu.




Paste Cell Data into Quantrix
Again we will paste into Quantrix. This time, click directly on the first cell, right click and select Paste from the menu. The cell data from the spreadsheet will be pasted directly into the appropriate Quantrix cells.



Create Automatic Summary Item in Quantrix
At this point, we want Quantrix to calculate the total value for the current asset group. An automatic summary item can be used at this point. Click on the Current Assets group name and then select the Sigma (Sum) icon from the toolbar. This will insert a summary item in the group totaling the current assets automatically. Rename this newly created summary item Total Current Assets.
Note: Alternatively, instead of selecting the Sigma (Sum) icon from the toolbar one could also opt to select Insert from the menu bar and then choose Summary item --> Sum from the drop-down menu.



Completed Current Assets Section
When completed with these steps the Quantrix matrix should look like this. Accuracy is ensured as no manual entry of items or data was needed to build the current assets section of the balance sheet.



Use Groups to obtain Summary Totals
Now the other sections of the balance sheet are ready to be built using the same techniques that were used to build the Current Assets section. To get started, simply copy the items from the spreadsheet for the next section, click on the preceding group name in the Quantrix Matrix and paste in the items. The items will be inserted underneath the group name selected. One can then create a new group by selecting the new items and then clicking on the group icon in the Quantrix toolbar as before and naming this appropriately. The cell data can be pasted in and the appropriate summary functions inserted exactly as done in the Current Assets example. After all the sections are built, there is a need to have summary items for Total Assets , Total Liabilities as well as Total Liabilities and Owners Equity. This can be accomplished by additional grouping of the existing groups in the Balance Sheet. In the case of building a Total Assets summary item the Current Assets and Other Assets will be grouped together into one group called Assets. Click and drag to select both asset groups, then click the group icon in the toolbar. With both Asset categories now grouped, rename this newly created group to Assets.



Creating the Total Assets Summary Item
With the Assets group now established, one can easily create a summary item to generate the Total Assets value. To do this, select the Assets group, then select the Sigma (Sum) item from the toolbar. Rename your newly created summary item Total Assets. One may wonder if the system is going to include the Current Assets and Total Assets summary items in the Total Assets item (i.e. double count). Quantrix' Summary Item functions automatically exclude other summary items that are contained in the range of items evaluated in order to avoid double counting. Therefore the preceding summary items are skipped in the calculation which allows the function to produce the correct result. This procedure can be replicated to create the Total Liabilities as well as the Total Liabilities and Owners Equity summary items. The entire balance sheet can be converted into a working Quantrix model without doing any manual data entry. Selectively copying and pasting the items and cell data allows you to quickly build your models from your existing spreadsheets.

Downloads:
>> Balance Sheet Model
>> Balance Sheet Spreadsheet

Please note the model file contains an example of an outline view of the balance sheet and a Ratio matrix that calculates the Current Ratio, Quick Ratio, Cash Ratio and Working Capital Value. To view an accounting format balance sheet that looks more like what you are used to please select View->Outline from the Toolbar.




idbs_banner_960x41
About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -