How to Create Sales and Purchase Ledger in Excel

If you are looking for a way to create a sales and purchase ledger in excel then you are at the right place. Today, in this article, I am sharing with you how to create a sales and purchase ledger in excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Simple Steps to Create Sales and Purchase Ledger in Excel

In the following, I have explained 3 simple and easy steps to create a sales and purchase ledger in excel.


Step 1: Create Dataset with Proper Parameters

  • Above all, open your workbook and type the “Company Name” and “Address”.
  • Then, below company information start the ledger by creating small boxes with “Month Name”, “Opening Date”, Closing Date”, “Opening Balance”, and “Closing Balance”.

Creating Sales Ledger Dataset

  • Now, to complete the making of the dataset we will make 5 new columns to fill the data for “Date”, “Sales of Product”, “Cash Sales”, “Credit Sales”, and “Balance”.

Read More: How to Make General Ledger in Excel (With Easy Steps)


Step 2: Use Formula to Calculate Sales Ledger

  • In this step, we will fill the cells with data to complete our sales ledger.
  • Let’s put the “Month Name”, “Opening Date”, and “Closing Date” manually.
  • Now, let’s imagine we have an opening balance of $50,000 in our hands.

Use Formula to Calculate Sales Ledger

  • Hence, for closing the balance, we will apply the following formula-
=F19

Where,

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

Use Formula to Calculate Sales Ledger

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

  • Now, let’s fill the cells (B12:E18) with values collected from sales records.

Use Formula to Calculate Sales Ledger

  • In order to check the total balance write down the below formula-
=C9+D12-E12

Use Formula to Calculate Sales Ledger

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

Use Formula to Calculate Sales Ledger

  • Similarly, we will put the formula for the next cell where the sold amount will be added with the previous total amount-
=F12+D13-E13

Use Formula to Calculate Sales Ledger

  • Click enter to get the total amount after sales.

  • Now, drag the “fill handle” down to cell (F18) to fill all the cells.
  • As the total value will be calculated in reference to total cash and credit transaction thus we have not filled that cell.

Use Formula to Calculate Sales Ledger

  • Starting with this step, we will calculate the total cash sold using the SUM function in excel.
  • Apply the formula in the cell (D19)-
=SUM(D12:D18)
  • Press the Enter button to determine the total cash sales.

  • In the same fashion, we will calculate the credit sales by writing the formula down in the selected cell (E19)-
=SUM(E12:E18)
  • Click Enter.

Use Formula to Calculate Sales Ledger

  • Finally, we will determine the total balance after all the cash and credit transactions. To do so-
  • Put the formula down in the cell (F19)-
=C9+D19-E19

Where,

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

  • In conclusion, you will see our “Total Balance” is automatically added to the “Closing Balance”.

Use Formula to Calculate Sales Ledger

Read More: Create General Ledger in Excel from General Journal Data


Similar Readings


Step 3: Calculate Total Ledger Amount Using Formula in Excel

  • In the same fashion, open another worksheet to create the purchase ledger.
  • Similarly, just like the first step, 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.

Calculate Total Ledger Amount Using Formula in Excel

  • Just like the previous steps, we will determine the total balance for a single date in cell (F12) by applying the below formula-
=C9-D12+E12

  • In the same style, for cell (F13) write the following formula-
=F12-D13+E13

Calculate Total Ledger Amount Using Formula in Excel

  • Now, drag the “fill handle” down to fill the cells.

  • Just like our previous method, we will calculate the “Total Amount” for each column.

Calculate Total Ledger Amount Using Formula in Excel

  • After completing our calculation, in conclusion, we have successfully created our sales and purchase ledger in excel.

Calculate Total Ledger Amount Using Formula in Excel

Read More: How to Create a Checkbook Ledger in Excel (2 Useful Examples)


Things to Remember

  • After applying the formula for the first transaction for a single date don’t drag the fill handle down. Because in the first-day transaction, the principal amount was the opening balance whether for the next cell the principal amount will be the amount after the first transaction.

Conclusion

In this article, I have tried to cover all the steps to create sales and purchase ledger in excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo