Using spreadsheets is an easy way to maintain purchase and sales data, both in a personal or business setup. In businesses, we have suppliers we source products from and we maintain a log of each purchase. Many a time, we tend to pay them the accumulated amounts on a scheduled basis. In this tutorial, we will use spreadsheets to calculate how much a business owes its suppliers at a given time.
Required Tools
The tutorial is specifically made for any version of Microsoft Excel. The same steps should work with Google Sheets with some minor changes.
Formulas / Functions Used
This tutorial uses basic Division, Multiplication, Addition and Subtraction formulas.
Calculating How Much a Business Owes its Suppliers
Let’s assume, we are working with a stationery shop that collects pencils, erasers, and sharpeners from 06 suppliers. We have the prices and quantity of the items for each of the suppliers. We have to calculate the total amount owed to the suppliers.
Step 1: Create a Table to Calculate the Costs
Firstly, we need to create a separate table to calculate the costs. At cell C20, let us create a table with the suppliers in the rows and items in the columns. Let’s add an extra row and column to calculate the totals. We will now calculate the item-wise costs for each supplier in the corresponding cells.
Step 2: Multiply Price and Quantity to get the Cost
To calculate the amount owed to Supplier 1 for pencils we need to multiply the price and quantity of pencils for Supplier 1. To do that, at cell D22, we will use the formula =D4*D13, and the required amount should appear in the cell.
Step 3: Copy the Formula to the Range D22:F27
We now have the amount owed to Supplier 1 for pencils. To calculate the amounts owed for all the products to each of the suppliers, we need to copy the formula to all the cells of the range D22:F27. To do that we can use any of the following approaches:
- Select the cell D22, and after pressing Ctrl+C, select the range D22:F27 and press Ctrl+V
- Select the cell D22, and after pressing Ctrl+C, select the range D22:F27 and press the Enter key
- Select the cell D22, and after the ‘cross hair’ icon appears at the corner of the cell, drag the formula to E22:F22, and then drag the range D22:F22 to D23:F27.
Step 4: Calculating the Total Amounts
To calculate the total amount owed for each item, we will use the formula =SUM(D22:D27) at cell D28, and copy the formula to the range E28:F28. To calculate the total amount owed to each supplier we can use the formula =SUM(D22:F22) at cell G22, and copy the formula to the range G23:G28. The total amount owed overall should appear in cell G28.
An easier way to do the same can be using the AutoSum feature. To do that, we will select the range D28:F28, hold down the Ctrl key, select the range G22:G28, and click on the AutoSum button from the top ribbon, and the same numbers should appear.
Learn More
Here are some similar tutorials that you might find interest in:
- How to calculate profit from sales using spreadsheets
- How to predict the number of customers a business will have in 10 years
- How to determine the price of a product to ensure profit maximization
- How to calculate the grades of a student from the marks that they obtained
Conclusion
So here’s how to calculate how much a business owes its suppliers. If you have any queries regarding this or anything related to spreadsheets, feel free to let us know in the comments or through the Contact Us page.
