# Excel Permutations and Layout

Good Morning Experts

I have a task which is beginning to give me a headache, hopefully you'll be able to advise...

I have some food items - chips, rice, coleslaw, Greek Salad, Caesar Salad...

The food items are categorised as Regular, Large or Salad,  Salads are considered large as well.

For each item I have a Selling Price and a Cost Price and Profit, this is what the list looks like:

These items are sold individually and as part of of a combination - one particular combination is '2 Large or 4 Regular' - I need your expert help to work out what all the different permutations could be and layout the information as clear as possible for end users to view - I will be applying conditional formatting to the profit column later.

I have attached a spreadsheet with the list from the above image, currently working with Excel 2007 but can move to Excel 2010 if there is any specific functionality which I should be taking advantage of.

Excel formulas or VBA acceptable

LVL 10
###### Who is Participating?

Commented:

Attached now includes ""1 Large Item AND 2 Regular Items". Previous points still apply.

Regards,
Brian.
0

Author Commented:
I should clarify that although the combination is named '2 Large or 4 Regular' a combination of 1 Large and 2 Regular would also have to be accounted for.

Let me know if you have any questions..
0

Commented:

Could you help me with the rules around the combinations, please? Are you looking for all possible combinations in the form "X Large and Z Regular" where X and Z are any values from 0 to 4 (e.g. "0 Large and 1 Regular", ""0 Large and 2 Regular" all the way up to "4 Large and 4 Regular")?

And then are you looking for the details of all the possible combinations for each of those (e.g. for "1 Large and 1 Regular" there are 3*6 possibilities from "1 Reg. Chips and 1 Mixed Salad" all the way to "1 Reg. Chips and 1 Large Coleslaw")?

Thanks,
Brian.
0

Author Commented:
Hi Brian, thanks for taking a look at this problem

Combinations can include the following

Any 2 Large Items
OR
Any 4 Regular Items
OR
1 Large Item AND 2 Regular Items

Any other combination is out of scope

Does this help?
0

Commented:

Absolutely! Thanks.

Regards,
Brian.
0

Commented:

Oops, you earlier mentioned "2 Large or 4 Regular", but that isn't in your new list.

Regards,
Brian.
0

Author Commented:
mmm...it is, and it isn't...lets see if this helps...

You order a pizza and you have the option to order '2 Large or 4 Regular' side items from the list of items in the excel file...

If you take the option to have 2 Large your choices are:

Large Chips
Large Rice
Large Coleslaw

If you take the option to have 4 Regular your choices are:

Reg Chips
Reg Rice
Reg Coleslaw

In addition to this you can take 1 Large and 2 Regular which is roughly the same as 2 Large OR 4 Regular.

Does that make sense?

0

Commented:

<Blush>. Yes, thanks, I hadn't noticed  that "2 Large or 4 Regular" was simply a superset of two valid ones.

Regards,
Brian.
0

Commented:

Please see attached for my understanding of "Any 2 Large Items" and "Any 4 Regular Items".  (I've not yet completed an exhaustive check that it's fully right, but it's looking OK.)

Few points...
(1) I haven't yet put in the pricing (which I assumed you wanted). Trivial.
(2)I haven't yet done "1 Large Item AND 2 Regular Items". I'm working on it.
(3) I've only started to think about "layout the information as clear as possible for end users to view". This should have been my very first question. How exactly will the users be using the list? What information will they bring to their "lookup" and what result will they want? Will the be using Excel or a printed list?

I'll be going out for a couple of hours shortly, so if anyone else wants to finish this in the meantime - go for it!)

Regards,
Brian.
0

Author Commented:
redmondb - that looks good!

1 - Defiantly will need the pricing
3 - They will be using this info to compare the different combinations - the pricing is what's important here - i have attached another sheet with a sample of what i'm doing where the combo option is simpler (2 Regulars). You'll see some conditional formatting to highlight which combos have low profit. I've changed the pricing numbers which will make more sense rather than just 1, 1.5 etc.

0

Author Commented:
whoops, forgot the attachment!!
0

Commented:

Thanks!

Regarding the pricing, do you just want a profit calculated for each of the combinations, with conditional formatting on it?

I'm still not clear on what your users have to do...
- Is it just a case that they take an order and may then suggest to the customer that a different order may be... preferable? (Based on the conditional formatting!)
- Will they have Excel or a (very long) printed list?

I'm just going out the door, so I'll check in again as soon as I get back.

Regards,
Brian.
0

Author Commented:
Basically I'm building a model for our guys to use for analysing how profitable our sales are based on different factors:

selling price
cost price
combination mix
sales data from till systems

This EE question refers to the combination mix

If you look at the last spreadsheet you'll see a combination of 2 chips is more profitable than 2 Coleslaw - this is a simple example but you can see that where there is the possibility of many more combinations for a different product it becomes harder to see what is profitable and what is less profitable.

Users will 'plug in' different combinations into a mock menu with sales data from 'Summer', 'winter', 'Easter' etc and see what effect this has on overall profit - summer months would have more salads sold etc...

From the sample spreadsheet you'll see that it's easy to map out this info where there are two combinations - one on the horizontal axis and one on the vertical axis. What I'm struggling with is how to display the info where the number combinations is higher.

All of this will be viewed in front of a computer, nothing printed - users have 20" monitors set to 1680x1050 which makes things easier! :)
0

Commented:

Excellent, that makes me feel much better!

First cut, let me update the sheet to show the profitability (amount and %) for each item. What about merging the 3 "menus" (with a code, so it's clear which menu an item came from). This would allow you to lookups etc.

The profitability would be calculated from a table (similar to your original table), so it could be easily updated.

Regards,
Brian.
0

Author Commented:
Brian,

I only need profitability as a percentage, amount is not needed.

With regards to merging the menus - this is not required as i have built the model in way so that users have areas to create 'profiles' for the following:

Cost
Combo
VAT

They use drop downs to select the options and formulas pull through the relative numbers.

Once i have the combo set up in a readable format i will update my combo profile sheet - this is where i have calculations which lookup combos and pick out low, high and average GP combos. in addition to this users can add custom combinations, all of which are later used in the menu...just need a way to display this particular combination in a readable format, the rest is basically done!

Shaz
0

Commented:
Shaz,

I'd produced the attached before I saw your latest email. I'm sending it anyway, while I digest your email!

Regards,
Brian.
0

Commented:
Shaz,

Okaaay. Without sight of your existing front-end, I don't have enough feel for this to be any help.

Regards,
Brian.
0

Commented:
I did something like this for a local cub scout event.  I was trying to build an order of supplies, based on the number of hot dogs, cheeseburgers, quesadillas, etc., that might get ordered.  So, for each type of "package" that would be sold, I'd identify how many pickles, how much mayo, bread, etc., would be associated with each order.  Then I determined how many of each type of order might get sold, so I could sum up my supplies and go shopping.

This is kind of like that, I think.  Perhaps if you thought about your data structure differently, it could shed light on your ultimate solution.

Take a look at the attached picture.  It shows items in the first column (like yours) and then order possibilities across the right.  If you get this right, its simple math to work out the profit/cost, etc., that you are deriving.

Enjoy!

Dave
OrderCombo.png
0

Commented:
If this works for you and you can take it from there, great.  If you want to work this, I'm happy to assist.  Just develop an initial set, and a side table on cost and sales for the "supplies" and "packaged sales".  Its matrix math from there to derive your output.

Dave
0

Author Commented:
redmondb - I was afraid that would be your reply once I gave a overview of the entire model I'm building hence my reluctance to go down that path. This question refers to working out combinations based on a set of rules and then the layout of those combinations - think of this as being completely independent to the rest of the model. If you don't have enough feel for what I'm doing then that's a shame - what you have done so far has been helpful and the sheet you last posted gets me half way there.

dlmille - your approach appears to be similar to redmondb (see his last posted sheet)

I think I now just need a way to present this as having a long list of 200+ combinations in one long list probably could be improved upon. I have attached another sample showing how i'm doing this for a '1 Large or 2 Regular' combo - you'll see the key information is being highlighted so that you can easily scan the table and see what's profitable and what's not. Any suggestions how this can be done using redmondb's last spreadsheet as a base?
0

Commented:

So, no sight of your front-end?!

Regards,
Brian
0

Author Commented:
I'm afraid I cant and have already explained that this combo table is to be considered independent to the rest of the model. Look, I've answered a few questions on EE myself and know that sometimes you may feel the need to see the big picture but in this case I can honestly say it is not - probably will cloud the original question further!

Hope you understand..
0

Commented:

OK, another approach.

When one of your users is, for example, looking at your latest list, what questions is he trying to answer? It must be more that which option is the most profitable - "my" list sorted gives that.

Is there something about time (don't want to repeat options used recently), preference towards mix (2 different preferable to 2 the same), etc? You mentioned seasons as being important, so include a "Season Weighting"?

I'm struggling!

Thanks,
Brian
0

Author Commented:
They'll look at what's profitable, what's not and what's in between - I was hoping someone might come up with a different way to layout the list...

I'm beginning to think perhaps a big long list is going to be the only option...

Seasons are not relevant here, just the combinations
0

Commented:

Don't give up just yet! Grab a "good" user for 5 minutes. Get him to play with the big list, see what works, what doesn't, what interests them, "wouldn't it be nice if...", , etc.

Regards,
Brian.
0

Commented:

Sales weighting? What use is a highly profitable item if you sell little of it?

Regards,
Brian.
0

Author Commented:
Brian,

Many thanks with your help on this one!

For the purpose of helping anyone who may come across this question in the future the first part of the question was answered and the second part relating to the layout was not answered (for me).

I still don't know exactly how I will achieve the optimal layout but I'll try and remember to come back to this question and leave a sample when I do!
0

Commented:

Thanks and good luck with your users!

Cheers,
Brian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.