Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Currently our head office (Johannesburg) charges our branches a 10% markup on cost. I have been task to determine what the branch's Cost of Sales would have been if it was at Johannesburg's Unit Cost.
I am stuggling to calculate this expression (expression highlighted in red below & attached)
Attached herewith is the Qlikview Model as well as the data (in excel format) on which my Qlikview model is based.
Please can you assist.
kind regards.\
Nayan
Financial Year | 2014 | |
Financial Period | 1 | |
Branch | Fisc.Per-Month | 1 (May) |
Bloemfontein | Sales Qty | 798 |
Sales | 79,800 | |
Cost of Sales | 43,890 | |
Gross Profit | 35,910 | |
GP% | 45.0% | |
Unit Cost | 55.00 | |
Johannesburg COS | 0 | |
Cape Town | Sales Qty | 479 |
Sales | 47,900 | |
Cost of Sales | 26,345 | |
Gross Profit | 21,555 | |
GP% | 45.0% | |
Unit Cost | 55.00 | |
Johannesburg COS | 0 | |
Johannesburg | Sales Qty | 13,160 |
Sales | 1,316,000 | |
Cost of Sales | 658,000 | |
Gross Profit | 658,000 | |
GP% | 50.0% | |
Unit Cost | 50.00 | |
Johannesburg COS | 658,000 |
Hi Nayan,
This is by no means probably the best way of doing it, but another way of doing it allowing for different markups for each product would be:
Resident Load the Joburg Unit Costs:
Load
[Item Code],
[Cost of Sales] / [Sales Quantity] as [Unit Cost]
Resident Data
Where Branch = 'Johannesburg';
Then use an aggr statement to calculate the overall cost of the items sold at Joburgs unit cost by:
=Sum(Aggr(Sum([Sales Quantity]) * [Unit Cost], [Item Code], [Financial Year], [Financial Period], Branch))
Making sure you allow for all the variables you need to break it down by in the Aggr statement!
I've tested that and it's working with the data you sent over. Let me know if you need any further explanation - I've attached what I did here.
Hi Nayan,
Is the markup fixed? If so, surely the unit cost will always be 10% higher for you than for Joburg, so you can simply take whatever your cost of sales is and divide by 1.1 to give you the answer?
Apologies if I've understood wrong!
Edit: And if you need this to be correct for Joburg too, have the expression as:
f(Branch='Johannesburg', [Cost of Sales], [Cost of Sales] / 1.1)
Hi Aaron
Thank you for your reply. Our actual Qlikivew model that we work with , the mark-up % varies per product. Thats' the difficulty I'm face with.
No need to apologise , you understood correctly.
kind regards
Nayan
Hi Nayan,
This is by no means probably the best way of doing it, but another way of doing it allowing for different markups for each product would be:
Resident Load the Joburg Unit Costs:
Load
[Item Code],
[Cost of Sales] / [Sales Quantity] as [Unit Cost]
Resident Data
Where Branch = 'Johannesburg';
Then use an aggr statement to calculate the overall cost of the items sold at Joburgs unit cost by:
=Sum(Aggr(Sum([Sales Quantity]) * [Unit Cost], [Item Code], [Financial Year], [Financial Period], Branch))
Making sure you allow for all the variables you need to break it down by in the Aggr statement!
I've tested that and it's working with the data you sent over. Let me know if you need any further explanation - I've attached what I did here.
Hi Aaron
Thank you for your answer. I will test it our on our QV Model we use and will give you feedback on Monday as I'm about to leave for home.
kind regards
Nayan
Hi Aaron
Your formula works. Thank you for all your help. Much appreciated.
kind regards
Nayan