Assignemnt: Strategic Sourcing Case- Data Base Manipulation, Analysis, and Presentation to Management
Introduction
Enclosed with the case is a Microsoft Excel database of information relating to dollars spent by our Program Partner, MillerCoors, in Maintenance, Repair, and Operations (MRO) purchases in FY '14 (actual spend and pricing has been changed to protect proprietary MillerCoors information).By definition, MRO purchases are so-called "indirect" purchases in contrast to "direct" purchases indicating these are purchased items that support the process of brewing and distribution (as opposed those items that go directly into the cost of goods sold).
Data was pulled from two different systems of record - the legacy Coors and the legacy Miller SAP systems. Strategic Sourcing analysis is then to be done (where possible) across the enterprise: 8 breweries, 1 glass plant, 1 can plant, and 1 end plant.
Each record (row) in the data set represents a purchase order/line item of purchase with suppliers. With the logical inclusion of vendor name and amount paid, come a couple dozen of other fields describing the plant (brewery), the spend category (sub-category 1 & 2), and other details describing the purchase and its characteristics.
There are over 124,000 records in the original table entitled "MillerCoors MRO Spend Analysis Data 081814". Your mission is to decide what records and fields to include in your spend analysis, then manipulate the remaining data using MS Excel to in turn, create your final Powerpoint presentation and set of recommendations.
You are trying to give the executives at this company evidence of several issues that exist in current procurement practices and what can be done about them. Typical issues in procurement practices include:
• The total spend in a spend category is large for the enterprise but individual buying groups (e.g., Plants) inside the company are not aware of the size of the spend or how to leverage spend data to secure better prices for their purchase
• The total number of suppliers is very large, in total and for each spend category. The company cannot create better spend advantage because they split their spending among many suppliers.
• Individual plants inside the company do business with the same supplier unbeknown to each other. As a result, one group's success in negotiating a price for goods or services is done at the expense of the other group's price.
• The spend categories to focus on to get the best value are unknown ... thus we (the company) may be spending our efforts on consolidating the supply base in areas where the spend or relationships don't matter.
• Purchase Orders (PO's) written to the same vendor in the same day. This is indicative of duplicate PO expense that may be avoided. A simple Google search around the average cost of creating a PO times the number of occurrences would yield a savings opportunity for the company if purchasing could be better coordinated or simplified by Legacy-Company or enterprise-wide.
• Different breweries (plants) use different suppliers for the same part thus fragmenting spend. How much was spent with companies with higher prices for the same part?
• The spend with a supplier for a high-value part or for a high volume of the same part is significant. Is there an opportunity to have the part stocked by the supplier (VMI) vs company-purchased and stored?
• Are there high-volume or high-value parts (e.g., repaired/spare rotables) that might be pooled or shared rather than held by each plant?
• Many others I'd like you to think about.
You are to make a presentation to management pointing out facts about corporate procurement, the implications of that data, and the conclusions and recommendations you would make given the opportunity.
Your assignment is to:
1. Identify the set of fields you need from the data set to complete your analysis. You are to use Sub Category 1 as your buy-classification system.
2. Manipulate the data in Excel so that you are able to answer these questions:
• Total spend by Plant
• Total spend by Supplier
• Total spend by Sub Category (1) by Legacy Company
• Vendor Count by Sub Category (1) across the Enterprise
• Spend and number of Plantsusing a spend class (Sub Category 1)
• Spend by Sub Category (1) by Plant
• Top 10 Sub Category (1) by Business Group
• Any Sub Category 2 analysis (within a plant or cross-legacy company) that you find interesting that might pose additional savings
• Spend by Supplier by Plant (sort on Vendor name and run all the Plants that use that Vendor and for what Sub Category (1)
3. Use these various analysis to prepare the power point presentation that should include at a minimum:
• Introduction/Objective/Study Parameters (what time frame, number of records, what was included, excluded, etc)
• List of Key Findings
• Total number of Sub Category titles (spend classes) engaged, number constituting 80% of the spend (Pareto chart)
• Total number of suppliers used and number of suppliers required to constitute 80% of the spend
• Top 10 spend classes (Sub Categories (1)) and amount of spend by category
• Median number of suppliers for thetop 10 Sub Categories (1)
• Number of Suppliers by Plant
• Examples where different Plants use the same Sub Category product and the value of the total spend among Plants in that Sub Category (1)
• A list of all the suppliers one Plant uses in one leading Sub Category (1) class. Then detail what the opportunity is or what additional data is necessary in order to recommend action.
• A preliminary "bubble chart" with waves depicted.
4. This case is primarily about analyzing data and preparing a presentation for management. However, it also (to a minor extent) is about getting and manipulating data which is often the most difficult thing to do.
As a result, I want you to focus on the analysis, and presentation preparation. Spend a smaller percentage of your time "cleaning and perfecting" the data for later manipulation. Some nominal cleaning you should consider includes:
• This is an MRO study. As such you should eliminate Direct Material (sub category name) from analysis consideration.
• The following Material Groups are considered Direct Material and as such, should be excluded from analysis:
- SHIPMTL, XB-CARR, XB-CRTN, XB-CRWN, XB-LBL-B, XB-LBL-N, XB-SLVE, XBTL, XB-TRAY, XCAN, XCAN-ABTL, XC-CONE, XC-DIV, XC-END, XC-TRAY, XKEG-DEP, XKEG-DUST, XOTHER.
Other notes regarding the data:
• PO's that start with "CR", "CRG", "E", "H", or "GRP" are repaired items (West) - "West" is a term generally associated with Legacy Coors Plants
• PO's that start with "75" are for services (West)
• PO's that start with "45" are for parts (West)
• PO's that start with "65" or have an "Order Price Unit" of $$ are for services (West)
• PO's that start with "63" are for parts (East) "East" is a term generally associated with Legacy Miller Plants
• PO's that start with "10" are for parts (East) unless "order Price Unit" is $$
• OEM parts must be purchased from OEM's unless the same part has been purchased (at least once) from an aftermarket company. In this case, the part can (forevermore) be purchased on the aftermarket (hint: generally at a deep discount over its OEM equivalent).
• The Vendor Number is UNIQUE across the Enterprise
• The Material Number is UNIQUE across Legacy Companies
• Note carefully possible unit-of-measure discrepancies.
• At this time, we do not have a mechanism in the data to know any "parent/child" relationships among vendors. In this case, a vendor (child) is owned as a subsidiary of another vendor (parent) on the list. While the category of spend may be completely different in buying from the two companies, knowing the parent/child relationship can offer an opportunity as "total spend with the parent" is a possible negotiation point. This bullet is offered to include in your quiver of possible Strategic Sourcing strategies in future analysis ... not for inclusion in this assignment.
• Repaired item (material) numbers may be the same as new item (material) numbers but prices should not be compared as "identical item disparities" as the price of a repaired item may be dramatically different than equivalent new item.
Record - Field Name Definitions (column designation)
• A - PO Number - unique identifier for a purchase
• B - Line Number - unique PO Line number identifier
• C - Vendor Number - unique identifier of a supplier - unique across enterprise
• D - Vendor Name - unique and scrubbed name (that is there are no AT&T vs A.T.& T. problems) associated with C
• E - Material Group - field sometimes used to delineate the type of purchase (e.g. direct vs indirect classification types)
• F - Material Number - or Item Number. Unique identifier for part purchased. Unique by Legacy Purchasing company
• G - Purchasing Organization - Is currently a worthless field identifying the buying company is Miller Coors (as opposed to another company that may be added to the limited partnership later).
• H - Purchasing Group - Is an identifier code for the Purchasing Agent that wrote the PO
• I - Plant Number - Unique identifier for the plant that did the purchase
• J - Plant Name - unique and scrubbed name associated with I
• K - Legacy Co - in this case means either Miller or Coors
• L - Storage Location - typical location for storing this item in MillerCoors facilities
• M - Document Date - PO generation date
• N - Order Quantity - amount ordered
• O - Order Unit - order unit of measure
• P - Price/Unit - price paid per unit of measure O
• Q - Order Price Unit - unit of measure the price of the produce is quoted in
• R - Total Cost - extended quantity x price per unit
• S - Currency - type of currency used for the transaction
• T - Purchase Document Type - Unknown at this time (8/16/14)
• U - Short Desc Text - Short (free format) description of product purchased (may vary for same item purchased depending on PO writer
• V - Sub Category 1 - Main Purchasing Class Description (used for classification grouping and sourcing team development)
• W - Sub Category 2 - Sub Purchasing Class Description (e.g., PVF is the Sub Category 1 description for Pipes, Valves, and Fittings. "Elbow" is the Sub Category 2 description for the group "pipe elbows" that are purchased under the category of PVF.)
• X - OEM/Aftermarket - Description if the item is an OEM part or an equivalent for a part purchased in the aftermarket. OEM specified parts must be purchased by the OEM vendor so as to maintain equipment warranty.
• Y - Repaired/New - Description if an identical part is repaired or new such that the discrepancy (typically large) in price paid may be properly explained.
Format your assignment according to the following formatting requirements:
1. The answer should be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides.
2. The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
3. Also include a reference page. The Citations and references should follow APA format. The reference page is not included in the required page length.