sageworks real estate holding company case study · sageworks real estate holding company case...

Sageworks Real Estate

Holding Company Case

Study

CREDIT ANALYSIS SOLUTION

2

sageworksanalyst.com

919 .851 .7474

866 .603 .7029

[email protected]

Case Study Goals

This case study provides an introduction to the Sageworks Credit Analysis solution. This will be a basic example

of a global cash flow analaysis for a real estate holding company loan. It is designed for someone with no

experience with the Sageworks software. The analysis will combine cash flows from the business, guarantor,

and real estate to to service the debt.

It should take you no longer than 1 hour to complete the case study, review the results, and check your work. It

will introduce you to the concepts of navigation, entry process, and reporting in Sageworks.

Background

John Doe is President and owner of a commercial real estate company. You are conducting the credit analysis

for an annual review and have received financial information for both the company and the owner.

Annual Review

Terms

$750,000 Fixed Payment Balloon

P&I with 3 year maturity

o Payment Amount: $5,000

o Interest rate 6.18%

o Loan Term: 36

o Amortized Over: 293

o Balloon Payment: $712,048.73

3


919 .851 .7474

866 .603 .7029

[email protected]

Overview of Entry Process 1. Set Up Lending Relationship on Group Profile page

Business

Person

2. Enter Financial Data

Personal Tax Return, Recurring Debt, and Personal Financial Statement for Individual

Tax Return and Debt Schedule for Business

3. Attach Existing Debt to Existing Collateral

When you have completed the 3 step entry process, you can generate output in a Standard Report.

4


919 .851 .7474

866 .603 .7029

[email protected]

Step 1: Who is the borrower?

Create the Group

Login to Sageworks Analyst manually:

Go to www.sageworksanalyst.com

Click Customer Login and enter your user name and password

Select Credit Analysis

1. In the Credit Analysis solution, click Add/Edit link in the navigation at the top and then select Group.

Please include your initials where indicated. This will make your group, business, person and real estate

unique from other users inputting the case study in Credit Analysis.

http://www.sageworksanalyst.com/

5


919 .851 .7474

866 .603 .7029

[email protected]

2. Name the Group XYZ Holdings (Your Initials).

3. Click Save.

4. After naming the Group, Add a New Business.

Entry Method – Real Estate

Business Name – XYZ Holdings (Your Initials)

Industry Code – 531120 Lessors of Nonresidential Buildings

Address, City, State (optional)

5. Enter information for a Person

First Name – John

Last Name – Doe (Your Initials)

Address, City, State (optional)

6. Click Save and Add Financial Data

You have now successfully set up and saved a lending relationship. This completes the entry of the group

profile which allows us to move to the next step of entering financial data.

Step 2: Enter Financial Data

1. In the Credit Analysis solution, click Add/Edit link in the navigation at the top and then select Financial

Data. 2. Enter tax return information for XYZ Holdings (Your Initials).

Within the tax return pages, we have made it look very similar to the tax formst that you usually work with.

Enter what is asked of you. There may be some lines missing on the tax return pages from the tax forms you

have in front of you. We only ask for lines that affect cash flow and only ask for items once (tax forms often

times will ask for a piece of information multiple times).

6


919 .851 .7474

866 .603 .7029

[email protected]

a. Hover over Add Financial Data for the business.

b. Select Tax Returns.

c. Select Form 1065.

d. Select 2016.

Important tax return fields that need to be inputted in Sageworks will be highlighted in red.

7


919 .851 .7474

866 .603 .7029

[email protected]

Follow the progression of the form input: Form 1065, Schedule K, Schedule L….

a. Enter the line items from the first page of the 2016 1065 form

Save and Continue

b. Don’t enter any lines on the Schedule K

Save and Continue

c. Enter the line items from the Schedule L

Save and Continue

8


919 .851 .7474

866 .603 .7029

[email protected]

d. Enter the line items from the Schedule M1

Save and Continue

e. Enter the line items for the schedule M2

Save and Continue

f. Don’t enter any lines on the Schedule F

Save and Continue

9


919 .851 .7474

866 .603 .7029

[email protected]

10


919 .851 .7474

866 .603 .7029

[email protected]

g. Click Add on the 8825.

h. Add 1 property.

i. Input the line items from the 8825 form.

j. Click Save.

11


919 .851 .7474

866 .603 .7029

[email protected]

k. Click Finish Tax Entry

Once you complete the Tax Return, a financial statement based on the Tax Return will be created.

You should now be on the Real Estate Investment page.

Enter Details for Real Estate Investments for XYZ Office Building (Your Initials)

XYZ Office Building (Your Initials)

a. On the Real Estate Investment page, the information on the page was mapped from the

Tax Form 8825.

b. Enter Property Value $650,000.

c. Click Save.

Enter Rent Roll

Enter Rent Roll information for XYZ Office Building (Your Initials).

a. Click on the Edit Tenants button on the Real Estate Investments page.

12


919 .851 .7474

866 .603 .7029

[email protected]

b. Enter the following information from the rent roll found in the information below.

Leases & Tenants

Property Name: XYZ Office Building (Your Initials)

Property Type: Existing

Borrower: XYZ Holdings (Your Initials)

Date: 3/31/2017

Total Square Feet: 15,375

Percent of Ownership: 100%

Property Appraisal Value: $650,000

Tenant Lease Maturity Square Feet Annual Rate

per Sq. Ft.

Total Gross Income

Geeks & Gadgets 12/31/2017 5,000 9.00 $45,000

The Black Pearl 03/31/2018 5,000 11.00 $55,000

Collier Law Firm 05/31/2021 2,300 8.00 $18,400

Vacant 3,075

c. In the Edit Tenants layover, make sure that ‘Use Rent Roll’ is switched to Yes.

d. Click Save and Close in the Edit Tenants layover.

e. Click Save and Continue on the Real Estate Investment page.

Itemize Debt: Enter Business Loans for XYZ Holdings (Your Initials)

13


919 .851 .7474

866 .603 .7029

[email protected]

a. You are now on the Income Statement.

Save and Continue

b. You are now on the Balance Sheet.

Save and Continue

c. You are now on the Statement of Equity.

Save and Continue

d. You are now on the Business Loans.

e. Select Add Loan.

f. You should now have 2 loans to be filled in.

g. Enter the information from the image on the next page for each loan.

h. For the Loans with your Institution - check the box on the left of the loan information, Loans A & B are

loans with your Institution (Collateral will be assigned later).

If you have a core integration, the option to select the loan as a loan with your institution will not exist;

please enter the loan regardless.

i. Be sure to select XYZ Office Building (Your Initials) as Loan Applies to for Loan A and B.

If you do not have ‘Do not automatically create loans for Balance Sheet debt not itemized below ’ checked

off, please check it off.

j. Click Save.

k. Click Save and Finish.

14


919 .851 .7474

866 .603 .7029

[email protected]

Enter Financial Information for John Doe (Your Initials)

2015 1040 Tax Form

Important tax return fields that need to be inputted in Sageworks will be highlighted in red.

a. Click Add Financial Data and choose Tax Return >> 1040 >> 2015.

b. Enter the line items from the first page of the 2015 1040.

15


919 .851 .7474

866 .603 .7029

[email protected]

16


919 .851 .7474

866 .603 .7029

[email protected]

g. Click for Save and Continue.

h. Click Finish Tax Entry tab in the blue navigation ribbon.

Once you finish entering the tax data, the system creates a Financial Statement based on the tax

form data.

You should now be on the Income and Expenses page. Add the date fo 3/31/2016 to the Personal Financial

Statement Date field. The date will default to the date of the Tax Re turn if no date is entered.

All information pulls through from 2015 1040; no entry needed. There should be a 0 for Personal Expenses. If there

is any value in the Personal Expenses field, please delete and click Save.

Itemize Debt: Enter Recurring Debt for John Doe (Your Initials)

a. Click for Save and Continue on the Income and Expenses page.

b. You are now on the Recurring Debt page where you will enter loans for John Doe (Your Initials).

c. You can add loans by clicking the Add New button. Enter the following loan information for each of the

loans:

Loan Category

Loan Description (Name)

Payment Type (If not filled in, the program will assume type)

Current Balance

Approximate Monthly Payment

Interest Rate (If not filled in, the program will auto calculate)

Remaining Term (If not filled in, the program will auto calculate)

If you have a core integration, the option to select the loan as a loan with your institution will not exist;

please enter the loan regardless.

17


919 .851 .7474

866 .603 .7029

[email protected]

d. Click Save and Continue

Enter Assets and Liabilities for John Doe (Your Initials)

a. On the Personal Assets page, enter the information from the Assets section of the Personal Financial

Statement page. (See below)

b. Click Save and Continue.

c. On the Personal Liabilties page, enter the information from the Liabilities section of the Personal

Financial Statement. (See below)

d. The information from the Recurring Debt page will automatically flow to the Liabilities page.

e. Confirm the Data.

f. Click Save and Finish on the Personal Liabilities page.

You have now successfully added financial data for the business, real estate, and person. This completes the

entry of financial data which allows us to move to the next step of collateral.

18


919 .851 .7474

866 .603 .7029

[email protected]

Step 3: Add Collateral

In the Collateral section, you are going to tie existing loans to existing collateral. We are only concerned with

the loans with your Institution and the collateral securing those loans. (You identified the Loans with My

Institution on the Business Loans and Recurring Debt pages)

From the navigation product menu at the top, go Add/Edit Collateral.

19


919 .851 .7474

866 .603 .7029

[email protected]

Add Collateral

a. On the Collateral page, scroll down to the Additional Information section.

b. On the Additional Information section, select XYZ Office Building (Your Initials) in the Existing

Property dropdown.

Add Liens Against the Selected Collateral

a. Select 555588 (Your Initials) in the Loan Number drop-down menu.

Only loans listed as loans with your institution will appear here. If you have a core integration, you will

not see your portfolio loans here.

b. Click Save.

Note: The combined Loan to Value is now calculated and included in the analysis .

20


919 .851 .7474

866 .603 .7029

[email protected]

21


919 .851 .7474

866 .603 .7029

[email protected]

Generate Standard Report

1. Click Add/Edit in the navigation.

2. Click Financial Data in the drop-down.

3. From the Financial Data Summary Page, click Run a Standard Report.

4. From Standard Report Page, click Create A New Report.

5. On Standard Report Set-Up Page,

Report Name: Enter XYZ Holdings (Your Initials) Report

Template to Apply: Select Sageworks Default Template Report

Select Portfolio Loans: Select All

Select Financial Periods: Select All

6. Click Save and Continue.

7. On a Standard Report Page,

Calculate Debt Service based on: Loan Information/Itemized Debt

CONGRATULATIONS! YOU HAVE RUN YOUR FIRST REPORT!

22


919 .851 .7474

866 .603 .7029

[email protected]

Check Your Work

In the Standard Report page, scroll down to the the Key Metrics portion.

You can find the following ratios to ensure if they match the expected values.

If the results don’t match, you can review the case study to make sure the inputs are correct.

People

Current: Debt Service Coverage Ratio: 3.22

Proposed: Debt Service Coverage Ratio: 3.22

The selection for Personal Cash Flow should be Gross Personal Income before Debt Service.

23


919 .851 .7474

866 .603 .7029

[email protected]

Business



The selection for Business Cash Flow should be EBITDA.

Real Estate



Global



Each section in the Standard Report provides valuable financial information about the person, business, real

estate, and/or group being evaluated. You can take a few moments to review each section to learn how you can

integrate this into your process.

Y O U H A V E N O W F I N I S H E D T H E C A S E S T U D Y !

You successfully created a group, entered in financial data and collateral, and generated a report. These various

functions are some of the core features of the program.

sageworks real estate holding company case study · sageworks real estate holding company case...

Documents