?
Solved

Excel Financial Reporting, P&L, Balance Sheets etc

Posted on 2014-02-05
6
Medium Priority
?
1,712 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 99

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
0
 
LVL 34

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.

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 99

Accepted Solution

by:
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
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 99

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

850 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