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

Kashflow logo
3

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...

Please Login or Register to read the full article

The full article is available to registered AccountingWEB.co.uk members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

Share this content

Replies

Please login or register to join the discussion.

avatar
By Anonymous
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
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)