Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: Create a P & L from monthly balances with a pivot table

by
20th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

David Carter's previous AccountingWEB pivot table tutorials show you how to create pivot tables from accounting transactions exported into Excel. However, you can also create pivot tables out of monthly balances. This tutorial shows you how.

Prior to this tutorial you have exported a Trial Balance report out of your accounts package into Excel. In the tutorial, you are going to apply pivot tables and Excel's Vlookup function to the Trial Balance file to create a Profit and Loss account for each department of your company.

Note: This tutorial is designed to be used with Excel 2003 and Excel 2000. If you use Excel 97 or earlier there may be one or two small differences.

Before you start you will need to download two Excel files from AccountingWEB ' Trial_Balance.xls and Lookup.xls (attached below).

Start up Excel. Open the file Trial _Balance.xls. The first row of data should read: 52000-100 Income-Audit-London -43000.00 -42000.00 -41000.00 '43000.00

Contents of the trial balance report
For this tutorial, we will assume your accounts package uses multi-segment account codes. Taking 52000-100 as an example, the first 5 characters before the dash represent the account code, the 3 digits after it the department code.

So 52000 is the code for Income-Audit on your chart of accounts.

And 100 is the department code for your London office; 200 is the code for your Birmingham office; 300 for your Edinburgh office.

53000 is the nominal account code for Income-Consultancy, and so on.

The accounts that begin with 5 are income accounts, those that begin with 7 are expense accounts.

Take a little while to familiarise yourself with the structure of this chart of accounts.

Looking at the balances on the right - they are the balances for each period. So you have just finished period 4 of this financial year.

Income accounts (those prefixed 5) are minus amounts because they are credits.

When data comes over in this format, your task divides into three parts:

  • First, you chop up the codes using the TEXT TO COLUMNS command.
  • Second, you pull in any additional fields you need via the VLOOKUP function
  • Finally, you create the pivot table.

    Split the codes via Text to Columns
    We need to split up the ACNO column into its two segments. So the first thing to do is to insert a blank column in Column B:

    Click on the top of column B to highlight, then from the main menu select Insert-Columns.

    Now click onto the top of column A to highlight the ACNO column.

    From main menu, select Data-Text to Columns. The Convert Text to Columns Wizard, Step 1 of 3 appears.

    Select Delimited as the data type, then Next. Step 2 of 3 appears. You are offered a set of possible delimiters, with Tab as the default.

    Our delimiter is a hyphen. Tick the 'Other' box. Then insert a hyphen in the box to the right.

    A vertical line should appear between the columns. Press the Next button, then Finish.

    ACNO is split and the cost centre 100, 200 or 300 now appears in column B. In B1 type the heading CC for cost centre.

    Using Vlookup to add more detail
    Not enough information has come over with the TB to enable us to create the Profit & Loss reports. We are going to have to pull in extra data from a lookup table.

    Open the second Excel file: Lookup.xls

    There are two worksheets here: cclookup and acctlookup.

    Click on to cclookup. This contains the names of the three offices ' London, Birmingham, Edinburgh.

    First, we will pull these names into the Trial Balance workbook as follows:

    Make sure that both Trial_Balance.xls and Lookup.xls are open.

    Now from the main menu select Window-Arrange-Horizontal. The screen splits into two. Both files are displayed, one above the other.

    Doing the Vlookup
    In Trial_Balance.xls insert a blank column to the right of CC. Head it CCNAME.

    Click onto the first empty cell of this new column, C2.

    From the menu, select Insert-Function, or simply click the fx icon.

    The Paste Function box appears. Check to see that "VLOOKUP" is in the right hand list. (If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP). Highlight VLOOKUP and click OK. The Vlookup box appears, starting with Lookup_Value.

    Lookup_Value: In Trial Balance.xls, highlight column B by clicking on the B at the top. Moving lines appear around the 'CC' column and B:B appears in the Lookup_Value field.

    Table_Array: Click the mouse onto the Table-Array field. In lookup.xls, click twice on A at the head of column A. Moving lines appear around the column.

    Now drag the cursor to the right to include column B. "[Lookup.xls]cclookup!:$A:$B" appears in the box

    Col_ Index_Num: Type 2 (ie the CCNAME column)

    Range_Lookup: Type false

    At the bottom left hand of the box you should see "Formula Result = London", meaning that you have filled in the values correctly. Click on OK. London will appear in cell C2.

    Copying down into the rest of the rows
    Click on to cell C2. Now drag the mouse to the right-hand bottom of the cell until it turns into a black cross.

    Double click to Fill Down to the bottom of the sheet. The column should now be filled with the office location names.

    Convert the Vlookup formulae to values
    The new CCNAME column is filled with Vlookup formulae. (Try clicking on one). These need to be replaced with values.

    Highlight the CCNAME column. Select Edit-Copy, then Edit-Paste Special and select Values. OK. The formulae will disappear and be replaced with values - again, check by clicking on one of the cells.

    Vlookup in ACCTGROUP and ACCTNAME
    You have pulled in the department names successfully. Now click onto the acctlookup worksheet.

    The ACCTNAME column contains descriptions of the nominal account (ie no department name).

    The ACCTGROUP column contains the groupings needed to summarise the TB into a P&L. You need to pull these into Trial_Balance.xls as well.

    In Trial_Balance.xls insert a couple of blank columns to the right of column A, ACNO.

    Now insert ACCTNAME into the blank column B and insert ACGROUP into the blank column C.
    Do this by repeating the Vlookup instructions set out beneath the four previous headings. above. Note that:

  • The Lookup-Value is column A, ACNO
  • The Table Array is columns A to C
  • Col-Index No is column 3, for ACGROUP

    When you have pulled in these two extra fields, the top row of your data should read: 52000 Income-Audit 1-REVENUE 100 London Income-Audit-London -43000.00 -42000.00 -41000.00 '43000.00

    Lookup.xls is no longer needed. Close it.

    Create the P&L with a pivot table
    You now have enough data in Trial_Balance.xls to create the reports you need. To do this:

    Click onto any cell in Trial_Balance.xls. From the main menu at the top of the screen, select Data-Pivot Table and Pivot Chart Report. The Pivot Table Wizard, Step 1 of 3 screen appears. Click on Next.

    Step 2 of 3: The Range box should say: $A$1:$J$88. Click Next.

    Step 3 of 3: Click onto Layout at the left. The COLUMN'ROW-DATA box appears.

    To the right are all the column headings of your spreadsheet Drag and drop them as follows:
    ACGROUP into the ROW area, and underneath it, ACCTNAME.
    CCNAME into the PAGE area.
    PD1 into the DATA area, and underneath it, PD2, PD3, PD4

    Now click on OK. Finish.The pivot table is generated.

    Put the periods side by side and add YTD
    In the pivot table PD1, PD2 etc are underneath each other. They need to be side by side. Depress the left-hand mouse button with your curson over the grey Data field button in C3. Drag it down a few millimetres until you see a horizontal 'brick' and below it, an icon with a slab of blue to the left.

    Keeping your eye on the blue slab, drag the brick up and to the right. When the slab of blue changes to horizontal, release the mouse key. Sum of PD1 to Sum of PD4 now appear side by side.

    [NB: The blue slab represents the pivot table. If the blue is vertical down the left, it will drop the item into the ROW area; if it's horizontal across the top, into the COLUMN area; if it's big blue at the bottom, the item will go into the DATA area.]

    We don't have Year to Date total. Calculate it via a Formula as follows: Right click within the pivot table, then from the menu select Formulas-Calculated Field. [ In Excel 2003 the Formulas option is buried away in the Pivot table toolbar; click on down arrow for the menu]

    The Insert Calculated Field box appears. In the Name box, replace Field1 with YTD. In the Formula box click to the left of the 0. Delete the 0, then find PD1 in the list of fields and highlight it. Then click on Insert Field. PDN1 appears in the Formula box.

    To the right of PDN1 add a + [plus symbol].

    Go back into the list of fields. Find PDN2 and highlight it, then click Insert Field. Add PD3 and PD4 in the same way.

    Finally, click on Add, then OK. A fourth column appears in the pivot table, entitled 'Sum of YTD'

    The numbers are messy. Highlight all five period columns and format them by selecting the Number option and choosing Number-0 decimal places and clicking Yes next to the Use 000 separator option.

    Create departmental P&L reports
    The pivot table you see is for the whole company. To see a P&L for one department, use the Page field.

    Click on the down arrow in B1. Select London, Birmingham or Edinburgh from the list.

    Show company totals broken down by department
    Alternatively, you may wish to see the YTD or period figures broken down by department. To amend the pivot table, right click for pivot table menu. Select Wizard-Layout.

    Switch CCNAME into the COLUMN area.

    Remove PD1, PD2, PD3 and PD4 from the DATA area, leaving only YTD. Then press OK and Finish on the next screen.

    The alteration shows you the YTD totals broken down by department.

    Well done, you've cracked a significant management reporting challenge within Excel. It's time to take break. Save your work - and then relax.

    David Carter

    To access regular spreadsheet tutorials subscribe to the free ExcelZone newswire. You must be registered and logged in with AccountingWEB first, then click the "MySubscriptions" link in the panel at the top right of this page and select "ExcelZone Newswire".

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By User deleted
06th Aug 2005 10:53

A Possible Access Alternative ....
Try the following steps:
a. Create a blank Access Db
b. Import the spreadsheets Trial_Balance.xls & Lookup.xls
c. Create additional fields - Account & CostCentre (say Text(20)) in table TrialBalance
d. Change data types ACCTNO (table acctLookup) & CC (table ccLookup) from integer to say Text(20) - allows relationship

Now run the following query to split out Account & CostCentre (qryStage1)
UPDATE TrialBalance SET TrialBalance.Account = Left(TrialBalance.ACNO,5), TrialBalance.CostCentre = Right(TrialBalance.ACNO,3);

We now have a TrialBalance table with separate fields for Account & CostCentre

The following will give a CrossTab Query for Account, PD1 split over CostCentres (qryStage2)
TRANSFORM Sum(TrialBalance.PD1) AS SumOfPD1
SELECT acctLookup.ACCTNAME
FROM (TrialBalance INNER JOIN acctLookup ON TrialBalance.Account = acctLookup.ACCTNO) INNER JOIN CClookup ON TrialBalance.CostCentre = CClookup.CC
GROUP BY acctLookup.ACCTNAME
PIVOT CClookup.CC;

The following will give Account, CostCentre, PD1-4 & Total - sorted by "acctLookup.ACGROUP" (qryStage3)
SELECT acctLookup.ACCTNAME, CClookup.CCNAME, TrialBalance.PD1, TrialBalance.PD2, TrialBalance.PD3, TrialBalance.PD4, [PD1]+[PD2]+[PD3]+[PD4] AS Total
FROM (TrialBalance INNER JOIN acctLookup ON TrialBalance.Account = acctLookup.ACCTNO) INNER JOIN CClookup ON TrialBalance.CostCentre = CClookup.CC
ORDER BY acctLookup.ACGROUP;

Comments
- All sorts of other queries etc could be generated - try generating other queries and explore
- With the figures are in Access they are far more manageable than Excel
- Once the processes have been set up (via queries) they can be run many times with minimum user intervention
- I have imported here but for the lookup's you could link to them ....and so on

It could be made a lot prettier but one gets the jist of it - furthermore the time taken for this solution in its current state was 10-15 mins from start to finish

(PS. copy & paste the query text)

Thanks (0)
avatar
By Shawn
11th Oct 2012 01:43

Hi David !

 

Thanks for the article. Wouldn't it be much better if you create a video of it on youtube or post some pics.

 

That would be more helpful.

 

Many many thanks

Thanks (0)
avatar
By barrysnashall
15th Mar 2013 15:59

Solution

A great solutions to this is The Analyser check out www.traxuk.com.

 

You can then go even further and drill down into the general ledger without even opening Sage.

 

Regards

Thanks (0)