Skip to content

Funding Report

This report was designed to give insight into loans that have funded for the date range selected in the report. My hope is that this report will act as a starting point to build out a Funding report that works best for your companies work flow.

Dataset

Before opening the template, please ensure that you have prepared either an Excel worksheet, a Microsoft SQL (MSSQL) Custom View, or a PostgreSQL Custom View to serve as the dataset, depending on the template you intend to use. Verify that the fields are named precisely as listed in the 'Field Name' column below. If Excel is your dataset, confirm that the sheet containing the relevant data is named Data.

Field Information

All these fields must be part of the dataset, if one of the fields listed is not something that is currently in your LOS then it is alright to use the same data point for the column. Example, if you don't have a field for your Branch name, then it would be ok to reuse the same field this is used for ORGID as the Branch data.

Field Name Field Description
ORGID Field used to group loans based on branch number or Cost Center
Branch Field used for the name of the Branch
GUID Unique identifier on a per loan bases
Disbursement Date Date whcih a Loan proceeds are Funded by Lender
Top Ratio Front end DTI for the loan
Bottom Ratio Back end DTI for the loan
Est Closing Date Estimated Date the loan will close
FICO The FICO score from the loan
Total Loan Amount The full loan amount
Funding Date The date the loan was funded
Interest Rate The interest rate of the loan
Loan Number The loans loan number
Loan Purpose The purpose of the loan such as Purchase, Refi, etc...
Loan Type The type of loan such as Conventional, FHA, etc...
LTV The loan to value of the loan
Loan Officer Loan Officer's name
Processor Processor's name
Underwriter Underwriter's name
Closer Closer's name
Current Milestone Field used to hold the current milestone value of the loan

These are the only required fields for the template to work correctly. If you want to expand your report to include other data points then you can add those fields in as long as they are not named the same as one of the columns above.

Note

This dataset can be used with the template_projection.pbit as well. It is possible to point the funding and projection report at the same dataset to minimize the number of reports that need to be exported out of the LOS.

Using a template

Using a Excel dataset

Once you have your Excel file ready to go with the correct column headers and sheet name open the template_funding.pbit and you will be prompted for the file path to your Excel file. This file path must be the exact full path to the file. Example is if I have my Excel file saved to my desktop as LOS_Data.xlsx then the path I would type in is C:\Users\Jody\Desktop\LOS_Data.xlsx

file_path

Once the file path is typed in click load, your data will be imported and the report will open. From here you can customize the report by swapping out fields in visuals or possible changing measures or custom fields to better suit your companies work flow.

Using a Microsoft SQL dataset

Once you have your MSSQL custom view built out with the correct column names, open the template_funding_sql.pbit and you will be prompted for 3 pieces of information:

  1. SQL_Server: This will be the IP address or name of the SQL server that houses the database with your LOS data.
  2. SQL_DB: This is the name the database that holds the custom view we will be using for the template.
  3. Custom_View: This is the name of the custom view that contains the data needed for the template.

template_funding_sql_params

Note

If you need to add a port number to the end of your SQL server address, simply add a colon to the end of your server IP or name followed by the port number, example would be: MY-SQL-SERVER:1234, where :1234 is the port number.

Once this information is loaded, you may be prompted for a user name and password for the SQL server, please work with your SQL server administrator to get the correct credentials. Once everything is connected up the report will open allowing you to customize the report to your companies needs.

Using a PostgreSQL dataset

Once you have your PostgreSQL custom view built out with the correct column names, open the template_funding_postgresql.pbit and you will be prompted for 4 pieces of information:

  1. PostgreSQL_Server: This will be the IP address or name of the PostgreSQL server that houses the database with your LOS data.
  2. PostgreSQL_DB: This is the name the database that holds the schema and data we will be using for the template.
  3. PostgesSQL_Schema: This is the name of the Schema that holds the custom views and data that will be used for the tempalte.
  4. Custom_View: This is the name of the custom view that contains the data needed for the template.

template_funding_postgresql_params

Note

If you need to add a port number to the end of your PostgreSQL server address, simply add a colon to the end of your server IP or name followed by the port number, example would be: MY-POSTGRESQL-SERVER:1234, where :1234 is the port number.

Once this information is loaded, you may be prompted for a user name and password for the PostgreSQL server, please work with your server administrator to get the correct credentials. Once everything is connected up the report will open allowing you to customize the report to your companies needs.

Report Pages

Pages on this report are intended to provide insight into loans that have funded in the time range selected. Pages in this report are intended to provide break downs based on funded units and volume for origination and operations, along with providing a high level average statistics of funded loans.

Branch Page

This page provides information broken down by Branch. Toggling between Units and Volume will provide an information on how many/much of each a branch is doing. Selecting a branch from the By Branch line graph will filter the rest of the visual on the page to show how much that particular branch contributed to the whole. While you have a branch selected the Drill to Details button will become available, this will allow a user to drill to the Branch Details page which provides many of the same visual just focused on that Branch.

Operations Page

This page provide insight of how operations preformed based on funded loans. Clicking on one of the users in a line chart will filter the page down to show who that user mostly worked with and what type of loans that user worked on during that date range.

Operation v2 Page

This page is optional to use, it provides similar visuals as the Operations page but allows you to focus on a particular operation role instead of having all the data for all roles on the same page.

Caution

This page may not work very well in larger datasets as the number of users in each role may cause a larger dataset and cause the report to load slowly.

Branch Details Page

This page is a drill down page from the Branch page. Drilling to this page will provide funding information for the branch selected as part of the drill down. This provides information for Loan Officers found in that branch.

Funding Stats Page

This page has average characteristics for funded loans in the date range selected. A chart showing Selected Year vs Previous year for Units and Volume. Finally a Year to Date line graph provides insight of funded loans over the last 5 years.

Customization Options

Projection Date

In the LOS_Data table a custom column with the name of Projection Date is used to determine when a loan is projected to close. By default that column calculates the max of the Est Closing Date or the Disbursement Date fields for the loan. If your company uses some other field or calculation to project when a loan will close, updating this custom column will bring the report closer to your companies work flow.

Default Date Range

By default all report pages have a filter applied to only show dates in the last five years. A custom column in the Dates table with the name of Date Range - 5 Years is used for this filter. By changing the DAX in this field you can narrow or expand the that date range.

Info

Most the time when updating a columns name in Power BI, visuals and filters that use that column will automatically update. If for some reason it doesn't simple remove the old column from the Filters pane and replace with your new column.

Download

Report Demo

Click Here to open the report in a new page.