WHAT IS STOCK CONTROL?
Stock control, also known as inventory control, is the process of maintaining the appropriate quantity of stock, so a business can meet customer demand without delay while keeping the costs of holding stock to a minimum.
WHAT IS A STOCK CONTROL SYSTEM?
A stock control system, also known as an inventory control system, incorporates all the functions are associated with inventory management and maintenance. It should encompass everything from purchasing, product tracking, and product turnover, to storage inputs, shipping and receiving and re-ordering products.
THE PROCESS.
For the purpose of this project, the excel workbook is made up of 4 different sheets namely: Form, Purchase, Sales, and Stock. We also recorded a macro which is then assigned to the Purchase and Sales button on the user form and this allows the user to update the Sales and Purchase sheet. The steps to make a Stock Control System are explained below.
Step One: Creating a user form. We decided on the information we want on the purchase and sales sheet and made a tabular form (Date, Invoice no, Product name, Quantity). By using this user form you can organize the purchase and sales sheet. In this user form, you just have to insert the Date, Invoice no, Name of the product, and the Quantity and then click on either the Purchase button or the Sales button. The purchase and sales icon have been assigned a Macro such that once the form is filled, and either of the icons are clicked, the purchase sheet and sales sheet are updated. How the macros were assigned will be explained in the steps below. On the user form, we also created hyperlinks (a link from a hypertext document to another location, activated by clicking on a highlighted word or image) to the purchase, sales and stock sheets respectively. These hyperlinks are added to a shape (a rounded rectangle).
Step Two: Creating a purchase sheet.
We added a new sheet to the workbook and created a table with the information on the user form as the heading. We also made a hyperlink that returns to the user form sheet once it is clicked.
Step Three: Creating a Sales sheet.
A new sheet was also added to the workbook and a table was created as done in step two above. A hyperlink that returns to the user form sheet once clicked on was also created.
Step Four: Assigning a Macro and updating the purchase and sales sheet respectively.
The Purchase and Sales button were made by inserting the rounded rectangle shape to the sheet. To assign a Macro to the icons and also update the purchase and sales sheet we recorded a Macro. To record a Macro for the Purchase sheet as we have done, the following steps can be taken.
• Fill the user form and then click on the “View” tab in the Ribbon, then click the “Macros” drop-down button in the “Macros” button group, then select the “Record Macro…” command to open the “Record Macro” dialog box.
• In the “Record Macro” dialog box, enter (Purchase) as a name for your new macro into the “Macro name” text box.
• Then copy the data filled in the user form and the paste it in the purchase sheet and then insert a new row above the row that contains the data and then go back to the form sheet and delete the data filled in the form.
• Then go back to the "Macros" button on the "View" tab and select the "stop recording command".
To record a Macro for the Sales sheet as we have done, the following steps can be taken;
• Fill the user form and then click on the “View” tab in the Ribbon, then click the “Macros” drop-down button in the “Macros” button group, then select the “Record Macro…” command to open the “Record Macro” dialog box.
• In the “Record Macro” dialog box, enter (Sales) as a name for your new macro into the “Macro name” text box.
• Then copy the data filled in the user form and the paste it in the sales sheet and then insert a new row above the row that contains the data and then go back to the form sheet and delete the data filled in the form.
• Then go back to the "Macros" button on the "View" tab and select the "stop recording command".
Step Five: Create the stock sheet.
We created a table with Product, Available stock, and Status as the headings. The stock sheet shows the available stock and the status to know if the product is available or to re-order. The available stock is calculated with the SUMIF function(SUMIFS is a function to sum cells that meet multiple criteria). The SUMIF function of the Sales sheet is deducted from the SUMIF function of the Purchase sheet. The status is also gotten by the use of the IF function.
Below is the link to the excel workbook containing the STOCK CONTROL SYSTEM docs.google.com/spreadsheets/d/1bzdhR-Vqnp…
Stock Control System - Microsoft Office sidehustle #sidehustlebootcamp