Without computer-aided techniques the data handling is not too efficient, it requires considerable human work, which is in turn prone to mistakes during the entry of the figures.
Support for price change management in the ERP
For a trading company which sells thousands of commodities, the selling price setting is a crucial part of the daily process. Manually managing such a high amount of data in a daily manner, reacting to all the price changes of the goods delivered by the suppliers is a time-consuming and quite tedious work for any office administrator. Still, if not done right, the very essence of the trading company, the selling margin might be affected.
Based on the purchase price the Excel program can calculate the new selling prices and prepares the upload list automatically
Calculation is one thing, but price management is usually more complicated. With the help of a customised Excel solution the benefits include:
The benefits of a customised Excel solution include:
Automatic selling price determination in Excel
I was asked to make a follow-up function to the goods receipt procedure in Excel I made for the same company. The price changes are identified during the receipt of the deliveries and all the necessary data that are needed in the next steps are entered here.
Previously all the selling price changes were manually identified, re-calculated and managed in the ERP, one by one.
You can hardly imagine a more boring procedure which is prone to human mistakes at the same time.
I made the pricing function in the same Excel file where the delivery handling resides. This way as soon as a shipment is handled, the pricing can be started automatically.
The logic of price calculation
The selling prices are tied to the purchase prices, so they react every time when the incoming goods get more expensive. Also the prices differ by shop, so the price calculation are made on shop-level.
In the list the colleague can review both the old and the new prices. He/she can check their gross margin and adjust the prices as necessary.
It is also possible to extend the list with a keypress from the product list, in case the alternative products shall receive an updated price.
Once the list is ready, with pressing a button several list are created automatically:
The technique used is a little bit of Visual Basic programming, and in a
greater part the new improvement of Excel called Power Query.
This method is thoroughly used when managing large amount of data in a well-defined way, so the automatic price determination is a perfect use-case.
