Project:             Accounting Data Warehouse (ADW)

Client:               Activision

Dates:               February, 2003 – June, 2003 

Industry:           Entertainment

Skills Required: Systems Analysis, Design, Development and Programming; Knowledge of Accounting Systems and Methodologies

Situation

·          The company develops and sells video games for all video game platforms (X-Box, Playstation, etc.)

·          Operating Expense forecasts are performed twice a quarter, with multiple passes at each reforecast.

·          Fifteen Excel workbooks each with multiple worksheets are used in the reforecast process.

·          Results of the forecast are consolidated, and then operating expenses are allocated down to the game platform and product level from the department level.

·          All procedures used to consolidate, calculate and allocate results are manual, and require approximately 40 hours of work per reforecast.

Requirements

·          Automate the consolidation of Excel forecast workbooks.

·          Automate the allocation of costs down to the product and game platform level.

·          Provide a tool to easily report on various metrics at varying levels of granularity.

·          Allow Actual and Budget data to be warehoused in the system and be compared to Forecasted amounts.

·          Reduce manual data entry and reporting hours required in the Forecast process.

Technologies and Methodologies

·          Microsoft Access XP (2002)

§          Automatically import and normalize Forecast and Budget data from Excel spreadsheets.

§          Automatically import Actual data from Oracle extract files.

§          Perform complex cost allocation calculations on data in the data warehouse via Access queries.

§          Design an advanced and customizable end-user tool for querying and reporting on data in the warehouse.

§          Crosstab Queries.

§          Visual Basic for Applications (VBA) Code Subroutines and User-Defined Functions.

Solution

·          Designed an Access XP based data warehouse for operating expense data.

·          Developed user-friendly forms to import external data, run cost allocations and report on results.

·          Normalized imported data upon import to streamline and simplify database structure.

·          Developed a sophisticated and intuitive end-user interface to navigate and operate system functionality.

Results

·          Reduced the amount of time required to perform cost allocations from 40 hours to less than five minutes.

·          Provided a warehouse of historical Actual, Forecast and Budget cost data for comparative reporting.

·          A sophisticated and intuitive query and reports tool allows users to easily utilize historical data in an ad-hoc fashion.

·          Designed an advanced user interface, including a hyperlink-based menu and navigation system that allows end-users with little Access experience to easily access and perform all system functions