Excel programming – Automatic price change

Price change automation in Excel
In the life of a trading company price changes happen regularly and in mass. With well-defined rules the procedure can be effectively automated.

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:

Prices will be calculated on-the-fly, when needed, e.g. the purchase price or exchange rate changes
No missed item, all affected product is covered
Separate rules can be assigned based on scenario, shop, product group or any other factor
The upload list towards the ERP (Enterprise Resource Planning) system is created automatically
The personnel is not obliged to use complex Excel files – just presses a button and the computer does the rest

Automatic selling price determination in Excel

Shortcuts and buttons during operation
Shortcuts and buttons during operation

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:

Dynamically populated selectors
Dynamically populated selectors
One for this individual delivery – only for history reasons
Összefoglaló az aznapi összes szállítás árváltozásairól
A summary for all the price changes from all the deliveries that day
Individual lists for every shop, so the shop managers could refresh the prices on the shelves.

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.

Scroll to Top