Wasim Akram
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”.
- 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.
- Hence, for closing the balance, we will apply the following formula-
=F19Where,
- 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.
- Now, let’s fill the cells (B12:E18) with values collected from sales records.
- In order to check the total balance write down the below formula-
=C9+D12-E12
- Hit the Enter button to continue.
- Here we got the total sales amount sold on “5-January”.
- 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
- 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.
- 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.
- 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-E19Where,
- 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”.
Read More: Create General Ledger in Excel from General Journal Data
Similar Readings
- How to Maintain Ledger Book in Excel (with Easy Steps)
- Make Subsidiary Ledger in Excel (With Easy Steps)
- How to Export All Ledgers from Tally in Excel
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.
- 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
- Now, drag the “fill handle” down to fill the cells.
- Just like our previous method, we will calculate the “Total Amount” for each column.
- After completing our calculation, in conclusion, we have successfully created our sales and purchase ledger 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.
