Versions Compared

Old Version 5

changes.mady.by.user Fiona Hardy

Saved on

compared with

New Version 6

changes.mady.by.user Fiona Hardy

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note

The tenancy income & forecast report is a reflection of the current data on the system as it stands on the day the report was run and should be used as an indication of projected income

Due to the changing nature of lettings and renewals , it is not possible to provide an accurate report of business income - therefore the report should be used as a guide only - it should not be used as an accurate report of business income

For example: if a report is run in September for the next 6 months, renewals for the later months in the report would not yet be completed, therefore would not show in the report

Launch report & main screen

1. Launch report

From Reports on main menu:

  • Click MI Analysis and select Tenancy Income Report

    The screen in step 2 will be shown

2. Tenancy Income Report main screen

The Tenancy Income Report main screen is displayed - it allows two types of tenancy report to be run:

  • Fees & Commission - this report is covered heregives an indication of fees/commission earned and can be broken down by negotiator/office - see guide: Fees & commission report (Tenancy Income Report)

  • Tenancy Income & Forecast - this report is covered in this guide, see next section onwards

...

1. Set report type & output options

Report Type

  • Select Tenancy Income & Forecast

    Image RemovedImage Added

Output Options

  • Include renewal & alteration breakdowns
    When ticked, includes a row per renewal/alteration that fall within the reporting period
    e.g. if there have been 5 renewals on the tenancy in the reporting period, 5 rows will be displayed for each renewal (in addition to any other individual allocations being shown - i.e. negotiator and/or office alterations)
    When unticked, all renewal/alterations that fall within the reporting period will be displayed in one row

  • Include landlord fees with no associated tenancy (ticked by default)
    When ticked, will include detail of landlord fees that are not associated to a specific tenancy
    Being able to apply landlord fees to a property is a configuration option, outlined in more detail here:
    Add landlord fees to a property - configuration option

  • Show individual negotiator commission allocations
    When ticked, one row per negotiator with a fee split will be included in the report
    e.g. if there are 3 negotiator fee splits, 3 rows will be displayed for each allocation (in addition to any other individual allocations being shown - i.e. office and/or renewals & alterations)
    When unticked, one row per tenancy will be shown in the report
    Being able to set negotiator fee splits and negotiator fixed commission is a configuration option - click here for more information: Tenancy commission allocation (or fee splits) - configuration option

  • Show individual office commission allocations
    When ticked, one row per office with a fee split will be included in the report
    e.g. if there are 2 office fee splits, 2 rows will be displayed for each allocation (in addition to any other individual allocations being shown - i.e. negotiator and/or renewals & alterations)
    When unticked, one row per office will be shown in the report
    Being able to set office fee splits is a configuration option - click here for more information: Tenancy commission allocation (or fee splits) - configuration option

2. Choose what to include in the report

These options allow you to narrow down what is returned in the report to suit your requirements

Tenancy Start

  • Specify a date or date range to limit tenancies returned in the report by the tenancy start date - or click Select for predefined options of Last month and This month
    Or, to include all tenancies regardless of start/end dates, leave date fields blank

    Image RemovedImage Added

Income Period

  • Defaults to the current year - meaning that the forecast will cover the current year, although this can be changed - click Select for predefined options of Last/This month and Last/This/Next year
    Or leave date fields blank to include all tenancies regardless of the income period)

    Image RemovedImage Added

Status

  • Defaults to Tenancy Current & Arranging Tenancy - meaning that only current & arranging tenancies will be included in the report - although this can be changed - click Select one or more status and select/deselect required option(s)

Property Office

  • To narrow the report down to properties from one or a selection of offices, click Select one or more office and select required offices
    Or, to include properties for all offices, leave blank

Neg. Allocation
Field available when Show individual negotiator commission allocations is selected in Output Options

  • To narrow the report down to one or a selection of negotiators/managers, click
    Select negotiator/manager and select required negotiators/managers
    Or, to include all negotiators/managers, leave blank

Office Allocation
Field available when Show individual office commission allocations is selected in Output Options

  • To narrow the report down to one or a selection of negotiators/managers, click
    Select negotiator/manager and select required negotiators/managers
    Or, to include all negotiators/managers, leave blank

Renewal & Alteration
Field available when Include renewal & alteration breakdowns is selected in Output Options

  • Specify a date or date range to limit tenancies returned in the report by the renewal/alteration date
    Or click Select for predefined options of Last month and This month
    Or, to include all tenancies regardless of renewal/alteration dates, leave date fields blank

3. Run report

To run the Tenancy Income & Forecast report:

  • Select report type, output options and criteria (as outlined in steps 1 & 2 above) and click Report

  • Depending on the amount of data being returned, a prompt is shown detailing the current activity

    Image RemovedImage Added

  • The Tenancy Income & Forecast report is displayed in Excel - content explained in the next section
    When Output Options to show individual negotiator and/or office allocations have been selected, this results in more than one row being shown per tenancy to detail the individual allocations

Image Modified

Tenancy Income & Forecast report output (in Excel)

Tip

The Tenancy Income & Forecast report automatically launches in an Excel spreadsheet containing two worksheets:

  • the first worksheet includes a PivotTable which summarises the report data

  • the second worksheet holds the report data behind the PivotTable

This :

1. Criteria used for example report

The example report shown in this section was run with the Output Options and criteria set as shown

below

Image Modified

2. PivotTable data

The PivotTable is shown on the first worksheet of the Excel spreadsheet - it uses a default format for how it presents its data - to change how the table is presented, see section 2a below

  • The report is broken down into two sections, Invoiced data and Pipeline data

  • Both the Invoiced and Pipeline categories contain sections titled:

    • Initial Tenancy (i.e. a tenancy that has not yet been renewed)

    • Renewal

  • Within each section is a breakdown of fees that have come in or are due to come in, which could include:

    • Add. Agent Fee - value of additional agent fee
      The additional agent fee is calculated from specific nominal fee(s) - the nominal fee type(s) used for additional agent fees are dependent on business requirements (and are set in configuration)

    • Letting Fee - value of letting fees as set on the tenancy

    • Management Fee - value of letting fees as set on the tenancy

    • Other Fee - value of other fees
      The other fee is calculated from specific nominal fee(s) - the nominal fee type(s) used for other fees are dependent on business requirements (and are set in configuration)

Image Modified

2a. Using the PivotTable

The PivotTable used in this report is standard Excel functionality - if you have used them before, the same principles apply

  • To change what the PivotTable contains and how it is displayed, click into a cell within the PivotTable to view the PivotTable Fields options to the right

    • To add/remove fields in the PivotTable tick/untick the field name in the list
      e.g. tick Tenancy status to add a breakdown by status to the table

    • To change how the PivotTable is presented, click and drag fields within Rows to change the priorities of the table and/or move a row to the Filters section
      e.g. in Rows move Type to the top and/or add FeeCategory to the Filters section

  • Hover over any figure in the PivotTable for a breakdown of where the figure is coming from
    Example

...

Manager view for 1 month

Manager view for 12 months

Company view for 1 month

Manager view for 12 months

  • shown below

  • Double-click a figure to see a breakdown of it in a new worksheet

Image Added
Tip

When using the PivotTable Fields options - if you make a change to the table that you don’t like, click Undo (or Control & Z)

3. Income Report data tab

The Income Report worksheet provides the PivotTable with its data

Standard Excel functionality to sort & filter the data can be used (usually via the Home ribbon, click Sort & Filter and select required option)

Image Added

The columns in the report include:

  • Property address & TenCode - the address of the property & the unique 9-digit tenancy reference

  • TenStart - the initial tenancy start date

  • Office - the property Main Office

  • Type -displays Initial Tenancy (i.e. a tenancy that has not yet been renewed), Renewal, Alteration or Periodic as appropriate

  • FeeCategory

    • Add. Agent Fee - value of additional agent fee
      The additional agent fee is calculated from specific nominal fee(s) - the nominal fee type(s) used for additional agent fees are dependent on business requirements (and are set in configuration)

    • Letting Fee - value of letting fees as set on the tenancy

    • Management Fee - value of letting fees as set on the tenancy

    • Other Fee - value of other fees
      The other fee is calculated from specific nominal fee(s) - the nominal fee type(s) used for other fees are dependent on business requirements (and are set in configuration)

  • Income Type - indicates whether the fee is Invoiced or Pipeline

  • Main landlord - name of main landlord on tenancy

  • Main tenant name - name of main tenant on tenancy

  • Tenancy status - current status of tenancy, e.g. Tenancy Current

  • Negotiator name - the tenancy Key Contact

  • Date - the date range the figures fall into - what is shown depends on what dates were selected when building the report criteria

Example reports

Forecast for next month - for one office, including renewals

  • For a company report, remove the Property Office criteria before running the report

Image Added

Forecast for next year - for one office, including renewals

  • For a company report, remove the Property Office criteria before running the report

Image Added

Office forecast for tenancies starting this month

  • For a company report, remove the Property Office criteria before running the report

Image Added
Filter by label (Content by label)
showLabelsfalse
max8
sortmodified
showSpacefalse
reversetrue
cqllabel = "reporting-accounts"

...