• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1783
  • Last Modified:

Excel Financial Reporting, P&L, Balance Sheets etc

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
Patrick O'Dea
Asked:
Patrick O'Dea
  • 3
  • 2
3 Solutions
 
JohnBusiness Consultant (Owner)Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
Patrick O'DeaAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
JohnBusiness Consultant (Owner)Commented:
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
 
Patrick O'DeaAuthor Commented:
Thanks for useful feedback.

I appear to have the right approach and your tips are useful.
0
 
JohnBusiness Consultant (Owner)Commented:
@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
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

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now