If you cannot do great things, do small things in a great way. ....
Talking isn't doing. It is a kind of good deed to say well; and yet words are not deeds.
The greatest gift of life is friendship, and I have received it.

Saturday, June 16, 2018

Business Cheque Writing/ Printing with simple Excel workbook

Print your Business or personal bank Cheques
simply using MS Excel.


This is a simple workbook,which will very useful for you if you write lot of cheques manually to your suppliers daily for your business. With this method you not need special printer or software only you need is computer with MS Excel and normal printer.



Yes, now you can use your normal printer and your laptop or desktop computer to print 100s of cheques in second.

First you have to enable macros to your MS excel.

I already crate a excel workbook using some macros and VB, you can download it free. and play....

These are the only steps you have to do to set your cheques printing.


  • 1) save attached excel file in your favorites folder.
  • 2) open office excel program and enable macros.

When you open the file , a yellow Message Bar appears in upper under menu bar like this. 

                    A) Click Enable Content button.


Then file will open as a trusted document.If you not enable macros in your excel program file not work properly:

How to do it manually  :  Macro settings are located in the Trust Center.

1. Click the File tab.
2. Click Options.
3. Click Trust Center, and then click Trust Center Settings.
4. In the Trust Center, click Macro Settings.
5. Make the selections that you want.
6. Click OK.



3) now open the save excel file. 



There are three work sheets in workbook .
  1. First worksheet for your cheque detail. (chdetails) You can add 100 of checks detials for print
  2. second cheque printer (cheqprint) You have to set it first time for your cheque paper size
  3. Third sheet for  customers/suppliers list. (payee list)


4) now scan an one of your check leaf from your check book and insert it to 2nd sheet to      format the margins.

According to size of your bank check size ,
drag and drop the  details to the correct place of check picture. These details linked with checks details sheet data. when you change the chcks details this data will change.

See the green color drop lines, it can move to any place to set your check leaf.

After you drag all cheque details to correct place. Remove check scan copy.





Now you can copy & paste first cheque margin to down for any nos of checks you wants.




Now enter your payee list and save. When you need to create checks just go to checks details sheet.










what you give to world 
  • select the payee name form drop down list.
  • enter the amount
  • now cheques ready to print. any way first time just print a photo copy of black cheque and see whether all the margins and places are correct. if not drag till it ok, 


                                                                                   





Thanks
If this is useful for you please share to world...
Instructable page

Direct download with Google Drive

Friday, June 1, 2018

Generate Invoices using simple database system with MS Access .

Generate  Invoice using simple database system with MS Access  .

using this ready made database system you can issue invoice to your customers with few steps.


Customized Invoice nos
Select customers with drop down list.
Customer wise sale and outstanding for month and any given period.
sales person and item vise sales reports.
All invoice data will auto save in database and could edit or print any time.
No need to install any special programs. just do it with MS offices package using MS access.
real time stock control . after update the oping balances and purchases sales and issues will auto update.
Could get current stock balances and stock valuation and much more..







This ready made system - if you Interest about this please send me a email -bandara.jmn@gmail.com

Tuesday, April 3, 2018

POS (point of sales) system for small shops groceries and service centers


How to create a simple POS (point of sales) system for small shops groceries and service centers using MS Excel

With this method you can manage following facilities without special software or expensive equipment.

v  Issue a sales bill using bar code reader.
v  Manage purchases
v  Control inventory
v  Day end and month end stock balance.



First create excel workbook with 6 worksheets for followings like this:



1.       Bills
2.       Pur
3.       Purchase
4.       Sales
5.       Stock balance
6.       Setup

Next: 
Create a setup page with this headings and setup your stock items




Category : item category
Code : Create specific code for your each items. This must be unique ID no for each items and use this to create the barcodes. Exp:



According to this, take all the inventory items & create a code and update sheet with opening stock, pur.price and Seles price. You have to give the correct purchase price and sales prices because when you issue a bill, price will pick from this sheet. Opening balance will link with stock balance sheet.




Create Bar code:
You can download Bar code Generator or can do it Online.

Example for barcode I generate for sample file.

If you don’t have barcode printer just print to A4 Sheet and paste it to your Sales items.

Next
Create Stock balance Sheet:

Create this sheet with below headings:



Copy this formula to each row and copy paste to down:

  • Code:  =IF(setup!$B$3:$B$323<>"",setup!$B$3:$B$323,"")
  • Description:  =IF(setup!$C$3:$C$323<>"",setup!$C$3:$C$323,"")
  • Opening Balance:   =SUM(IF(B3=setup!$B$3:$B$1021,setup!$D$3:$D$1021))
  • Purchase: =SUM(IF(B3=purchase!$B$2:$B$2005,purchase!$D$2:$D$2005))
  • Sales: =SUM(IF(B3=sales!$H$2:$H$2551,sales!$J$2:$J$2551))
  • Stock: =+D3+E3-F3
Next Create a bill sheet:




Create sheet according to this format and give below formula to each row and create macro with below codes. 

You can print bill with this sheet with using bar code reader or select the item code with drop down list

  • Line:  =IF(C5="","",B4+1)
  • Code: Create a list box link with setup page item code and name. when you connect a bar code reader with bar code sticker details will auto pick.
  • Description: =I4
  • Qty : this column you have to enter manually according to customer purchase qty.
  • Price: =IF(E4="","",VLOOKUP(C4,al,5,0)*E4)
** macro for Save bill
Create a button called Save bill and copy this code: 
Sub Dayendsales()
'
' Dayendsales Macro
''
    Sheets("Tsales").Select
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2:E255").Select
    Selection.copy
    Range("G2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("sales").Select
    Range("B3:D1572").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D3").Select
End Sub
Sub DayendPurchases()
'
' DayendPurchases Macro
'
    Sheets("Tpurchase").Select
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("D2:D643").Select
    Selection.copy
    Range("F2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("purchase").Select
    Range("C3:D625").Select
    Selection.ClearContents
    Range("E3").Select
End Sub
Sub SaveBill()
'
' SaveBill Macro
'
    Application.Run "'shop sales control.xls'!copy"
    Application.Run "'shop sales control.xls'!SaleReplace"
   
End Sub
Sub DayEnd()
'
' DayEnd Macro
   
End Sub

Next Create Pur sheet: according to this format.



Now create the Purchase and sales data save page with this format:

Sales data base