EXCEL PROGRAMMING

Visitors for training programs on Advanced Excel VBA / Financial Modeling, please go to the 'Financial Training' Page to download Program Brochures.

READ ON if your interest is in retaining VTA for developing financial models for your organization or for Excel Programming.

VTA's Excel Programming Practice delivers state-of-the-art programming precepts by manipulating objects constituting the Excel application and by using custom-designed user-forms to develop solutions that are fully attuned to a situation.

Excel exposes two access points to the functionality of the product:
The first access point is via the familiar Graphical User-Interface (GUI) - the access point for 95% of Excel users.
The second access point is via manipulation of objects constituting the Excel application - Microsoft exposes underlying objects for programmatic manipulation.
" The risks from human error in complex models are enormous - one error in a large spreadsheet can totally change the viability of a major project. Virtually every major investment decision that has been made in the last few years will have been supported by the results from a computer model. "

- KPMG Ireland's Business Advisory Services

For sophisticated applications, there is no alternative but to work with Excel Objects which are manipulated using Visual Basic for Applications, the language for programming Excel. While some users program Excel using Macros, the true power of Excel can be accessed only with visual basic code. Many enterprises are unaware that the database in which they have invested millions of dollars is good only for storing and retrieving data but ill-suited for numerical analysis for which Excel is the appropriate tool. Thus, databases and numerical analysis tools (such as Excel) have distinct roles to play and an Excel based solution often requires the data component of the application to be supplied from an external source such as the Internet or the back-end server.

The advantage of programming Excel is that the impressive analytical capabilities built-into Excel can be leveraged without recreating any of the functionality. What ever your industry, chances are you have not tapped into the full power of Excel:

 Financial Institutions and stock broking companies
Non Banking Financial Institutions
Research Agencies
Chemical and Biological Research and Pharmaceutical Companies requiring statistical analysis on large population of data
Central Banks and Economic Think Tanks requiring sophisticated econometric and financial models as basis for policy measures
Mining and Electricity Generating Companies needing to build sophisticated financial analysis models that often spawn 30 years or more
Consumer Finance Companies requiring sophisticated pricing tools
Merchant Banking Firms requiring tools to evaluate companies, M&A, pricing
Companies needing to analyze data stored in backend servers such as SAP, Oracle, DB2, Sybase

 

There are seven distinct phases in developing an Excel applications (some may be rolled into other phases or while some may not be required):

 
•
 Analysis: Understanding the requirements for an Excel application
•
Interaction: Translating preliminary thoughts into tangibles via interaction with end-user to ensure that activities are properly aligned with end-user expectations.
•
Coding: Writing of code and development of Graphical User Interface Elements to transform ideas into a working application.
•
Documentation: Development of documentation to provide information on features of the application.
•
Packaging: Developing an installation package for the application when the application needs to be distributed.
•
Security: Developing and embedding security procedures into the application when the application is made available via licensing or usage is restricted.
•
 Training:   Providing hands-on training in the use of the application

Programming Excel requires abilities that are not easily or widely available - Excel programming requires a very intimate knowledge of Excel Objects which can only be acquired over long periods of extensive usage - VTA has been working extensively with Excel for the past 14 years. The following are two major Excel Programming projects carried out by VTA:
CapInvest


CapInvest is an Excel application that provides world-class Transaction Structuring and Product Creation Tools to enables financial institutions to develop user-friendly and market-driven financial and savings products. Incorporating around 100,000 lines of code and with an installed size of 30 MB, CapInvest is a superb example of programming Excel to develop a World Class application that has no peers. CapInvest has been developed entirely with Office XP Developer.

 
LeaseEx 2000
 
 

 
   
 
LeaseEx 2000 is a 3D Financial Model, developed with Microsoft Excel and Visual Basic for Applications; the engine comprises of 17 modules (encompassing a total of 180 tightly-linked worksheets) and proprietary leasing equations that efficiently process data - typical recalculation time for a fully loaded model is less than 5 seconds! LeaseEx 2000 is an essential tool for a wide variety of project tasks, such as, carrying out appraisals of leasing projects, structuring operations by identifying and eliminating inefficiency points, evolving optimal capitalization structures that reflects project, capital market and environmental constrains, preparing business plans, providing training in Leasing, and so on.

The model simulates a leasing company's operations, employing sets of optimized leasing equations that capture lease flows in response to changes in assumptions. LeaseEx 2000 models a lessor's activities over a ten year period, employing monthly lease inflows and outflows that are aggregated into quarterly totals for cash book reporting and into annual totals for financial statement reporting. The monthly tracking of lease flows infuses a high degree of precision in forecasting interest expenses, capital requirements and in preparing accurate financial statements.

A very large number of lease variables / Assumptions enable precise structuring / forecasting / sensitizing of virtually every aspect of a lessor's operations over a ten year time period; the analytical capabilities of LeaseEx 2000 enable inefficiencies to be structured away and project assumptions to be redrawn for pursuing optimal financial targets.

 

©2005-2007 /Vish Tumu Associates - Designed by Essel