The market simulator can use simple numeric values in the Revenues and Costs dialog for calculating cost and price. However, formulas and user-defined variables can also be used.
The syntax for formulas is similar to syntax in Microsoft Excel, where formulas begin with an equals ('=') sign. Similar to Excel, functions can be used and be embedded within another to create the desired result.
The following operators can be used in formulas:
Mathematical: '+' (addition), '-' (subtraction), '*' (multiplication), '/' (division), '^' (power), '%' (modulo)
Comparison: '=' (boolean equals), '<>' (boolean not-equal), '>' (greater than), '<' (less than), '>=' (greater than or equal), '<=' (less than or equal)
Examples of formulas:
=if(totalvolume*share() > volumetier1, 0.85, 0.90)
=if(totalvolume*share() > volumetier1, costtier1, if(totalvolume*share() > volumetier2, costtier2, costtier3))
In these examples, we use user-defined variables that have been created in advance: 'totalvolume', 'volumetier1', 'volumetier2', 'costtier1', 'costtier2', and 'costtier3'. In the first example, the if() function is checking to see if the product's share multiplied by totalvolume is greater than volumetier1. If so, then use the value 0.85. Otherwise use the value 0.90. The second example shows how a 3 tier system can be created as well as how user-defined variables can be used in place of specific values.
User-defined variables can be defined using the User-defined Variables within the Revenues and Costs dialog. A label for the variable goes in the Label column, and the corresponding value should be added in the Value column. User-defined variables can include formulas and reference other user-defined variables. However, a user-defined variable must evaluate to a single value during simulation, or an error will occur. Examples:
Label Value
totalvolume 1000000
volumetier1 100000
volumetier2 =volumetier1 * 0.75
costtier1 0.85
costtier2 0.90
costtier3 0.95
The following functions are available for use within formulas:
Mathematical Functions
Abs(x)
Returns the absolute value of the specified value. Usage:
=abs(-3.141)
Average(x,y,z)
Returns the average of the specified values. This function can be used with any number of values, but requires at least one. Usage:
=average(1, 2, 10)
E()
Returns the value of the numeric constant e. Usage:
=e()
Exp(x)
Returns e raised to the specified power (i.e. e^x). Usage:
=exp(3.141)
Log(x,base)
Returns the logarithm of a specified value in a specified base. Usage:
=log(1000, 10)
Ln(x)
Returns the natural log of a specified value. Usage:
=ln(3.141)
Max(x,y,z)
Returns the maximum of the specified values. This function can be used with any number of values, but requires at least two. Usage:
=max(10, 100, 1000)
Min(x,y,z)
Returns the minimum of the specified values. This function can be used with any number of values, but requires at least two. Usage:
=min(10, 100, 1000)
Pi()
Returns the value of the numeric constant pi. Usage:
=pi()
Product(x,y,z)
Returns the product (multiplication) of the specified values. This function can be used with any number of values, but requires at least two. Usage:
=product(5, 10, 15)
Sum(x,y,z)
Returns the sum (addition) of the specified values. This function can be used with any number of values, but requires at least two. Usage:
=sum(5, 10, 15)
Sqrt(x)
Returns the square root of a specified value. Usage:
=sqrt(3.141)
Logical Functions
And(x,y,z)
Returns true if all values evaluate to true; false otherwise. This function can be used with any number of values, but requires at least two. Usage:
=and(x=2,y=3,z=4)
Or(x,y,z)
Returns true if any values evaluate to true; false otherwise. This function can be used with any number of values, but requires at least two. Usage:
=and(x=2,y=3,z=4)
Not(x)
Returns the opposite of the specified boolean value. Usage:
=not(x)
If(x,y,z)
If x is true, then y is returned; otherwise z is returned. Usage:
=if(x=2, 3, 4)
Reference Functions
Share()
Returns the share for the product at simulation time. Note: This function is provided for informational purposes, and should not be used as a scaling factor in determining price or cost as those values are already multiplied by share in the simulation results. Usage:
=share()
NetShare()
Returns the net share for the set of products that the current product is included in at simulation time. If the produt is not part of a netted share set, the individual share is returned. Note: This function is provided for informational purposes, and should not be used as a scaling factor in determining price or cost as those values are already multiplied by share in the simulation results. Usage:
=netshare()
RespCount()
Returns the number of respondents in the utility set. Note: This function is provided for informational purposes, and should not be used as a scaling factor in determining price or cost as those values are already multiplied by share in the simulation results. Usage:
=respcount()
MarketSize()
Returns the market size as specified in the Revenue and Costs settings. Note: This function is provided for informational purposes, and should not be used as a scaling factor in determining price or cost as those values are already multiplied by share in the simulation results. Usage:
=marketsize()