We developed a purchasing database in Microsoft Access to centralize prices negotiated by buyers. The initial phase of the project was to import Excel files and later to include forecasts and consumption. It was used to produce key performance indicators and in procuring components from suppliers. ## Scope of the Discussion This article discusses the company context and the departments, actors and people involved. We review the project’s ownership and the importance of the system for corporate communications. From a technical point of view: the functions it provided, an overview of its role for management, and operationally, the challenges of consolidating multiple heterogeneous sources. We assess improvements that could be made, including a configurator to allow marketing to enter predicted sales of costed, finished products, derive component forecasts and KPIs. ![[scope-purchasing-post.png]] ## The Purchasing Context The main internal actors concerned were Marketing, Purchasing and Procurement. ![[purchasing-database-context.png]] The company sold computers directly, with orders coming in via sales representatives and distributor channels. It maintains storage depots, a logistics hub, and a production facility. ![[worldwide-purchasing.png]] The following sequence diagram shows how customers convey their forecasts to marketing, who consolidate them, send them on to purchasing, who secure volume supply commitments, and prices are then used by procurement to pass orders. ![[purchasing-context-sequence.png]] The above sequence diagram shows five main actors. The purchasing database stored component prices from suppliers, negotiated on sales forecasts. ## The Management Process KPI (Key Performance Indicator) reporting was about measuring payment terms and the number of suppliers. ![[purchasing-consolidation-process.png]] ## Purchasing database project Purchasing secured allocations against the competition based on higher forecasts but when sales didn’t follow through, unhappy suppliers might want to rescind these favourable conditions and even claw back lost profit margins. The more complex the process, the more difficult to track marketing commitments, who also interfaced directly with partners. So we started to import production figures to create accountability for volumes consumed. But this had not been foreseen in the initial specification, which was to document the flows and consolidate the prices of key components. Secondary objectives were to measure improvements using KPI outputs, but these were not defined at the outset. ## Database Requirements The requirement was to bring prices together in one place, to control quality, exclude suppliers with poor conditions, and improve cash flow. Buyers felt that it did not respond to their needs, as they had to create satellite systems to administer their daily work. Managers wanted to measure performance. Users were cooperative only when they could explain why they met their targets but also why they did not. There is better participation if the process is collaborative rather than to penalize. It was not originally intended that other departments connect to the database, and so their requirements were either assumed or imposed. The lesson is to communicate specifications in a language understandable by all. The model should have been reviewed in the round, but the project was driven by just one business area, with little external analysis. It may have been better to have an independent champion arrive at a consensus rather than a stakeholder with a vested interest. The solution is for all parties to validate a functional prototype, especially if they have conflicting priorities. It was felt for instance, that if marketing had access to calculated machine costs, they would cut margins too finely. In the end most people recognize that a single common structure is beneficial. Purchasing took possession of contracts, quality and volume to secure supply over the medium term, and this unified system gave them an identity and a significant dataset on which to base its activities. Procurement was heavily involved in meeting production requirements and was responsible for day-to-day orders, but confusion ensued when quotes contained incorrect prices. ## Forecast consolidation The process required consolidating Excel sheets from each product line into the database tables. Marketing would break down the configurations that they were planning to sell, and the import translated the names of components into those used by purchasing, and attach a forecast. 1. Import Excel sheets into an Access table 2. Search and compare item descriptions against the database 3. If found, attribute an identifier; otherwise create a new item 4. Copy temporary imports into main tables Integrating data from a variety of disparate and changing sources led to a substantial increase in complexity. Management control was insufficient to force participants to use a standard template, even though this would have reduced the effort. Despite the project’s high business profile, significant divisions existed between departments, and no one entity was able to federate all actors around the objectives. - Reporting was not real time. - One person should have been focused on analysis. - A lack of engagement resulted in delays. - The project suffered due to the variability of the process Resources should be allocated to carry out quality checks and feed back into the process. If output is not real time, this feedback was only felt in the next run, thus lengthening the improvement cycle. The cycle from customer estimate to supplier PO may have been three months. This caused problems checking negotiated volumes and prices against actual orders. ## Lessons Learned The challenge is to extract the positives while providing warnings about the pitfalls and dangers. The complexity of the monthly production cycle was a strong motivation to automate, but frustrating. The system was a victim of its own success, since the more it achieved, the more was required of it. Here are some specific and generic factors to consider. - A project needs strong management support - There may be significant resistance to change - Information is power - Management must standardize company-wide requirements and flows. - Agree a shared written model. - The information system can change habits and improve communication - Inaccurate data may lead to poor management decisions - People will resist if their data is used against them, rather than to reward good performance. - The system could have been better leveraged by everyone, not just for reporting. - All stakeholders shoul be asked to identify functional requirements. - Incremental design: the initial brief did not include all the objectives and often evolved with insufficient change control. - Decision making was taken at senior level. However, the impact was not always understood thoroughly. The database had a significant role for management, but KPIs were far too complex. - There was too much to accomplish in the available time. - We did not have good systems for defining priorities and tasks. - We didn’t have the tools to implement a shared project. - The processing schedule was not clear. - There was a mix of ad hoc and unplanned maintenance. - Staff often worked long hours. There is a strong argument to have fewer actionable indicators. <aside> 💡 Machine cost should have been calculated in the database, but there were no resources to build this because of the complex and volatile import process. </aside> ## New design including forecasting We should have been able to calculate machine costs in a configurator, in which you setup quantities for key items, accessories and peripherals. Marketing would enter finished product forecasts directly and the database explodes this into component requirements. ![[purchasing-new-version-forecasting.png]] This would allow the user to select specific items, according to rules, for each component type. ![[desktop-forecast-entry.png]] This would be the new Entity Relationship diagram, which includes the following tables: | Table name | Purpose | | ---------------- | -------------------------------------------------------------------- | | configuration | A table defining configurations | | sales_contact | marketing contact who commits to the forecast entered | | config_forecast | A table for configuration forecasts | | item_forecast | A table for item forecasts | | config_volume | A table for configuration volume actually consumed | | item_volume | A table for component volume actually consumed | | item | A list of components available to configurations | | item_commit | committed component purchase volumes from supplier based on forecast | | item_supplier | supplier who can provide the component | | supplier | list of suppliers | | supplier_contact | contacts who also commit to supply the volume of components | | | | The explosion of the configuration into item forecasts. ![[purchasing-database-new-design.png]] ## Conclusion and final thoughts The purchasing database project brought meaningful structure to complex procurement workflows, helping unify scattered data and support long-term supplier engagement. While it didn’t fulfill every expectation, it encouraged collaboration, surfaced key challenges, and offered a valuable foundation for future process refinement. It also served as a reminder that technical solutions gain strength through inclusive dialogue, flexible design, and mutual understanding. With broader alignment on clear specifications, the system could have reached further, but its contribution remains significant, especially in how it shaped purchasing into a more cohesive and data-driven function.