Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Please could somebody help me with a percentage calculation?
I am busy with What If analysis and Input boxes and have the below scenario:
I have 4 products with a Sales Price. The user is allowed to enter a Discount% into an Input box. I then calculate the Fee after Discount. Each of these products have a Payback (Cost of Sale). I then calculate the Margin which is the Sales Price - Discount% - Payback. After this I calculate the Margin%. It calculates the Margin% per product correctly, but my problem is with the Total Margin% which is the 10% in red. It should actually be the 31% in green. It looks like it does the 20% Discount on the Total Sales Price and calculates the Margin% from there. Just below the table you can see the calculations I use. What should I change so it would use the Total Margin (4300.2) / Total Payback (13860) to get the 31%
Product | Sales Price | Discount% | Fee after Discount | Payback | Margin | Margin% | Header |
---|---|---|---|---|---|---|---|
TOTAL | 19116 | 18160.2 | 13860 | 4300.2 | 10% | 31% | |
A | 4779 | 4779 | 3465 | 1314 | 38% | ||
B | 4779 | 4779 | 3465 | 1314 | 38% | ||
C | 4779 | 20 | 3823.2 | 3465 | 358.2 | 10% | |
D | 4779 | 4779 | 3465 | 1314 | 38% | ||
Sales Price = sum(ExchangeFee*vSalesPctChange)
Discount% = InputSum(UnitPriceInputDiscount)
Fee after Discount = sum(ExchangeFee*vSalesPctChange)*(1-(Discount%/100))
Payback = sum(PaybackAmount*vCostPctChange)
Margin = [Fee after Discount] - sum(PaybackAmount*vCostPctChange)
Margin% = [Margin]/sum(PaybackAmount*vCostPctChange)
Thanks
Antoinette
If you are using a Straight Table, you can try to change the Total Mode for the Margin% to Average. You can find the Total Mode by going to the Expressions tab, selecting Margin% and changing the Total Mode.
what is PaybackAmount and vCostPctChange
why dont you just use [Margin]/[Payback]
One can use Column(1) / Column(2) ... adding some more logic might be required for your exact calculation...but easy way to create a calculation. Otherwise - perhaps using some set analysis might be required?
There is a "Relative" option in the Expression tab in the chart properties that might be of use to you.
I tried [Margin]/[Payback] and it stills gives me the 10%
Hi,
I've tried Column(1)/Column(2) and the relative option also doesn't work in my scenario.
Can you maybe give me an example of the set analysis?
Have you played with the Aggregation on the expression in the expression's tab? "Total Mode"
If you are using a Straight Table, you can try to change the Total Mode for the Margin% to Average. You can find the Total Mode by going to the Expressions tab, selecting Margin% and changing the Total Mode.
Hi,
Thanks for the useful doc. I will work through it.
Thank you for this. It looks like it is working.