Simulation of Dealer Commissions with Matlab

Posted on December 22, 2013 by admin

As demonstrated in an earlier article dealer or sales commissions can easily be calculated with the .NET Calculation Engine finaquant® calcs.

This article demonstrates how changes in commission amounts can be simulated by varying selected table-valued inputs. In example presented here, the math software Matlab is connnected with the Calculation Engine implemented with finaquant® calcs in order to:

  1. Control the variation of selected inputs; Sales and Commission Scales.
  2. Reporting: Visualize the resultant total commissions with plot diagrams.

Simulation of Dealer Commissions

Simulation Scenario

  • The sales amounts in the sales table are increased or decreased in proportion for all products. For example, if the Sales Multiplier is 1.2, all sales amounts for all products are multiplied with 1.2
  • The commission scale (lower limit) amounts of the scale table are increased or decreased in proportion for all commission contracts.

Simulation Scenario

Configuration of the Calculation Network

C#/.NET code below shows how the Calculation Network is configured for the simulation with two variable table-valued inputs: Sales and Commission Scales. Other inputs of the Calculation Nodes are fetched from the external database as before. In Simulation Mode, not input or output table is stored in the generic database.

You may find all the C# code examples presented here in the Visual Studio project file FinaquantCalcsStarter (Demo Functions for Getting Started) beginning from release 1.02 (see related downloads at finaquant® calcs).

Configure Calculation Network for Commission Simulation
// Return Calc. Network for Commission Calculation and Simulation
private static Network ConfigNetwork_SimpleCommission(bool SimulationMode = false)
{
// MS SQL database connection
string ProjectPath = Path.GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
 
String ExtConnStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename="
				+ ProjectPath
				+ @"\Databases\externaldb.mdf;Integrated Security=True";
 
DemoFunctions.ConnStrExt = ExtConnStr;
DemoFunctions.DBproviderExt = "System.Data.SqlClient";
 
// Call helper method to generate & store test tables
if (!SimulationMode) WriteTablesToExternalDatabase(ConnStrExt, DBproviderExt);
 
//******************************************************************
// INITIATE CALCULATION NETWORK
//******************************************************************
string NetworkName = "ComNW";
Network nw = new Network(NetworkName);
 
// Set DataStore to Network for connection to generic database finaquantdb
// Update database connection string and provider here if you want to use another database
 
// MS SQL database connection for generic database where
// multiple instances of input & output tables are stored
string connStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename="
				+ ProjectPath
				+ @"\Databases\finaquantdb.mdf;Integrated Security=True";
 
string DBprovider = "System.Data.SqlClient";
 
// initiate DataStore object
DataStore dstore = new DataStore(DBprovider);
 
// Assign a DataStore to Network for connection to Generic Database
nw.DataStore_Network = dstore;
nw.ConnectionString = connStr;
 
// Database table for storing log table instances
if (! SimulationMode) nw.Set_OutputSink_LogTable("comnw_log");
 
//******************************************************************
// CREATE 1. CALCULATION NODE
//******************************************************************
#region "Calculation Node-1"
 
// Calculation Node with 2 input (sales, product_pool) and 1 output (aggregated_sales) tables
Node node1 = new Node(NodeFunc: NodeFunc_CalcAggregatedSalesPerPool, InputCount: 2, OutputCount: 1,
	NodeName: "CalcAggregatedSales");
 
// add input sources
if (SimulationMode)
{
	node1.Add_InputSource(); // variable input table
	node1.Add_InputSource(TableName: "product_pool", NodeName: "CalcAggregatedSales");
}
else
{
	node1.Add_InputSource(TableName: "sales", NodeName: "CalcAggregatedSales");         // 1. table
	node1.Add_InputSource(TableName: "product_pool", NodeName: "CalcAggregatedSales");  // 2. table
}
 
// register custom functions to event in order fetch data from external data stores
// ... and store input table instances in generic database finaquantdb
if (!SimulationMode)
{
	node1.BeforeReadingInputs += ReadSalesTable_EventFunc;
	node1.BeforeReadingInputs += ReadProductPoolTable_EventFunc;
}
 
// input validation
node1.Set_AllInputs_IfNullPermitted(false);     // null-valued input tables are not permitted
 
// validation for 1. input: Sales Table
node1.Set_Input_ValidationParameters(InputIndex: 0, IfNullPermitted: false, MinColumnCount: 3, MinRowCount: 1,
	ContainsFields: TextVector.CreateVectorWithElements("dealer", "date", "sales"));
 
// validation for 2. input: Pool Table
node1.Set_Input_ValidationParameters(InputIndex: 1, IfNullPermitted: false, MinColumnCount: 2, MinRowCount: 1,
	ContainsFields: TextVector.CreateVectorWithElements("pool_id", "dealer"));
 
// custom validation function for all inputs
// checks if a pool_id is defined for each dealer in sales table
node1.AllInputs_ValidationFunc = CheckSalesAndPoolTables;
 
// store output table instance in generic database
if (!SimulationMode) node1.Add_OutputSink("aggregated_sales");
 
// store log table instance
if (!SimulationMode) node1.Set_OutputSink_LogTable("node1_log");
 
// get execution timestamps from log table
node1.IfGetTimeStampsFromLogTable = false;
 
// Add Node to Network
nw.AddNode(node1, UseNetworkDataStore: ! SimulationMode, AssignStdNodeName: false);
 
#endregion "Calculation Node-1"
 
//******************************************************************
// CREATE 2. CALCULATION NODE
//******************************************************************
#region "Calculation Node-2"
 
// Calculation Node with 3 input (aggregated_sales, scale_pool, commission_scales) 
// and 2 output (commissions_per_pool, commissions_per_dealer) tables
Node node2 = new Node(NodeFunc: NodeFunc_CalcCommissionsPerAgent, InputCount: 3, OutputCount: 2,
	NodeName: "CalcCommissions");
 
// add input sources
node2.Add_InputSource(FeederNode: nw[0], OutputIndex: 0);         // direct output from 1. node
 
if (SimulationMode)
{
	node2.Add_InputSource(TableName: "scale_pool", NodeName: "CalcCommissions"); 
	node2.Add_InputSource();	// variable input table
}
else
{
	node2.Add_InputSource(TableName: "scale_pool", NodeName: "CalcCommissions");  
	node2.Add_InputSource(TableName: "commission_scales", NodeName: "CalcCommissions"); 
}
 
// register custom functions to event in order fetch data from external data stores
// ... and store input table instances in generic database finaquantdb
if (!SimulationMode)
{
	node2.BeforeReadingInputs += ReadScalePoolTable_EventFunc;
	node2.BeforeReadingInputs += ReadComScaleTable_EventFunc;
}
 
// input validation
node2.Set_AllInputs_IfNullPermitted(false);     // null-valued input tables are not permitted
 
// validation for 1. input: AggregatedSalesTable
node2.Set_Input_ValidationParameters(InputIndex: 0, IfNullPermitted: false, MinColumnCount: 5, MinRowCount: 1,
	ContainsFields: TextVector.CreateVectorWithElements("dealer", "pool_id", "year", "quarter", "sales"));
 
// validation for 2. input: Pool Table
node2.Set_Input_ValidationParameters(InputIndex: 1, IfNullPermitted: false, MinColumnCount: 3, MinRowCount: 1,
	ContainsFields: TextVector.CreateVectorWithElements("scale_logic", "pool_id", "scale_id"));
 
// validation for 3. input: Com. Scale Table
node2.Set_Input_ValidationParameters(InputIndex: 2, IfNullPermitted: false, MinColumnCount: 3, MinRowCount: 1,
	ContainsFields: TextVector.CreateVectorWithElements("commission_rate", "lower_limit", "scale_id"));
 
// custom validation function for all inputs
// checks if a pool_id is defined for each dealer in sales table
node2.AllInputs_ValidationFunc = CheckAggrSalesAndScaleTables;
 
// store output table instance in generic database
if (! SimulationMode)
{
	node2.Add_OutputSink("commissions_per_pool");
	node2.Add_OutputSink("commissions_per_dealer");
}
 
// store log table instance
if (!SimulationMode) node2.Set_OutputSink_LogTable("node2_log");
 
// get execution timestamps from log table
if (! SimulationMode) node2.IfGetTimeStampsFromLogTable = false;
 
// Add Node to Network
nw.AddNode(node2, UseNetworkDataStore: !SimulationMode, AssignStdNodeName: false);
 
#endregion "Calculation Node-2"
 
// return Network
return nw;
}

Calculating Total Commissions

C# method for calculating Total Commissions for given pair of Sales and Scale multipliers. The output table Commissions per Dealer is aggregated to obtain total commissions for all dealers.

Calculate Total Commissions
// Return total commissions for given pair of Sales and Scale multipliers
private static void SimNetwork_GetTotalCommissions(Network nw, TableRow CalcInstance, 
	MatrixTable SalesTbl, MatrixTable ComScaleTbl,
	double SalesMultiplier, double ScaleMultiplier, out double TotalCommissions)
{
	// obtain adjusted input tables
	MatrixTable NewSalesTbl = SalesTbl * SalesMultiplier;
 
	MatrixTable NewComScaleTbl =
		MatrixTable.MultiplySelectedKeyFigureByScalar(ComScaleTbl, ScaleMultiplier,
		"lower_limit", "lower_limit");
 
	// explicit assignment of input tables
	nw[0].SetInput(0, NewSalesTbl);        // 1. input of 1. node
	nw[1].SetInput(2, NewComScaleTbl);     // 3. input of 2. node
 
	// execute calculation network
	MatrixTable LogTable;
	nw.ExecuteNetwork(CalcInstance, out LogTable);
 
	// return total commissions
	MatrixTable ComTbl = nw[1].GetOutput(1);
	ComTbl = ComTbl.ExcludeColumn("dealer");
	ComTbl = MatrixTable.AggregateAllKeyFigures(ComTbl, null);
	TotalCommissions = (double) ComTbl.GetFieldValue("commission", 0);
}

Simulation Function called by Matlab

Simulation method that receives two equal-sized NxM input matrices, SalesMult (sales multipliers) and ScaleMult (scale multipliers) from Matlab, and returns NxM output matrix TotalCom with total commissions for each multiplier value pair in input matrices.

Get Total Commissions for Every Multiplier Pair
// Method called from Matlab: Execute Network for all input values, and return a matrix of results.
public static double[,] SimNetwork_GetTotalCommissions(double[,] SalesMult, double[,] ScaleMult)
{
	try
	{
		KeyMatrix M = KeyMatrix.ArrayToMatrix(SalesMult);
		KeyMatrix N = KeyMatrix.ArrayToMatrix(ScaleMult);
 
		KeyMatrix R = SimNetwork_GetTotalCommissions(M, N);
 
		return R.toArray;
	}
	catch (Exception ex)
	{
		throw new Exception("SimNetwork_GetTotalCommissions: " + ex.Message);
	}
}
 
// Execute Network for all input values, and return a matrix of results.
public static KeyMatrix SimNetwork_GetTotalCommissions(KeyMatrix SalesMult, KeyMatrix ScaleMult)
{
	// check inputs
	if (SalesMult == null || SalesMult.IsEmpty)
		throw new Exception("SimNetwork_GetTotalCommissions: Null-valued or empty input matrix SalesMult");
	if (ScaleMult == null || ScaleMult.IsEmpty)
		throw new Exception("SimNetwork_GetTotalCommissions: Null-valued or empty input matrix ScaleMult");
 
	if (SalesMult.nRows != ScaleMult.nRows || SalesMult.nCols != ScaleMult.nCols)
		throw new Exception("SimNetwork_GetTotalCommissions: Input matrices SalesMult and ScaleMult must have identical row and column sizes!");
 
	//******************************************************************
	// DEFINE META-DATA
	//******************************************************************
	MetaData md;
	MatrixTable SalesTbl, ComScaleTbl, ScalePoolTbl, PrdPoolTbl;
 
	Create_Sales_And_Commission_Tables(out md, out SalesTbl, out ComScaleTbl, out ScalePoolTbl, out PrdPoolTbl);
 
	// date-related  additional attributes
	MetaData.AddNewField(md, "year", FieldType.IntegerAttribute);
	MetaData.AddNewField(md, "quarter", FieldType.IntegerAttribute);
	MetaData.AddNewField(md, "month", FieldType.IntegerAttribute);
 
	// additional key figures
	MetaData.AddNewField(md, "interval_rate", FieldType.KeyFigure);
	MetaData.AddNewField(md, "effective_rate", FieldType.KeyFigure);
	MetaData.AddNewField(md, "commission", FieldType.KeyFigure);
 
	//******************************************************************
	// CONFIGURE NETWORK
	//******************************************************************
	Network nw = ConfigNetwork_SimpleCommission(SimulationMode: true);
 
	//******************************************************************
	// EXECUTE NETWORK
	//******************************************************************
 
	// Create Calculation Instance
	var RowFields = TextVector.CreateVectorWithElements("quarter", "year");
	var InstanceTblFields = TableFields.CreateTableFields(RowFields, md);
	TableRow CalcInstance = TableRow.CreateTableRowWithElements(InstanceTblFields, 2, 2012);
 
	// explicit assignment of input tables
	nw[0].SetInput(0, SalesTbl);        // 1. input of 1. node
 
	// TEST: view input
	// MatrixTable.View_MatrixTable(nw[0].GetInput(0), "nw[0].GetInput(0)");
 
	nw[1].SetInput(2, ComScaleTbl);     // 3. input of 2. node
 
	// TEST: view input
	// MatrixTable.View_MatrixTable(nw[1].GetInput(2), "nw[1].GetInput(2)");
 
	// create Nx1 output matrix for total commissions
	KeyMatrix TotalCom = KeyMatrix.CreateConstantMatrix(SalesMult.nRows, SalesMult.nCols);
	double SalesMultiplier, ScaleMultiplier, TotalCommission;
 
	for (int i = 0; i < SalesMult.nRows; i++)
	{
		for (int j = 0; j < SalesMult.nCols; j++)
		{
			SalesMultiplier = SalesMult[i, j];
			ScaleMultiplier = ScaleMult[i, 1];
 
			SimNetwork_GetTotalCommissions(nw, CalcInstance, SalesTbl, ComScaleTbl,
				SalesMultiplier, ScaleMultiplier, out TotalCommission);
 
			TotalCom[i, j] = TotalCommission;
		}
	}
	return TotalCom;
}

Calling Simulation Function directly within C#/.NET

The simulation method above that expects matrix inputs can also be called directly within C#/.NET; we need an additional software like Matlab especially for data visualization (plots, graphs etc.) and reporting.

Call Simulation Function in .NET
// Simulate Calculation Network to obtain Sales Commissions
public static void SimNetwork_GetTotalCommissions()
{
	// get total commissions for multiple Sales and Scale multipliers
	KeyVector SalesMultipliers = KeyVector.CreateSequenceVector(StartValue: 0.8,
		Interval: 0.1, nLength: 5);
 
	KeyVector ScaleMultipliers = KeyVector.CreateSequenceVector(StartValue: 0.9,
		Interval: 0.05, nLength: 5);
 
	// Get multiplier matrix with all possible combinations of sales & scales multipliers
	KeyMatrix SalesMult =
		KeyMatrix.CreateConstantMatrix(nRows: ScaleMultipliers.nLength, nCols: SalesMultipliers.nLength);
 
	KeyMatrix ScaleMult =
		KeyMatrix.CreateConstantMatrix(nRows: ScaleMultipliers.nLength, nCols: SalesMultipliers.nLength);
 
	for (int i = 0; i < SalesMult.nRows; i++)
	{
		for (int j = 0; j < SalesMult.nCols; j++)
		{
			SalesMult[i,j] = SalesMultipliers[j];
			ScaleMult[i,j] = ScaleMultipliers[i];
		}
	}
	KeyMatrix TotalCom =
		SimNetwork_GetTotalCommissions(SalesMult, ScaleMult);
 
	// TEST
	Console.WriteLine("Sales Multiplier Matrix: \n" + SalesMult);
	Console.WriteLine("Commission Scales Multiplier Matrix: \n" + ScaleMult);
	Console.WriteLine("Total Commission Matrix: \n" + TotalCom);
	Console.ReadKey();
}

Calling Simulation Function from Matlab; Displaying Results

Following matlab script illustrates how the simulation function in C#/.NET is called from matlab to initiate simulation and display the results in graphs.

See Calling .NET Methods in Matlab for general information about the integration of Matlab and .NET.

Matlab Script for Simulation
% Simulation of Sales Commissions
% see: http://forum.finaquant.com/viewtopic.php?f=4&t=1224
 
% dll assembly (replace this path with your own path)
assemblypath = 'C:\Users\finaquant\Documents\Visual Studio 2012\Projects\FinaquantCalcs\FinaquantCalcsStarter\bin\Release\FinaquantCalcsStarter.exe';
 
% load .NET matrix function library
NET.addAssembly(assemblypath);
 
% Generate Sales & Commission Scale Multipliers
[SalesMult, ScaleMult] = meshgrid(0.8:0.05:1.2, 0.7:0.05:1.3)
 
% get total commissions
SalesMult_NET = NET.convertArray(SalesMult,'System.Double');
ScaleMult_NET = NET.convertArray(ScaleMult,'System.Double');
 
TotalCom_NET = FinaquantCalcsStarter.DemoFunctions.SimNetwork_GetTotalCommissions(SalesMult_NET,ScaleMult_NET);
TotalCom = TotalCom_NET.double
 
% visualize simulation results in graphs
 
% 3-dim plot: Total Commissions vs Sales & Scales multipliers
mesh(SalesMult, ScaleMult, TotalCom);
figure
 
% 2-dim plot: Total Commissions vs Scale multiplier 
% where Sales multiplier = 1.0 (5. column of matrices)
plot(ScaleMult(:,5), TotalCom(:,5));
figure
 
% Contour plot: Total Commissions vs Sales & Scales multipliers
[C,h] = contour(SalesMult, ScaleMult, TotalCom);
text_handle = clabel(C,h);
 
Simulation Results

Conclusions

Simulations with Calculation Engine

  • This demonstration shows generally how the Calculation Engine finaquant® calcs implementing any analytical relationship can be used for simulations.
  • Note that this simulation example doesn’t make much sense from the business perspective. However, it can be used as a technical prototype for any kind of simulations.
  • The Calculation Engine could represent any analytical relationship, or mechanism that is based on table data, like Dealer or Sales Commissions, Performance Fees, Financial Plans; in fact Estimations and Predictions about anything in business and science.
  • Ability to simulate means also ability to optimize. Optimization functions of software like Matlab could be used for this purpose.
Digiprove sealCopyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu
Share
This entry was posted in Calculation engine, Fee and commission calculations. Bookmark the permalink.

Leave a Reply