Solved

Excel Financial Reporting, P&L, Balance Sheets etc

Posted on 2014-02-05
6
1,571 Views
Last Modified: 2014-02-06
Hi,

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?
0
Comment
Question by:Patrick O'Dea
  • 3
  • 2
6 Comments
 
LVL 94

Assisted Solution

by:John Hurst
John Hurst earned 334 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
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 166 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.

Thanks
Rob H
0
 

Author Comment

by:Patrick O'Dea
ID: 39838548
Rob,
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.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 94

Accepted Solution

by:
John Hurst earned 334 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
0
 

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.
0
 
LVL 94

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
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot 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…

820 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