|
Solutions Overview
Scenario
The CFO of Related relied on a single, huge
Excel workbook to manage his current budget model and used this same workbook
to perform analysis for future budgetary decisions and investments. By using
the same workbook for both current figures and projections, he had numerous
ambiguous data errors and consistently “lost” data in the workbook. To compound
the issue, the workbook was so complex, only one or two employees could
understand the model. Related wanted to create a new system to manage and
analyze the budget model, one that would ensure data integrity, integrate with
other departments at Related to always ensure that they had the right data at
the right time, and standardize the model so that it could be more widely
understood and utilized.
Company Profile
Based in New York City, NY, Related is one of the
nation’s largest developer, manager, and financier of premier real estate
properties. From 1996 to present they will have developed over $5 billion worth
of developments, overseeing more than 1,100 properties in 47 states, many in
metropolitan areas.
Situation
The complex and unwieldy Excel workbook that the
CFO relied on was difficult to maintain and modify. Each time that the CFO
wanted to see a different projection on the budget model, a new copy of the
workbook would be created and the new projection data was entered. In this way,
the CFO had numerous copies of the same Excel workbook, each with slightly
different data sets. Willow was hired to develop a new system comprised of
tools to import data from various departments into an SQL Server database,
tools to allow the users to make specific modifications to that data, and a
report engine that would generate budget relevant reports, based on
customizable templates, to the CFO and Related’s Board.
|
|
- Willow created a number of tools to import data
from numerous departments throughout Related into a central backend SQL Server
database.
- Tools were also provided to allow Related
personnel to make specific, necessary modifications to the data in the SQL
Server database.
- Customizable Excel templates were provided to a
reporting engine that would mine the central database to create standard,
consistent reports.
- A detailed audit trail was maintained by the
system, recording all activity on the database.
The single Excel workbook used for the budget
model grew over the years as new financial opportunities had to be taken into
account. The workbook had grown to over 120 worksheets and 4MB in size! These
worksheets had historic numbers for the budget mixed with future projections.
The calculations used a mix of these numbers, never the same set from one
projection to another. To make matters worse, the historic numbers were often
estimates, there was no mechanism in place to make sure they had the actual
numbers from the department that tracked them. It was imperative to Related
that the budget system becomes more reliable, with better data accuracy and
better data integrity.
The first goal was to ensure data accuracy.
Willow talked to the head of each department that would supply data to the new
system. We wanted to get the data as close to the “horse’s mouth” as we could.
We made sure that each department was aware of what was expected to them and we
discussed the best way to get their data sets into the central database.
Willow developed a set of tools to allow the CFO
and his assistants to modify the data in the central database. This was
necessary for two reasons. First, some of the data that the new system would
need would not come from a specific department at Related, but rather from the
CFO himself. The second reason was that future projections were often based on
scenarios created by the CFO and his staff. They wanted to be able to enter and
keep track of different scenarios for the budget model. Willow designed the
tools and the database in such a way that the CFO could maintain multiple
future projection sets and turn these on or off as he desired.
Finally, Willow designed a set of customizable
Excel templates to be used by a reporting engine. This reporting engine would
use the templates and the central database to provide standardized, reliable
reports to the CFO. These reports were simple and straightforward so that not
only could they be used by the CFO but also someone not familiar with the
budget model could maintain and use them.
|
