Alright, here's my challenge:
I am attempting to streamline our data entry and reduce the redundancy of input. Currently we have a software program that we input audited financial statements of various companys to monitor their performance. The sole purpose of this software is essentially data storage. It kicks out a few basic calculations but other than that it is worthless and serves no point. Regardless, we have it for now. It does however, provide the ability to to spit out stored financial statements in .csv, which inherently is raw data and is useful to me, but not currently used. As it stands this piece of software (POS), prints the financial statements for multiple periods which we can then perform both vertical and horizontal "eyeball" analysis. If anything beyond that is required, we are at the mercy of punching in the data again, and using arcane methods of extrapolating seemingly meaningful data that an underling without a financial background decided to include in his/her analysis. Hopefully you see where this is going. My goal is to use this information in a more productive and efficient manner by creating a financial model that will analyze this information for Senior Management using dashboard reporting through integrating a standardized template in which existing data (POS) can provide the input (.csv format) which can then be "put through the ringer" and sensitized for reporting purposes. I have been reading several books about financial modeling studying various posts on this message board have produced a fianancial model that should suit our needs. It is still a work in process, features still need to be added, but my model will require inputs regardless so now is as good of a time as any to seek your help.
Above is an overview, and the .csv output file looks like this:
My financial model contains different accounts for simplicity and modeling purposes. Main acounts such as Accounts Payable, Acounts Receiveable, Inventory, Fixed Assets, Accumulated Depreciation, Retained Earnings, Current Maturities, Long Term Debt, Intangible Assets, etc. will *usually* be included on every financial statement. The POS has the ability to customize individual line items or set them to default line descriptions. I can make it a policy to default any custom lines before exporting to a .CSV if that is required and helps to narrow the range of possibilities. The goal in this step is to take the .CSV raw data and input as much data into my financial model so that people do not have to input the data twice (1st time into POS, 2nd time into my model). My suggestion for the accounts that don't fit my models accounts is to dump them into a "Other" line in the appropriate section of the Balance Sheet (B/S) or Income Statement(I/S) For example: 'Other Current Assets', 'Other Current Liabilities', 'Other Long Term Assets,' Other Long Term Liabilities,'.
My model looks like this:
Much thanks in advance to anyone up for an ongoing challenge. In know this will not be an easy task. I didn't mention it, but I would also like this to be an automated feature, meaning that once the .CSV has been created, the user opens the financial model template and clicks an 'Import .CSV' file to upload the accounts. Please keep in mind that although I am familiar with excel, to the point that I understand pivot tables and regression analysis, I do not, nor have I ever used VBA. I have recently purchased a book that I am learning, so things will need to be spelled out for me concerning that topic (maybe others). Thanks.
I am attempting to streamline our data entry and reduce the redundancy of input. Currently we have a software program that we input audited financial statements of various companys to monitor their performance. The sole purpose of this software is essentially data storage. It kicks out a few basic calculations but other than that it is worthless and serves no point. Regardless, we have it for now. It does however, provide the ability to to spit out stored financial statements in .csv, which inherently is raw data and is useful to me, but not currently used. As it stands this piece of software (POS), prints the financial statements for multiple periods which we can then perform both vertical and horizontal "eyeball" analysis. If anything beyond that is required, we are at the mercy of punching in the data again, and using arcane methods of extrapolating seemingly meaningful data that an underling without a financial background decided to include in his/her analysis. Hopefully you see where this is going. My goal is to use this information in a more productive and efficient manner by creating a financial model that will analyze this information for Senior Management using dashboard reporting through integrating a standardized template in which existing data (POS) can provide the input (.csv format) which can then be "put through the ringer" and sensitized for reporting purposes. I have been reading several books about financial modeling studying various posts on this message board have produced a fianancial model that should suit our needs. It is still a work in process, features still need to be added, but my model will require inputs regardless so now is as good of a time as any to seek your help.
Above is an overview, and the .csv output file looks like this:
My financial model contains different accounts for simplicity and modeling purposes. Main acounts such as Accounts Payable, Acounts Receiveable, Inventory, Fixed Assets, Accumulated Depreciation, Retained Earnings, Current Maturities, Long Term Debt, Intangible Assets, etc. will *usually* be included on every financial statement. The POS has the ability to customize individual line items or set them to default line descriptions. I can make it a policy to default any custom lines before exporting to a .CSV if that is required and helps to narrow the range of possibilities. The goal in this step is to take the .CSV raw data and input as much data into my financial model so that people do not have to input the data twice (1st time into POS, 2nd time into my model). My suggestion for the accounts that don't fit my models accounts is to dump them into a "Other" line in the appropriate section of the Balance Sheet (B/S) or Income Statement(I/S) For example: 'Other Current Assets', 'Other Current Liabilities', 'Other Long Term Assets,' Other Long Term Liabilities,'.
My model looks like this:
Much thanks in advance to anyone up for an ongoing challenge. In know this will not be an easy task. I didn't mention it, but I would also like this to be an automated feature, meaning that once the .CSV has been created, the user opens the financial model template and clicks an 'Import .CSV' file to upload the accounts. Please keep in mind that although I am familiar with excel, to the point that I understand pivot tables and regression analysis, I do not, nor have I ever used VBA. I have recently purchased a book that I am learning, so things will need to be spelled out for me concerning that topic (maybe others). Thanks.
