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

 

[Cosmic Wizard]

Actually on second thought try this formula with the oil =IF(ISERROR(FIND("E",A2,1)),"",MID(A2,1,7))

Will return blank cell if you have no E and the the 7 text string if you do!

 

[mflatt23]

Alright I'm trying this all out now and will let you know! Enjoy the pub

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

Do you mean the Motor?

 

[mflatt23]

=IF(F2="",I2+L2,F2+I2+L2)

So how would that formula look for the column A2, B2, etc.? In other owrds, F2 = oil type, I2 = ? L2 = ? Thanks!

 

[Cosmic Wizard]

Alright I'm trying this all out now and will let you know! Enjoy the pub



Do you mean the Motor?

No the formulas i gave you return for Motor and BOM say TF7 & 550
Because TF7 has letters the vlookup can search for it and return a value, whilst 550, although it look like 550, will return #N/A because some formulas can't be vlookup.
You need to copy and paste the 'numbers', maybe in a new column, and then convert the text into numbers for the vlookup to work.

 

[Cosmic Wizard]

So how would that formula look for the column A2, B2, etc.? In other owrds, F2 = oil type, I2 = ? L2 = ? Thanks!

Basically lets says some components have oil and some do not, which means that some will have an added price.
If you use the formula =IF(ISERROR(FIND("E",A44,1)),"",MID(A44,1,7)) to see if there was an E (oil) in the cell, you would get a blank if there was not.
I presumed that both Motor and BOM can also return different prices.
So to get the full value of the component, you need to add the price of oil, if applicable, and both the Motor and BOM prices.
But if you use a =SUM(G2+I2+L2) for the three cells, and there is no oil price but a blank cell, that =sum formula will return #VALUE!.
To avoid this and add up the price correctly, you use =IF(G2="",I2+L2,G2+I2+L2).
Basically if there is blank in the Oil cell, it will add up only the Motor and BOM cells.
But if there is a number, it will add up all three prices of oil, Motor and BOM.
Hope that helps!