We will demonstrate how to create a Profit and Loss by Department and by Dimension value.

We’re starting with blank P&L spreadsheet where we have already populated the values that we’re going to use.

There’s the ‘G/L Account No.’, ‘Account’ names, ‘Net Change’, and then some blank spaces for the Dimension values to calculate against, and then the ‘Totals’.

On the right we also have already filled in our key with the ‘Connection’, ‘Company’, ‘Business Unit’, ‘Global Dimension 1’, ‘Global Dimension 2’, ‘Start Date’, and ‘End Date’.

Begin by pulling in the ‘G/L Accounts’.

Select the first cell in this column, then make sure to select the ‘Astral Reports’ tab and then click ‘Get Table’ on the ribbon, and ‘General Ledger Accounts’.

By default, the ‘Connection’ has been selected as we only have one tenant that we are analysing from in this demo.

Select the ‘Company Name’ from the dropdown, and then tick the ‘Include Totalling Accounts’ option so that the totalling accounts are included in the report.

Click ‘OK’, and the G/L Account No’s will be populated down the column.

Next, select the first cell in the ‘Account’ column, and go to ‘Insert Function’ to place the first function.

Select ‘Astral Reports’ for the category, and then ‘BC_GLName’ for the function and click ‘OK’.

Now fill in the Function Arguments.

Starting with the ‘Connection’ and ‘Company’, select them from the key, making sure to lock the values by clicking F4.

Note here, if you only have one connection then you don’t need to fill in the ‘Connection’ argument. However, for demonstration purposes we have.

Now select the first ‘G/L Account No.’ for ‘GLAccount’, but this time, only lock the column and leave the row to flow freely by adjusting the formula so that the dollar sign is before the column number but remove it from the row – such as in this example, ‘$F16’.

And click ‘OK’.

Copy this formula down the column, either by dragging it or double clicking the corner, and all the Account numbers will be populated using the same formula.

Scrolling down, you can see when the Balance Sheet values begin. But as they are not necessary, highlight all the rows involved, and right click to ‘Delete’ – that is a standard Excel function.

Now, to populate the ‘Net Change’, again, click on the first cell of the column, and go to ‘Insert Function’ again.

Now, with ‘Astral Reports’ still as the category, select ‘BC_GLAmount’ for the function, and click ‘OK’.

Fill in the ‘Connection’ and ‘Company’ as before, locking them in using F4 as these values will not be changing.

Then select the ‘Start’ and ‘End Dates’ from the key, locking them in too.

Select the ‘G/L Account’ from the ‘G/L Account No.’ column and lock it the same as before, where the column is locked but the row can move freely.

Then click ‘OK’.

Copy the formula down the column to populate the rest of the Net Change data.

Now, just to make it a bit easier to read which are the totalling accounts and which aren’t, select the accounts that are not in caps – the regular G/L accounts – and indent them.

This is purely to make the report easier to read.

Also, for the particular calculated amounts like ‘COST OF GOODS SOLD’ and ‘NET INCOME’ you could leave them in or calculate them separately if you wanted.

Now we’ll look at the values according to some Dimensions.

Selecting the first empty cell, select the ‘Astral Reports’ tab and go to ‘Get Tables’ then ‘Dimension Codes’.

Select the ‘Company Name’ again and then the ‘Dimension Code’ – in this case we’ll go for ‘DEPARTMENT’.

Make sure ‘Fill Across Rows’ in ticked on so that the Dimensions fill across the top of the report and click ‘OK’.

In the last column, we will place the symbol in figure 22.

As mentioned previously, this symbol represents any dimensions that are outside of the specified dimensions of ‘ADM’, ‘MAN’, PROD’, and ‘SALES’.

So by having a column that represents everything outside of the dimensions, this allows you to make sure that the ‘Totals’ are correct.

Now place the Dimension functions.

Select the first cell in the first Dimension column – in this case I16 for ADM – then go to ‘Insert Function’.

Now select the function ‘BC_GLAmount’ and click ‘OK’.

Fill in the ‘Connection’ and ‘Company’ function arguments as before, locking the cells.

This time, for ‘Global Dimension 1’, select the first Department Code from the spreadsheet, but this time lock the row and allow the column to move freely.

Then fill in the ‘Start Date’ and ‘End Date’, locking the cells.

For ‘G/L Amount’, select the first G/L Account No., but lock the column and leave the row to move freely.

You can of course include ‘Additional Currency’ at this stage, however, for this demonstration we will not include it.

There is a separate video and help page on using the ‘Additional Currency’ function if you would like to.

So for now, click ‘OK’.

Drag the formula across the dimensions.

And then copy it down the page.

Now just change the formatting slightly on the figures.

Now to finish, fill in the ‘Total’ column.

Select the first cell, and then type in a simple formula that will add up the figures across the dimension values.

Then copy the formula down once again.

So now you can see that the Dimension values Total is the same as the Net Change.

At the bottom, the total for the totalling accounts has also been calculated.

You can manually check the ‘NET INCOME’ value by placing a simple formula below it and adding up the totalling accounts.

Finally, you can use the standard Excel function of Hiding the G/L Account No. column if you wanted to.

But there you have it, that’s a completed Profit and Loss report by Department.