Our Client wanted to convert a Microsoft Excel Budget Model
into a web based Forecast Model. The client was looking for a
technology partner to develop a Forecast Model that could be
centrally updated and accessed at any time by the corporate
financial analysis team. The client’s property management teams
would access the model at least weekly and they would be rated
on their ability to closely project their property’s ongoing
financial status.
Integrated Services was asked to design and develop the
application within 90 days, conduct a month long pre-production
test, and to integrate the application into production. The
client had an Intranet with WAN connection speeds ranging from
56k dial-up connections to T1 connections. The model would have
to be customizable by almost 200 locations across North America,
be subject to corporate updates and maintenance, and the
application would have to support multiple layers of security.
ISI was engaged to manage and develop the technical design,
development and testing of an Intranet based application that
would enable automated customization of a master MS-Excel
workbook with data from a centralized database for utilization
at each of the client’s managed and owned properties. The
application included interfaces to an Essbase database.
ISI implemented an application that automatically customized a
master workbook for each property based on the SAP based chart
of accounts for that property. Dependent workbook templates were
automatically created to allow for multiple security layers and
workload distribution. The production master workbook was over
150 worksheets containing an average of 100,000 user enterable
fields supporting property- customized formulas. The workbook
contained over 1 million fields and the interface to SAP and
Essbase was over 10,000 elements. The forecast model was
accessed through a web site and transferred using a customized
ActiveX control based on FTP. When utilized by a specific
property the forecast model is customized, dynamically
populated, and displayed in Excel within the browser window. A
local copy of the complete forecast is available for authorized
users to approve and submit the forecast for processing at
corporate. ISI also developed a self-updating Servlet extension
that enables the automatic installation of server-side software
as well as automatic update and deployment of client-side
software. ISI developed performance enhancements to address the
WAN limitations, the volume of data being forecast, as well as
the user type accessing the model.
The user interface was provided by Java Servlets connecting to
an Oracle Database Server presented by Microsoft’s Internet
Information Server and Allaire’s J-Run software. Microsoft
Visual Basic and Visual Basic for Applications was used populate
the browser instance of Excel, to customize MS-Excel menus, and
apply security to the property workbooks.
