IDIS 4785 – STRATEGIC PRICING FOR DISTRIBUTORS Term Project – Fall 2016

1.0 BACKGROUND:IDIS Adhesives, Inc. is a mid-size industrial distributor serving the California market.Currently, the company carries one line of products – hot melt adhesives. There are 12 differentproducts in this line. The four major market segments for these products are: new construction,packaging manufacturers, furniture manufacturers, and transportation.Ten salespeople cover different areas of the market throughout the state. In 2014, the totalCalifornia market size was 105.1 million pounds and IDIS Adhesives sold just over five millionpounds. Market projections indicate a growth to 131 million pounds by 2022 as hot melt adhesivesbecome more popular than volatile organic compounds (VOCs) due to environmental reasons.Ethylene vinyl acetate adhesives (EVAs) are the biggest selling hot melt adhesives. A list of some ofthe big players in the industry is listed in section 7.0.Currently, IDIS Adhesives is pricing its products using a cost plus strategy. The companycalculates its prices as mark-up on cost. The CEO of IDIS Adhesives recently read an article onstrategic pricing and believes there is an opportunity to increase profitability of the company. TheCEO has been reading about distributors in other parts of the country who have been successful inincreasing profitability by adjusting the pricing of their products. He is uncertain about how toproceed and has decided to hire a few student who have taken a course in Strategic Pricing forDistributors to help him price his products strategically. He plans to later hire the students who comeup with the best pricing strategy that can be sustained in the long term. 2.0 AIM:You have been provided with IDIS Adhesives’ sales transaction data from an ERP / databasewhich has been downloaded into Excel. Using the Excel spreadsheet provided, your group willfollow the principles and steps of Strategic Pricing for Distributors as outlined by Brent R. Grover inhis book, Strategic Pricing for Distributors, 2009. 3.0 SCOPE:Transactional data has been downloaded as an Excel file for students. Students will berequired to analyze the data, make pricing recommendations and prepare a report. Students will useExcel software to: Sort and sub-total the dataConduct Pareto Analysis and create Pareto ChartPrepare Scatter PlotsCreate a Pivot TableCreate a VLOOKUP Table Instructor: Jeanne Lawrence Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logistics4.0 RESPONSIBILITIES:4.1 This is a team project. Students will work in assigned groups of two. Please see the list of teamsand associated product classes. 5.0 PROCEDURE:You have been provided with a set of transactional data (TransData) for sales generated over aperiod of 12 months. Do not work off the data set. Instead, copy and paste the data set into a newworksheet as necessary before you manipulate the data for each step of the assignment. Always keepa copy of the original data set.5.1 Sort the Transaction Data Copy and paste the transaction data into a new sheet in your workbook. Label the tab5.1Sort. Insert two columns at the end of the data set. Label the first column ‘Price’ and thesecond column ‘Sales Revenue’. Calculate the price by multiplying the cost of productby the salesperson’s multiplier. Calculate the sales revenue for each transaction bymultiplying the price by the quantity. Sort the data by segment in ascending order (from A to Z).5.2 Prepare scatterplots for products in each segment (See Pg. 33 of Grover) Open a new sheet in your workbook and label it 5.2Scatter. Select four SKUs that are being sold to customers in all the segments. Using the data from 5.1, create a table for each SKU across all segments. The tableshould include: SKU ID, SKU description, customer description, total revenue generatedby each customer regardless of product purchased or segment (this is the sub-total in 5.2). Add another column to the table. Include the price that each customer pays for theparticular SKU. Using only the data on Price and Customer Revenue, prepare a scatterplot for each SKU.Plot price on the y-axis and customer size on the x-axis. Embed each scatterplot on the right side of the worksheet adjacent to its data table. Instructor: Jeanne Lawrence Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logistics Note your observations. See pg. 33 of Grover.5.3 Sub-Total Revenue by Customer Copy and paste the sorted transactional data from 5.1 into a new sheet in your workbook.Label the tab 5.3CustRev. Sort the data first by segment, then by customer sales. Using the sub-total function inExcel (do not enter formulas), sub-total the sales revenue for each customer. (You willneed this information to classify your customers).5.4 Sub-Total Revenue by Product Copy and paste the sorted transactional data from 5.1 into a new sheet in your workbook.Label the tab 5.4ProdRev. Sort the data first by segment, then by product sales. Using the sub-total function inExcel (do not enter formulas), sub-total the sales revenue for each product. (You willneed this information to classify your products).5.5 Conduct Pareto Analysis and prepare a Pareto Chart to classify Customers Using the data from 5.3, on a new sheet in your workbook create a table for each customersegment with customer name and sales revenue generated by each customer. Label this sheet5.3ParetoCust. Conduct a Pareto analysis of customer sales revenue for each customer segment. Hint: Toconduct a Pareto Analysis, you must first rank sales revenue in descending order. Classify your customers within each segment based on buying power as shown below:Customer SizeXLLMS % of Total RevenueTop 60% of company revenueNext 25%Next 10%Bottom 5% 5.6 Conduct Pareto Analysis and prepare a Pareto Chart to classify Products Using the data from 5.4, on a new sheet in your workbook create a table for each customersegment with SKU ID, SKU Description and Sales revenue generated by each product.Label this sheet 5.6ParetoProd. Use the data obtained in step 5.3 to complete the table for each segment. Instructor: Jeanne Lawrence Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logistics Conduct a Pareto analysis of product sales revenue for each SKU (product). Hint: Toconduct a Pareto Analysis, you must first sort rank sales revenue in descending order. Classify your products sold within each segment based on price sensitivity as shown below:SensitivityCore1Core2NonCore3NonCore4 % of Total RevenueTop 60% of company revenueNext 25%Next 10%Bottom 5% Create a Pareto Chart.5.7 Research appropriate gross margins for your products Using the Internet, research appropriate gross margins for companies in similar industriesselling similar products. See section 8.0 on companies in the industry. Hint: Use theIncome Statement of the company you plan to use for comparison. Determine floor and ceiling margins for each customer segments. (For this step, you willneed to decide what is appropriate based on experience and other facts. Remember thatpricing is both an art and a science. Please discuss as a team. Your final report needs tohave a well-articulated discussion on how you determined the floor and ceiling marginsfor each segment, taking into consideration, the company’s size, competition andcustomers and other data pertinent to strategic pricing.5.8 Prepare a Margin Lookup Table On a new sheet in your workbook, prepare a Margin Lookup Table for each customersegment. Label your worksheet 5.8MarginLookup. Based on your research in 5.7, insert your recommended margins into the Margin LookupTable so that your table for each customer segment looks like one side of the BusinessIntelligence Cube on pg 118 of Grover.5.9 Prepare a Pivot Table for each customer segment On a new sheet in your workbook, prepare a Pivot Table for each customer segment.Label your worksheet 5.9Pivot.Note: Once created, your Pivot Table will look similar to the Margin Lookup Table.However, to create the Pivot Table, your data must be organized differently. Prepare a table with the following headings: Customer Size, Product Sensitivity,Recommended Margins. Enter the data from step 5.8. Instructor: Jeanne Lawrence Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logistics Create a pivot table for each customer segment that will allow you to look up therecommended margins based on customer size (S, M, L, XL) and product sensitivity(core1, core2, noncore3, noncore4).5.10 Price your Products and Analyze Profitability Open a new worksheet and label it 5.10Price. For each segment, enter the product, product cost, product sensitivity, customer name,customer size, quantity purchased by customer, and recommended gross margin. Calculate the new price using the Cost-plus Gross Margin method (review your notes on‘Calculating Transaction Prices.’ Calculate the new total revenue. Compare this revenue to the revenue obtained under the old pricing method. 5.11 Prepare a VLOOKUP Table Prepare a VLOOKUP Table for the target prices set in 5.10.6.00 DOCUMENTATION: Prepare a report in the format shown on page 195 of Grover. You should use the sameformat but tailor the report to be relevant to this project. In your report, you shouldinclude the following: The phases and steps of strategic pricing as outlined by Brent R. Grover in hisbook on ‘Strategic Pricing for Distributors.’ Important principles of strategic pricing discussed by both Grover (StrategicPricing for Distributors) and Nagle (Strategy and Tactics of Pricing) and howthese principles were applied to your project. The steps you took to analyze the data Your observations related to the analysis of the data:-Who are Nelson Industrial Distributors’ extra large, large, medium and smallcustomers. How much sales revenue does each customer size category generate? Interpret your scatterplot diagrams. What are your observations about the company’scurrent pricing strategy? What have you observed about the company’s gross margins? Why did we begin by sorting the data based on customer segment? Instructor: Jeanne Lawrence Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logisticsooo Your specific pricing recommendations (what prices should be charged and why)Impact of a new pricing strategy on the company’s profitabilityConclusion Embed your excel spreadsheet into your word document in the appendix. Upload a single WORD file to BlackBoard labeled as follows:GroupX_Lastname1_Lastname2_LastName3_LastName4.*I must be able to open and read your file to grade it. I will not inform you thatyour file cannot be opened and will not request that you send me a new or differentfile.7.0 GRADING RUBRIC:Please pay special attention to the grading rubric below. Additionally, please be aware thatlate work will be subject to a 5 point penalty for each calendar day late.PointsPossible5.15.25.35.45.55.65.75.85.95.105.115.12 EXCELFormat and PresentationSorting the Transaction DataPrepare scatterplots for products in each segmentSub-total revenue by customerSub-total revenue by productConduct a Pareto Analysis and Preparing a ParetoChart to classify customers based on size.Conduct a Pareto Analysis and Preparing a ParetoChart to classify products.Calculations of gross margins for a distributor sellingcleaning productsPrepare a Margin Lookup tablePrepare a Pivot tablePrice products and analyze profitabilityPrepare a VLOOKUP tableREPORTFormat and PresentationIs the Excel spreadsheet correctly embedded as an iconin the Word document and can it be opened?Discussion of strategic pricing principles in generaland relative positioning within the target market, withconsideration to company size, product sensitivity andcustomer sizeDetailed explanation of how margins were determinedfor each segment, with references providedTOTAL Instructor: Jeanne Lawrence ActualPoints 1.500.501.000.500.501.251.251.001.501.501.501.501.502.002.00 1.0020.00 Revision 1: 10/16/2016 EAST CAROLINA UNIVERSITYDepartment of Technology Systems: Industrial Distribution and Logistics 8.0 RESOURCESMajor Companies Supplying the California Adhesives Market: The 3M CompanyAmerican Chemicals, Inc.Worthen Industries, Inc.Ashland, Inc.Wisdom AdhesivesAvery Dennison Corp.Jowat AGDow Corning CorporationEvans Adhesives Corp. Ltd.H.B. Fuller CompanyDynea OYHenkel AG & Company KGAACostChem SRLHexcel CorporationBostik, Inc.Huntsman CorporationBEMIS Associates Inc.SIKA AGBeardowADAMS (Adhesives), Ltd. 9.0 OTHER RESOURCES: Links on ‘How to’ Excel videos have been placed on BlackBoard under Course Documents in afolder labeled ‘Excel Resources’. If you have forgotten how to use Excel, please use theseresources to help you through the steps of the project. Please note that this course is not about teaching you how to use Excel. You should already knowhow to use Excel from your previous classes. I will only address concerns related to the use ofExcel after you have reviewed the resources, tried the analysis yourself and can pinpoint whereyou are having problems. 10. REFERENCEShttp://www.adhesivesmag.com/articles/94321-california-hot-melt-adhesives-market-to-see-modestgrowth-through-2022). Instructor: Jeanne Lawrence Revision 1: 10/16/2016