TFPP Performance vs Plan

Formula

=ANT.ACT.Live.TFPP_Performance_Vs_Plan()

Description

This formula allows you to see your performance against plan for the current underwriting year. This can be used to see TFPP total, or you can filter on the master_carrier_grouping to see performance split by IG, Syndicate or TPC.

Warning

This dataset is based on the daily Prequel snapshots and is considered the most up-to-date. The data underlying this dataset is not validated between days and can be subject to changes depending on the data being entered into Prequel. As such, this dataset should not be used for external reporting because it is not signed off / validated.

Daily movements in this dataset will not be explained by the analytics team.

Tip

The best practise for once you have used this formula is to create a table from the results and use this table within your analysis. This ensures that your data changes only when you want it to and you are not dependent on the table columns being in the same order each exercise.

1. Use the formula to get the data you need

2. Select all the data that the formula returns (including headings)

3. Go to Data >> Get Data >> From Other Sources >> From Table/Range

4. Right click on the 'Array' name and rename it to something sensible

5. Check to see if the first row has been promoted to headers, this is usually done automatically but
if not  on the "Home" tab you can select "Use the first row as headers".

6. If you are seeing errors in the layer_id or program_id columns then you need to change the data type.
To do this go to the Applied Steps on the right hand side, choose 'Changed Type' and change
{'layer_id', Int.64.Type} to {'layer_id', type text} and {'program_id', Int.64.Type} to {'program_id', type text}.

7. Click Close & Load.

This will create a table in your worksheet. If the data changes you will need to refresh the table by going to Data >> Refresh All or Data >> Queries & Connections >> right click on your table and refresh.

Examples

For a full list of available parameters see Inputs.

This formula will return all plans for the current year.
```
=ANT.ACT.Live.TFPP_Performance_vs_plan()
```
The formula below will return TFP plans for the current year.
```
A1 = 'TFP'
=ANT.ACT.Live.TFPP_Performance_vs_plan(A1)
```

The formula below will return TFP plans for the all years.

A1 = 'TFP', A2 = 'All'
=ANT.ACT.Live.TFPP_Performance_vs_plan(A1, A2)

The formula below will return TFP plans for the years 2025 & 2026.

A1 = 'TFP', A2 = '2025', A3 = '2026'
=ANT.ACT.Live.TFPP_Performance_vs_plan(A1, A2:A3)

The formula below will return All plans for the years 2025 & 2026.

A1 = 'All', A2 = '2025', A3 = '2026'
=ANT.ACT.Live.TFPP_Performance_vs_plan(A1, A2:A3)

Inputs

Parameters default to ALL and current year

PARAMETER	DESCRIPTION
`plan_type`	Values can be TFP Plan or Pine Walk or HGM or Broker (or All) If left blank this returns all plan types TYPE: `str`
`show_years`	The planned year. This can be a single cell, or multiple cells to return more than one year. If left blank this will default to the current year. TYPE: `str`

Outputs

PARAMETER	DESCRIPTION
`snapshot_id`	Alias: Snapshot ID The snapshot id TYPE: `int`
`snapshot_date`	Alias: Snapshot Date The snapshot date TYPE: `date`
`master_carrier_grouping`	Alias: Master Carrier Grouping The capacity provider. This can be the IG, F3123, Consortium, Allianz or Capital Provider. TYPE: `str`
`business_segment`	Alias: Business Segment This is the IG's mapping to business segment (INS / REIN). TYPE: `str`
`mgup_split`	Alias: MGUP Split This is the full list of MGUP master and MGUP light classes combined together separated by a hyphen. TYPE: `str`
`mgup_split_light`	Alias: MGUP Split Light This class definition is used in MGUP Production meetings, similar to Minor Class. It will split, e.g. P,C&E MGUP class into Construction, Non-Renewable, Renewables, Property and D&F. TYPE: `str`
`mgup_split_master`	Alias: MGUP Split Master This class definition is used in MGUP Production meetings, similar to Major Class. TYPE: `str`
`mgup_split_pillar`	Alias: MGUP Split Pillar This pillar definition is used in MGUP Production meetings. The main difference is the how RI classes are mapped from Speciality / Bespoke to Reinsurance and how some Reinsurance classes are mapped back to Specialty / Bespoke. Unless you want to tie up with MGUP pillar definitions, you should not use this pillar. TYPE: `str`
`mgup_reporting`	Alias: MGUP Reporting Class This is the MGUP Master class broken down into the main reporting classes. TYPE: `str`

PARAMETER	DESCRIPTION
`plan_type`	Alias: Plan Type TYPE: `str`
`plan_name`	Alias: Plan Name TYPE: `str`
`plan_grouping`	Alias: Plan Grouping TYPE: `str`
`entity`	Alias: Entity TYPE: `str`
`mga_code`	Alias: MGA Code TYPE: `str`
`is_pinewalk_flag`	Alias: Is Pinewalk Flag TYPE: `str`
`plan_placing_basis`	Alias: Plan Placing Basis The placing basis grouped into Non-Binder, Binder and Pinewalk. Non-binder includes all open market and lineslips. TYPE: `str`
`plan_class`	Alias: Plan Class TYPE: `str`
`pillar`	Alias: Pillar This pillar definition is consistent with the IG definition of pillar and should be used in most scenarios. TYPE: `str`
`major_class`	Alias: Major Class This is consistent with the IG definition of major classes. TYPE: `str`
`minor_class`	Alias: Minor Class This is consistent with the IG definition of minor classes. TYPE: `str`
`digest_class`	Alias: Digest Class TYPE: `str`
`month_from`	Alias: Month From TYPE: `date`
`month_to`	Alias: Month To TYPE: `date`
`year_definition`	Alias: Year Definition TYPE: `str`
`year_from`	Alias: Year From TYPE: `date`
`year_to`	Alias: Year To TYPE: `date`
`binder_year`	Alias: Binder Year TYPE: `str`
`uw_year`	Alias: Underwriting Year TYPE: `str`
`tfpp_bound_premium_usd`	Alias: Bound Premium USD This is the total bound premium in USD. TYPE: `float`
`tfpp_pipeline_premium_usd`	Alias: Pipeline Premium USD This is the amount of premium in the pipeline with either a submission or quoted underwriting status. TYPE: `float`
`tfpp_unrealised_premium_usd`	Alias: Unrealised Premium USD Unrealised premium is a estimate of new business which we might expect to bind in the month coming in from policies we do not yet know about. It is estimated based on the new business premium from the same month last year. It also allows for premium not yet entered and allocated to policies in Prequel. TYPE: `float`
`tfpp_not_in_prequel_premium_usd`	Alias: Not in Prequel Premium USD TYPE: `int`
`tfpp_expected_premium_usd`	Alias: Expected Premium USD This is bound + pipeline + unrealised to equal the expected premium as at the period end. This is the figure which is compared against plan. TYPE: `float`
`tfpp_plan_premium_usd`	Alias: Plan Premium USD This is the planned premium in USD as provided by the FP&A team. Any questions on plan figures should be directed to the FP&A team TYPE: `float`
`tfpp_expected_vs_plan_usd`	Alias: Expected vs Plan Premium USD This is the difference between expected premium and planned premium in USD. TYPE: `float`
`bound_premium_py_m1`	Alias: Bound Premium PY M1 TYPE: `int`
`bound_premium_py_m2`	Alias: Bound Premium PY M2 TYPE: `int`
`bound_premium_py_m3`	Alias: Bound Premium PY M3 TYPE: `int`
`period_id`	Alias: Period Id TYPE: `int`
`year`	Alias: Year The underwriting year TYPE: `int`
`month_id`	Alias: Month Id The month id in numerical form (1, 2, 3, 4, 5, ..) TYPE: `int`
`quarter_id`	Alias: Quarter The quarter id in numerical form (1, 2, 3, 4) TYPE: `int`
`quarter`	Alias: Quarter The quarter in short format (Qtr 1, Qtr 2, Qtr 3, Qtr 4). TYPE: `str`
`month_short`	Alias: Month Short The short month description, e.g. Jan, Feb, Mar etc. TYPE: `str`
`period_desc`	Alias: Period Desc The short period description, e.g. January 2025, February 2025, March 2025 etc TYPE: `str`
`period_short_desc`	Alias: Period Short Description The short period description, e.g. Jan 25, Feb 25, Mar 25 etc TYPE: `str`
`current_month_flag`	Alias: Current Month Flag A flag indicating the current month. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current month in your analytics / report. A value of 1 indicates the current month, 0 otherwise. Note: This flag only is 1 for the current month in the current year only. If you need the current month in previous years you can use the Current Month Last Year Flag or Current Month All Year Flag. TYPE: `int`
`current_quarter_flag`	Alias: Current Quarter Flag A flag indicating the current quarter. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current quarter in your analytics / report. A value of 1 indicates the current quarter, 0 otherwise. Note: This flag only is 1 for the current quarter in the current year only. If you need the current quarter in previous years you can use the Current Quarter Last Year Flag or Current Quarter All Year Flag. TYPE: `int`
`current_year_flag`	Alias: Current Year Flag A flag indicating the current year. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the current year in your analytics / report. A value of 1 indicates the current year, 0 otherwise. TYPE: `int`
`is_this_month_or_before_flag`	Alias: Is This Month or Before Flag A flag indicating that the policy incepted in, or before the current month. This calculates YTD policies incepting up to the current month. This flag is used in the TFP production meetings for YTD. These flags get updated daily with the pipeline. Use this flag to automatically get the YTD data in your analytics / report. A value of 1 indicates data up to and including this month, 0 otherwise. TYPE: `int`
`before_current_month_flag`	Alias: Before Current Month Flag A flag indicating that the policy incepted before the current month. This calculates YTD policies incepting up to the last month. These flags get updated daily with the pipeline. Use this flag to automatically get the YTD data in your analytics / report. A value of 1 indicates data up to but not including this month, 0 otherwise. TYPE: `int`
`last_month_flag`	Alias: Last Month Flag A flag indicating the previous month. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the previous month in your analytics / report. A value of 1 indicates the previous month, 0 otherwise. Note: This flag only is 1 for the previous month in the current year only. If you need the previous month in previous years you can use the Last Month All Year Flag. TYPE: `int`
`last_quarter_flag`	Alias: Last Quarter Flag A flag indicating the last quarter. These flags get updated daily with the pipeline. Use this flag to automatically get the data for the last quarter in your analytics / report. A value of 1 indicates the last quarter, 0 otherwise. Note: This flag only is 1 for the last quarter in the current year only. If you need the last quarter in previous years you can use the Last Quarter All Year Flag. TYPE: `int`
`id`	Alias: ID TYPE: `int`