How do you keep records of your daily sales? In a notebook? Notepad? Excel? How long does it take you to prepare a sales and purchases report? are they done daily, weekly or monthly? Do you run a one man retail or wholesale business and you want to track your daily Purchases, Sales and Stock balance? Are you a Sales Person and you receive Sales report on Excel from various sales point? With the use of Specially Designed Excel Template you can quickly put a whole lot of reports together. We have put together a little Template on Excel with a whole lot of features to enable you record your daily transactions (Purchases & Sales) and view your daily current Stock level. This Template was designed to meet up with the recent demands from our subscribers who have used our STOCK-INVENTORY MANAGEMENT TEMPLATE 1. The Stock-Inventory Template was designed to manage your stock balances on a daily basis. So, we thank our Subscribers for the encouragement and feedback. Here is an Excel Template that can manage your Stock and still give you a Gross Margin Report.
Before you continue with this article, kindly read up HOW TO MANAGE STOCK/INVENTORY WITH EXCEL because the Templates share the same formats and headers. I will not be discussing the Headers and Features already discussed in the Stock Management Template. I will only discuss the ADDITIONS MADE.
The Additional features of this Template include;
1. Recording Purchases
2. Recording Sales
3. Scanning Product Bar-codes or Typing in Product Codes
In addition to the Stock Management Template this Template enables you to do the following;
1. Purchase
2. Sale
3. Obtain Gross Margin
4. Check Daily Transactions
Let’s get started with the Template. Get the Template on our FREE TEMPLATES PAGE on ITEM NUMBER 8.
When you Download and open the Template, click on ENABLE CONTENT if it appears. Also "OK" the "ALLOW EDITING" if it appears.
The first page you will see is the WARING PAGE.
When you Download and open the Template, click on ENABLE CONTENT if it appears. Also "OK" the "ALLOW EDITING" if it appears.
The first page you will see is the WARING PAGE.
Click on "NEXT" and move on to "SET UP"
1. SET UP THE PRODUCT CATEGORIES OR GROUPS
Just type in the Categories of Products you have under the "Product Group" and the S/N will generate automatically. You can only type in 30 Product Groups here.
2. SET UP THE BUSINESS PARTNERS:
The Partners include Staff, Customers and Suppliers.
Type in the Partner's Name under the "Partner Name' Column and Select a Partner Type from the Drop down list.
3. TYPE IN THE NAMES OF THE PRODUCTS:
You can list up to 500 Products!
You need to list all your products under the "Product Name" column. The Product ID will generate automatically. You can use a Bar-code Scanner to get the Product Code. Kindly double click inside the cell under the "Product bar-code" column before using the scanner to scan. If there are no bar-codes or scanners, you can type in any product serial number you find on the product or you leave that column empty. Then you select a Product group under the "Product Group" column. The rest will update automatically. Your product balance will only update when you start keying in products. If you have done this correctly you should have something like this;
4. Enter Company Details:
After you have done so, your "SET UP" Page will look like this below;
Next you click on the "RECORD TRANSACTION" Button and it will take you to the DATABASE where you key in the Transactions directly. If you have staff who will be keeping this record and you wish to lock up the database so that the staff can record and post transactions by use of a USER FORM like the one below then you need to contact TheAccountSolutions on +234 90 8364 9810 so that we can customize this Template specifically for you at a very affordable fee. You can also send a mail to theconsultsplatformsolutions@gmail.com.
5. RECORDING TRANSACTIONS
To understand how the headers work on the RECORDING SHEET, kindly go to the Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE. Below are headers to this Template;
a. Date Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
b. Invoice Number Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
c. Type Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
d. Partner Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
e. Product Name Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE. Note that this has been changed to "Product Bar-code. You can receive or sell Products with a Bar-code scanner or just selecting the Product Code from the drop down list. To scan, kindly double click on the cell and then scan the product.
f. QTY Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
g. Unit Amount Header: See
Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
Below is an introduction to the headers;
h. Transaction Header: This is found between the Invoice Number Header and the Type Header.
All you need to do is select a transaction (Purchases, Sales, Sales Returns, Purchases Return, Stock Adjustment In & Stock Adjustment Out) from the drop down list on the selected cell as shown below;
Below are Picture to aid you record your transactions with the HEADERS;
After recording your transactions, your database should look like the picture below;
Additional Tips:
a. If you select "Purchases" under Transaction, you need to Select "Stock In" under Type
b. If you select "Sales" under Transaction, you need to Select "Stock Out" under Type
c. If you select "Sales Returns" under Transaction, you need to Select "Stock In" under Type
d. If you select "Purchases Return" under Transaction, you need to Select "Stock Out" under Type
e. If you select "Stock Adjustment In" under Transaction, you need to Select "Stock In" under Type
f. If you select "Stock Adjustment Out" under Transaction, you need to Select "Stock Out" under Type
6. THE DASHBOARD:
As you record your transactions on a day to day basis you need to go to the Dashboard and view your reports. To Update the Reports on the Dashboard all you need to do is place your cursor on any of the Tables and right click, a drop down menu will appear, kindly select "REFRESH". You need to this anytime you posted a new item and you want to view reports.
The Dashboard has the following reports;
a. Current Inventory Balance (Which is divided into Product List Balances and Product Group Balances.
b. Purchases and Sales Records (Gross Margin)
In this report the Sales and Purchases Values of various Products and Groups are placed side by side with the resultant total as Gross Margin. This is a Monthly Report.
7. ADDITIONAL REPORT
On the Dashboard or Record Sheet you will see a button named "TRANSACTION REPORT". Click on it and you will see a Table like this below;
We hope this article on PURCHASES, SALES & STOCK MANAGEMENT TEMPLATE 1 was useful. Kindly Leave a Comment below.
a. Date Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
b. Invoice Number Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
c. Type Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
d. Partner Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
e. Product Name Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE. Note that this has been changed to "Product Bar-code. You can receive or sell Products with a Bar-code scanner or just selecting the Product Code from the drop down list. To scan, kindly double click on the cell and then scan the product.
f. QTY Header: See Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
g. Unit Amount Header: See
Number 4 (Daily Stock Recording) on this link STOCK MANAGEMENT TEMPLATE.
Below is an introduction to the headers;
h. Transaction Header: This is found between the Invoice Number Header and the Type Header.
All you need to do is select a transaction (Purchases, Sales, Sales Returns, Purchases Return, Stock Adjustment In & Stock Adjustment Out) from the drop down list on the selected cell as shown below;
Below are Picture to aid you record your transactions with the HEADERS;
After recording your transactions, your database should look like the picture below;
Additional Tips:
a. If you select "Purchases" under Transaction, you need to Select "Stock In" under Type
b. If you select "Sales" under Transaction, you need to Select "Stock Out" under Type
c. If you select "Sales Returns" under Transaction, you need to Select "Stock In" under Type
d. If you select "Purchases Return" under Transaction, you need to Select "Stock Out" under Type
e. If you select "Stock Adjustment In" under Transaction, you need to Select "Stock In" under Type
f. If you select "Stock Adjustment Out" under Transaction, you need to Select "Stock Out" under Type
6. THE DASHBOARD:
As you record your transactions on a day to day basis you need to go to the Dashboard and view your reports. To Update the Reports on the Dashboard all you need to do is place your cursor on any of the Tables and right click, a drop down menu will appear, kindly select "REFRESH". You need to this anytime you posted a new item and you want to view reports.
The Dashboard has the following reports;
a. Current Inventory Balance (Which is divided into Product List Balances and Product Group Balances.
b. Purchases and Sales Records (Gross Margin)
In this report the Sales and Purchases Values of various Products and Groups are placed side by side with the resultant total as Gross Margin. This is a Monthly Report.
7. ADDITIONAL REPORT
On the Dashboard or Record Sheet you will see a button named "TRANSACTION REPORT". Click on it and you will see a Table like this below;
In this report the Sales and Purchases Values of various Products and Groups are placed side by side with the resultant total as Gross Margin. This is a Daily Report.
You can get hold of the Video Tutorials to help you understand the Template better;
We hope this article on PURCHASES, SALES & STOCK MANAGEMENT TEMPLATE 1 was useful. Kindly Leave a Comment below.
You contact us for Suggestions and Other Questions.
Be the first to receive our articles. Subscribe Now!
Cheers.
You are free to ASK THE ACCOUNTANTS any question and get quick response to your questions. Just drop your question on the space for ENTER YOUR COMMENT on this ATAs PAGE.
You may also be interested in CASH FLOW BUDGETS.
Comments
Post a Comment