Solved

Excel Permutations and Layout

Posted on 2011-02-15
28
871 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
  • 15
  • 11
  • 2
28 Comments
 
LVL 10

Author Comment

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

by:redmondb
Comment Utility
shahzadbux,

Absolutely! Thanks.

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
whoops, forgot the attachment!!
EE-Combo-Headache-2-Item-example.xlsx
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Author Comment

by:shahzadbux
Comment Utility
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
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
shahzadbux,

So, no sight of your front-end?!

Regards,
Brian
0
 
LVL 10

Author Comment

by:shahzadbux
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
shahzadbux,

Thanks and good luck with your users!

Cheers,
Brian.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now