IS480 Team wiki: 2009T2 Wasabi

From IS480
Jump to navigation Jump to search


The Team, Roles and Skills


Members (L to R):

Richa Dayal - Project Manager & Secondary Business Domain Expert
Pawan Gupta - Lead Developer & Business Domain Expert
Varun Khanna - Ajax Specialist & Secondary Developer
Vivek Desai - User Interface Designer and Secondary Developer
Henry Roxas - Database Engineer and Secondary Project Manager



Team Supervisor

Kevin Steppe

Project Sponsor

Shinsei Bank, Japan [1] is a bank based out of Tokyo, Japan. It has clients all across Asia Pacific. The Balance Sheet Reporting System is a reporting tool used by the senior management of the company. The project was scheduled to go live by Dec 2010 but was preponed as our team approached them for the Final Year Project.

The client resides in Tokyo and all communication is purely via telephone and email. Videos of the system are currently being created to send across to the client for their feedback.

The client was engaged for the FYP as team members - Pawan Gupta and Varun Khanna have done a project with them earlier.

Team Meetings

Meeting with Kevin Steppe (Supervisor) - Tuesday 5-6 pm
Meeting with Anand Sarvaiya (Shinsei Bank) - Wednesday 9.30 pm

Internal Meeting Timings:
Monday: 8 - 12 pm
Wednesday: 9 - 1 am
Saturday: 12 - 5 pm

Project Description

Balance Sheet Reporting System

Balance Sheet Decomposition is a process by which the balance sheet of an organization is ‘decomposed’ to analyze and understand individual assets, liabilities and shareholder wealth in detail. The aim of the process is to reconcile the differences in information between the published accounting values and the business views. By virtue of this process, management gets a comprehensive view of the balance sheet and is able to easily track and manage the various components of their business. This allows for better decision making and a clearer understanding of asset values.

The objective of the project is to clearly present the individual assets as accounted on the published balance sheet into segments along with current book value. The project aims to have a presentation tool where the main business assets of the published balance sheet can be visually decomposed into business lines, product segments with further drill-down into accounting treatment and credit rating.

Project Motivation

There are various organization-wide reporting processes to provide extensive information about the bank’s assets. The information provided in these reports is adequate to provide a picture of the assets, but it does not demystify the layers of transformation between business views and accounting terms. A single accounting figure could have been obtained by linking assets from many different business areas. It is difficult to understand how the various businesses areas, products and services are linked to a particular asset. In order to gain a clearer understanding of this, the Finance group of Shinsei Bank has started an initiative, sponsored by the CFO, to achieve:

1. Clarity in order to help decision making Business leaders should be able to clearly relate their assets, as represented on the balance sheet, to the different business and product lines. They should have information that helps reconcile the product balances with General Ledger and provide a common ground to make informed decisions.

2. Better communication in order to improve efficiency Provide a common understanding of assets between financial accounting teams by classifying them into different business and product lines. Also, the business teams should be able to easily clarify any issues regarding asset ownership by going into further detail about each asset.

Project Scope

The following is the scope of the project. It gives a clear boundary between what has been completed and what is required to be done by the team.

Phase 1 – Completed (by Shinsei Bank)
Data gathering from various sources for focus area of balance sheet assets as of 31st March 2009.

What is this phase about?
Phase 1 is simply about gathering the necessary information, recording the transactions, preparing the financial statements and transferring data into an Excel file for further use. The aim of this is to consolidate all the balance sheet assets (taken from balance sheet) and their respective transactions (taken from General Ledger).

How is this phase implemented?
This phase has already been completed. The process used was manual wherein all business transactions were recorded in the General Ledger. Upon the passing of the relevant period, J-GAAP regulations were applied to these transactions to publish the financial statements for that period. The balance sheet was then pulled out and the data was inserted into an excel file along with the transactions from the General Ledger. This excel file then held all the necessary information required for subsequent steps. At any point, a user has the option of inserting multiple excel files and thus can generate comparison reports and forecast trends.

Phase 2A - Things to do (by Team Wasabi)
A prototype of the presentation tool which will take the data in its current form (MS Excel), decompose assets into its constituents, and present it to the user with the help of graphs and charts. Various levels of decomposition will have to be built into the system.

What is this phase about?
This phase is about the decomposition of the assets into the different business lines, product lines as also accounting treatment and credit ratings. The purpose of doing is to clearly identify the ownership of each asset so that the management can take decision regarding investment, disinvestment, writing off etc. Different views will provide information like growth of an asset as compared to other products in the same class or the effect of the credit crisis on a particular business line and, hence, on the asset. Additionally, categorizing assets by credit rating or accounting treatment can add new dimensions such as sensitivity of certain asset values when accounted for under particular accounting methods and, hence, can eventually influence decision regarding these accounting methods.

How is this phase implemented?
The system developed using .NET technology will take in the excel (output of phase 1). The Excel holds the business data, all assets and all transactions from the General Ledger. This data will then be transferred into SQL Server 2008 for subsequent steps. Once the data is stored in the database, the end-user is free to choose the view he/she desires and the decomposition module will pull the relevant data from the database, use the mappings and other algorithms (still to be developed) and produce the desired output. Once the relevant view is generated, the user has the option to see all the statistical analysis possible on that view. Typically, the analysis will produce a trend analysis, showing the future trends (based on market data and other algorithms involving financial calculations), forecasting of values, sensitivity analysis, and other statistical parameters like variance, average, weighted average, moving average, standard deviation, market risk, risk-return profile etc.

Phase 2b - Further steps
In addition to the above mentioned reports and analysis, addition of further characteristics for the assets such as mark downs (impairments, write-offs as the case may be), credit reserves, risk weighted asset, risk capital to help understand the quality of the balance sheet can be added. These risk weighted assets can be analyzed on the basis of their exposure to market risk and thus the impact of the credit crisis on the asset values can be determined.


Balance Sheet Decomposition UI


Technology Used

Technology Used



GUI Programming is easy; Data driven project with the UI itself being the business logic

SQL Server 2008

Team's competence in SQL is good and easy to integrate Asp.Net and C# with SQL Server within Visual Studio 2008

FusionCharts/Adobe Flex/SilverLight

Easy to use API for building interactive charts with C#


UI is more intuitive and appealing with the use of CSS and Ajax

Weekly Progress

Week 1

- Revised Work Breakdown Structure
- Revised Project Plan
- Client revised requirements
- Revised deliverables

Week 2

Functionalities Completed

- Database created, connections made, SQL Scripts completed
- Back-end Login functionality created
- Updates on ER Diagram
- Step 1 of 3 completed on division of meta-data

Challenges Faced

- No experience in C# and .Net programming
- No experience in Ajax UI enhancement

Challenges Addressed

- Consulted Prof. Ilse on basics of C# and .NET programming
- Read documentation on Ajax development

Week 3

Functionalities Completed

- Login done
- Create user profile done
- Edit user profile done
- Add Data done – without linking to rest of project
- UI Template created and sent to Client for feedback and revision
- Creating copies of Meta-Data (for analysis module)

Challenges Faced

- Mapping data from Excel into DB extremely challenging due to complexity of data
- Linking UI to back-end functionalities (like login) difficult due to inexperience

Challenges Addressed

- Consulted Supervisor Kevin Steppe for Mapping of data
- Consulted w3schools.com and related examples and successfully linked UI

Week 4

Functionalities Completed

- Add data completed
- Create Backup started
- Delete user profile completed
- UIs for all admin tools (add user, edit user, delete user, create backup) completed
- started on create business view logic (crux of the BI)

Challenges Faced

- Linking of charting software to back-end business logic

Challenges Addressed

- Consulted supervisor, practice with low level charting

Week 5

Functionalities Completed

- Revamped UI using CSS
- Decided charting software (Fusion Charts [2]) and implemented on Business View Level 1
- Fixed auto complete functionality
- Improved speeds of Meta Data upload
- Created all Meta Data Sheets

Challenges Faced

- Migration of Code to Wasabi Repository was complicated due to changes in namespaces and linkages - Team's progress was hampered due to Midterms and other course commitments, could not accomplish as planned earlier

Addressing Challenges

- Cleared SVN completely and updated the Wasabi Project - Revised work schedule

Week 6

Functionalities Completed

- Business Level 1,2,3 and 4
- Changed database schema and integrated all Business Level tables

Challenges Faced

- Progress was slow because, whenever the login button is clicked, associated event to be executed is not being called. Members unable to code due to this problem. Need to figure out how to fix it.
- Whenever we add a class file to the project created by another member (from SVN), the database changes – Visual Studio is doing something in the background and we had trouble connecting to the DB. So right now we’ve just deleted the old database and created a new one. Need to figure out how to fix it.
- Link is specific for particular database – everyone has to create their own Link class file – slightly inconvenient especially with integration. Link file is auto generated with Visual Studio. Did not foresee this difficulty.

Addressing Challenges

- SVN - Ensure that all auto-generated files like .dll, .exe, .pdb etc are not committed, causing difficulties in subversioning.

Week 7

Functionalities Completed

- Got add data up
- Product views 1 and 2
- Accounting view 1
- Linked UI to all working functionality
- Delete Data done (only need to create button on UI)
- Completed Mid Term Proposal Report
- Created Demo Video for Client, received positive feedback

Challenges Faced

- Add data was not up and running for a long time due to some minor coding errors which were only realized after a long time
- Progress was hampered a little bit, since other functionalities could not be completed as Add Data was not up and working

Addressing Challenges

- Team worked extra long hours this week in order to complete everything by midterm reflection time
- Went through each line of code multiple times in order to solve bugs, quality assurance check

Week 8

Functionalities completed

- Product view level 2,3
- Accounting Treatment view 1,2

Challenges Faced

- Progress was slow as team had very heavy workload during this week for midterms ahead
- Team was not able to gain clarification on certain doubts from client for a few days, hence progress was hamperd

Addressing Challenges

- Team shifted few items to from iteration 2 to iteration 3 in the schedule

Week 9

Functionalities completed

- Save as PDF
- Differentiate User Views
- Created the following sequence diagrams: login, create user, edit user, delete user, business level 1

Challenges Faced

- Heavy team workload this week as well - many members had midterms

Addressing Challenges

- Team worked outside of meeting times extra hard to accomplish tasks set out

Week 10

Functionalities completed

- Record Scroller for tab view
- Save as Image (PNG & JPG)
- Created new UI view based on discussion with two charts (one latest, one previous)

Challenges Faced

- Spent a long time trying to save as PDF, Image etc as the feature was unavailable in Fusion Charts 3.0.2 which we are using
- Difficulty in finding statistical package for statistical analysis

Addressing Challenges

- Searched Microsoft libraries and found Microsoft Math Library called MathNet.Numerics

*Team decided to add in Data Analysis module into the system in order to perform basic analysis (apart from statistical) based on user preference.

Week 11

Functionalities completed

- Completed all stored procedures
- Differentiated between admin & non-admin users
- Fixed UI table alignment, design scroller and breadcrumbs

Challenges Faced

- Unclear about credit rating view requirements, need clarify with client
- Non admin buttons appearing in Admin view

Addressing Challenges

- Have decided to disable all unnecessary buttons from admin view

Week 12

Functionalities completed

- Completed Credit Rating View
- Completed basic Data Analysis module, need to integrate with UI and put in error handling
- Completed FYP Poster

Challenges Faced

- Team was delayed in sending second UAT video

Addressing Challenges

- Instead of using a video for the UAT, the team decided to use Remote Login via www.LogMeIn.com to set up second UAT with client, so that he could navigate the system on his own - set up date for this UAT

Week 13

Functionalities completed

- Data Analysis Module
- Statistical Analysis on each page
- Email view
- Revamped UI completely and put it into all existing pages
- Second UAT to be held via Remote Login on April 6, 2010: 9 - 10 pm (10-11 pm Japan Time)

Challenges Faced

- Configuring the header and footer of the new UI - it kept moving based on the page size and was inconsistent
- Lot of time was spent changing UI on all the pages

Addressing Challenges

- Decided to only use the background image in order to edit the header instead of using the individual images
- Team spent a lot of time this week in meeting together, figuring out the new UI and fixing it

Week 14 & 15

Functionalities completed

- Completion of all change requests from client
- Credit Rating view fixed
- Statistical Analysis and Record Scroller fixed
- Data Analysis import to excel fixed

Challenges Faced

- Doing FYP work during final exams was very difficult to manage

Midterm Reflection

The Midterm Reflection was submitted on 19th February, 2010.
The Midterm Reflection can be found here: Media: Wasabi_Midterm_Reflection_V8_0.pdf

Final Reflection

The Final Reflection was submitted on 19th April, 2010.
Due to the sensitive nature of the project, and the confidentiality agreements signed, our submission was given through a CD to our supervisor and reviewer.

Meeting Minutes

Meeting Minutes 15 Dec Media: FYP_Meeting_Minutes_15_Dec.docx
Meeting Minutes 11 Jan Media: FYP_Meeting_Minutes_11_Jan.docx
Meeting Minutes 16 Jan Media: FYP_Meeting_Minutes_16_Jan.docx
Meeting Minutes 18 Jan Media: FYP_Meeting_Minutes_18_Jan.docx
Meeting Minutes 20 Jan Media: FYP_Meeting_Minutes_20_Jan_(Conf_Call_with_Client).docx‎
Meeting Minutes 23 Jan Media: FYP_Meeting_Minutes_23_Jan.docx
Meeting Minutes 27 Jan Media: FYP_Meeting_Minutes_27_Jan.docx
Meeting Minutes 1 Feb Media: FYP_Meeting_Minutes_1_Feb.docx
Meeting Minutes 3 Feb Media: FYP_Meeting_Minutes_3_Feb.docx
Meeting Minutes 8 Feb Media: FYP_Meeting_Minutes_8_Feb.docx
Meeting Minutes 10 Feb Media: FYP_Meeting_Minutes_10_Feb.docx
Meeting Minutes 12 Feb Media: FYP_Meeting_Minutes_12_Feb.docx
Meeting Minutes 15 Feb Media: FYP_Meeting_Minutes_15_Feb.docx
Meeting Minutes 17 Feb Media: FYP_Meeting_Minutes_17_Feb.docx
Meeting Minutes 24 Feb Media: FYP_Meeting_Minutes_24_Feb.docx
Meeting Minutes 3 March Media: FYP_Meeting_Minutes_3_March.docx
Meeting Minutes 8 March Media: FYP_Meeting_Minutes_8_March.docx
Meeting Minutes 2 April Media: FYP_Meeting_Minutes_2_April.docx
Meeting Minutes 3 March Media: FYP_Meeting_Minutes_3_April.docx
Meeting Minutes 6 April Media: FYP_Meeting_Minutes_20_Jan_(Conf_Call_with_Client).docx‎

Meeting Presentations with Supervisor

Meeting Presentation with Supervisor Week 3 Media: Week_3_Update.pptx
Meeting Presentation with Supervisor Week 4 Media: Week_4_Update.pptx
Meeting Presentation with Supervisor Week 5 Media: Week_5_Update.pptx
Meeting Presentation with Supervisor Week 6 Media: Week_6_Update.pptx
Meeting Presentation with Supervisor Week 7 - Midterm Evaluation Media: Week_7_Update.pptx
Meeting Presentation with Supervisor Week 8 Media: Week_8_Update.pptx
Meeting Presentation with Supervisor Week 9 Media: Week_9_Update.pptx
Meeting Presentation with Supervisor Week 10 Media: Week_10_Update.pptx
Meeting Presentation with Supervisor Week 11 Media: Week_11_Update.pptx
Meeting Presentation with Supervisor Week 12 Media: Week_12_Update.pptx
Meeting Presentation with Supervisor Week 13 Media: Week_13_Update.pptx
Meeting Presentation with Supervisor Week 14 Media: Week_14_Update.pptx