-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Creating Financial Statements
- Thread starter wacky
- Start date
-
- Tags
- financial statement
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
If you have done this many times in the past, I suspect you have more experience than most (all) who are likely to come upon your post.
Personally, I wouldn't even think of doing something like this. I'd use a program designed for the task. Something that maintains a trail of the various transactions and how the various amounts landed up in the accounts they landed up in.
I imagine it could be done in XL, but it would be writing Quickbooks or Money or whatever other program in XL/VBA. {shudder, shudder}
Personally, I wouldn't even think of doing something like this. I'd use a program designed for the task. Something that maintains a trail of the various transactions and how the various amounts landed up in the accounts they landed up in.
I imagine it could be done in XL, but it would be writing Quickbooks or Money or whatever other program in XL/VBA. {shudder, shudder}
wacky said:
Upvote
0
to Clarify
Sorry, I guess I should have been more specific.
I do use Quickbooks in this case. But generally speaking, accounting programs do not give you the versatility to make the financials look exactly like you want. Even the big accounting programs use exernal programs like crystal reports or F9 to actually create financials.
What I do is export the trial balance and then use XL purely as a reporting tool. So I am looking for feedback on just the reports.
Sorry, I guess I should have been more specific.
I do use Quickbooks in this case. But generally speaking, accounting programs do not give you the versatility to make the financials look exactly like you want. Even the big accounting programs use exernal programs like crystal reports or F9 to actually create financials.
What I do is export the trial balance and then use XL purely as a reporting tool. So I am looking for feedback on just the reports.
Upvote
0
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
Ah, I see.
My guess would be you are better off using your own experience or asking the target audience. You may also want to look at how the SEC requires publicly traded companies to report their numbers. Check out the EDGAR database as www.sec.gov. The files have a XML-type architecture (probably/hopefully some derivative of SGML).
My guess would be you are better off using your own experience or asking the target audience. You may also want to look at how the SEC requires publicly traded companies to report their numbers. Check out the EDGAR database as www.sec.gov. The files have a XML-type architecture (probably/hopefully some derivative of SGML).
Upvote
0
more clarification
I am actually in Canada and therefore not regulated by the SEC, we are also not a publicly traded company. I am not looking for information in regards to content, as I know what I want and need the end result to look like. What I am asking for is advice on how to get it there.
Specifically, I am curious if users have found, for example, it more effective to use database references such as the DGET formulas, or Pivot tables or some other way?
For those who may be able to help but do not understand how financial statement "work", here it is. I start with a database of trial balances for each month. A trial balance is a list of all accounts and the respective dollar amount (what I have found works best is one big database with account numbers/names in column A and then each month listed in subsequent columns with amounts listed below, then on a go ahead basis, each new month is just inserted) but this can be changed if something else works better. Then, in simple terms, an income statement and balance sheet are created by either retrieving amounts from trial balance, combining amounts, or recording the change in amounts from one period to the other.
Any suggestions?
I am actually in Canada and therefore not regulated by the SEC, we are also not a publicly traded company. I am not looking for information in regards to content, as I know what I want and need the end result to look like. What I am asking for is advice on how to get it there.
Specifically, I am curious if users have found, for example, it more effective to use database references such as the DGET formulas, or Pivot tables or some other way?
For those who may be able to help but do not understand how financial statement "work", here it is. I start with a database of trial balances for each month. A trial balance is a list of all accounts and the respective dollar amount (what I have found works best is one big database with account numbers/names in column A and then each month listed in subsequent columns with amounts listed below, then on a go ahead basis, each new month is just inserted) but this can be changed if something else works better. Then, in simple terms, an income statement and balance sheet are created by either retrieving amounts from trial balance, combining amounts, or recording the change in amounts from one period to the other.
Any suggestions?
Upvote
0
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
This discussion started with "...best way to design a full set of financial statements in Excel" and has devolved into "...I know what I want and need the end result to look like."
Maybe, you should share the *exact* problem at hand. How are the data laid out? Where is the final result you want? How is it organized? How do you know which account belongs to which category of which financial statement?
If doing this by hand, how would you get from what you have to what you want?
Since you have indicated you have done this many times you should definitely share what you do and have done, how it comes up short, and how you expect to improve on the process.
Finally, my suggestion vis-a-vis EDGAR was not about content. It was more about style and format and data transfer methodology.
Maybe, you should share the *exact* problem at hand. How are the data laid out? Where is the final result you want? How is it organized? How do you know which account belongs to which category of which financial statement?
If doing this by hand, how would you get from what you have to what you want?
Since you have indicated you have done this many times you should definitely share what you do and have done, how it comes up short, and how you expect to improve on the process.
Finally, my suggestion vis-a-vis EDGAR was not about content. It was more about style and format and data transfer methodology.
Upvote
0
WOW!
Here I thought I would just ask what I thought was a simple question that would maybe develop a conversation about the different methods available for retrieving/summarizing data from one source into another.
Since we are in an Excel forum, by writing “design” I thought it would be implicit that I meant formulas/methods (pivot tables etc), not visually or theoretical.
I am starting from scratch on this one, so I do not have a current layout and I am completely open to set things up in anyway that would work best for my ultimate method used.
As far as what accounts/amounts are to be included on a particular report, that is something that needs to be set up in the spreadsheet. Again, how this is done would depend on what method is used. It is however predetermined.
Ultimately what I would like to do is have a workbook that contains the financial statements. The user would then be able to enter the date they would like to see, and then the financials would populate with the appropriate data.
Here I thought I would just ask what I thought was a simple question that would maybe develop a conversation about the different methods available for retrieving/summarizing data from one source into another.
Since we are in an Excel forum, by writing “design” I thought it would be implicit that I meant formulas/methods (pivot tables etc), not visually or theoretical.
I am starting from scratch on this one, so I do not have a current layout and I am completely open to set things up in anyway that would work best for my ultimate method used.
As far as what accounts/amounts are to be included on a particular report, that is something that needs to be set up in the spreadsheet. Again, how this is done would depend on what method is used. It is however predetermined.
Ultimately what I would like to do is have a workbook that contains the financial statements. The user would then be able to enter the date they would like to see, and then the financials would populate with the appropriate data.
Upvote
0
I have used the database functions to extract the data. I then set up the data ranges on another spreadsheet or in an unviewed area. I would also be interested in another approach as this is quite a tedious set up and requires maintenance each time a new account is added.
Upvote
0
