December 6, 2021


Accounting + Finance Blog

Using MS Excel to input sales transactions

Using MS Excel to input sales transactions

Revenue is the core income for every business. In trading entities sales transactions are common. Service businesses like a Plumber’s sales are based on services rendered and the labor fee paid for such service per hour or work done. In this article, the focus is on trading entities. That is, you sell a commodity, either physical or digital.

Sales transactions can occur in two ways

1. Cash sales transactions
2. Credit sales transaction

Cash sales transactions

This occurs when the customer pays cash, performs a bank transfer through a mobile app, a paywall link you provided (such as paystack paywall), or through a cheque. The cash sales transaction is done immediately.

Credit sales transactions

This occurs when the customer does not pay for the goods immediately. The customer or buyer promises to pay at a future date. The two scenarios explain it.

Scenario 1: Alero sold shoes to Tobi for 48,500 Naira. Tobi used a payment App on his mobile phone to pay for the shoes immediately. Alero got a credit alert on her phone. This is a cash sales transaction. The payment was done immediately.

READ ON  Suspense Account Meaning and its uses

Scenario 2: Alero sold shoes to Amina for 90,000 Naira. She agreed with Amina that the payment will be done in 3 days. At the end of the three days, Amina made a transfer of the money to Alero. This is not a cash sales transaction, payment was not made immediately.

Although Amina paid for the shoes, the payment was not done immediately. Scenario 2 is a credit sales transaction. This is discussed later on in the article.

How to record cash sales transactions in MS Excel

Recording cash sales transactions are simple. The first thing is to ensure that there is a source document for it. Normally, as the entity selling the item you will issue a receipt to the customer. You will also have a copy of the receipt. It is from this receipt you record the sales transaction.

In an MS Excel workbook, you create a table for a cash account and sales account. What you will do is to post the amount on the cash account and sales account.

You will post the amount as money received in the cash book or on the Debit (Dr) side of the cash book. Depending on the format you choose and post the same amount on the credit side of the sales account. To make it simple, I use “received” for the cash debit (Dr).

READ ON  What is account in accounting?

Using MS Excel to input sales transactions

How to record Credit sales transaction in MS Excel

As stated earlier in the article, scenario 2 applied to credit sales transactions. When such a transaction occurs, it means that Amina is a debtor to Alero. Therefore, Alero must write down Amina’s name in her book. For her to remember her debtors. This is usually done in a subsidiary book. In this case, a sales Daybook.

In the MS Excel worksheet, there is already an account for sales. You will include the debtor’s account (a/c receivables) where the amount Amina owes will be recorded. Next, Debit the debtor account and credit sales account.

Using MS Excel to input sales transactions

You will notice that in both transactions (cash and credit transactions), the sales account is credited. This is always true! The sales account is always credited. We don’t record any other transactions in the sales account. Unless, if you need to make adjustments in the sales account.

READ ON  Meaning of Capital and Key Explanations

From the lesson, you have learned the following:

When there is a cash sale, Dr: Cash a/c and Cr: Sales a/c.

When there is a credit sale, Dr: A/c Receivables a/c and Cr: Sales a/c.

Sales a/c is always credited unless there is an adjustment on the account.

In the article that follows, we will discuss how to deal with VAT when a business makes sales.