Introducing What-If Formulae

The What-If Formula helps you to explore various scenarios you want to achieve using one or more formulas on measures, expressions, and fixed values. The What-If formula relies on the goal-seek method to give you suggestions. In other words, if you are unsure about the input value for an expression but know the result you want, the What-If formula can help you get the result based on the suggested inputs. You can also apply the filters to the What-if formula based on dimensions available in that Datasource.

Note

The What-If formulae you create here will be readily available in the What-If analysis for detailed analysis and exploration.

Steps to Create and Setup What-If Formulae

1. To create and set up a What-If formula based on measures and dimensions available in Datasource, click on Analysis What-If Formula . All What-If formula, those created by you or shared with you will be displayed under the tabs My What-If Formula and Shared What-If Formula respectively.
2. To formulate a fresh formula, click on New Formula.
3. Enter the formula Name.
4. Select Datasource or Query, from the respective drop-down list.
   • Datasource Use the existing Datasource to create the formula where you can call all the predefined measures and dimensions.
   • Queries You can use the related existing queries that consist of measures and dimensions.
5. When selecting Queries to configure the What-If formula, also define the required Measures and Dimensions.
6. Click on Save to add the What-If formula to Infoveave.

Note

In order to configure What-If formulae, it is necessary to have the corresponding Datasource or query properly configured. To learn more about writing queries in Infoveave, visit Queries.

Caution

Once the What-If formulae is saved, the Datasource or Query cannot be edited. The What-If formulae Name cannot be changed once saved.

Identify and Define Scenario Elements

Identify and define the key variables that play a role in your What-If scenario. These elements involve the measures and values of your scenario that you want to analyze or manipulate for goal-seeking purposes.

To configure the What-If formula in Infoveave, please follow the steps outlined below

1. To start configuring click on Edit icon on the newly created Formulae.
2. The dialog opens up for you define the Measures, Values, and Expressions for the formula.

Measures

Assign the selected measures with a distinct key for easy reference and identification, to help you formulate the scenario.

To configure the Measure formula element in the What-If feature of Infoveave, please follow the steps outlined below

1. Click on measure icon to add Measure. Select the Key from the drop-down and enter the Name.
   • Key Use alphabets as keys to define each measure. You can configure multiple measures by assigning a unique key to each.
   • Name It is mandatory to provide a name for the measure formula element. This name should be descriptive and help you identify the purpose of the formula element.
   • Measure Select the appropriate measure that you want to use for the formula element. The measure represents the quantitative aspect of the data that the formula will calculate or manipulate.
   • Dimension Choose the relevant dimension(s) associated with the measure formula element. Dimensions provide additional context or information for the measure.
   • Value Type Specify whether the value provided for the formula element should be interpreted as a numerical value (Value) or a percentage (Percentage).
2. Click on Save to save the Measure in formula.

Note

At least one measure and dimension are required to save the formula.

Value

Values are the numerical quantity or percentage that serves as a basis for evaluating different scenarios and assessing the impact of changes within the analysis.

To configure the Value element in the What-If formula in Infoveave, you can follow the steps provided below

1. Click on add value icon to add Value and select key from the dropdown list.
   • Key Use alphabets as keys to define each value formula element. Each key represents a unique element, and you can configure multiple elements by assigning different keys to each.
   • Name It is mandatory to provide a name for the value formula element. The name should be descriptive and help you identify the purpose of the element.
   • Initial Value Specify the initial value for the formula element. This value serves as the starting point for calculations or modifications in the What-If analysis.
   • Value Type Choose whether the provided value should be interpreted as a numerical value (Value) or a percentage (Percentage).
2. Click on Save to save the value in formula.

Define Formula/Expression

Effortlessly configure your scenario formula for analysis through Expressions, leveraging predefined measures and values. Enhance convenience in your analysis by efficiently replicating a measure’s value to a new variable using the Copy of Value functionality.

Expressions

Expressions allows you to define the contributing goal-seeking formulas using the assigned key variables.

To configure the Expression in Infoveave, refer to the following steps

1. To add an Expression click on the expression icon. Select the Key from the drop-down list and enter Expression Name.
   • Key Assign a unique key to each Expression formula element. You can use alphabets as keys, and it is possible to configure multiple elements.
   • Name Provide a name for the value formula element.
   • Expression Type Choose from the following options
     • Standard This expression type supports Excel functions and formulas. It always returns the result as a numeric value. It is suitable for basic calculations using standard Excel functions.
     • JavaScript Select this expression type if you prefer to use JavaScript functions or expressions. It allows for more advanced computational capabilities. The output of the JavaScript function is returned as the result.
     • Standard String This expression type supports Excel functions and expressions, but the output is always treated as a string. It is suitable when the result needs to be in string format, such as for text or statements.
   • Expression Based on the selected expression type, enter the respective formula or expression. This is where you define the logic or calculation for the value formula element.
2. Click on Save to save an expression in formula.

Tip

Expression is a formula that is built up of measures keys. Example- Consider you have defined measures with the key ‘a’, ‘e’, ‘g’, and ‘h’. You can define the formula in the expression type as (a + g) * (e + h).

Example

• Standard Expression =SUM(A1:A5)

• Explanation This Excel formula calculates the sum of values in cells A1 to A5. The result will be a numeric value representing the total sum of those cells.

• JavaScript Expression

let data = [14, 28, 42, 56, 70];
let sum = data.reduce((total, value) => total + value, 0);
let average = sum / data.length;
average;

• Explanation This JavaScript expression calculates the average of values in the array data using JavaScript’s array functions. The output will be a numeric value, which is the average of the values in the array.
• Standard String Expression "Hello " & A1
  • Explanation This formula concatenates the string “Hello ” with the value in cell A1. The result will be a string containing the combined text, regardless of the content in cell A1. For instance, if cell A1 contains “World”, the result will be the string “Hello World”.

Copy of Value

Copy of value feature allows to bring a new variable to formulate the scenario with the same value as that of an existing variable.

Tip

The “Copy of Value” function allows you to conveniently reuse the value of an existing formula element within your What-If formulae. This feature eliminates the need to manually re-enter the value, saving you time and effort in configuring your formulas.

To configure the Copy of Value formula element in Infoveave’s What-If feature, please refer to the following steps

1. To configure the Copy of Value in What-If formula, click on Copy of Value.
   • Key Assign a unique key to the “Copy of Value” formula element. You can use alphabets as keys to distinguish it from other elements.
   • Name Provide a name for the “Copy of Value” formula element. This name should describe the purpose or nature of the element.
   • Value Choose the key from which you need to copy the value. This refers to the existing formula element’s key that you want to replicate or duplicate the value from
2. Click on Save to save the changes made.

Formulating and Implementing What-If Formula

Problem Statement

Create a What-If Formula to calculate a retailer’s MSRP or RRP.

• Manufacturer’s Suggested Retail Price (MSRP), or Recommended Retail Price (RRP) Price of a product at which the manufacturer recommends the retailer to sell.

Solution

1. To determine the MSRP, or the RRP of a product, initially we need to know the total Cost of Goods (COG).

   • To determining Cost Of Goods (COG), we can depend on the below equations
     • Manufacturing Cost = Raw Material cost + Processing cost + Fabrication cost
     • Packing & Assembly cost = Packaging Material cost + Assembly cost
     • Delivery Cost = Shipping cost + Package cost
     • Hidden cost = (Manufacturing Cost + Packing & Assembly cost + Delivery cost) x Hidden cost %
     • Cost of Goods (COGs) = Manufacturing Cost + Packing & Assembly cost + Delivery cost + Hidden cost

2. With the COGs estimated we can now determine the Manufacturer Suggested Retail Price (MSRP) and the Profit Margin.

   • We can use the below formula for that
     • MSRP = COG + (MSRP factor x COG)
     • Discount contribution = MSRP x Discount contribution %
     • Final MSRP = MSRP – Discount contribution
     • Profit Margin = Final MSRP – COG

3. With the formula estimated we need to define the Measures, Expression, and Final Values.

   • Click on to add Measure.
     • Select the Key as ‘a’ from the drop-down and enter the name Raw Material cost.
     • Select the Measure as Raw Material cost from the drop-down.
     • Select the Dimensions as Product Name from the dimension tab.
     • Select the value type as Value and click Save.

4. With the one Measures defined we move on to defining more Measures.

   • ‘b’ as Processing Cost
   • ‘c’ as Fabrication Cost
   • ‘e’ as Packaging Materials Cost
   • ‘f’ as Assembly Cost
   • ‘h’ as Shipping Cost
   • ‘i’ as Package Cost

5. Click on to add Expression, to calculate the manufacturing cost, we require three measures keys ‘a’, ‘b’, and ‘c’

   • Raw Material cost (‘a’)
   • Processing cost (‘b’)
   • Fabrication cost(‘c’) Select Expression Type as Standard because the resultant manufacturing cost should be in numeric value.
   • Select the key as d from the drop-down list, and enter the name as Manufacturing Cost.
   • Create an expression as (a + b + c). (Here a, b, c are keys that represent measures).
   • Click on Save as an expression.

6. In the similar manner define the other Expressions.

   • ‘g’ as Packing & Assembly Cost, whose expression is (e + f),
   • ‘j’ as Delivery Cost whose expression is (h + i),
   • ‘l’ as Hidden Cost, whose expression is (d + g + j) * k,
   • ‘m’ as Cost of Good (COG), whose expression is ( d + g + j + l )
   • ‘o’ as MSRP, whose expression is m+ ( n * m )
   • ‘q’ as Discount Contribution, whose expression is (o * p )
   • ‘r’ as Final MSRP, whose expression is ( o – q )
   • ‘s’ as Margin, whose expression is (r – m )

7. Click on to add Value.

   • Select the key as k from the drop-down list, and enter the name as Hidden Cost %.
   • enter the initial value to 10.
   • Select value type as value or percentage.
   • In this case, select percentage and click Save.

8. Create and add other Fixed values same as above formula step

   • ‘n’ as MSRP Factor, enter initial value to 1.5, value type is selected as value.
   • ‘p’ as Discount Contribution %, enter initial value to 20 value type is selected as percentage.