Purchasing Decision Support System Using Supplier Price Analysis - A Demonstration

Author(s):

Richard G. Newman, DBA, C.P.M.
Richard G. Newman, DBA, C.P.M., Professor, Rockhurst College, Kansas City, MO 64110 JI816/926-4563

79th Annual International Conference Proceedings - 1994 - Atlanta, GA

This paper provides the framework for analysis of price structure of publicly held manufacturing operations. In addition it shows the impact of price increases on individual price components as well as the use of competitive bids for target price determination. The program uses a widely available spreadsheet and is user friendly.

INTRODUCTION
The role of computers in Purchasing in the form of decision support has always been somewhat minimal in comparison to other corporate areas. In the early 50's much of the computing capability was under the control of the Accounting department. As time passed the computer applications spread to other areas of the firm. Yet, for some reason, Purchasing was still at the "end of the line" with respect to applications. The expansion of computing capability brought about by the meteoric development of the micro or PC's offer an opportunity for the Purchasing professional bridge this gap and to carry out several applications, directly related to potential cost savings.

The traditional barriers to the use of the computer have been lowered by the employment of "user friendly" spreadsheets, databases and communication devices. No longer is the buyer slaved to the schedule of the MIS department in gaining that application. It can be done "in house" in the using department. The issue now becomes the selection of the most worthwhile projects. Selection criteria could include:

1. interactive or iterative capability
2. robustness of the model
3. minimal data requirements
4. multiplicity of uses

There are several options or opportunities that may be pursued. They include:

1. supplier performance modeling
2. implementation of ABC analysis of suppliers
3. supplier price analysis

I have selected the last item on the list to demonstrate as an application of the use of the PC as an analytical tool. A brief explanation of Supplier Price Analysis is in order. The pricing structure of the supplier has always been an interesting topic. What are the sources of price information? What value is received for the money paid? How much of the price is absorbed into overhead categories? What is the true cost versus the price?

The real or true costs are obtained from the supplier's cost data. Forcing the supplier to divulge this data may be difficult. There are exceptions. Honda American Motors requires a cost breakout into direct labor, materials, overhead, tooling, sales and administrative costs and profit. General Motors has long investigated their suppliers prices and has auctioned off the business in some cases. The advantage lies in the size of their orders. Yet the majority of buyers must be content with the traditional indicators of price: bids and quotes.

SUPPLIER PRICE ANALYSIS
Supplier price analysis tries to move a few steps to the right on that spectrum. It is not a complete answer to the issue of price structure and it makes some assumptions that purists would call naive. These allegations may be true, yet the models used try to close the obvious gap between the bid and supplier provided cost data. This approach attempts to separate the pricing structure of the supplier into its components of direct labor, materials, overhead, sales, general and administrative cost and operating income.

In price negotiation, the buyer's intent is gaining the greatest value for the dollar received. In the price structure there are value enhances, value neutral costs and value detractors. Typical value enhances would be direct labor, material, quality, warranty, design efforts, distribution, storage and innovation or technology enrichment. of this list, two are measurable and the others are scattered throughout the cost structure. This is not to say that other cost elements are unnecessary. Profit is essential. It is the reward for risk taking. Yet if there are to be reductions in the product cost, they should come from the non value adding areas. Removal of costs in value enhancing areas can be counter productive. Yet when asking for a price reduction, suppliers are often guilty of taking it out of profits, as opposed to reducing costs. The term can is used because some reduction of labor via learning reduces cost without reducing value.

The issue then becomes one of "leaving money on the table." If the cost structure is not known, what are the sources of the price concessions? Are concessions being made in value enhancing areas? Examples can include material substitution, process substitution to reduce costs, lesser warranties, less engineering effort, etc.

There are obvious areas where costs can be reduced and not impact on the value received. Long term contracts negate the need for "selling the product." Since S,G&A accounts for 15 to 20% of the price of an item, selling cost reduction is simply a more equitable cost allocation.

The offer then going to the seller may reflect a desire on the part of the buyer to keep the value enhancing elements and reduce the value detractors.

Often sellers will approach buyers with price increases. These increases normally are a percentage of the price of the item. Yet the rationale for the increase is higher material costs, increased labor costs or spiraling overhead. Inherent in the price increase is an increase in profits. This is not often discussed. Having a breakout of the price structure will allow the buyer to assign the increase to the specific price part and measure its impact on the overall price.

Finally, the model must be simple to use with avoidance of exotic programming. It must be interactive and allow the buyer to query the model easily. It must be expandable to update data and incorporate innovations. It must be capable of being integrated into other sources of data such as databases. To illustrate consider the following example.

AN EXAMPLE
The decision support system is divided into two segments. The first segment is the comparison of the supplier to a significant portion of the industry. This analysis is performed to see if the supplier has any outliers in the P&L statement. This is a simple data gathering process where the analyst simply enters the data into the cells of the spreadsheet. The spreadsheet computes averages and measures of dispersion and allows the analyst to make simple comparisons.

The next portion of the analysis uses data from the suppliers annual report in one of two forms. The most recent P&L statement can be used with its data or historical data, normally ten years, would be used. In the latter case, averages are used to make the data comparable. Data taken from the annual report looks at percentage relationships between cost components. For example, what is the Cost of Goods Sold as a percentage of sales?

Once these values are determined, the next computation is the material to labor ratio. Using twenty years of historical data from the Census of Manufactures by four digit SIC code, the energy costs are subtracted from the material costs and the resulting value is divided by the production payroll cost. A sample of that data is seen in table 1. below.

Table 1.
Material to Labor Ratios(truncated)

* millions of dollars

Next this data is used to forecast the Material to Labor ratio for the next year. The tools used for developing the forecast are time series analysis using a regression model, moving averages, Fourier series or simple exponential smoothing. Normally, the time series model is useful if a trend is detected as in the case of the data set for SIC 3714, the code used in this demonstration. The decision rule for model selection is based on the growth of the ratio over the last five years. If the growth percentage averaged over the last five years exceeds the fifth year, the smoothing techniques are used. If the most recent year exceeds the average, a time series is used.

The inputs for the model then become:

1. the sellers asking price
2. the material costs in the product
3. any learning factor(curve) and when reached

Having this data, it is possible to structure an estimate of the price components as seen in table 2. below.

Table 2.
The Price of the Item as Offered by the Vendor

Once the price structure has been estimated, all types of analysis are possible. The model can be used for "Should Cost" analysis by altering the values of the overhead. The overhead is allowed to vary since all the other values have been established by the annual report data or Census of Manufactures information. Input is required by the analyst. Remember the computed rate was 493.61%. A rate of 400% is used.

Table 3.
Should Cost

Price increases can be analyzed as to their impacts on individual components. If all the increase of say 3% were to be blamed on labor, this cost element would have risen by 52%. Table 4 shows the impact of a 3% increase allocated to each individual price element. Naturally, this is not going to be the case, but it does point out the weakness in the typical arguments of the supplier in blaming a single component for the increase.

Table 4
Three Percent Increase Allocated to Individual Elements

It is possible to restructure the pricing data to give the supplier the price increase in those areas blamed for the increase. If labor has risen by 3%, what is the "bottom line" impact of a 3% increase in labor. This example allows a 3% increase in both labor and overhead to allow for "across the board" wage and salary increases. Table 5 below shows the impact.

Table 5
Price Increase Component Analysis

Thus, the three percent increase is in reality, slightly above one percent with all the relevant cost increases covered.

Finally, the application offering the most promise is that of Target Pricing and Target Costing. This technique, coming from Japan, tells the supplier how much the buyer is willing to pay for the 1, 2, 3, product. The program tests out four different approaches to gaining the target price from the supplier:

1. Proportional reduction of all costs except labor and material. This retains the value of the product by paying the full costs of value adding activities.
   
   
2. Alteration of the learning curve. Lowering the curve decreases labor costs.
   
   
3. Reduction of material costs by using the same philosophy of target pricing with respect to the materials portion of price. This "passes on" the concept backward through the supply chain.
   
   
4. The use of bids to determine the range of offering by suppliers and the variation among offerings to determine the reasonableness of the target price. If the computed target or "should cost" falls inside the statistical limits of the bid range, then it is considered legitimate to provide to the suppliers. If it falls below the lower statistical limit, re-examination of the target price is undertaken.

A summary of the first three options is seen in table 5. Table 6 shows the bid summary. Computing the standard deviation shows that, in the probability sense, 95% of the bids should fall between the mean and – two standard deviation or $94.56 – 2($2.56) or a range of $89.44 to $99.68. The target price is statistically, not unrealistic.

Table 5.
Summary of Options

Table 6.
Using First Round Bids

CONCLUSIONS
Ideally, more information should be available to the buyer. The analysis should not have to depend on macro data and assumptions. Yet suppliers are under no obligation to share this information to place us at the right end of the spectrum. Since they will not place us there, we must move there ourselves. Unfortunately, this is done in small steps and with a degree of uncertainty. Progress normally comes in small steps.

REFERENCES

1. Worthy, F.S.,"Japan's Smart Secret Weapon", Fortune, Aug. 12, 1991, pp: 72-75.
2. Morgan, M., "A Case Study in Target Costing", Management Accounting, May 1993, pp: 20-22
3. Polakoff, J.C.,"Hitting the Bull's Eye with Target Costing", Corporate controller, September/October 1992, pp: 41-43

Back to Top