July 24, 2021

In-House Sales Order Processing Using Excel Spreadsheet

Small businesses making their first foray into internet sales are justifiably excited as the first orders start to come in. After a few weeks of steady sales however, it becomes apparent that an efficient way of processing the sales orders is the next priority. There are software packages to address this issue, but the small business owner is often unsure if the software on offer will perform all the functions that the business requires.

This article is about making in-house sales order processing software using Microsoft’s Excel spreadsheet, a component of Microsoft Office. Excel includes VBA – Visual Basic for Applications – that can be utilised by means of “macros”, or programmes within Excel. For instance, a macro can be created to perform one or more functions, such as copying and pasting, printing, inserting rows or columns, increasing a value by one, etc., all at the click of the macro button. Drop down menus and radio buttons are other examples of controls that can be created in, literally, seconds.

An easy way to become familiar with macros is to use the record-macro facility, in the Excel developer menu. This facility allows the user to record keystrokes that can then be played back and re-played by clicking on the macro’s control. Macros are also capable of including “if statements” that allow for functions to be carried out only if certain conditions apply.

The programmes can be tailor-made to reflect the requirements of the product or service. For instance, where batch recording of materials used in the product is necessary (as in the upholstered furniture industry, where this is a mandatory legal requirement), this information can easily and automatically be documented and archived for retrieval when necessary. Printouts of picking lists, packing lists, cost and price financial information, and delivery labels can all be produced at the click of a button, even using multiple printers. The time saving is obviously enormous, and automation means that consistency, accuracy, and proper process with nothing being forgotten can be taken for granted.

A cautionary word; it is not immediately apparent, when using Excel, how to find the macro facility. It is necessary to select the Developer Toolbar option from the Office button towards the top left of the screen. And – be careful! Macros carry out their functions in nanoseconds; if there is a mistake in the coding, untold damage can be done to existing programming!

Leave a Reply

Your email address will not be published. Required fields are marked *