Complex Excel Forumula

DullGreyGuy

Layer	Rate	Commission	Overrider	Profit share
1	60%	30%	5%	10%
2	20%	30%	15%	0%
3	50%	20%	0%	10%

I am trying to sell a complex product where I sell it to the customer for X and then have to distribute those monies to a series of suppliers based on a percentage of X and in exchange for that they give me some money back in the form of a commission, overrider and/or a profit share, the actual calculation of these can be complex but for simplicity they are expressed as a percentage of the money I am giving them.

The suppliers are in layers so for layer 2 the Rate is the percentage of the net monies after all of layer 1 has happened so for example if I sold something for £1,000

Layer 1
Gross = £1,000
Gross to Supplier 1 = £1,000 x 60% = £600
Commission from Supplier 1 = £600 x 30% = £180
Overrider from Supplier 1 = £600 x 5% = £30
Profit Share from Supplier 1 = £600 x 10% = £60
Net to Supplier 1 = £600 - £180 - £30 - £60 = £330
Net left with me £670

Layer 2
Gross = £670
Gross to Supplier 2 = £670 x 20% = £134
etc

Not all sales go to all layers and not all sales go to the same number of laters but the hierarchy is always the same. ie the next sale could only involve layer 2 and 3 so the gross to layer 2 would be £200 on another £1k sale.

What would be the formula to work out the Gross to Supplier for any given sale and layer? Have been trying to use a nested Product/If statement testing the Sales ID is the same and the Layer number is less than the current layer but seem to have to many cycles to go through and my head hurts.

tacpot12

The peice of information that seems to be missing is how you are deciding what layer of deductions applies to any particular sale. Is this something that can be looked up, or do you have to manually set the layers that apply? If you are doing this manually, I would tend to do this as a series of numeric flags, like this:

              Layer 1    Layer 2   Layer 3
SaleID1    1               1             0
SaleID2    0               1             1
SaleID3    1               0             0


Your formula for the gross to a supplier would then be:

SaleGross * ((Layer1Flag * SupplierNetMargin@Layer 1)  + (Layer2Flag * SupplierNetMargin@Layer 2) + (Layer3Flag * SupplierNetMargin@Layer3))

DullGreyGuy

There is a second table Sales ID, Layer ID, Customer ID and Original Sale Value to that I need to append the Amount to be paid and what the three amounts we are currently estimating to get back

In principle you could do a matrix as you suggest but volume of sales and layers are going to be high in some cases making it unwieldily quickly (there are over 30 suppliers and each deal is renewed every year on slightly different terms so would get over 100 columns quickly with most sales going only to 2-3 tops)

Secondly with your suggestion the gross would only work for Layer 3 it wouldn't auto-adjust if it was one of the earlier layers involved. 

tacpot12

I can see that I didn't factor in the fact that the margin available at each level depends on what (if any) was used at the previous level. I'll have another think, but it would help to see how you would prefer to record the deals, if a matrix is going to be too unwieldy.

Heedtheadvice

It strikes me, given layers and suppliers and deals and years etc that the record of each sale and the calculations to be done, that this is ideally suited to using a proper database. Even a simple one like Access would be good.

However I can well understand that the visual display aspects of a spreadsheet have desirable aspects.

Where you have lots of repetitive calculations ( like you will have) but have variability then a matrix approach will be both unwieldy and difficult to keep under control. It only needs one cell to pick up an anomoly or error ( that may or may not get copied to other similar cells) to make several desired results be in error.

It does lend itself very well to having the repetitive formulae produced as a specific (custom) formula produced in the built in Visual Basic for applications (VBA). It is used on similar lines to the built in formula I.e. in a cell the content is =myformula ( parameters passed in....and the cell then holds the result of the formula.

Thus a once written but quite simple custom formula giving the desired result depending upon the parameters passed into VBA. The calc within VBA can use the second (reference) table's contents depending upon the passed in parameter. You can pass in the monetary value, year of table to use & supplierID to get the correct percentage etc.

That is just a short overview and I am sorry that I cannot give more time to this at the moment but hope others who also know VBA can chip in and ( if you cannnot) even write the code for you. It would not be difficult or complex and can be easily understood  and tested once written.

There is a lot about custom function on the web but if you have not used them before uSoft have a starter at https://support.microsoft.com/en-gb/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f where you hopefully will see some similarities to your need.

DullGreyGuy

Long term solution will be a db, this is more a proof of concept, test the logic works/ show others and then someone that knows what they are doing with tech but not the business can rebuild it in a more efficient way with the XLS able to be used whilst they do it. 

Heedtheadvice

Then the VBA approach ( in excel initially) is even more desirable as the tested/proven code can be ported into a dB later.

DullGreyGuy

I can dabble with VBA but it's mainly finding some code that does roughly what I want and then customising it. I'd be worried if anyone tried to productionise what I've done, its fit for purpose for analysis only.

GDB2222

DullGreyGuy said:

I can dabble with VBA but it's mainly finding some code that does roughly what I want and then customising it. I'd be worried if anyone tried to productionise what I've done, its fit for purpose for analysis only.

Ask ChatGPT

Heedtheadvice

Have you come up with  a solution or now passed it by?

DullGreyGuy

I went for a sub prime solution that enables me to use SumIfs statements and some hidden columns doing the intermediary maths. I'm sure someone brighter than me could have done it in one step but was beyond me and not yet into ChatGPT