Challenge Overview
The customer for this project is a big company working in the area of building supply and construction products. They sell a lot of various products. The business area relevant to this application is selling steel pipes on the commodity market. Products are steel pipes, which can be very different in their types, sizes and characteristics. The customer sells those products nation wide (within the USA) through their branches. The country is geographically divided between several branches, and actual sales are performed through distribution centers (DCs) of those branches. It means the customer has a wide distribution network over the country, so they can sell and deliver even the same products from various geographical places. The product price will vary significantly on the geographical locations of the branch and vendors producing those products.
Each product can be manufactured by one or multiple vendors in various locations throughout the US and abroad. Each vendor sells the product by their own prices, but vendor-price combinations do not determine the final cost of the product for the buyer at the delivery point (branch). That’s because of the product specifics – they are large steel pipes. Their dimensions and weight are so high, that transportation is a very valuable part of their final price. So, the final cost of the product depends on vendor-price and logistics, where logistics determines transportation of the product from the vendor site to the seller site (i.e. branch). Each combination of vendor and branch geographical locations are involved in determining logistics costs. Those costs are high when compared to the vendor price of products, so they become an important part of total cost.
The main idea of this business process is to minimize final cost of products at each branch. Buyers need to get competitive prices in their local market from this project’s output. That optimization is performed per each product SKU (Stock Keeping Unit) and per each branch. So, the lowest supply and delivery network is ultimately chosen per SKU of each branch and then it is used for centralized purchases of products.
The previous business process was mostly manual and it was implemented through a set of MS Excel spreadsheets where prices, freight costs, adjustments, and many special calculations were performed. There were multiple input spreadsheets with costs and rates data, calculation macros, and the resulted output pricing CSV file which was delivered to the legacy system for setting branch prices. Calculations weren’t too complex, but there is a lot of data in the tables, many variations of calculations, many details, and a great deal of data per branch. It was simply inconveniently and error prone to process so much volume of data just through MS Excel files.
The client needs more reliable and useful solution for such calculations and price data processing.
The main goal of this project is to deliver an efficient solution for centralized calculation of pipe prices data through a modern, convenient, reliable and user friendly application. Optimal prices have to be calculated, easily visualized to the user, and integrated with actual purchases through the legacy system. Part of this goal will be high flexibility, with the ability to manually adjust or replace price data, perform data searching, and special analytics – i.e. ad-hoc queries on market quotes for determining sales opportunities and possibilities according to the buyers’ requests.
Final Submission Guidelines
N/A