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

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.

Continued...

» Register now

The full article is available to registered AccountingWEB 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.

Comments

A Possible Access Alternative ....

Anonymous | | Permalink

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)

Hi David !

Shawn | | Permalink

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

Solution

barrysnashall | | Permalink

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