Solved

Excel Permutations and Layout

Posted on 2011-02-15
28
878 Views
Last Modified: 2012-05-11
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:

 List

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


EE-Combo-Headache.xlsx
0
Comment
Question by:shahzadbux
[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
28 Comments
 
LVL 10

Author Comment

by:shahzadbux
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

by:redmondb
ID: 34897238
shahzadbux,

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

by:shahzadbux
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:redmondb
ID: 34897401
shahzadbux,

Absolutely! Thanks.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34897450
shahzadbux,

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

Regards,
Brian.
0
 
LVL 10

Author Comment

by:shahzadbux
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:

Mixed Salad
Greek Salad
Ceasar Salad
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

by:redmondb
ID: 34897660
shahzadbux,

<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

by:redmondb
ID: 34898318
shahzadbux,

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.
EE-Combo-Headache-Interim.xlsx
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 34898579
shahzadbux,

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

Regards,
Brian.
EE-Combo-Headache-Interim2.xlsx
0
 
LVL 10

Author Comment

by:shahzadbux
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

by:shahzadbux
ID: 34898638
whoops, forgot the attachment!!
EE-Combo-Headache-2-Item-example.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34898815
shahzadbux,

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

by:shahzadbux
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

by:redmondb
ID: 34899498
shahzadbux,

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

by:shahzadbux
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
Menu Price
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

by:redmondb
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.
EE-Combo-Headache-Interim3.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
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

by:dlmille
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

by:dlmille
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

by:shahzadbux
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?
EE-Combo-Headache-1-Large-or-2-R.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34905347
shahzadbux,

So, no sight of your front-end?!

Regards,
Brian
0
 
LVL 10

Author Comment

by:shahzadbux
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

by:redmondb
ID: 34905438
shahzadbux,

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

by:shahzadbux
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

by:redmondb
ID: 34905603
shahzadbux,

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

by:redmondb
ID: 34905618
shahzadbux,

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

Regards,
Brian.
0
 
LVL 10

Author Closing Comment

by:shahzadbux
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

by:redmondb
ID: 35084525
shahzadbux,

Thanks and good luck with your users!

Cheers,
Brian.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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

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

Join & Ask a Question