 |
 |
 |
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 |
|
|
 |
|
|
|
 |
|
|
|
|
|
|
|
|
|
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.
|
|
|
 |