Asset Allocation Spreadsheet

Overview

This organization needed to communicate in a format as custom as a client's financial needs. The implementation worksheet detailing a client's allocation of assets included a client's details, advisor credentials, asset information, and allocation categories. These details would produce a spreadsheet with customized and dynamic columns, pie charts, data tables, and formatting.

Solution

Ms. VBA developed an easy data entry solution in Microsoft Excel 2003 that translated a client's information into a professional, presentable worksheet. A dynamic data form asks a user for specific details pertinent to the worksheet, eliminating the need to revise any sort of template. With the click of a button, the resulting spreadsheet is a visually appealing presentation of the calculated details provided, ready for printing and/or emailing.

Project Details

The data form first appears when the Excel file is opened. The user enters the client and advisor's name and credentials, the number of entities being implemented for the client, selects which Asset Classes will be used, and then the number of investment vehicles that will be used in each asset class. The simple "Create File" button closes the data form and creates the customized worksheet.

Figure 1: Data Form

The client and advisor's information are clearly positioned for professional presentation. Assigned colors for each asset class are reflected in the pie charts, making a visual connection for easier relational comprehension. Formulas are automatically included to calculate the statistics that drive the respective pie charts. The "Add" and "Fix Formulas" buttons allow flexibility for updating.

Figure 2: Asset Allocation Worksheet

Upon entering and allocating all of the Investment Vehicles, the report can be printed and/or emailed to the client for their review.

Figure 3: Asset Allocation Report

Questions about this project? Interested in something similar? If so, please contact us for a free quote!