Intern Help: Charting & Delineation

[mflatt23]

Hey guys and gals -

Long time lurker here finally posting as I've been given quite the Excel challenge for my first week of an internship. First things first, I'm using Windows 8 and Excel 2013. Alright, so here's my dilemma:

The company I'm interning with sells compressors that come in various models. Here's an example of what a typical model looks like:



A model can be broken down into four main components: 1) displacement, 2) oil type (either E or no E), 3) motor, and 4) BOM. Within the "UPDATED Sales" tab of the Excel spreadsheet attached below, you can find all the various model types under the "MODEL" column. My task over the next two weeks is three-fold:

1. Plot price/unit (i.e., price/qty) by customer ("NAME" column) for Korea ("CTRY" column)


2. Plot price/unit by model ("MODEL" column) for Korea


3. Assign dollar values for each of the four main components by comparing the price/unit of model types that differ by only one component and then average the dollar values across the samples to create component tiers by breaking down each component into its drivers (see attachment and examples below).

For example:
ZB48KQ-TF7- 559 --> Price/unit of $100
​

ZB48KQ-TF7- 524 --> Price/unit of $101
Attribute $1 in value to the BOM component, $1 in value to 524
​

ZB48KQ-TF7-559 --> Price/unit of $100
ZB76KQ-TF7-559 --> Price/unit of $105
Attribute $5 in value to the displacement component, $5 in value to 76
​

In this example, the displacement component is worth $4 more than BOM component and 76 is the driver of displacement. Now, #3 is pretty complicated and I might not have described it very well, so I'm open to potential other solutions that arrive at similar outcomes. Here's the spreadsheet:



My questions are as follows:

1. On the "CUST PriceUnit" tab, does the line graph look fine as a start? Is there a better way to display this data visually? I still need to tweak some things.

2. What is the most efficient way to collect price/unit by model? The model types are easy to get as they are all in a column within the tab "UPDATED Sales." The price/unit data is in rows at the bottom of each customer type (e.g., column R, row 247) within that same tab, which is not as easy to collect. Only thing I can think of is manually using the "filter" option to go through each one, but there's got to be a faster way (pivot table seems like it would be the answer, but I can't figure out how).

3. Where do I even begin here? How do I go through and find models that vary by only one component? I'm pretty suck here at where to get started.

Any help that you all can provide on any of my questions would be much appreciated!!

Michael

 

[mflatt23]

Someone told me that the Excel spreadsheet was rather large, so I took out some worksheets that were uncessary. Here's the new link:

http://www.filedropper.com/sales

 

[Cosmic Wizard]

Someone told me that the Excel spreadsheet was rather large, so I took out some worksheets that were uncessary. Here's the new link:

http://www.filedropper.com/sales

1. On the "CUST PriceUnit" tab, does the line graph look fine as a start? Is there a better way to display this data visually? I still need to tweak some things.

Look brilliant, leave as it.


2. What is the most efficient way to collect price/unit by model? The model types are easy to get as they are all in a column within the tab "UPDATED Sales." The price/unit data is in rows at the bottom of each customer type (e.g., column R, row 247) within that same tab, which is not as easy to collect. Only thing I can think of is manually using the "filter" option to go through each one, but there's got to be a faster way (pivot table seems like it would be the answer, but I can't figure out how).

One question, you have different dates, do you want the models price per unit to be collected by the year of just combine them all together?


3. Where do I even begin here? How do I go through and find models that vary by only one component? I'm pretty suck here at where to get started.


Any help that you all can provide on any of my questions would be much appreciated!!

What do you mean by one component? And do you need models that vary by more than one component? IE ZB76KQE-TF7-524 and ZB76KQE-TF5-524 varies from TF5 to TF7, is that two much? You also have blank row colour yellow, are they to be included in the data, or just ignore.

Will give any help I can, just need some more info. thankyou.

 

[mflatt23]

Thanks for the reply!

One question, you have different dates, do you want the models price per unit to be collected by the year of just combine them all together?

Just combine them all together. So, for example, model # ZB76KQ-TF7-550 has a price/unit of let's say $225. That's all I need. There's just a ton of model #'s if you look at the filter.

What do you mean by one component? And do you need models that vary by more than one component?

Take a look at the examples in my first post. You'll see that ZB48KQ-TF7-559 and ZB48KQ-TF7-524 vary by only the BOM component (559 to 524). I don't need models that vary by more than one component as they said that would be too much work for now (thank god!). For example, I wouldn't need to analyze ZB45KQE-TF5-524 vs. ZB45KQE-TF7-558.

Now, models can vary within components once too (see example below). The blank row color yellow is just to provide a visual break between the customers and to provide a line where I could calculate price/unit. That might not have been the best way to go about it. So, ignore.

For objective #3, I've started to use LEFT/RIGHT/MID/SEARCH functions to break the four components into columns, but I'm getting a little stuck with Motor and BOM. Attached the updated spreadsheet here: http://www.filedropper.com/sales_2

Ps- Your example is correct. In that case, the model only varies by the Motor component (TF7 vs. TF5). Other examples would be: ZB76KQ-TF7-550 vs. ZB29KQ-TF7-550 and ZB45KQE-TF5-524 vs. ZB45KQE-TF5-558.

 

[mflatt23]

Thinking about it some more, if you look at the "Tier Drivers" worksheet, it seems like I need to now add a "Price/Unit" column that corresponds with each model type to complete objective #2. The problem is that my formatting isn't great right now to extract price/unit data with model type since price/unit data is in rows (the yellow highlighted rows within the "UPDATED Sales" worksheet) and is calculated by month.

To complete objective #2, it sounds like it's basically the same as objective #3, but I just need two columns of model type and price/unit. For objective #3, I need columns of not only each model type with its corresponding price/unit, but also columns of the 4 components broken down (displacement, oil type, etc.) so I can then create a pivot table.

 

[Cosmic Wizard]

For objective #3, I've started to use LEFT/RIGHT/MID/SEARCH functions to break the four components into columns, but I'm getting a little stuck with Motor and BOM. Attached the updated spreadsheet here: http://www.filedropper.com/sales_2

So we are on the same page, ZB45KQE-TF5-524 need to be broken up into how many sections?

Is it ZB45KQE-TF5-524, or do you also need the 524. Below is some example we can use.


ZB45KQE-TF5-524 =TRIM(LEFT(SUBSTITUTE(MID(B5,FIND("|",SUBSTITUTE(B5,"Z","|")),LEN(B5)),"-",REPT(" ",LEN(B5))),LEN(B5))) gives us ZB45KQE

Then ZB45KQE becomes =TRIM(LEFT(SUBSTITUTE(MID(B5,FIND("|",SUBSTITUTE(B5,"-","|",1))+1,LEN(B5)),"-",REPT(" ",LEN(B5))),LEN(B5))) becomes TF5.


If this is not what you want we can easily change it. Have to go home now, will clean it up tomorrow.

It a simple process once we can extract the data

 

[mflatt23]

Wow great formula! Going through it all now. ZB45KQE-TF5-524 should be broken up into the following parts:

ZB45KQ - Displacement

ZB45KQE - Oil Type (either E or no E - not every model has an E)

TF5 - Motor

524 - BOM

See the Tier Drivers worksheet of the updated spreadsheet here for how I've broken it up thus far (and used your formula!): http://www.filedropper.com/sales_4

I think I finally figured out objective #2 by taking YTD QTY and YTD Price data and combining it with model # (MODEL PriceUnit (calc) worksheet). I then used the consolidate function to combine the overlapping model #'s (MODEL PriceUnit (final) worksheet). Still trying to figure out if there's a good way to graphically display 95 models

For objective #3 (Tier Drivers worksheet), I still might need some help on how to go about creating a pivot table that creates a pricing mix analysis (e.g., Oil type E is worth $1 for every X unit.)

 

[mflatt23]

Crap, just missed the 10 minute edit period. I ended up using an area chart to graphically display the 95 models (seems to work fine).

 

[Cosmic Wizard]

Crap, just missed the 10 minute edit period. I ended up using an area chart to graphically display the 95 models (seems to work fine).

Here we go.

Motor: =TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",1))+1,LEN(A2)),"-",REPT(" ",LEN(A2))),LEN(A2)))

BOM: =TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",2))+1,LEN(A2)),"-",REPT(" ",LEN(A2))),LEN(A2)))

Oil Type: Two Formulas, wasn't sure if you wanted it to say it was an oil type or then the actually code so here goes:

First Column: =IF(ISERROR(FIND("E",A3,1)),"","Oil Type")

Second Column: =IF(E3="Oil Type",(MID(A3,FIND("E",A3)-6,7))," ")

These two formulas show blanks if there are no E, but you can change that to text if you need it.

Displacement: Your formula works just fine: =LEFT(A3, SEARCH("q",A3,1))

Then you can do a Vlookup to match the components, say 550, or TF7 to their prices.

And if you want to get the number in the string, =MID(J2,3,2).

Then you can you use =IF(E2="","",VLOOKUP(E2,Sheet1!$G$2:$H$2,2,FALSE)), if you want to get rid of the #N/A for those Models which do not have oil.

If you need anything else just post, but remember I am in Australia and its Friday and at 3.00pm we are going to the pub!

 

[Cosmic Wizard]

Wow great formula! Going through it all now. ZB45KQE-TF5-524 should be broken up into the following parts:

ZB45KQ - Displacement

ZB45KQE - Oil Type (either E or no E - not every model has an E)

TF5 - Motor

524 - BOM

See the Tier Drivers worksheet of the updated spreadsheet here for how I've broken it up thus far (and used your formula!): http://www.filedropper.com/sales_4

I think I finally figured out objective #2 by taking YTD QTY and YTD Price data and combining it with model # (MODEL PriceUnit (calc) worksheet). I then used the consolidate function to combine the overlapping model #'s (MODEL PriceUnit (final) worksheet). Still trying to figure out if there's a good way to graphically display 95 models

For objective #3 (Tier Drivers worksheet), I still might need some help on how to go about creating a pivot table that creates a pricing mix analysis (e.g., Oil type E is worth $1 for every X unit.)

With the BOM number, you will have to convert it to a number for a Vlookup.

And whilst we are here =IF(F2="",I2+L2,F2+I2+L2) to a get the price of ZB76KQ-TF7-550, which does not have E and returns a blank cell when we are looking for E!