What-If Formulae
Contents
Overview
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.
Define Measures
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.
Define Value
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.
Define Expression
- 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.
- 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.
- 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
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.