How to Create Sales and Purchase Ledger in Excel

Step 1 – Create a Dataset with Proper Parameters

• Open your workbook and type the Company Name and Address.
• Start the ledger by creating cells for Month Name, Opening Date, Closing Date, Opening Balance, and Closing Balance.

• Make 5 new columns to fill the data for Date, Sales of Product, Cash Sales, Credit Sales, and Balance.

Read More: How to Create Excel Checkbook Register with Reconciliation

Step 2 – Use Formulas to Calculate the Sales Ledger

• Put the Month Name, Opening Date, and Closing Date values manually.
• Suppose we have an opening balance of $50,000 in our hands.

• For closing the balance, we will apply the following formula-

Where,

• The total amount will be added to the closing balance after completing sales for the total month.

• Press Enter to complete the formula.
• It will show a hyphen (–) as we haven’t put any values yet.

• Fill the cells (B12:E18) with values collected from sales records.

• Use the following formula in F12.

• Hit the Enter button to continue.
• Here we got the total sales amount sold on “5-January”.

• Use the following formula for the next cell where the sold amount will be added with the previous total amount:

• Hit Enter.

• Drag the fill handle down to cell (F18) to fill all the cells.

• Apply this formula in the cell D19:

• Press the Enter button.

• Calculate the credit sales by writing the formula down in the selected cell E19:

• Click Enter.

• Insert this formula in the cell F19:

The formula stands for, Total Balance = Opening Balance + Total Cash Sales – Total Credit Sales

• You will see that the Total Balance is automatically added to the Closing Balance.

Step 3 – Calculate the Total Ledger Amount Using Formula in Excel

• Open another worksheet to create the purchase ledger.
• Create your dataset and fill it with all the Purchased Records for the following month.
• To start the calculation, suppose we have $10,000 in our opening account.

• Determine the total balance for a single date in cell F12 by applying the below formula:

• In cell F13 use the following formula:

• Drag the fill handle down to fill the cells.

• Calculate the Total Amount for each column.

• We have successfully created our sales and purchase ledger in excel.

Download the Practice Workbook

<< Go Back to Accounting Templates | Excel Templates