Comingle P&L and Balance Sheet Data

Overview

A user has the ability to comingle P&L and Balance Sheet data in a single template. This feature allows a user to create reports with blended data such as a KPI, or any other report that requires the merging of P&L and Balance Sheet data. It is best to start with a jumpstart template from the Qvinci Gallery.

Configure a Blended Report Starting with a Gallery Template

Step 1.  Migrate to Reports & Libraries > Libraries then choose Qvinci Gallery.

Step 2. Find a report named "P&L; Detail; Reporting month; % differential; Portrait" and choose if you want a SCoA (S) based or one based on No-SCoA (N) template in the grid. Click the blue Edit button to open the template.

Pay no attention to the date range or context listed in the template. This will be updated. 

Step 3. The chart of accounts structure will be shown in macro form in column A. Largely, this needs to stay intact so that the relative CoA or SCoA (Standard Chart of Accounts) feeds through the macros, but modifications can be made. 

Learn more about using the Accounts Chooser to edit the format here. 

Step 4. With the Gallery template open (ensuring that the macros appear in the template, as opposed to financial data and the CoA/SCoA), click the Save As  button from the left to create a working copy for modification.

Step 5. Name the working copy. This new template will appear on screen and will now exist in your Mine tab (as opposed to the Gallery). All modifications need to be done in your working copy moving forward.

Note About Special Characters in the File Name

When naming the file, avoid using special characters: "<, >, \, {, and }" which will produce an error until those values are removed.

Modify the Newly Saved Template

Now that a working copy of the template exists, the modifications can be input to create a blended report. 

Step 1. The template should be open. If it is not, open the template by locating the report from your Mine tab and clicking the blue Edit button in the column on the right.

Step 2. In the Profit & Loss template rename the tab at the bottom of the template to say PL, or something similar if it indicates something different.

Step 3. The pre-existing column macros will still appear in the columns, but they now need to be updated.

Step 4. Update an existing column macro by clicking in the cell that contains the existing macro (typically column C) and then click to open the Column Chooser  from the top left corner of the screen in the blue.

• • From the top dropdown menu, select Profit and Loss Monthly.
  • Then select a desired frequency, date range, or other options that should appear in that column. 

Learn more about using the Column Chooser here. 

The reason the Profit and Loss Monthly option must be chosen from the drop-down is because this template is going to have both P&L and Balance Sheet data in it, so the explicit report type must be selected.

Step 5. Once these changes are made press the Save  button to save this template.

Add the Balance Sheet Data on the Second Tab/Worksheet

After updating all P&L macros through the Column Chooser, it's time to add the Balance Sheet portion to this report template. It is advised to input the Balance Sheet portion of the template on a second tab and then utilize cross-sheet referencing or HLookups / VLookups to blend the data in the reporting template.

Step 1. Return to the Qvinci Gallery and find a sample Balance Sheet template, by locating BS-RM (S) or BS-RM (N).

Step 2. Click "Edit" to open the template.

Step 3. Copy the structure of the chart of accounts from the Balance Sheet template (Excel hotkeys, like Ctrl + C to copy, work for this).

Step 4. Return to your P& L template under the Mine tab and click the Edit button to open the template.

Step 5. Click the Plus  button at the bottom of the template to create a new tab to drop the Balance Sheet structure on.

Step 6. On this second tab paste (Ctrl+V) the copied Balance Sheet structure. Resize the columns as desired.

Step 7. Edit the name of the tab by clicking on it and calling it something like Balancesheet.

Now it's time to edit the column macros through the Column Chooser to get the desired date range and frequency for the Balance Sheet tab.

• • Update an existing column macro by click into the existing macro and then visit the Column Chooser .  Otherwise, if no existing column macro exists, click into a cell above and to the right of the first row of the CoA and open the Column Chooser.
  • From the top drop-down menu, select Balance Sheet.
  • Then select the desired frequency, date range, or other options that should appear in that column. 

Learn more about using the Column Chooser here. 

The reason the Balance Sheet option must be chosen from the drop-down is because this template has both P&L and Balance Sheet data in it, so the explicit report type must be selected on the corresponding tabs.

Blend the Data

Now that the P&L template exists on one tab, and the Balance Sheet template exists on a second tab, you may wish to create a KPI type of report, or another report that needs data blending, on a third tab. This is useful when you need to source data from the P&L and the Balance Sheet but don't actually need to show the full format of the P&L or the Balance Sheet. 

Step 1. Add a new tab at the bottom of the template by clicking the Plus  button and naming the new tab (click onto the tab to rename it).

Step 2. If cross-sheet referencing is preferred, the next step is for the user to begin to create those references. 

• • If point-click between sheets does not appear to create the reference formula, follow this format to manually input a reference: ='SheetName'!Cell
  • Therefore, if a formula needs to be inputted to divide Total Income by Total Fixed Assets, and Total Income is on the P&L tab in cell C8, and Total Fixed Assets is on the BalSht tab in cell C19, the formula might look like this: ='P&L'!C8/'BalSht'!C19
  • As the template is previewed or downloaded in a package, and the chart of accounts expands column A, you will find that your formula references move dynamically. For example, if cell C8 is the Total Income row on the P&L tab in the macro-based template, but becomes cell C24 after the template renders data, the formula will still reference the correct cell for Total Income.

Step 3. If the desired output for the report is to simply show the blended report tab in a PDF download, and not the P&L or Balance Sheet tabs, you can edit the P&L and Balance Sheet tab names to be prefixed with a "#" .

The prefixed hashtag will ensure that the blended report tab hydrates with data, but that the source tabs (P&L and Balance Sheet) do not appear in the PDF export.

Step 4. Once all your changes have been made to the comingled report, you can choose to Save the report.  From then on to run this report, use the Preview button.

Have Questions?
We're More Than Happy to Help

Schedule a Call with Customer Success below, email us at support@qvinci.com or call us at 1-512-637-7337 Ext. 1 Available M-F, 7:30am-6:30pm CT and it is always FREE!

Schedule an Appointment with Customer Success