Excel tips: Auto fit cell/column to text/numbers

Microsoft Excel is full of surprises. Today while working in it I realized a simple yet tedious work ahead of me. It so happened that I had to copy a tab/sheet full of numbers and paste it into a new spreadsheet. When I did that I noticed the numbers were larger than the default cell size. Therefore all I could see was ‘########’!! Since the sheet was full of numbers, double clicking on the column header would have taken a long time. I googled and found some solutions:

OPTION 1:

Select the whole sheet (CTRL + A) or a part/section of it as necessary:

Press and hold ALT key; then press ‘O’, then ‘C’ and then again ‘A’ keys. Once those keys are pressed release ALT key.

ALT + (O,C,A) keys

OPTION 2:

Select the whole sheet (CTRL + A) or a part/section of it as necessary:

Press and hold ALT key; then press ‘H’, then ‘O’ and then again ‘I’ keys. Once those keys are pressed release ALT key.

ALT + (H,O, I) keys

OPTION 3:

If you like using mouse too often then go for this option:

Make your selections and use this navigation: HOME > Format > Autofit column width

MS Excel: Fit to cell or colomn

OPTION 4:

This is the simplest option if your data table is small or with only few columns.

Select the columns that you want from the header; keep your cursor right in the middle of the last column header (of your selection) and the column header next to it. You will see your cursor turn into a header pointer/marker …..something like that 😉 Double click! Your columns will autofit your numbers into the cell.

MS Excel: Fit to cell or colomn

I tried the first option for a change and Bingo! Cells adjusted itself to the text/numbers that were in-there or more correctly, the columns expand so the texts/numbers within the cell can stretch their legs as far as they can and be visible! This, simplified my work. Hope it helps you as well. Good luck!

Posted in Excel tips | Tagged , , , , | Leave a comment

What is a Trial Balance?

A Trial Balance is a list of all the general ledgers a company maintains and their closing balances on a certain date (month end/year end).

Generally:

  • Expenses a/c, purchase a/c, inventory a/c and asset a/c will have a DEBIT closing balance
  • Sales/other income a/cs and all liabilities a/cs will have a CREDIT closing balance

It looks like this:

Trial Balance of XYZ company as on 31.12.2017
Particulars Debit Credit
General expenses XXX
(Purchase/Salary/Rent…)
Income XXX
(Sales/interest received…)
Assets XXX
(Cash/Land/Receivables…)
Liabilities XXX
(Capital/Payables…)
Total  XXX XXX
(Dr & Cr Balances will/must match)

 

A Trial Balance is drawn at the end of certain period (month end/year end/Quarterly etc). Trial Balance is used as a base in preparing financial statements (P/L a/c or Income statement and the Balance Sheet).

In accounting software Trial Balance is automatically done. You just have to click a button to fetch them. This can be done at any time and any number of times.

In manual accounting this is mostly prepared during the year end while closing books. This is when all General Ledger a/cs are finalized and closed. Thereafter those closing balance numbers are mentioned in the Trial Balance. If all the General Ledger closing balance is accurately taken into Trial Balance, the debit and credit side will automatically match.

 

Posted in Accountancy | Tagged | Leave a comment

Withholding tax: Meaning | Examples | Journal entries

Withholding tax, also known as retention tax, is a government requirement of holding the taxable amount on income earned not by the recipient of the income but from the payer of such amount. It is a way for the government to tax at the source of income, rather than trying to collect income tax after it is earned.

The amount of withholding tax on income payments other than employment income is usually a fixed percentage.

 

Example1: Company A provides service to Company B. When the obligations are met Company B pays company A after deducting withholding tax. Company B thereafter pays the deducted withholding tax amount to the government. The government, therefore do not have to wait for Company A to pay its income tax later on. Thus, the government receives its tax money before hand.

 

Company A (Service provider) Company B (Client/Customer)
Issues sales invoice (#001) of $100,000 to Company B.  Sales invoice are raised for the full amount without showing any W/H tax amount on it.

Dr…Receivable/Company B = 100,000
Cr…Sales a/c = 100,000

Once payment is made, the Payment advice copy (Remittance advice) looks like this:

Transferred via Bank X =  97,000
W/H Tax amt  =  3,000
Sales invoice#001  =100,000

When money is received:

Dr… Bank a/c   =  97,000
Dr…W/H Tax deducted   =    3,000
Cr…Receivable/Company B= 100,000

When money is paid:

Dr…Company A  = 100,000
Cr…Bank a/c  =   97,000
Cr…W/H Tax payable a/c =     3,000

 

Example2: A Company pays salary to its employees after deducting withholding tax. The deducted amount is remitted to the government. The government does not have to wait for employees to file their returns and thus collects the tax amount beforehand.

_*_

Posted in Accountancy | Tagged , , , | Leave a comment

How to prepare Cash Flow Statement using Indirect Method

Indirect cash flow statement is divided into three areas:

  • Operating activities: Cash inflow/outflow from business activities such as sales, royalties, commissions, fines, lawsuits, supplier and lender invoices, and payroll.
  • Investing activities: Cash inflow/outflow from sale/purchase of long term assets (CAPEX/Capital expenditures). Eg: purchase or sale of fixed assets/securities issued by other entities.
  • Financing activities: Cash inflow/outflow with regard to equity or borrowings of a business. Eg; sale of company shares, repurchase of shares and dividend payments.

 

Operating activities

Steps for calculating cash flow from operations using the indirect method:

  1. Start with net income.
  2. Add back non-cash expenses.
    • (Such as depreciation and amortization)
  3. Adjust for gains and losses on sales on assets.
    • Add back losses
    • Subtract out gains
  4. Account for changes in all non-cash Current Assets.
    • Add decrease in current assets (Accounts Receivable, Prepaid Expenses, Inventory etc.)
    • Subtract increase in current assets
  5. Account for changes in all non cash Current Liabilities (except notes payable and dividends payable).
    • Add increase in current liabilities (Accounts Payable, Accrued Liabilities, Tax Payable etc.)
    • Subtract decrease in Current Liabilities
    The result is ‘Net Cash Flow from Operating Activities’

Note:
A healthy business should generate positive net cash flow from operating activities and should grow the amount over time. If a business fails to consistently generate positive net cash from operating activities, it may need to rely on outside financing to operate, which will not sustain a business long term.

 

Investing activities

Steps for calculating cash flow from investments using Direct/Indirect method:

  • Add back losses (Cash spent)
    • Purchase of Fixed Assets
    • Purchase of investment instruments (Stocks & Bonds) of other companies
    • Lending money
  • Subtract out gains (Cash earned)
    • Sale of Fixed Assets
    • Sale of investment instruments
    • Insurance settlements on damaged Fixed Assets
    • Cash receipt from payback of loan

Note:
A stable or growing business typically has negative net cash flow from investment activities, which occurs when it buys more assets than it sells. A growing business routinely invests in new assets to expand its capacity, replace old equipment and to keep up with new technology.

Financing Activities

Steps for calculating cash flow from financing activities using Direct/Indirect method:

  • Add cash inflows
    • Sale of stock / issuance of new equity shares
    • Issuance of debt, such as bonds and debentures
    • Donor contributions restricted to long-term use
    • Proceeds from borrowings from banks or other financial institutions.
  • Subtract cash outflows
    • Repurchase of company stock
    • Repayment of debt/borrowings/loans
    • Payment of dividends

Note:
A healthy business may occasionally show positive net cash flow from financing activities as it raises money from investors and creditors to grow its business, but a healthy business should more often show negative net cash flow from financing activities. A negative amount suggests the business is using its cash flow from operating activities to pay dividends and pay off its outside financing.

 

_*_

Posted in Accountancy | Tagged , | Leave a comment