IS480 Team wiki: 2009T2 Wasabi
- 1 The Team, Roles and Skills
- 2 Team Supervisor
- 3 Project Sponsor
- 4 Team Meetings
- 5 Project Description
- 6 Project Motivation
- 7 Project Scope
- 8 Prototype
- 9 Technology Used
- 10 Weekly Progress
- 10.1 Week 1
- 10.2 Week 2
- 10.3 Week 3
- 10.4 Week 4
- 10.5 Week 5
- 10.6 Week 6
- 10.7 Week 7
- 10.8 Week 8
- 10.9 Week 9
- 10.10 Week 10
- 10.11 Week 11
- 10.12 Week 12
- 10.13 Week 13
- 10.14 Week 14 & 15
- 11 Midterm Reflection
- 12 Final Reflection
- 13 Meeting Minutes
- 14 Meeting Presentations with Supervisor
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
Shinsei Bank, Japan  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.
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
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.
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.
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.
ASP.NET & C#
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
Easy to use API for building interactive charts with C#
UI is more intuitive and appealing with the use of CSS and Ajax
- Revised Work Breakdown Structure
- Revised Project Plan
- Client revised requirements
- Revised deliverables
- 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
- No experience in C# and .Net programming
- No experience in Ajax UI enhancement
- Consulted Prof. Ilse on basics of C# and .NET programming
- Read documentation on Ajax development
- 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)
- 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
- Consulted Supervisor Kevin Steppe for Mapping of data
- Consulted w3schools.com and related examples and successfully linked UI
- 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)
- Linking of charting software to back-end business logic
- Consulted supervisor, practice with low level charting
- Revamped UI using CSS
- Decided charting software (Fusion Charts ) and implemented on Business View Level 1
- Fixed auto complete functionality
- Improved speeds of Meta Data upload
- Created all Meta Data Sheets
- 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
- Cleared SVN completely and updated the Wasabi Project - Revised work schedule
- Business Level 1,2,3 and 4
- Changed database schema and integrated all Business Level tables
- 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.
- SVN - Ensure that all auto-generated files like .dll, .exe, .pdb etc are not committed, causing difficulties in subversioning.
- 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
- 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
- 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
- Product view level 2,3
- Accounting Treatment view 1,2
- 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
- Team shifted few items to from iteration 2 to iteration 3 in the schedule
- Save as PDF
- Differentiate User Views
- Created the following sequence diagrams: login, create user, edit user, delete user, business level 1
- Heavy team workload this week as well - many members had midterms
- Team worked outside of meeting times extra hard to accomplish tasks set out
- Record Scroller for tab view
- Save as Image (PNG & JPG)
- Created new UI view based on discussion with two charts (one latest, one previous)
- 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
- 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.
- Completed all stored procedures
- Differentiated between admin & non-admin users
- Fixed UI table alignment, design scroller and breadcrumbs
- Unclear about credit rating view requirements, need clarify with client
- Non admin buttons appearing in Admin view
- Have decided to disable all unnecessary buttons from admin view
- Completed Credit Rating View
- Completed basic Data Analysis module, need to integrate with UI and put in error handling
- Completed FYP Poster
- Team was delayed in sending second UAT video
- 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
- 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)
- 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
- 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
- Completion of all change requests from client
- Credit Rating view fixed
- Statistical Analysis and Record Scroller fixed
- Data Analysis import to excel fixed
- Doing FYP work during final exams was very difficult to manage
The Midterm Reflection was submitted on 19th February, 2010.
The Midterm Reflection can be found here: Media: Wasabi_Midterm_Reflection_V8_0.pdf
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 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