Excel Financial Reporting, P&L, Balance Sheets etc

Posted on 2014-02-05
Medium Priority
Last Modified: 2014-02-06

This is a "high-level" question - suited perhaps to people who are used to producing financial reports.  (Accountants?)
I am a fairly good Excel user with moderate VBA skills.

I have been asked to develop some financial reports for a couple of clients.
The General ledger account balances will be imported into Excel and from there I will create the reports according to clients specification.

I think most of this is easy.  I will used named ranges and sensible formulas.

QUESTION: Are there any tricks of the trade that I should be aware of?
Question by:Patrick O'Dea
  • 3
  • 2
LVL 101

Assisted Solution

by:John Hurst
John Hurst earned 1336 total points
ID: 39837738
I use named ranges and good formulas (lots of sumif formulas to add like accounts for example).

I get raw data from a trial balance which I export to a sheet in the main report tool. Then formulas can get the raw data via vlookup formulas. You can do the same with the GL but it is a larger report. The vlookup formula will probably still work.

Make sure formulas to get the data and form the report are locked. Users love to plug balancing numbers into formula slots to avoid finding problems. This will wreck your work.

... Thinkpads_User
LVL 35

Assisted Solution

by:Rob Henson
Rob Henson earned 664 total points
ID: 39838474
The introduction in Xl07 of SUMIFS makes summary reports a lot simpler as these allow multiple criteria in one formula without the need to concatenate cells or use complex array formulas.

It effectively allows you to create the equivalent to a Pivot Table in your own layout. Having said that the use of Pivot Tables is also much better, with the introduction of Power Pivot in xl2013.

Also with xl07 and beyond now having so many more rows and columns, be wary of creating range names that use whole column or row references; could result in resource issues. Also with Range Names be wary of the scope of a Range Name, ie Worksheet or Workbook.

Rob H

Author Comment

by:Patrick O'Dea
ID: 39838548
Thanks for you comments.
Interestingly, I have recently discovered SUMIFS and have used it in presenting a P&L report. Very effective.

Other points noted too.

I am meeting people next week and am supposedly the "expert" on producing financial statements in Excel.  However, I wanted to check with people who have actually produced these reports in Excel.

So far, it has been mainly more theory than practice with me.  However, I appear to be on the right lines.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 101

Accepted Solution

John Hurst earned 1336 total points
ID: 39838711
As I noted (or at least implied), I extract a Trial Balance from QuickBooks, and have a report in Excel that shows me an Operating Statement (Balance Sheet would work the same way).

The Operating Statement is one sheet and the Trial Balance is another sheet.  There are other sheets.

Individual accounts are brought from the Trial Balance sheet via VLOOKUP formulas.

Multiple row summarizations (some accounts are split by job) are done with SUMIF formulas.

I have been using this Excel sheet monthly for several years, so it is a very reliable method.

.... Thinkpads_User

Author Closing Comment

by:Patrick O'Dea
ID: 39838828
Thanks for useful feedback.

I appear to have the right approach and your tips are useful.
LVL 101

Expert Comment

by:John Hurst
ID: 39838861
@21Dewsbury - Thank you, and good luck with your project. The approach we are discussing works well and you can always post back with questions.

.... Thinkpads_User

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question