Here to Help

What-If Formula

Estimated reading: 9 minutes 289 views

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 section details on 

Create and Set up a What-If Formula

How to create and set up a What-If formula

  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.
  1. To formulate a fresh formula click on New. The What-If dialog box will display.
  1. Enter the formula Name .
  2. Select the option which you are opting to create What-If formula. Select from:
    • 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.
    • Select the datasource or query which you want to use from the respective drop-down list.
  1. Click on Save to add the what-if formula to Infoveave.

Define Measures

  1. To add measures or expressions or values, to the new formula click on Edit  .
The dialog opens up for you define the measures , values   and expressions   for the formula.
  1. Click on  to add Measure. Select the Key from the drop-down and enter the Name.
  2. Select the Measure and Dimensions from their respective drop-down list. To learn more on visit the section on Measures and Dimensions .
  3. Select the Value Type as either “Value” or “Percentage” and click on Save to save measure in formula.

By choosing a separate key for each additional measure, you can add more than one measure to your formula.

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

Define Expression

  1. To add an Expression click on the  icon. Select the Key from the drop-down list and enter expression name.
  2. Select the Expression Type from the following:
    • Standard: It supports excel functions and formula. It always returns result as a numeric value.
    • JavaScript: It supports JavaScript functions or expressions. It returns the output of the function as a result.
    • Standard String: It also supports excel functions and expressions with string or statement. It always returns result as a string.
  1. Click on Save to save an expression 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).

Refer the example described in the following section for better understanding on creating a What-If Formula.

Define Value

  1. Click on  to add Value. Select key from the dropdown list.
  2. Enter Initial Value to key and select value type as either value or percentage.
  3. Click on Save to save the value in formula.
  1. If you want to export What-If formula, click on Manage Export Formula  , on the preferred What-If formula.
    • After clicking on export, click on New and assign a name.
  1. Select the column which you would want to export by checking Check-box. If you uncheck the column, that columns will get ignored.
    • If you want to specify another heading to the columns, then assign the label and it will display while exporting What-If formula.
  1. Click on Save to save the changes for export.
  1. If you want to copy What-If formula, click on  to copy the formula.
    • After clicking on Copy, select the name for the next new formula.
    • Click on Save and all formula will get copied to new formula with the same keys.

Before deleting What-If formula, you have to delete What-If analysis that is associated with it.

  1. If you want to delete What-If formula, click on Delete .

Managing What-If Formula

What-If formulas can be managed with the options to manage export formats, share, edit, clone, move to a folder, add a description, and delete.

  1. If you want to export What-If formula, click on Manage Export Formula  , on the preferred What-If formula.
    • After clicking on export, click on New and assign a name.
      • Select the column which you would want to export by checking Check-box. If you uncheck the column, that columns will get ignored.
    • If you want to specify another heading to the columns, then assign the label and it will display while exporting What-If formula.
      • Click on Save to save the changes for export.
  1. Share the What-if formula with the team/role from the share option.
    • Choose between the options
    • To share with the users, select the “share with users” tab. Pick the user(s) with whom the What-If formula needs to be shared from the share dialogue box.
    • Choose the “share with role” tab to make a What-If formula available to a certain work role. From the dialog box, choose a role.
    • Select the users from the presented list of users.
      • If you wish to share your What-If formula with all users then select Share With Everyone.
    • Select the user from the available list and click  to share the What-If formula.
    • To remove a particular user/role from the shared list, select the user/role and click  .

    • Click Save to share your What-If formula with the selected users.

      • Shared personals receive a notification after the successful share.
    • To unshare a What-If formula, just unselect users or uncheck the Share With Everyone.
  1. If you want to copy What-If formula, click on  to copy the formula.
    • After clicking on Copy, select the name for the next new formula.
    • Click on Save and all formula will get copied to new formula with the same keys.
  1. To move to a folder, create a folder by clicking the New Folder option, then drag the What-If formula into the desired folder using the drag-and-drop feature.
    • You may also choose a folder from a drop-down menu by clicking the folder symbol on the What-If formula.
  1. Click “Add Description” and fill out the description details to add a description to the What-If formula.
    • Select the Editor Type.
    • Add necessary Tags (if required).
    • Add Description and Content.
    • Click Ok
  1. If you want to delete What-If formula, click on Delete .
    • Type in “delete‘ in the text box.
    • Click Yes.

Before deleting What-If formula, you have to delete What-If analysis that is associated with it.

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 ) 

The fixed values chosen are only to demonstrate the process. 

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.