Report of the Week - What-If Analysis (Influence of Discount over Sales)

When an archer is aiming to hit the bullseye (or a sniper, her target), she has to consider external factors like gravity, wind direction and wind speed. Similarly, when running your business, you will have to ask various questions. Have you aligned yourself against all external factors, when it comes to meeting your target? Should you raise the price, or increase the production to make more money in the current market trend? By how much percentage? Is it the right time to run a marketing campaign?

Would it not be easier if you knew what the outcome can be when you undertake such a change? What-if Analysis helps you evaluate such options. It shows you how external changes might affect the outcome of a decision you are about to take.

What can be done?

What-if Analysis shows what might happen if one or more factors change. It lets you know the impact of various factors on your business and gives you an idea of what needs to be done.

Here are a few scenarios where What-if Analysis can help you.

You are planning for a sales day. Based on your previous sales, you can measure the impact of discount percent on your revenue and learn what percent of discount makes people buy more. And you can set your discount percentage to an optimal value so that it leads to maximum profit for you.

You have spent various amount on marketing over the months, which affects the leads gained in each month. With What-If analysis, you can learn what will happen if you increase or decrease the expenses.

You have multiple teams working on different projects. There will be different billing prices and hours per week, each team member might be working for. You can check the time and money needed for each member - project combination and choose the best combination out of it to assign projects.

You have projected sales for the next six months. There is a possibility of your revenue increasing or decreasing and you can know with What-if analysis how much your revenue would be, based on percentage increase/decrease.

And much more...

Now, let's see how to implement the first example of measuring the impact of discount percentages on your revenue.

How to do What-if Analysis

You can easily set up a What-if report by following the steps here.

Step 1: Creating Variables

As we said earlier, What-if Analysis is to show the outcome when one or more parameter value changes. To parameterize dynamic inputs, you need to store all the possible values in a placeholder named Variable.

Here, we have created two variables to apply the change in discount percentage and sales volume.

Discount Percentage

Create a variable with a range of discount percentages you are planning to provide. We have set the Range between 5 to 50 with an Increment Size of 5. And the Default Value is set as 10.

If you plan to have different discounts for each product category, you need to create different variable for each category.

Sales Volume

Create a variable with a range of possible sales volume change. We have set the Range between -50 to +50 with an Increment Size of 5. And the Default Value is set as 0.

Step 2: Creating Aggregate Formulas

Construct an Aggregate Formula to compute your metrics. Insert the variables appropriately in the formula to visualize the impact as the dynamic value changes. You can specify different calculations for each value or a range of values.

The following is the formula to calculate the sales after discount.

‌ sum( "Sales"."Sales" )-(sum( "Sales"."Sales" )/ 100 )* ${Discount %} *(1+${Sales Volume}/100

If you have different product categories, then use the following formula to calculate the Sales after discount for all products with different discounts.

‌ Sum( "Sales"."Sales" )

-(sumif( "Sales"."Product Category" = 'Furniture' , "Sales"."Sales", 0 )/ 100 * ${Furniture Discount %} )

-(sumif( "Sales"."Product Category " = 'Stationery', "Sales"."Sales", 0 )/ 100 * ${Stationery discount %} )

-(sumif( "Sales"."Product Category" = ' Grocery', "Sales"."Sales", 0 )/ 100 * ${Grocery Discount %}))*(1+${Sales Volume}/100 )

Step 3: Creating Report

Create the What-if report using the Aggregate Formula in Step 2.

X-Axis - Date with Month & Year
Y-Axis - Sales with Sum and Sales After Discount (Aggregate Formula with variable) with Actual

Report will be created as shown below. Sales after Discount will be calculated with a 10% discount based on the Variable's default value.

Apply Forecast for next one month to see the prediction for the upcoming month.

Now, how do you dynamically change the input for the reports? We will see how it's to be done in the following step.

Step 4: Adding Variable as User Filter

Add the discount variables we had defined in Step 1 as User Filters in the report. Each User Filter will be in the form of a slider and will list the discount values in steps of 5. As you change the input (discount percentage) by moving each slider, the report will get re-generated dynamically based on the appropriate calculations defined in Step 2.

The final report will be as shown here. As you drag the What-If Variable sliders (the User Filters at the top), the total sales after discount will be calculated based on the values.

Now you have the privilege to know what might be the outcome of your sale month. And you are one step closer to a discount campaign run successfully!