These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 13, 2010

A First Step Towards User-Defined Financial Ratios



‘VBA Corporation’ is an international healthcare IT software and services’ company dedicated to providing affordable RIS/PACS solutions to the global medical imaging community. After graduating from the Marriott School in 2007 I went to work for VBA Corporation as its Project Cost Accountant. Although the company had been around since 1990, it had lacked a sophisticated Accounting Department. Part of my role in the company was to assist the Finance Manager restructure the Accounting Department with the short-term goal of issuing Financial Statements and Management Reports.


One of the questions we asked as we began working on the Management Reports was how to handle the company’s financial ratios. Because the company prepared Management Reports every month, we had to adjust some of the statements in FRx to generate the last twelve months of data for the Income Statement so that its numbers would match up with the Balance Sheet. This was accomplished by building a report in FRx. The problem that was left behind was that of manual processing. At the end of every month I had to download the financial statements in Excel, input them into another Excel spreadsheet that I had created, and then recalculate all of the ratios and redo all of the charts by hand.


To solve this problem I decided to automate the task using VBA. The purpose of the project is to save VBA Corporation time and money. This project is broken up into three main stages.

First, the application must provide a way for the user to import a new Income Statement and Balance Sheet every month which will be added to previous month’s information. In addition, the program should confirm that all of the line items on the new financial statements match those of the template and give the user the ability to change the wording of any line item accordingly.

Second, the application should give the user a way to add new line items to the template if new financial statements include additional line items.

Finally, the program should allow the user to build his, or her, own financial ratios from scratch. The program should then be able to remember the formulas of those user-defined ratios and calculate the ratios using any month of data that the template file contains and any date set by the user.

This version of the program accomplishes only the first of the three stages. The next version will allow the user to add new line items. And, the final version will give the user the ability to define his, or her, own financial ratios which can then be automatically generated every month.

Project Write-up

Master File

Balance Sheet

Income Statement

No comments:

Post a Comment

Blog Archive