There are 14 different types of financial transactions that the Small Business Finance Manager Excel template supports.
Choosing the correct type of transaction is important as that drives how the financial transaction impacts your cash flow statement and the Profit & Loss statement.
The following screenshot shows the type of impact. 1 indicates positive impact, -1 indicates negative impact and blank indicates no impact.
Let's look at each one in this article.
1. Instant Sale
This transaction is used when you make a sale and you receive instant money from your customer.
In the above screenshot, an instant sale happened on July 1, 2020 to Customer One for 300. We categorize this transaction as 'Income' and Sub category is 'Product Revenue', since we sold a product to customer. The money will flow into Checking account. Hence we chose that account in the Account field.
All other fields are optional.
This will be reflected in Profit & Loss statement under Income section for July 2020.
Cash Flow Statement will show Money In of 300 in July 2020.
Dashboard will show the money adding to your total balance and to the Checking Account.
Insights Dashboard will reflect this in Sales for July 2020.
2. Invoice
This transaction is used when you make a sale but you send an invoice to customer and allow customer to pay the invoice on a later date.
We enter the data similar to an Instant Sale, except we also have to enter the Document Number and Due Date for the invoice.
We have also entered that this amount will end in PayPal account. This is optional. You can enter this when the payment is actually received from customer.
In this example, Invoice was created on July 5th, but the due date is Aug 31st.
This will be reflected in Profit & Loss statement under Income section for July 2020.
Cash Flow Statement will not show any change since there is no actual money movement in creating an invoice.
Dashboard will show the amount [500] in the Accounts Receivable section, since the money is expected to come to us in future.
Unpaid Report will show the invoice as unpaid and 'Not due yet' if today's date is <= Due Date. It will show 'Past Due' if today's date > Due Date.
Insights Dashboard will reflect this in Sales for July 2020.
When the customer makes a full payment, please enter the paid date in the Paid Date column,
Now the cash flow happens. So, Cash Flow Statement will reflect Money In of 500 in August, since the payment was made in August.
Profit & Loss Statement will not have any changes.
Dashboard will show the money adding to your total balance and to the PayPal Account.
Insights Dashboard will not change since this is not a new sale or expense.
Unpaid Report will now not show the invoice as unpaid.
Please note that if customer makes partial payment or bulk payment across multiple invoices, please use the Bulk Payment from Customer transaction type - which will be explained now.
3. Bulk Pymt from Customer
Let's say in the previous example, customer (instead of making full payment on Aug 5th) decided to make 2 partial payments of 250 each, one on July 31st and another on Aug 5th. We would enter the transactions as shown below.
Cash Flow statement will show Money In of 250 in July and Money In of 250 in August.
Profit & Loss Statement will not have any changes since this is purely a cash flow transaction - not a new sale.
Dashboard will show the money adding to your total balance and to the PayPal Account.
Insights Dashboard will not change since this is not a new sale or expense.
Unpaid Report will now not show the invoice as unpaid anymore after both Bulk payments are entered. After the first Bulk payment, 250 would have remained as unpaid.
Please note that Bulk Payments can also be applied to multiple invoices for the same customer. If the customer had two invoices unpaid, one bulk payment can be entered to pay off those invoices. Here is an example.
2 invoices for 500 and 400, and one bulk payment of 900 will pay off those 2 invoices.
4. Refund to Customer
If customer returns the product and you have to refund, enter a 'Refund to Customer' transaction. Here, we are refunding an instant sale made in July.
Cash Flow statement will show Money Out of 250 in July.
Profit & Loss Statement will show a negative entry under Income section.
Dashboard will show the money deducted from your total balance and from the Checking Account.
Insights Dashboard will show the refund amount in a separate chart.
Special Case:
If the original sale was an Invoice transaction and if the customer has not paid any amount, enter the Paid Date to reflect as though the customer has paid. Then, enter the Refund to Customer transaction with date same as Paid Date in the Invoice transaction. This will create Money In and Money Out of same amount on same date in Cash flow statement. Net, there is no cash change. However, this will ensure that the invoice does not appear in the Unpaid report.
5. Instant Expense
Let's now switch to the expense side for the next few transactions.
I have entered above, an instant expense of 100 to the Electricity Co.
An instant expense is where money is sent to the supplier or vendor immediately.
Cash Flow statement will show Money Out of 250 in July.
Profit & Loss Statement will show an entry under Operating Expense section.
Dashboard will show the money deducted from your total balance and from the Visa Card.
Insights Dashboard will show the amount as expense for July.
6. Bill
This transaction is used when you receive a bill from a supplier or vendor.
We enter the data similar to an Instant Sale, except we also have to enter the Document Number and Due Date for the Bill. We have also entered that this amount will be paid using Visa Card account whenever the payment is made. This is optional. You can enter this when the payment is actually made.
In this example, Bill was created on July 12th, but the due date is Aug 12th.
This will be reflected in Profit & Loss statement under Expense section for July 2020.
Cash Flow Statement will not show any change since there is no actual money movement in creating an Bill.
Dashboard will show the amount [600] in the Accounts Payable section, since the money is expected to leave us in future.
Unpaid Report will show the Bill as unpaid and 'Not due yet' if today's date is <= Due Date. It will show 'Past Due' if today's date > Due Date.
Insights Dashboard will reflect this in Expenses for July 2020.
When you make a full payment to supplier, please enter the paid date in the Paid Date column.
Now the cash flow happens. So, Cash Flow Statement will reflect Money Out of 600 in August, since the payment was made in August.
Profit & Loss Statement will not have any changes.
Dashboard will show the money leaving your total balance and Visa Card will show extra -600.
Insights Dashboard will not change since this is not a new sale or expense.
Unpaid Report will now not show the Bill as unpaid.
Please note that if you make partial payment or bulk payment across multiple Bills, please use the Bulk Payment to Supplier transaction type - which will be explained now.
7. Bulk Pymt To Supplier
Let's say in the previous example, you (instead of making full payment on Aug 5th) decided to make 2 partial payments of 200 and 400, one on July 30th and another on Aug 10th. We would enter the transactions as shown below.
Cash Flow statement will show Money Out of 200 in July and Money Out of 400 in August.
Profit & Loss Statement will not have any changes since this is purely a cash flow transaction - not a new expense.
Dashboard will show the money leaving your total balance and Visa Card will be charged.
Insights Dashboard will not change since this is not a new expense.
Unpaid Report will now not show the Bill as unpaid anymore after both Bulk payments are entered. After the first Bulk payment, 400 would have remained as unpaid.
Please note that Bulk Payments can also be applied to multiple Bills for the same Supplier. If the Supplier had two bills unpaid, one bulk payment can be entered to pay off those Bills.
8. Refund from Supplier
If you returned the product or service to your supplier, and supplier sends a refund, enter a 'Refund from Supplier' transaction. Here, we are receiving a refund for an Instant Expense made in July. Refund comes in August.
Cash Flow statement will show Money In of 100 in August.
Profit & Loss Statement will show a negative entry under Expense section.
Dashboard will show the money added to your total balance and to the Visa Card Account.
Insights Dashboard will show the refund amount in a separate chart.
Special Case:
If the original sale was a Bill transaction and if you have not paid any amount, enter the Paid Date to reflect as though the you have paid. Then, enter the Refund from Supplier transaction with date same as Paid Date in the Bill transaction. This will create Money In and Money Out of same amount on same date in Cash flow statement. Net, there is no cash change. However, this will ensure that the Bill does not appear in the Unpaid report.
9. Transfer From
10. Transfer To
Transfer transactions can be used to transfer money between your accounts. Common transactions of this type are credit card payments, moving money to Savings account from Checking, and withdrawing Cash from ATMs.
We enter transfers as two transactions in this template.
In the above screenshot, we have entered Transfer From transaction to take money from Checking Account, and a Transfer To transaction to put that into Visa Card.
Cash Flow statement will show Internal Transfers of 0 for all accounts. If you look at individual accounts, you will see transfer of +1000 for Visa Card account and -1000 for Checking account in August.
Profit & Loss Statement will not show any changes.
Dashboard will show the money added to your balance of Visa Card Account (thus reducing the amount owed to Visa Card) and reduce the balance of Checking account.
Insights Dashboard will not show any transfers.
11. Issue Customer Credit
When customer returns the product you sold to them, but instead of you refunding the paid amount to customer, you may issue a credit (or credit memo) so that customer can use later to buy products from you.
In this scenario, you can enter a Issue Customer Credit transaction.
In this example, sale of 300 on July 1st, is followed by a Customer credit issued on July 31st.
Account does not have a role to play here, as there is no cash flow.
Choose a Sub Category like 'Customer Credit' and it will appear as such in Profit & Loss Statement. It will appear as -300 in Profit and Loss Statement.
No changes to the Cash Flow statement.
Insights Dashboard will not show Customer Credits.
Dashboard will show the Customer Credit as negative amount in the Accounts Receivable section.
12. Apply Customer Credit
When the customer wants to use the Credit issued to receive product/service, enter an 'Apply Customer Credit' transaction.
In this example, customer credit of 250 was applied on Aug 15th.
Account does not have a role to play here, as there is no cash flow.
Choose a Sub Category like 'Customer Credit' and it will appear as such in Profit & Loss Statement. It will appear as +250 in Profit and Loss Statement.
No changes to the Cash Flow statement.
Insights Dashboard will not show Customer Credits. Please note that this transaction will not record as a sale in Insights Dashboard.
Dashboard will reflect that 250 out of 300 customer credit has been applied and thus used.
13. Receive Supplier Credit
When you return the product/service you purchased from Supplier/Vendor, instead of them refunding the paid amount to you, they may issue a credit (or credit memo) so that you can use later to buy products from them.
In this scenario, you can enter a Receive Supplier Credit transaction.
In this example, Cost of Sales transaction of 600 on July 12th, is followed by a Supplier credit received on Aug 11th.
Account does not have a role to play here, as there is no cash flow.
Choose a Sub Category like 'Supplier Credit' and it will appear as such in Profit & Loss Statement. It will appear as +600 in Profit and Loss Statement under Cost of Sales section.
No changes to the Cash Flow statement.
Insights Dashboard will not show Supplier Credits.
Dashboard will show the Supplier Credit as the negative amount in the Accounts Payable section.
14. Use Supplier Credit
When you want to use the Supplier Credit issued to purchase product/service, enter an 'Use Supplier Credit' transaction.
In this example, Supplier credit of 600 was used on Aug 15th.
Account does not have a role to play here, as there is no cash flow.
Choose a Sub Category like 'Supplier Credit' and it will appear as such in Profit & Loss Statement. It will appear as +600 in Profit and Loss Statement in the Cost of Sales section. If Receiving Supplier Credit and Using Supplier Credit happen in the same month, they net (-600 and +600 would net to 0).
No changes to the Cash Flow statement.
Insights Dashboard will not show Supplier Credits. Please note that this transaction will not record as an expense in Insights Dashboard.
Dashboard will reflect that 600 out of 600 Supplier credit has been used.
