The samples have been truncated to allow for a
reasonable file size.
Accounting, Reporting and Work Flow Samples
Collaborative Online Work Flow Manager – this is a current work in progress. With any department, the flow of information needs organization, clear deadlines and communication in order to ensure a timely and accurate work flow. With this tool, projects are broken down into individual tasks, with participants automatically notified of progress, changes, or updates to the distribution of tasks. Access the project here using login “guest” and password “wvaguest.” Feel free to explore the interface and drop me a note with any questions or comments. In this initial project just started, a month end work flow is being designed for the close, reconciliation and analysis process.
Income Statement & Balance Sheet 2009 YTD – in this work sample, a reporting solution was needed to provide trend operational data for YTD income as well as comparative balance sheet data.
Lead Sheets – this workbook contains standard audit leads with all components of an organization balance sheet, summarized by section, and detailed by each account and its reconciliation. The workbook is hyperlinked from the summary page, to its audit lead; from there, each account balance for the reporting period is hyperlinked to its reconciliation and detail page. This is done for every account on the balance sheet including assets, liabilities and stockholders equity (per example).
Excel Database – this work sample highlights the simplicity of using Excel as a self-contained database, where a full database program is not needed for the volume of data being entered and accessed. In this example, the database contains customer list information, as well as revenue associated with each customer. Information can be extracted through the use of a pull down filter function, located at the top of each column. When this is done, a subtotal at the top of a corresponding column containing financial or statistical information is produced making summary data accessible.
Analysis Work Samples
In the NCUA June 2009 Comparative Data project, datasets from NCUA Call Reports were used. Each NCUA insured credit union in Idaho is represented, with a focus on key performance ratios as well as financial data. This data was compiled using macros, special formatting and lookup functions, and the results are graphically represented with quick access between ratios and performance data.
Budget Snapshot, this is a dynamic model using Microsoft Excel to utilize historical data trends at the PCA level, producing a graphical presentation between PCAs showing the financial trend, variances in the form of spikes or missing data and remaining budget for the particular category. This tool can also be used for projections and forecasting by utilizing unexpired months in the fiscal year.
In the following work samples, two scopes of financial data are examined as part of the annual fiscal budget process.
The Financial Trends Report examines revenue, key expenditures, fund balances by fiscal year, cash and various other trends over a 7 year period. Different source data was used to compile the reports and including the audited comprehensive annual financial reports and system generated financial statements.
The Cost Recovery study examines fees charged on a program-by-program basis, providing a cost recovery rate (percentage) of program cost covered by user fee/and or license revenue. This study allows goal progress and performance to be measured.
Excel Functions and Course Samples
Excel Course – this workbook contains a working syllabus for an in-house, intermediate level Excel course. The index sheet tab contains topics and subtopics that are hyperlinked to their working sheet tab. Each sheet tab has a work sample that can be manipulated using the actual formula or imbedded example.
Excel Functions – this workbook contains an index and sheet tabs with live examples of common, and some lesser known formulas and automations accumulated over the years, as well as a few items for extracting data from database programs for use in Microsoft Excel. This short list is intended to be a quick reference guide with the intention of sharing and adding to, as other ideas prove helpful.