How to Create an Accounts Payable Spreadsheet
The Excel software program is a good time-saving, error-proofing tool to use for accounting purposes. The program is simple to use, whether you are using it for business or personal finances. Setting up an Accounts Payable spreadsheet will keep your balances owed to each creditor and total Accounts Payable balance at your fingertips. This template will show how to create an overview worksheet with supporting worksheets for each individual creditor. Additional worksheets can be added to the workbook as required for new creditors.
Other People Are Reading
Instructions
-
-
1
Open a new Excel file. From the menu, select "File." From the drop-down list, select "Save As." In the pop-up window, type in the file name and click on the "Save" button. In this example, the file is named "Accounts Payable Spreadsheet."
-
2
Right-click on the second tab of the workbook. From the drop-down list, select "Rename." Left-click on the second tab and begin typing the tab name. Here the tab is named "Creditor A."
-
-
3
Select cell "A1" and enter the creditor name. Here the cell is labeled "Creditor A."
-
4
Select cell "K1" and enter the formula: "=SUM(H:H)-SUM(I:I)-SUM(J:J)."
-
5
In the second row, label the columns as follows: "A-Date, B-Type, C-Item, D-Item #, E-Invoice #, F-Memo, G-Check #, H-Charge, I-Payment, J-Discount, K-Balance."
-
6
In the third row, select cell "K3" and enter the formula: "=H3."
-
7
In the fourth row, select cell "K4" and enter the formula: "=K3+H4-I4-J4."
-
8
As an invoice is received, enter data in the appropriate columns on the next available row, starting with row 3. As a check is written, enter the data in the appropriate columns on the next available row. As discounts are taken, enter the data in the appropriate columns on the next available row.
-
9
Copy this worksheet, by right-clicking on the tab and from the drop-down list, select "Move/Copy." In the pop-up window, in the "Before Sheet" list select "(move to end)" and check the "Create a Copy" box. Follow the steps given to rename this worksheet with the new creditor name.
-
10
Right-click on the first tab of the workbook. From the drop-down list, select "Rename." Left-click on the first tab and begin typing the tab name. Here the tab is named "Overview." In the first row, label the columns as follows: "A-Creditor," "B-Balance Owed." In the first row, column C, enter the formula: "=SUM(B:B)." This will total all outstanding balances for all creditors. In the second row enter the formulas as follows: "A2-='Creditor A'!A1, B2-='Creditor A'!K1," for each creditor worksheet. This will provide an overview of outstanding balances for each creditor.
-
1
