I've received some great input and help from Kara and Darin on this project. The first attempt was not particularly successful but turned out to be a springboard for some much better ideas on making the creation of financial reports easier.
Initially the idea was to set up a workbook that already contained formulas and these formulas referenced input cells where a user could enter a G/L Account number or range of numbers and the result would be displayed. This seemed like a good approach but turned out to be too restrictive and cumbersome to use.
The next attempt incorporated creating a table where each account could be categorized (i.e. Sales, Cost of Goods, Warehouse Expense, etc.) and this has the makings of being very workable. The table is easily maintained using Excel and is available for any workbook that is connected to the HD database. When this table is joined to one of the G/L files and the data is loaded into a worksheet named ranges were created for the Category, MTD Amount, YTD Amoutn, Current Month Budget and YTD Budget. This is done for each of the three G/L files. The result is a very manageable five named ranges for each of the three files. Now the user can enter a formula in any cell and return the data for a specific Category of accounts for a particular period (i.e. =SUMIF(MCODE,"Sales",MCURAMT) will return the Total MTD Sales for the Current accounting period). An alternative and preferred method would be to replace the name of the Category, Sales, with a cell reference so the formual becomes =SUMIF(MCODE,B10,MCURAMT). This allows the user to change the results by simply changing the value entered in cell B10 and more importantly allows the formula to be copied down the desired number of rows. Now the user simply types a valid Category in column B and the Amount for the Current Month is displayed in the formula column as below.
The date in the second column is from Table File GL03 and will always display the current period. The third and fourth column dates are based on the selection that the user chooses in a table named "Periods". The data displayed in columns three and four is based on the Periods selected by the user.
Hopefully, within the next week the workbook will be completed and ready for user testing. At that time there will be a series of posts detailing the various elements used in creating the workbook. These posts will include such things as the parameter queries used to extract the previous month and prior year data. The drop down list of only closed months in the current year that automatically adjusts as accounting months are closed or reopened.
As always, if you have any questions about this or anything related to Excel, please, post a comment here or email me at the office.
No comments:
Post a Comment