Hanging Out with Excel

As an Accountant, you generally use Microsoft Excel for, well almost everything (i.e. amortization/depreciation schedules, control matrix, etc.).  An ongoing task I’ve been privileged with spearheading is the Internal Controls Audit Recommendation Follow up System (I’m still thinking of a more creative title).  The general idea is to create a system that will document, track and alert the internal controls section/division (ICS) of updates, changes and action needed based on our recommendations we have made to FDIC personnel through our reviews/audits.  Several issues I ran into while developing the system include my lack of familiarity, making it user friendly, incorporating an alert system and linking documents.

Lacking experience in creating this type of database proved to be an unexpected problem because envisioning how a process should be streamlined and how it can actually become operational are two very different ideas.  For instance, one idea I had was to create a way for the system to automatically alert ICS about the status of a particular recommendation.  Simple enough idea.  Unfortunately, Microsoft Excel does not have a single formula to make such alerts.  It does, however, provide several alternatives.  In essence, I would create a formula that stated whatever the Current Date (September 25th for example) happened to be.  I also stated the final due date a certain recommendation should have been completed (lets say September 20th is the Due Date).  Next I would enter in a formula that compared the dates.  If the Current Date was greater than the Due Date then Excel would automatically flash a “Past Due” note.  If not, then it would display a “Current” note.  For those of you wondering, the formula is

=IF($D$65<F12,”Current”,”Past Due”)

D65 would hold the cell that has today’s date [use the formula is =Today()]

F12 would be the designated due date

An additional problem I unexpectedly ran into is user-friendliness.  As a consequence of me being the designer of the database, I could readily locate where within about a dozen tabs of the workbook key information would be.  Basically, it similar to attempting to give someone unfamiliar with your neighborhood directions to your house.  While you may have completed the task so many times that its second nature to you, attempting to convey this to a person unfamiliar with the area makes it a bit of a struggle to verbalize.  In my case, I could easily find what recommendation and personnel follow ups were made for an audit performed 2 years ago within seconds.  Therefore, a secondary task I will be completing once the system is completed is drafting an instruction manual.

Overall the project is going well & I’m expecting to have it completed by the end of the month.

Advertisements