Complex Problem: Row based costs based on multiple variables

DDrob · May 7, 2015 12:31PM

Hi there

I've come across a pretty complex issue which I could do with some extra brain power with.

I have monthly revenue data for my clients. Each client pays a % of their revenue each month, based on how much revenue they have generated in their current financial year i.e. anything between 0-5m is 4%, anything from 5m-10m is 3.5% etc. The issue is, each client has a different set (and sometimes number) of brackets for their tiers, and pays a different percentage for those tiers. To complicate things further, each client has a different financial year. The final thing is that each client has a minimum spend for each month, so if the % of their revenue hasn't met the minimum, I need to use the minimum instead.

I need to work out the monthly charge that we take for each client, taking into account how far through their brackets they are.

My thought in tackling this is to have a match table (so that I can add or edit clients details when needed) which lists each client's different variables, bracket 1 value, bracket 1 %, year start, year end and minimum spend. From there, merge all of those fields with my data and work out a cumulative spend each month. Then the tricky bit which I can't quite work out, is to work out what we charge by incorporating the different brackets and the cumulative spend.

I understand this may be difficult to understand but I'm hoping somebody might have an idea how to help. It's also quite good for me to write it down so I can understand better!

Thanks

paola · May 7, 2015 1:05PM

In the Data Manager use the Aggregate block to create monthly revenue totals for each client (you might already have this).
Then create a 'data mapping' spreadsheet to allocate financial year for client/month, via multi-field merge, as described here:
http://forums.visokio.com/discussion/comment/7974/#Comment_7974

Fields :
[Client] [Start Date] [End Date] [Fin Year]

Each client will have 1 row for each financial year, that will be merged with your main dataset fields
[Client] =[Client],[Date]>[Start Date],[Date]<[End Date] , while the 4th one, [Fin Year] is the new field you will add to your dataset.

Similarly you can use another mapping sheet to assign the % value, in accordance to their monthly revenues:
[Client] [Min Revenue] [Max Revenue] [%Bracket]

Please post a data sample if this doesn't help.

DDrob · May 8, 2015 5:08AM

Hi Paola

Thank you very much for your response, that does cover both issues, but there's just an extension of the % value that I need to discuss.

So in one month, a client might have been in the first bracket, and then moved into the second bracket. I need to use the % for the first bracket all the way until the benchmark for the second bracket. I was thinking it would need to be done at a row level, but even then, how would I use two separate percentages in 1 calculation?

I can't really attach any data because it's all confidential.

Thanks

paola · May 8, 2015 5:22AM

The merge above is done on the monthly total basis, as each row represents one month (done in the aggregation step). You will then do the multiplication on the row level, so [Revenue] x [%Bracket], and then add the total for the year, or other period.

Welcome!

Categories

Ideas Parade