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.
- The What-If formulae you create here will be readily available in the What-If analysis for detailed analysis and exploration.
Create and Set up a What-If Formula
Steps to create and set up a What-If formula
- 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.
- Use the search option to look for any existing What-If Formulae.
- Use the Filter by Datasource option to filter the What-If formulae based on the datasource.
- To switch the view between Card View and List View, click on the respective icons near to the search bar.
- To create a New Folder in the What-If formula, click on the Folder icon.
- To download the list of all existing What-If formula, click on the Download icon. It delivers the details on (• Entity Id Name • Description • Content Tags • Created By • Created By User • Created On • Folder • Datasource Id • Datasource Name • Is Folder Public)
- To formulate a fresh formula, click on New Formula.
- Enter the formula Name .
- Select Datasource or Query, from the respective drop-down list.
- Datasource: Use the existing datasources 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.
- In order to configure What-If formulae, it is imperative to have the corresponding datasource or query properly configured.
- When selecting Queries to configure the What-If formulae, also define the required Measures and Dimensions.
- Click on Save to add the what-if formula to Infoveave.
- Once the What-If formulae is saved, the Datasource or Query can not be edited .
- The What-If formulae Name cannot be changed once saved.
Configure What-If Formulae
- To start configuring click on Edit icon on the newly created Formulae.
- The dialog opens up for you define the measures , values and expressions for the formula.
To configure the measure formula element in the What-If feature of Infoveave, please follow the steps outlined below:
- Click on 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).
- Click on Save to save the Measure in formula.
- In order toAt least one measure and dimension are required to save the formula.
To configure the value formula element in the What-If feature of Infoveave, you can follow the steps provided below:
- Click on to add Value. 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).
- Click on Save to save the value in formula.
- 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).
To configure the Expression formula element in Infoveave’s What-If feature, please refer to the following fields:
- To add an Expression click on the 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.
- 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.
- Click on Save to save an expression in formula
Copy of Value
- The "Copy of Value" function allows you to conveniently reuse the value of an existing formula element within your What-If analysis. 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 fields:
- 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
- Click on Save to save the changes made.
Formulating and Implementing a What-If Formula: An Example
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.
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.