Solved

Excel Financial Reporting, P&L, Balance Sheets etc

Posted on 2014-02-05
6
1,522 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 90

Assisted Solution

by:John Hurst
John Hurst earned 334 total points
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 166 total points
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 90

Accepted Solution

by:
John Hurst earned 334 total points
Comment Utility
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
Comment Utility
Thanks for useful feedback.

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

Expert Comment

by:John Hurst
Comment Utility
@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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now