Olivetree Securities

Agency Broker

Summary

Challenge: At the end of every month, the management team at Olivetree Securities, an independent agency broker that specializes in equities transactions, is spending hours manually combing through trades to tally up portions of the commission paid out to their traders. The process is time-consuming, error-prone, and, most importantly, stifling the company's potential to grow the business.
Solution: Excel Clout tailors a spreadsheet application to Olivetree's operations. The application manages trades information, accurately calculates portions of commission paid to the traders, and automatically generates monthly performance reports. The performance reports not only give the management team a bird's-eye view of the business but also provide the traders the granular information on the trades executed on the company's behalf.
Features Utilized:

Examples of Requirements and Solutions

Requirement

The management team would like to know the year-to-date commission earned for each of their clients and compare those figures with the commission earned over the same period last year.

Solution

A pivot table groups the commission data by clients for the current year and last year. A VBA script automatically filters the date range for both years, and refreshes data in the pivot table and the charts in the final report.

Features Implemented

Screenshot

Note: All sensitive information has been removed for illustration purpose.

Requirement

The management team needs to send out month-end reports to traders for them to verify all trades executed on the company's behalf.

Solution

The spreadsheet program automatically generates month-end performance reports for traders with relevant trades data, and saves the reports in properly named PDF files.

Features Implemented

Screenshot

Note: All sensitive information has been removed for illustration purpose.

Requirement

Traders would like see how much commission they earned for the company over time for the current year.

Solution

The spreadsheet application automatically filters the trade data in a pivot chart for the specified date range, showing the year-to-date commission earned over the past months. The chart is included in the PDF reports sent to the traders at the end of every month.

Features Implemented

Screenshot

Note: All sensitive information has been removed for illustration purpose.

Requirement

When the spreadsheet application was completed, it had than a dozen of tabs. Navigating the tabs became unwieldy.

Solution

A sheet with links to all tabs is created and placed as the first tab in the spreadsheet application, serving as a navigation menu. When a user needs to find another tab, simply go to the first tab where the menu is.

Features Implemented

Screenshot