# Making use of Global variables

Modified on Mon, 4 Oct, 2021 at 7:36 PM

Calculating Profit/Loss Variance based on What-if Analysis

Description:

Finding out the variance of profit or loss by deriving from columns like Actual profit, Actual profit %, Estimated profit, Estimated profit% based on what-if scenarios for sales% and cost%

Example:

UDDC Expression:

For Calculating Actual profit

 GrossSales - CostofGoods

For Calculating Actual profit%

 ((GrossSales-CostofGoods)/GrossSales)*100

For Calculating Estimated Sales (where \$Sales_Percentage\$ is the global variable)

 GrossSales  + (( GrossSales * \$Sales_Percentage\$) / 100)

For Calculating Estimated Cost (where \$Cost_Percentage\$ is the global variable)

 CostofGoods + ((CostofGoods * \$Cost_Percentage\$) / 100)

For Calculating Estimated profit

 Estimated_Sales – Estimated_Cost

For Calculating Estimated profit %

 ((Estimated_Sales - Estimated_Cost )/Estimated_Sales)*100

For Calculating Profit Variance%

 ((Estimated_Profit_Perc- Actual_Profit_Perc )/ Actual_Profit_Perc) * 100

Here we will take an Alcoholic drink example to understand the expressions:

For Actual Profit : (GrossSales – CostofGoods)

-> (68110629 - 51419996) = 16690633.

For Actual Profit% :  ((GrossSales CostofGoods) GrossSales) 100

-> ((68110629 - 51419996) / 68110629) = 24.51

For Estimated Sales :  GrossSales ((GrossSales \$Sales_Percentage\$) 100

-> 68110629 + ((68110629 * 24.51) / 100) = 71516161

For Estimated Cost : CostofGoods ((CostofGoods \$Cost_Percentage\$) 100)

-> 51419996 + ((51419996 * 193496) / 100)  = 51934196

For  Estimated profit : (Estimated_Sales – Estimated_Cost)

-> (71516161 - 51419996) = 19581965

For Estimated profit %: ((Estimated_Sales -Estimated_Cost )/Estimated_Sales) *100

-> (71516161 - 51934196) / 71516161) * 100 27.38

For Profit Variance%:((Estimated_Profit_Perc ActualProfit_Perc) Actual_Profit_Perc) *100

-> (27.38 - 24.51) / 24.51) * 100  = 11.74