[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

Excel Permutations and Layout

Posted on 2011-02-15
Medium Priority
880 Views
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

0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 15
• 11
• 2

LVL 10

Author Comment

ID: 34895700
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

LVL 26

Expert Comment

ID: 34897238

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

LVL 10

Author Comment

ID: 34897309
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

LVL 26

Expert Comment

ID: 34897401

Absolutely! Thanks.

Regards,
Brian.
0

LVL 26

Expert Comment

ID: 34897450

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

Regards,
Brian.
0

LVL 10

Author Comment

ID: 34897598
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

LVL 26

Expert Comment

ID: 34897660

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

Regards,
Brian.
0

LVL 26

Expert Comment

ID: 34898318

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

LVL 26

Accepted Solution

redmondb earned 1500 total points
ID: 34898579

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

Regards,
Brian.
0

LVL 10

Author Comment

ID: 34898621
redmondb - that looks good!

In response to your questions;

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

LVL 10

Author Comment

ID: 34898638
whoops, forgot the attachment!!
0

LVL 26

Expert Comment

ID: 34898815

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

LVL 10

Author Comment

ID: 34899270
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

LVL 26

Expert Comment

ID: 34899498

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

LVL 10

Author Comment

ID: 34900311
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

LVL 26

Expert Comment

ID: 34900906
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

LVL 26

Expert Comment

ID: 34900958
Shaz,

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

Regards,
Brian.
0

LVL 42

Expert Comment

ID: 34902956
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

LVL 42

Expert Comment

ID: 34902964
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

LVL 10

Author Comment

ID: 34905282
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

LVL 26

Expert Comment

ID: 34905347

So, no sight of your front-end?!

Regards,
Brian
0

LVL 10

Author Comment

ID: 34905393
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

LVL 26

Expert Comment

ID: 34905438

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

LVL 10

Author Comment

ID: 34905529
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

LVL 26

Expert Comment

ID: 34905603

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

LVL 26

Expert Comment

ID: 34905618

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

Regards,
Brian.
0

LVL 10

Author Closing Comment

ID: 35083100
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

LVL 26

Expert Comment

ID: 35084525

Thanks and good luck with your users!

Cheers,
Brian.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.