Sehar Shah '12
June 7-June11
I'm interning at JC industrial. This company retails electronics. It's a pretty big and complicated business. My first week here has been all about getting to know the company better and meeting new people.
This was the orientation week so I met the rest of the interns here and we spent some time going over the details of the company such as their focus on employees, the different departments and the types of products they sell.
This company is very employee focused. The whole company is owned by employees so that everyone that works here has a stake in the profit every month, which makes everyone more motivated to work harder. This positive attitude is evident everywhere in company. This ownership by employees defines the culture at JCI
Mike Hunter, my manager, spent a lot of this week bringing me up to date with the changes that will be taking place within the company with respect to pricing.
I met the pricing analyst that JCI hired, Shay Ruth to assist Mike with pricing decisions. She takes Eclipse reports and uses them to help the company make better decisions. Eclipse is the company's main software that controls pretty much everything. I don't know much about it yet.
Now that the strategy is down, the main thing happening while I'm here is actually implementing the strategy into the company, while keeping in mind the culture of the company and ensuring that salespeople are given enough information about the new system. After that, we have to make sure that the new pricing system is working the way it is supposed to. That is we have to ensure that this new system is actually bringing in more money than before. In order to make sure that a big change is not just made and causes the company big losses, the pricing strategy will only be implemented for tiny customers, the customers that make up the lowest twenty percent of the total revenue. This change will be tested for a month and the results will be analyzed before turning the strategy on for Small customers until eventually the strategy is active customers of all sizes.
The goal of SP is to ensure fair and consistent pricing. "Fair" in the sense that bigger customers that buy a lot more in terms of dollar amount and frequency, get a better price than smaller customers. A company that buys a thousand light bulbs should get a better deal than a customer that only buys just a couple. Interstate, the bigger customer should get a better deal than the smaller walk-up customers. Products that are very sensitive such as the 1/2 inch EMT(I have no idea what this is, but it's one of JCI's several products) should have a lower price than products that are not sold as often because customers will judge a company's pricing based on commonly bought/sold products.
So the rest of this summer I will be seeing all the pricing rollouts in Minnesota and Iowa. The first one is June 14th in Minnesota. Once the data starts to come in, I will be looking it over and coming up with a standard method of analyzing it and presenting it to the management. I haven't been given details on how any of this will be done but I look forward to starting it. I have a cubicle with a dual screen which is pretty cool since I've never worked in an actual office before.
This whole week, I loved how I was treated like an adult by everyone that works here. Everyone gets excited that I'm interning and make me feel welcome and important, which makes me want to do my job better.
June 14- June 18
Mike went into more detail with pricing strategy with me this week.
Mike decided that the company needed outside help where pricing was concerned started consulting with Strategic Pricing Associates (SPA) in December 2009.
SPA has clients such has Phillips lighting, Caterpillar, Granger and other huge companies. This company introduced JCI to a pricing model that is more of a statistical model than what JCI has traditionally used. Right now the salespeople will sell at the price they think is "fair" or "right" based on how they feel about it. These decisions are not based on the actual market price. This new strategy gauges the market value of a product based on past sales and vendor listings. The statistical model lets the company charge customers based on their type and the type of product. According to this strategy, all customers are put into certain buckets, as below.
| Customer Type | SI | Contractor | OEM | Industrial | Commercial |
| Customer Size | Tiny | Tiny | Tiny | Tiny | Tiny |
| Small | Small | Small | Small | Small | |
| Medium | Medium | Medium | Medium | Medium | |
| Large | Large | Large | Large | Large | |
| Huge | Huge | Huge | Huge | Huge |
For each of these customer Size/Type combinations, products with different prince sensitivities are given different price multipliers. Since JCI has several branches in Iowa and Minnesota, two different analyses had to be run for these two states because of the difference in markets.
I attended a color training session with a lot of the workers here and learned my personality color is orange. It was a very interesting session but I wasn't sure how it was essential to my internship except that I learned how there are different personalities and people think differently. I guess it helped me in that I now consciously realize that people are different and take instructions in different ways so you have to be careful when you are trying to get your message across and I should be more understanding if someone acts dumb. They just think differently than me!
I have also had some training sessions for Eclipse. Eclipse is the software that JC uses for pretty much everything. All sales orders and purchase orders are stored in Eclipse, such as customer data, inventory details and such. At the moment I only use Eclipse to clock in and clock out. Mike has been away for some pricing rollouts in Mankato and some other branch location has me watching the training videos for eclipse online. These took about two days, but I learned was how to move from one option to another (this is a DOS based system, so it works very differently than windows). I'm not sure yet how I'll be using this system myself but Mike keeps saying it's important.
I also had to read all the SPA presentations which were actually very interesting. They talked about the thought behind Strategic Pricing and were just more in depth than some things we learnt in my Managerial Economics class.
I'm hoping I'll actually start doing stuff next week and not spend so much time catching up with the company.
June 21-June25
This has been an interesting week.
I job shadowed two different inside salesmen. Inside salesmen are the guys that take care of customers over phone calls. They helped me out on Eclipse a lot so that I'm more comfortable with it. They showed me how to download stuff from Eclipse to Excel and where to look for certain data. This was helpful because I will need to download sales data to analyze it once Strategic pricing has been implemented.
It was interesting to watch the salespeople at work but it was definitely not something I myself would want to do. They had a lot of books in their cubicles about products from different vendors. There were all these products that sounded like they were from Mars. It would be a whole different thing to learn about JCI's gigantic product line and know everything well enough to convince customers to buy it.
I also job shadowed at the CDC. That is the Central Distribution Senter. Or the warehouse. This is where all the shipping takes place and most of the stocked products are. I shadowed the wiring department and got to see how advanced and customized their wire rolling is and I helped with shipping and picking. It was really interesting to know what happens behind the scenes once orders are placed. It also gave me an idea of what I don't want to do in life. They definitely do a good job of making mundane tasks like shipping and picking more rewarding for workers but I still don't know how someone can do that for four years as the guy I was working with has been doing.
June 28-July 2
SP was implemented in Eu Claire and La Crescent this week. Data is starting to come in.
I have started looking at some numbers now. The problem is my computer has Excel 2003 while Shay, the one who sent me some documents to show me how she has been analyzing the data, uses Excel 2007. This makes data exchange a bigger task than it has to be. So I had to change all her formulae in excel to fit Excel 2003 and that took forever because I'm more used to Excel 2007. Looking at just a few days of SP and comparing it to a few days last year is not very conclusive. This will get better with time though. The IT department told me they would update that for me but I was traveling with Mike to Minnesota for the rollouts at the end of the week so they couldn't really do that.
At the SP rollouts, Mike basically gives presentations on Strategic Pricing to salespeople in different branches. It has given me a chance to visit the different branches and see first hand the reactions of salespeople at this new system.
These reactions ranged at both extremes. Some didn't care about the change while others were furious and tried their best to make the change look bad until Mike convinced them that it was good. Some were just not willing to accept this new change, without good reason, and were hard to deal with.
These little "business trips" are so different from what I have always expected from them. The trips mean that I spend most of my time with my bosses, including dinner and drinks. You get to see a different side of the people that you work with, especially during the long road trips.
July 5- July 9
I have starting working with Eclipse a little more now. I have been downloading data from Eclipse to analyze.
This week I finally got to work on analyzing the sales to tiny customers in Minnesota. Mike showed me how to download data for all sales for any specific date range. So I downloaded data for three different date ranges and put it into excel to start drawing conclusions from it.
Mike wanted me to look at 12 days of Strategic Pricing (SP) since SP was turned on, 12 days of last month before SP was turned on and 12 days of sales last year. These three ranges are good for comparison purposes.
The only problem is that 12 days is not enough to draw solid conclusions. Also, since SP was implemented on different days for different branches, when I download data for the first 12 days of SP, some branches have not had SP turned on for all of 12 days, so data can be a little distorted. Also, since a lot of orders were made before SP was turned on and are just being invoiced now, those get included in my range as well.
These problems, of course, are minimal and will go away with time. The most important thing is that I prepare a document that will analyze any data that comes later on.
I've been working with a lot of pivot tables since that seems the most straightforward to me. I have been preparing tables and visuals to go with them. Making the tables based on any data is easy but Mike does not know much about pivot tables so I'll either have to make macros that do the work for him or explain in extreme detail how to make a specific pivot table. I could also just make a pivot table and make a macro that updates it for new data.
Right now I'm looking at:
- Profit made in every range through Tiny customers of all categories (GP$)
- Total Revenue made in each range through tiny customers in each category (Ext$)
- Total number of sales made to tiny customers in every range (Hits)
All of these numbers are categorized by Sell Override type. Since SP is only effecting Manual Overrides and products that were sold previously by Price Class. JCI basically sells products through a few different categories:
- Price Class
- Contract
- Customer Specific
- Manual Override
A Manual override is when a set price is manually overridden for another one by someone with the authority to do so. Previously, any salesperson could change the price but not prices are locked so people on the pricing team are the only ones authorized to change prices so as to decrease the number of products sold at a low price, which is a basic way of effectively implementing SP. Customer Specific and Contract are specially set up prices for customers who buy a certain bundle of products frequently.
And now there is a new one: Strategic Price. SP only affects Price Class and Manual Override, so I have to make sure I don't look at numbers from Customer Specific and manual Override.
This new system is supposed to completely replace Price class so I should see the contribution of Price Class sales decreasing until it eventually doesn’t contribute at all.
In the start of the week I had a problem with deciding how to measure my GP%. I could either just use the average GP% from my downloaded data but that would give me a distorted result because products that were small but made a higher GP% would inflate my end result. I could go the hard way and try and calculate the weighted GP% myself or just make it easy and calculate the ending GP% for every category by using the ending GP$ and Cost$. I decided to use the easy way since it's the most convenient and summarizes GP% most accurately without inflation.
July 12-July16
This week I have just been working on my own a lot. My boss is usually out of the office and I need to do this work on my own anyway.
There has just been a lot of excel tables and cleaning up everything. I have also started creating a power point so I can summarize what I have been doing. The presentation makes all my data look cleaner and summarizes it very well but I don't know how I'm going to make it a method of "standard analysis."
Shay, the pricing analyst has already made this very complex table that gives a detailed summary of pretty much everything that you would want to know about the data so I need to pay more attention to visual data than the actual numbers. While Shay is really good at getting excel to calculate the numbers, I would say her tables are a complete eye sore as Santhi Hejeebu would have put it.
My boss just has just given me a general idea of what I should be doing but I’m on my own for the rest of it.
I showed Mike how to work with pivot tables this week and he was pretty impressed. I’ve been trying to learn how to make macros but the fact that I don’t understand Visual basic at all is making this a lot harder.
Macros, by the way, are basically instructions you feed into excel so that you can make excel do the work for you. That is, I can record a macro so that tasks that are easy to do but take a lot of my time are memorized by excel so that the next time I want to do the same thing again, such as put in certain headings and change the size and fonts, I can do all of it with a press of a button, this is obviously a very simple example for the use of macros, which can actually be used to do a lot of different hard tasks but I'm not a pro at that yet.
Right now, with macros I'm trying to:
- Put headings and range number on data downloaded from eclipse
- Consolidate these sheets together into one
- Make a pivot table looking at certain numbers ((GP$, Ext$, Cost$, hits, all of these by sell override Type, in each range)
- Create summary bar graphs and pie charts to make data easier to visualize
Let's see how that turns out.
July 12-July23
Once again this has been a pretty routine week with me researching on macros a bit more. There are a lot of websites where you can get VB code for certain macros. I've been trying to understand them but it's been pretty hard. Most sites will give me the first couple pages but for more details they want payment so I'm stuck on that.
I've decided to maybe go an easier route and just make pivot tables and charts that can be used for any data. This will be easy but tedious as it requires that I do all the work by hand and then test it out several times. Because of the sheer amount of data this takes a very long time too.
For example, I created this one macro that puts in the customer size and type and range for you in all three sheets. The macro was hard to make but I did it using relative references (so that it can be used to any data). This macro on its own, which is pretty basic, takes at least 5 minutes to complete. It’s so slow! But there isn't anything that I can do about it.
July 26-July 30
I was traveling a lot again visiting the JCI branches in Ottumwa and Des Moines for SP rollouts in Des Moines and Ottumwa. These presentations can be pretty tiring but they’re always different just because different groups of salespeople will have different questions based on the different markets they think they deal with.
Some of the questions that sales people ask every single time, however are:
- Does a list of Tiny Customers get published?
- What do we do about customers that are tiny right now because they are new but have the potential to become very large customers?
- How fast will the process of getting an override be?
- What do you do in case you know you're going to have to do an override every single time?
The answers to these questions are that new customers that have the potential to become very large customers may be given overrides but they will be re-evaluated in a few years to see if they have achieved that potential. If not, they will go back to being the size that they appear to be and priced accordingly. Getting an override can be fast depending on the customer and product. There will always be someone to assist a salesperson for an override. In cases where it looks like the product price will have to be overridden every single time for that customer, the salesperson should try and change the pricing to contract or customer specific so it's more long term.
Some questions asked are very annoying such as this one guy that had been sleeping during the entire presentation asked:
- So how does this pricing affect the brand name of JCI which is supposed to sell at list price?
This question was annoying because in the aspect of branding, SP is no different than what JCI was previously doing with price classes where different products were sold at different prices, jut in a less standardized way.
August 2-August 6
I had yet another trip to another branch, this time in Sioux City and Waterloo. At least when I'm done with this internship I will have seen most of Iowa and Minnesota with al my road trips. This trip took up most of my time and the rest of the time I was just finishing up on my Excel startup sheet.
I met up with one of the IT specialists at work so he could help me figure out how to debug my macros so they can work with any data. He helped out a little bit but I'm still on my own and this whole thing is really confusing. I also have only one more week left to figure it out.
This week has been crazy because I crashed my car on my way to work on Thursday and that complicated things. Things we so normal at work though so I'm glad there's a solid line separating my personal life (like car crashes) and work life. This makes you realize you probably shouldn't talk to your boss about your personal life.
Next week's going to be crazy!
August 9- August 13
This last week has been filled with a lot of wrapping up and finishing my project.
I finalized my worksheet and presented it to Mike. I also wrote out step-by-step instructions on how to use the worksheet and how to make that worksheet from scratch. Chris was very impressed and was sure that he would be using the document a lot.
Everyone at work said that I could use them as references for future job applications so that was very exciting for me.