Preparing Financial Statements Using Pivot Tables-Books Pdf

Preparing Financial Statements using Pivot Tables

2019 | 6 views | 0 downloads | 9 Pages | 699.54 KB

Preparing Financial Statements using Pivot Tables I am often asked the question of whether it is possible to prepare an income statement and a balance sheet in Excel. I always reply that of course it is simple to do that providing you know the basic layout of those statements. Use the power of the spreadsheet to do your work for you




Preparing Financial Statements using Pivot Tables
Duncan Williamson
January 2011
I am often asked the question of whether it is possible to prepare an income statement and a
balance sheet in Excel I always reply that of course it is simple to do that providing you know the
basic layout of those statements
This set of notes illustrates that all you really need to prepare an income statement and a balance
sheet using Excel are
The data a list of revenues expenses assets liabilities and capital are all you need In these
notes I will be dealing with the income statement and balance sheets separately
A Pivot Table well in this set of notes two Pivot Tables but the homework at the end of the
notes is for you to combine everything into one list and one Pivot Table
I am assuming that you already know the basics of
Financial statements
Preparing and using a Pivot Table
Just put Everything into a list
One major lesson that I give to anyone who is looking to prepare financial and other quantitative
statements is to follow these simple rules
1 Classify everything first write down what it is you want to see at the end of this process
2 Set up a table to record all of your data based on step 1
3 Record everything in the form of a list in that table you don t even need to sort it
4 Now create a Pivot Table from your list
Honestly that s it the secret of your success is in step 1 classifying everything before you start
Let s work through the examples firstly the income statement
Income Statement
I am going to take you through the process out of order So I will begin with steps 2 and 3 together
then step 1 and finally step 4 I need you to see the importance of step 1 so will demonstrate what
happens when you don t take it
Here is a screenshot of some basic income statement data but notice that some numbers are
positive and some are negative these are equivalent to debits and credits and the rules are
All revenues income money coming in are shown as positive figures
All expenses costs and money going out are shown as negative figures
Applying these two rules helps to make the process entirely automated
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 1 of 8
We need to create column headings so that Excel knows how to set up a Pivot Table
I have put the information in a bit more of a logical
order now but is it ready to create a Pivot Table from
this table now does it give you what you want
can it give you what you want
The answer is that no it is not good enough for
preparing a final income statement What is missing
is step 1 the classification of accounts we need a
chart of accounts for this as follows
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 2 of 8
By classifying the accounting information we have split everything into three sections
Revenues
Cost of sales
Other income
Expenses
We have then sub classified everything by giving them their own account number
So we can see that all revenues are classified as account category 1000 and then sales are allocated
to account number 1010 and sales revenues allocated to account number 1020
All other categories work in a similar way
Prepare the Pivot Table now and what happens
My Pivot Table based Income Statement looks like this now
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 3 of 8
It works but some things are missing where is the gross profit the gross income and the net
profit Well we didn t set up any categories for them yet In my case I have coded them as follows
Account Category Account Account Name
Number
2500 Gross Profit 2500 Gross Profit
3500 Gross Income 3500 Gross Income
4500 Net Profit 4500 Net Profit
Add those to your list and program the necessary formula to calculate these values and you should
find the following As a matter of interest as I hinted at the beginning you could simply type these
extra categories at the end of the table without sorting and the Pivot Table will use your coding to
put everything in its proper place
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 4 of 8
By the way you could consider creating a category for cost of sales and total other income I didn t
and have programmed the gross profit gross income and net profit cells accordingly For example
Gross profit in cell G34 SUM G28 G29 SUM G30 G33 you should see the benefits of negative
and positive values now
In the end then a full screenshot of my income statement and Pivot Table is here
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 5 of 8
Balance Sheet
In this example I am going to give you just three things
The data
The chart of accounts
The final Pivot Table
The rest is up to you
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 6 of 8
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 7 of 8
Homework
Combine the two sets of data and prepare one Pivot Table to prepare a combined Income Statement
and Balance Sheet
Conclusions
Preparing almost any statement by combining the power of a classified list and a Pivot Table is such
an easy and smart thing to do As I have demonstrated here it s not that difficult with just a little bit
of knowledge of accounting and Pivot Tables
You should feel confident after having worked though this with me of doing and finishing the
homework and of completing similar projects yourself
Duncan Williamson
21st January 2011
Financial Statements from a Pivot Table
Duncan Williamson January 2011
Page 8 of 8


Related Books

BHARAT SANCHAR NIGAM LIMITED - chhattisgarh.bsnl.co.in

BHARAT SANCHAR NIGAM LIMITED chhattisgarh bsnl co in

- 1 - BHARAT SANCHAR NIGAM LIMITED (A Government of India Enterprises) O/o The GMTD, Bilaspur Executive Engineer (E), BSNL Electrical Division , Bilaspur

Simulation Modelling using Practical Examples: A Plant ...

Simulation Modelling using Practical Examples A Plant

2 OVERVIEW OF PLANT SIMULATION 8 2.1 Object Orientation 8 2.2 The Desktop 10 2.3 Working with the Class Library and Toolbox 11 2.4 Overview of Basic Objects 12 2.5 Objects used in this Tutorial 17 2.6 Functionality, animation, and visualisation 18 PART A: BASIC SIMULATION MODELLING 19 3 BUILDING A MODEL: GENERAL PRACTITIONER 20 3.1 Creating a ...

BHARAT SANCHAR NIGAM LIMITED - e Tenders

BHARAT SANCHAR NIGAM LIMITED e Tenders

bharat sanchar nigam limited (a govt. of india enterprise) office of the principal general manager bsnl warangal lr. no.pgmt-wl/admn/tender h.v/2016-17/2 dated at wl the 12-08 -2016 tender document open tender for hiring of vehicles with valid permit for the use of fields & hqs duties in bsnl warangal ssa

Bharat Sanchar Nigam Limited - chennai.bsnl.co.in

Bharat Sanchar Nigam Limited chennai bsnl co in

Bharat Sanchar Nigam Limited (Electrical Wing) Office of Executive Engineer (E) BSNL Electrical Division III, Ground Floor, Kelleys Telephone Exchange Kelleys, Chennai -10 Ph.No.044-26474545,26476767 . TENDER DOCUMENT (SHORT NOTICE TENDER) Name of Work:-Providing Electrical infra structure works for the proposed Cable landing

Bharat Sanchar Nigam Limited - chennai.bsnl.co.in

Bharat Sanchar Nigam Limited chennai bsnl co in

Bharat Sanchar Nigam Limited Electrical Wing Electrical Division: ED-III,Chennai Sub Division:VII Item rate tenders on single bid system are invited on behalf of CMD,BSNL for the work of ^Replacement of scrapped 3 nos PAC units with (5+1) Nos 1.5 TR Split AC units in SWITCH ROOM

E-tender for telephone bill printing for SSAs under Zonal ...

E tender for telephone bill printing for SSAs under Zonal

E-tender for telephone bill printing for SSAs under Zonal GM Jabalpur Signature of Tenderer with seal 1 Bharat Sanchar Nigam Ltd. (A Govt. of India Enterprise) OFFICE OF THE ZONAL GENERAL MANAGER, NEW CTO COMPOUND, JABALPUR-482001 M.P. Document No: - BID DOCUMENT E-Tender for Supply of Multi coloured pre-printed Telephone Bill

Issue 373 May 2012 Community Newsletter

Issue 373 May 2012 Community Newsletter

Issue 373 May 2012 District Council of Robe, Royal Circus (PO Box 1) Robe SA 5276 Phone l 08 8768 2003 Fax l 08 8768 2432 Email l council@robe.sa.gov.au Website l www.council.robe.sa.gov.au Community Newsletter Pg 2 National Volunteer Week Pg 6 2012 Advantage SA Regional Awards Pg 7 National Career Development Week CCOWS NEWS Pg 8 Trees for Life Mayors Report CCOWS 10th Birthday On 3 April ...

Agiot May 2012 - theagiot.com

Agiot May 2012 theagiot com

MAY 2012 SINCE AUGUST 2007 PAGE 2 On the 25th of August the fourth Agiotfest (Music Festi-val) takes place in Agios Ioannis. Over the last three years crowds have averaged over three hundred, attracted by both in-ternational and local bands. Financially the festival is strug- ...

Genetic Testing for Hereditary Cancer

Genetic Testing for Hereditary Cancer

Genetic testing with a Multi-Gene hereditary cancer Panel in individuals with an indication for testing for hereditary Breast and Ovarian cancer is proven and medically necessary if all of the following criteria are met: The suspected hereditary cancer syndromes can be diagnosed by testing of two or more genes included in the

Contaminant Fate and Transport Process

Contaminant Fate and Transport Process

CONTAMINANT FATE AND TRANSPORT PROCESS Xi Yang and Gang Yu Department of Environmental Sciences and E ngineering, Tsinghua University, Beijing , P. R. China Keywords: chemical fate, physical transport, chemical transport, transformation, surface waters, subsurface environment, atmosphere, biodegradation, biotransformation,