Excel Workouts: Client Reconciliation and Business Analysis

Delegates use the data and work from EX101 (this will be supplied to them), as well as confirmations from the buy-side. The buy-side files contain deliberate errors which need spotting and cleaning.

Using this case study participants are introduced to a range of Excel tools to efficiently reconcile the confirmations, spot errors, clean the data and match them once cleaned. Delegates also learn how to produce a storyboard summary dashboard that includes:

Heat maps of rolling monthly client summary data

Rank the clients according to various statistics (e.g. revenue, revenue per trade)

Client Scores for input to strategic decisions, identifying whether commissions should be adjusted for certain client groups, inactive clients and different classes of concentration risk

Forecast client data over future months

Suitability

This course is for individuals who are new to Excel.

Learning Outcomes

By the end of this session, delegates will be able to:

Use Excel tools to identify and clean data of errors which need spotting and cleaning
Reconcile and match trade confirmations between Buy and Sell sides
Produce a storyboard summary dashboard
Create heat maps and rankings by criteria
Calculate and visualise rolling monthly client summary data
Produce statistical analysis for management information
Forecast using historic client data

Course Content

Skills/ Techniques covered:

Named cells and ranges
Editing links for relocated source files
Storyboarding with Summary Dashboards/ Analysis of the business statistics (summarised by client split by region and rolling month):
Concatenation, SUM, AVERAGE, COUNT, MIN, MAX, SUMIF, COUNTIF, LARGE, SMALL, EDATE, SUMPRODUCT, PERCENTRANK, IFERROR, INDEX, MATCH, OFFSET
Find/ replace to add/ remove $ signs
Adding form controls (Developer Tab)
Conditional formatting: Colour Scales (Heat Maps) and Data Bars
Forecasting revenue: TREND, GROWTH arrays, FORECAST
Charts: Pie, spark lines

100%

of our clients would recommend Alpha to a colleague or peer

90%

of a cohort was promoted within 2 years after participating in an Alpha course.

96%

of participants felt more confident in their role, following an Alpha commercial leadership course.

Why study with Alpha?

Innovation and creativity born from experience

We are thought leaders in instructional and learning journey design and holistic solution architects. We have extensive finance and investments experience combined with skills application to deliver performance improving results. We develop immersive learning environments that maximize time to productivity, support talent retention and added value to improving quality of hires.

Knowledge Exchange Evangelists

We are focused on mining the embedded organisational intellectual capital for the benefit of the next generation. We create and curate best in class practice gathered from our experience with the leading financial institutions. We design our programmes with the end in mind – what results are you trying to achieve with this intervention? What metrics will we set ourselves to achieve that?

Generation Proof

Quality and innovation, using current market and industry best practices, have made us a trusted partner in delivering dynamic and motivating training for the financial and capital markets. Our programmes are generation proof and responsive to evolving learner needs and styles. Our solutions use a multi-stakeholder engagement strategy that expands beyond relationships between the learner and learning provider. We create connections with managers, peers and the wider business to drive impactful return on investment..

Enquire now

"*" indicates required fields

The team are so friendly and pleasant to work with, everyone is very professional and keen to help us. Building a relationship over the past couple of years helps us to feel like the Alpha team are even more able to understand our needs and provide more proactive solutions.