Product Order Optimisation

[darrenp80]

I'm looking to use excel to help me find the optimal order of products displayed on a eccommerce page.
Different revenue is generated from a click for each product dependent on the position on the page, so in the example below, Product 1 makes £33 for a click in position 1, £10 in position 2 etc. (Numbers are all made up here). The volume by position is ofcourse different (ie 40% will click P1 vs
20% click P2, however I have accounted for that in the £s to keep this simpler, so assume it is evenly spread)

Can I use excel to try every possible order to show what the optimal order is.

In this example there is on 5 products and therefore 5 positions, however in reality there may be 30-40 products

Capture.PNG

Many Thanks

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[darrenp80]

Sure thing, I've added a small workbook. I've also added the click volume and click share and a mock up of a desired solution output (which is effectively a output of what the product order should be and what that would be maximum revenue this would generate - although any better or easier output is welcome!)
Thanks in advance for any help

[AliGW]

You have not included the desired outcome in B20 to B29.

[darrenp80]

here you go

[AliGW]

I'm sorry - you are going to have to explain your desired outcomes. Why, for instance, is product 8 in first position? What's the logic? Which bits of this worksheet are you asking us to help you automate? It's a long way from clear to me - sorry!

[darrenp80]

Thanks for your help so far, sorry for not explaining very well!

The product order was just a random order I put in there for the sake of an example of how the output could look (I'm sure it's not optimal at all!)

So as an example, if the products on the table were ordered numerically and there were 1000 clicks:
Product 1 in Position 1 would generate £116.49 (1000 clicks * 35.5% click share * £0.33 revenue per click or B3*B4*B6)
Product 2 in Position 2 would generate £14.92 (1000 clicks * 15.2% click share * £0.06 revenue per click or B3*C4*C7)
And so on across all 10 products.. which would generate a certain £

Obviously there a multiple orders you could arrange the products, too many to manually check, (10^10 i think), one of which would give the maximum revenue

The question was really was there an analysis tool within Excel that can do this calculation on all the different orders and show what the optimum order would be
Appreciate its not just a straight forward formula! But I'm unsure how a lot of the analysis tools work (like Solver) and whether they can help in this example -

or whether this is out of scope for what Excel could do and another modelling/analysis tool is more suitable- hope this makes more sense.

Thanks again in advance

[AliGW]

Over my head, I'm afraid!

I'm sure someone else will get it and chip in.

[darrenp80]

Over mine too! Thanks for looking at it though!

[Alf]

Not really sure I understands you problem properly but I've set up a solver model for max profit based on you only can select 1 product from a position.

As for building the table, I first need to know if this setup is of any use to you.

Alf

[darrenp80]

Thanks so much for your work so far -
So In theory yes, this would be a good output for me -
What needs to be added into this is the fact that 1 product can only be used on one position.
So in your current output on the binary grid (if I'm reading it right!) says position 1-6 should be product 5 for all of these rows. However if Position 1 was Product 5, then Position 2 could be any product except for Product 5 (as that's already been used)
Its effectively finding the best combination only each product once - hope that makes more sense??

Thanks again for helping with this!

[Alf]

Please Login or Register  to view this content.

No problem fixing that just adding another constraint.

If this is what you want I'll have a go at constructing the table. Don't know how to do it at the moment but looks interesting.

Alf

[darrenp80]

Hi Alf,
This is perfect thank you so much for your work - I think this is just what I need for now

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[darrenp80]

Yes I think it does for now, I will thank you

[Alf]

You are welcome and thanks for feedback.

With time on my hands I've made a last version of your original file.

In this file there are two macros, the first one assumes that you run solver manually first. Then you run the "MakeTable" macro and
this macro first clears the range A40:B49 and B50 and then adds the values from the solver run into range A40:B49 and cell B50.

The second macro "SolverAndTable" first clears the all ranges A40:B49, B50 and B18:K27 and the runs solver and builds the table.

But to run the "SolverAndTable" macro you must first set a reference to solver in "Visual Basic"

Click on "Developers" tab, then click on the "Visual Basic" icon. In the new window that opens click "Tools" then "References"

find and tick box marked "Solver" click "OK" and close "Visual Basic" window. You can now run solver and get
the table updated using the "SolverAndTable" macro.

The way the table is filled is by entering the highest value first by using the "Large" function (cell C35) and looping the number from 1 to 10 in
cell E35.

As you do have some rather small values and they are shown with only two decimals some results may look odd at the first glance
for instance E28, F28 and I28 all show the same value but checking these values in the table F28 is position 5, product 6 and this value is really 0.06185. And I28 is position 8, product 10 witch has a value of 0.056712 and finally E28 is position 4 and product 9 with a value of 0.05654

Must say this was not obvious to me at the start. I've modified the spreadsheet in order to get the data for table building. Not my best effort I must admit but it seems to work so


Alf