Formation of Non Profits and the Types of Non Profits

Posted on August 6, 2011 by esol4489

Some of the fundamental questions if one is interested in knowing more about the Non Profits world are the questions; how are non profits formed? And how many types of non profits are in existence? This blog posting is an attempt to help answer these two fundamental questions.But, before I answer these two questions, let me introduce some statistics about non profits in the United States:

According to the Urban Institute, the number of non profits has been growing steadily. During the last five years from 2004 to 2010 the number of registered nonprofits has increased from 1.5 million to 1.9 million, up 27 percent. This increase is also accompanied by an increase in revenues from $2.4 trillion to $3.4 trillion, up 42 percent, and assets have sky rocketed from $4.8 trillion to $7.8 trillion, up 67 percent. Also, based on IRS reports, Nonprofits accounts for about 12 percent of the gross domestic product (GDP).With this brief introduction, let me now go ahead and answer the preceding two questions.

The way non profits are created may differ from country to country, however in the United States, nonprofit organizations are created by filing bylaws and/or articles of incorporation in the state in which they expect to operate. Examples of these nonprofit organizations include charities, trade unions, trade associations, foundations etc. Creation of public nonprofit organization is sanctioned by formal community action to provide community services. Also the law allows them to levy taxes to enable them carry out their mission. Example of public nonprofit organizations includes federal, state, and local government units.

The management of these non profits is in the hands of board of directors. These are leading citizens who volunteer their time. They may delegate the day to day running of the organization to a paid employee with titles such as executive director, executive secretary, manager etc. In most cases, the board also appoints one of its members to play the role of a treasurer whose office is of high importance second to the chairman of the board.

Nonprofit organizations are formed to cater for societal needs that are not directly provided by the government.  They have no profit motive as their name indicates .They have no equity shares that can be traded at the stock exchanges. They may be financed by taxes and or donor contributions.

The chart below shows different categories of nonprofit organizations.

Share
Posted in Accounting for Non Profits | 2 Comments

Accounting For Non Profits-Basics

Posted on August 4, 2011 by esol4489

For the last couple of days I have been involved in lively discussions with friends about how non profits organizations keep their books. The hottest area of discussion was how to properly account for unrestricted and restricted revenues and expenses. And the journal entries that need to be booked when restricted revenue is released from restriction. During the discussions, I realized that it very easy to assume that as long as you know how the for-profits accounting is performed then you would not have issues replicating that knowledge to a non -profit entity. Well, this is true however, it is worthwhile noting that there are key differences that are note worthy and necessary to highlight in this blog posting. To this end this blog posting will explain in detail the basics of accounting for non Profits and specifically the following:

        I.            Background information for accounting for Non Profits

      II.            Fund accounting as used in the Non Profits world

    III.            Non Profits Inter Fund Accounts

    IV.            Net assets released from restrictions

      V.            Chart of Accounts

        I.            Background information for Accounting for Non Profits

Accounting for non profits is very similar to project accounting. Revenues and expenses are tracked at a much more detail than what happens with for profits detailing information such as the source, purpose and where to allocate revenues and expense in terms of various funds. Generally non profits follow what we call fund accounting. Each revenue and expense must be booked in the right fund, the right fund expense, the right revenue fund, the right account and the right department.

 When dealing with the for-profit accounting, we have retained earnings however when dealing with the nonprofit accounting, we have funds or net assets. Though the word fund has many meanings, for the purpose of this explanation fund and net assets mean one and the same thing.

The main purpose of fund accounting is to show flow of resources and how the resources have been spend based on certain restrictions such as legal, donor or other requirements. Therefore, to reflect this level of detail accounts are set up to account for the type of revenue or expense, type of fund, and type of grantor, department etc. Due to this level of detail, many nonprofit organizations accounts have several digits to identify general ledgers accounts, departments, grants, funds etc. Therefore, in this regard, it is not uncommon to have eight digits in the accounting code such as, 6700.22.986.004. The first four digits identify an expense account, the“22” is a department, 986” is a funding source and “004” identifies the fund. At the end of the year, revenues and expenses are closed to the fund they belong.

      II.            Fund accounting as used in the Non Profits world

There are three main types of funds:

  • General fund-Unrestricted net assets
  • Restricted fund-Temporarily restricted net assets
  • Permanently restricted fund-Permanently restricted net assets

As mentioned previously, the purpose of creation of funds is to segregate resources, which could be limited by law or donors. I feel obligated to repeat this emphasis several times because this is the key to clearly understanding the non profits accounting. SFAS 117 provides the rules of what is booked in each fund. For more details about what to book in each of the three categories of funds go to Statement of Financial Accounting Standard # 117

Again I repeat that funds/net assets are not assets. Think of them as projects with their own set of financial statements.  At the end of the year when the books are closed, revenue and expenses are closed in different funds similar to a set of different books for different companies.

Below I will go into further detail about how each of the three funds is utilized:

General Fund-Unrestricted Net Assets

This fund contains resources that are not restricted and is used to run day to day operations of the nonprofit. For example if a nonprofit receives $1,000 to be used for current activities. This amount is booked in the unrestricted fund. The journal entry would be a debit to cash and a credit to revenue-contributions-unrestricted.

Most expenses are recorded in this fund despite the fact that revenues are booked in other funds. For example, an organization gets $50,000 donation for scholarships for needy students. The entries to book this donation are debit to cash and credit to temporarily restricted account- scholarships revenue account. After the $50,000 is spent, then a credit is made to

cash, and a debit is made to Scholarship account expense- unrestricted in the general fund. Sometimes some non profits create inter fund receivable and a payable which I will discuss in more details later in this blog posting. But for now let’s assume these inter fund accounts are not set up and the following entries are booked directly. So the other journal entry is debit  the “Net Assets Released from Restriction” to decrease the temporarily restricted fund, and to credit the general fund, actually to reduce the Scholarship account expense account previously debited to pay the expense. The result is that expenses are shown in the unrestricted fund, but are credited with a journal entry.

Restricted Fund-Temporarily Restricted Net Assets

This is fund is used to account for moneys to be used for a specific purpose, as per donor or legal restrictions. But bear in mind that there is no need to have to open a fund for each type of donation and at the same time, there is no need lose track of restricted funds.

Permanently Restricted Fund-Permanently Restricted Net Assets

Permanently restricted fund is also popularly known as “endowment fund.” In most cases the organization uses investment income and leaves the principal intact.

    III.            Non Profits Inter-Fund Accounts

Inter-fund accounts are accounts that connect all funds. At year end, these accounts are eliminated during consolidation. Let me give an example to make this explanation more clear. Assume the general fund needs to borrow from restricted funds to meet current expenditures. This creates a liability in the general fund and a receivable in restricted fund. Therefore, for record keeping purposes, “due to/due from” accounts is used to track inter fund receivable and payable.

    IV.            Net assets released from restrictions 

This account is normally used between the unrestricted and temporarily restricted funds and is shown as line item in the revenue area of financial statements. For example, assume organization A pays $5000 for Scholarships. Further, assume that scholarship donations are kept in a restricted fund. At the time of payment cash for the expense comes out of the general fund checking account. The journal entries would be:

1. JE-100- General Fund-Scholarship expense $5000 DR

General Fund -Cash $5,000 CR

2. JE-200-General Fund- Inter-fund receivable $5,000 DR

Restricted Fund- Inter-fund payable $5,000 CR

3. JE-300 Restricted Fund-Net assets released from restriction 5000 DR

General Fund-Net assets released from restriction $5,000 CR

Explanation of the journal entries:

  • The first journal entry records the transaction in the General Fund through the Accounts Payable module at the time a check is processed.
  • The second journal entry records the inter-fund receivable and payable because the money for this expense is deposited in the restricted fund cash account and needs to be transferred out of the general fund cash account. Eventually, when cash is transferred from one banking account to another, this entry is reversed. Management can make a policy that cash transfers occurs monthly , quarterly or on other regular basis  based on balances on inter-fund accounts or other parameters
  • The third and last journal entry recognizes the transaction as restricted. This release from restrictions entry reclassifies the expense from the general fund to the restricted fund.

It is good accounting practice to keep an audit trail of why money is being released. This may be in a separate spreadsheet with details, or even enough description in the journal entries may be adequate.

     V.            Chart of Accounts

The chart of accounts drives everything in accounting and supports accurate preparation of financial statements and other management reports. Chart of accounts should be set carefully to ensure that it is able to accommodate many funds, inter-fund receivables/payables, special financial, grant and 990 mandated reporting. Most of the nonprofit organizations set up their chart of accounts following the line items in the 990, and this is usually good practice.

Share
Posted in Accounting for Non Profits | Leave a comment

How to Detect Fraud Quickly

Posted on July 28, 2011 by esol4489

Is your organization facing Frauds threats? Below are quick transactions tests that if executed with precision may unearth these frauds. If not, but continue doing them regularly, you may scare away fraudulent activity in your organization.

There are many frauds test that can be carried out but I will focus on the following 3 major ones that we can do to detect fraud namely:

  1. Purchase Frauds
  2. Payroll Frauds and
  3. Management override of controls review

Below is a description of each of them and how we may execute them.

1. Purchase Frauds

Purchase fraud is probably the most common type of fraud in an organization. It may be the simple submission of a dummy invoice, the re-use of another valid invoice, the withholding of a credit note, or a more complex arrangement. Many frauds involve the manipulation of the payments information on personal accounts within the Accounts Payable system.

 Testing for Purchase Fraud Procedures:  

There are 5 different tests that we can test for purchase frauds. The following are the files we need to perform these tests:

a. Supplier Master File: The supplier master file contains names and addresses and usually additional information such as contact telephone numbers, bank account details, and alternative addresses which we require in this type of test.

b. Employees  Personnel Records: This should show names and addresses and usually additional information such as contact telephone numbers, bank account details etc.

Procedure:

a.) Match supplier names against a list of employee names (primary key) from a payroll or personnel file.

b.) Examine purchase ledger transactions for entries at or just below the approval level of managers. If the computer system captures the approving authority for a transaction, examine the value distribution for each manager.

c.) Examine to see if amounts are being approved at or just below, break points in authority level by a value distribution across the whole ledger. If approval authority is not directly available, perform subsidiary analysis by types of supplier or approving department.

d.) Look for split invoices to enable approval to be kept by an individual. Extract all invoices within 90 percent of an approved limit (preferably for a suspected manager or department) and search for all invoices from that supplier. Index by approving manager, department and date to identify possible split invoices or summarize payments by invoice number to determine how many part-payments have been made for each invoice.

e.) Test for duplicate invoices using value and supplier code as the key fields for one test and purchase order number for another.

2. Payroll Frauds

Payroll frauds are one of the most common types of fraud committed. Often a fictitious or ”ghost” employee is set up on a salary system with payments following automatically. This is particularly true in the case of electronic payments into bank accounts where no checks need to be collected. Other common ways to defraud a payroll system are by not removing leavers (terminations), then channeling their pay into another bank account, or by submitting excessive overtime, expense, or allowance claims.

There are 3 different tests that we can test for payroll frauds. The following are the files we need to perform these tests:

a. Payroll transactions file: This should show  names and addresses and usually additional information such as contact telephone numbers, bank account details etc.

b. Employees  Personnel Records: This should show names, SSN etc

Procedure:

Test for duplicates

a.) Test for duplicate employees on the entire payroll file, using the employees’ Social Security Numbers as a unique employee identifier.

b.) Check for duplicate bank accounts. This test may report family accounts where more than one member of a family is employed by the organization. However, these can be eliminated from the list of duplicates leaving the fraudulent items.

Cross-matches

a.) Match master information from the payroll file with the organization’s personnel file to determine whether there are ”ghost” employees on the payroll.

b.) Compare the payroll file at two dates, for example the beginning and the end of a month, to determine whether recorded starters and leavers (hires and terminations) are as expected and if any employees have received unusually large salary increases.

Exceptions  

a.) Ensure each employee’s salary is between the minimum and maximum for his/her position or grade. Test also the reasonableness of allowances to position or grade.

b.) Investigate excessive overtime and allowance claims to ensure there has been no over-claim.

c.) Compare holidays and sick leave taken to the limits for a particular grade or position, and if there is a high rate of absenteeism for sickness this could be analyzed by department to identify problem areas.

d.) Evaluate the reasonableness of tax codes and compare changes in tax code over a period.

Below is an additional audit procedure that may be expanded:

3. Management Override of Controls Review

a.) Examine journal entries and other adjustments for evidence of possible material misstatements due to fraud. For example non standard or unusual entries.

b.) Evaluate the business purpose for significant unusual transactions. Such as very complex transactions or those where the accounting does not reflect the underlying substance of the transaction.

Share
Posted in Frauds and Illegal Acts | 31 Comments

How to Use VLookup Excel Function

Posted on July 23, 2011 by esol4489

As promised in my previous post, this time I will start by discussing the Vlookup Microsoft excel function. VLookup is one of the most commonly used excel functions in data analysis today.I would like to show you with examples when and where you could use VLookup in your day to day work. Microsoft® Excel is a simple and yet POWERFUL tool if you master it.

What is Excel’s VLookup Function?

VLookup is an excel function that is used to search for specific information located in a spreadsheet table. Vlook up is also known as the vertical lookup. The lookup looks for the needed information in the first column of the table range. There is also Hlookup. But because most of the look up needs involve Vlookup, this article will focus on Vlookup.

V-Lookup formula is written as follows:

 VLookup (look up value, table range, column index (range lookup))

Below is an explanation of each of the terms:

  •   Look up value: This is the value we want to look for in the spreadsheet table.
  •  Table range: This is spreadsheet table range that we will need to search for the look up value. Here we identify the table range of cells where the Vlookup will search for the needed information.
  •  column index: This is the vertical column in the spreadsheet table range where to look for the needed information. Usually this is the first column 
  •  Range lookup: This is an optional argument. This command instructs Vlookup function to search for exact match or approximate match of the needed information. If an exact match is required we use False or 0 range look up command. If no exact match is obtained excel returns # NA response. If an approximate match is needed then we use True or 1 range look up command; alternatively we can leave the range of lookup command blank. If the range lookup argument is true or omitted and exact match does not exist, excel bases the look up on the largest value in the first column that is less than the look up value. For approximate match range look up to work correctly the first column of the table range (the column of our interest for the search value) must be in an ascending numerical order. 

Following below is an example of how to use Vlookup function.

Let us assume you are tasked with doing bank reconciliation and you need to come up with a list of outstanding checks at the end of a particular month say June 30, 2011.Since your firm uses positive pay, the bank provides your firm with a list of outstanding checks per the bank records. At the same time you have a file of outstanding checks per the firm’s internal records. The bank’s records of the outstanding checks does not reconcile to the firm’s internal records. Therefore, you will need to compare the two records and find out what are the reconciling items. This is how you solve the problem using Vlookup function.

Table A represents your firm’s internal records and table B represents the bank’s records. Follow the link below to access the Vlookup tables:

 VLOOKUP Table A and Table B

Further explanation follows below:

The Analysis:

1)      As the difference of $31,715 shows there are checks in table B but not in table A

2)     Also, there are checks included in table A but not in table B 

3)     Therefore, we will need to use Vlookup to analyse the cause  of the differences 

4)     In this example I will look for checks in table B but not in  table A 

5)     On your own look for the checks that are in table A but not in table B 

6)     The  check number is our unique identifier so our query will be based on check # 

How to write the Vlookup Function

Below is the format that we will use to construct the Vlookup function.

VLookup (look up value, table range, column index (range lookup))

 

Steps:

1)      In cell H, 1 type equal sign then type v, a menu window will open with Vlookup function among others,  double click it.

that now satisfies the first part of the formula: Vlookup

2)     Our look up value is the check # in cell F1. We want to find out if this check # is also found in table A. So click cell F 1. Now we have our look up value 

3)     Next we select our table range and in this case it is A1:C 18  then press F4 to make the selection absolute

4)     Next we select our table range and in this case it is A1:C 18  then press F4 to make the selection absolute

5)     Our column index is the first column of our table (A) selection where to look for our look up value in this case we write 1

6)     Now copy the formula down. You now have missing check numbers with N/A results. Now it is Easy!

Note1

Remember to separate each function with a comma.

Exercise

Now that you have become an expert do the following exercise on your own

On your own look for the checks that are in table A but not table B

Good Luck!

Share
Posted in Microsoft Excel Simplified | 27 Comments

The Accounting Cycle Process Flow Chart

Posted on July 23, 2011 by esol4489

This is a follow up of my blog posting of July 2 titled Detailed Description of the Accounting Process Cycle. This is a continuation of this blog posting. Below is the Life Cycle of the Accounting Process summarized in a flow chart. Hope this helps.

Share
Posted in Accounting Simplified | 21 Comments

How to Prepare for a Clean Audit and Keep out of Trouble

Posted on July 22, 2011 by esol4489

Most of us get frightened when we hear that the auditors are about to audit our section/department. Audit is inevitable the moment we became custodians of other people’s assets. Audits can be sanctioned by business owners, shareholders, government requirements etc. but whoever sanctions the audits, you do not need to get frightened when auditors visit you. We read in the news numerous cases about qualified audit reports and litigation in courts  about improver accounting.There are numerous cases involving improper keeping of books in Securities and Exchange Commission  data base. For more details go to :  http://www.sec.gov/litigation/litreleases.shtml.

The following are some housing keeping things you need to do to get a better audit report. Though this list is not exhaustive it can be the starting point.

1.       Follow the company/ business policies and procedures. Most businesses have written down policies and procedures that are supposed to direct the day to day operations of a business. These policies are normally approved by the senior management and if need be by those charged with governance. During the understanding of the internal controls process the auditor reviews your policies and procedures and determines whether they are being applied as intended. He/she goes ahead to verify whether you are using them or not to execute business transactions. Examples of failure to follow established business procedure may include; failure to have required dual signature on expenditure beyond certain limits; failure to have supporting documents to support  expenditures, unauthorized expenditures, improperly recorded transactions, etc.  The auditor will accumulate these breaches of failure to follow the company policies and procedures and report to those charged with governance in form of a report usually called findings and recommendations report. Repeated findings can have bad consequences for the employees specifically the controller/ CFO .Their job competency begins to be put into serious questions.

 

2.       Follow the relevant accounting principles when recording accounting transactions. Depending on the nature of the entity ensure that you follow the relevant authoritative standards when recording accounting transactions. Majority of business and organizations are guided by generally accepted  accounting standards (GAAP).The power to set GAAP actually rests with the Securities and Exchange commission (SEC) however except in very cases, SEC has authorized the accounting profession to set GAAP.

 

 

3.       Follow the related laws and regulations that regulate the business you operate. While  most of the company policies and procedures incorporate the relevant laws and regulations , it is  prudent to follow the related laws and regulations that regulate the business you operate just in case no guidance is given by the company’s internal policies and procedures.

 

4.       Avoid being involved in any illegal or fraudulent act. This may involve fraudulent financial reporting or misappropriation of assets. Misuse of company assets, corruption, kickbacks or even theft of any nature would not be tolerated when detected. Cheating in time sheets, making fraudulent payments, including ghost workers in payroll register, receiving bribes to award procurement contracts, misuses of company credit cards, ‘cooking the books’ etc may land you to a place you never imagined you will ever go and ruin your entire career and may even render all your hard worn academic and professional credentials worth less.

 

 

5.       Have internal independent reviews. It may be a good business idea to have regular internal  transactions reviews. An internal audit department is the most ideal solution but not all business can afford to have these departments.  Alternatively, a departments work may be reviewed by another department’s personnel on sample basis. The reviews may be done monthly, quarterly or semi annually as may be agreed by the management. Again every organization is different and both of these suggestions may not be practically possible. The other alternative if already is not incorporated in the company policies and procedures is to have segregation of duties embedded in key business processes such as initiation, authorization, recording, processing, posting of transactions and reconciliations. This will ensure that the work of one person is being checked by another; the so called checks and balances. In smaller organizations the owner has tight control over all business transactions and he / she alone may be enough in ensuring that adequate internal controls are in place.If you can afford it,then the other option is to hire an external  CPA for pre-audit accounting services to help you to  ensure that your records are audit ready.

 

6.       Hire the right Personnel. Success of  an organization is driven by having the right people in the organization.Have the right people in the departments. If a job needs certain specialized skills then hire the person with those skills.Failure to do this would be very costly in the long run.Time and gain we have seen departments run by unqualified personnel even when the organization can afford the specialized skills personnel. Let’s take as an example for explanation purposes the accounting department. In some medium to large organizations you find that from the CFO, controller, all the way to the accounting clerks there is none who is a CPA and even some of them do not even have accounting degrees. This is where the majority of the problem begins. To maintain their licenses CPA’s  go through continuous professional education (CPEs).They are constantly informed of what is going on  in the accounting professional  including new standards and regulations.They are well positioned to interpret these new rules and regulations and apply them in their respective organizations.Poor accounting records may also be costly in terms of the audit fees. The CPA/auditor most of the times will charge higher fees to audit your records.

 

These six points are some of the basics. Follow them and you will be on your way towards getting a clean audit. Otherwise it may lead to a qualified audit opinion or an audit with a lot of findings and deficiencies. Getting a qualified opinion is not a pleasant experience. This may at times lead to disagreements with the auditor/ CPA during the audit process. Manipulation of the auditor/CPA in order to get a clean opinion is not a solution to the underlying problem of improperly maintained accounting records. If this happens and a compromise is reached by both of you, then you all risk going into more serious problems including business closure. Our memories are still very fresh with what happened to Enron  Inc and  the Arther Anderson Accounting firm. For more details go to  http://news.bbc.co.uk/2/hi/business/1688550.stm  “Enron files for bankruptcy” and  http://www.forbes.com/2002/01/18/0118topnews.html  “The Scapegoating Of Arthur Andersen”.

 

In my next blog post I will introduce the 7 Accounting Transactions Cycles and go through  each one at a time for what is needed to be done to esure that  each of these accounting transaction cycles pass audit test as and when needed. So stay tuned!

Share
Posted in Auditing Simplified | 23 Comments

How to Copy and Paste Only Visible Cells in an Excel Worksheet

Posted on July 15, 2011 by esol4489

Jobs have been hard to come by these days for inexperienced accounting staff among others. The other day a friend of mine who is looking for a job told me that during interview for a junior accountant job he was asked on different occasions to take a basic Microsoft excel skills test. One of his challenges was how to filter data and then copy and transfer the data to another excel worksheet with only the filtered information. But every time he copied and pasted the data in another worksheet, the result was that all the data pasted was the original unfiltered data. This was very frustrating to him given that time was of essence! Does this sound familiar that at one time you knew what you needed to accomplish but did not know how? This time I will explain in plain English supported with an example of how to copy filtered data from one excel sheet to another using the ‘Go To Special Excel’ tool, a very powerful tool.

‘Go To Special’ Excel tool can be used in a variety of ways that may include copying and pasting only wanted piece of data, eliminating blanks among other uses. Assume you download from the company financial information system a trial balance report but you realize that after each account details information, there is blank space between each set of data. See below for a specimen of the downloaded report:

Account # Account Description Amount
11000 Travel Expense $433,221
     
11001 Training Expense $3,456
     
11003 Meals Expense $6,458,654
     
11004 Hotel & Lodging Expense $5,463
     
11005 Medical Expense $876,554
     
11008 Salaries and wanges $346

You realize that the data runs several pages and the only option you have is to delete each unwanted space manually. Unfortunately time is not on your side. So you wonder what to do next. Worry no more!

If you are using Microsoft Excel 2007 version then follow the following steps:

  1. Select the entire table
  2. Go ‘Data’ tab
  3. Click ‘Filter’
  4. Click the  ‘filter arrow’ under the account number
  5. Uncheck ‘blanks’
  6. Select the entire table again
  7. Under the home tab go to ‘Find & Select’  menu at the far right corner
  8. Select ‘Go to Special’ menu
  9. A ‘Go to special’ window opens
  10. Check ‘visible cells only’
  11. Click  ‘ok’
  12. Copy all the table again
  13. Paste the table to your destination worksheet. This command copies only the visible cells and not any hidden cells. See results below.
Account # Account Description Amount
11000 Travel Expense $433,221
11001 Training Expense $3,456
11003 Meals Expense $6,458,654
11004 Hotel & Lodging Expense $5,463
11005 Medical Expense $876,554
11008 Salaries and wages $346
Share
Posted in Microsoft Excel Simplified | 32 Comments

Find and Replace Excel Function

Posted on July 14, 2011 by esol4489

Find and replace found in Microsoft applications is a powerful function that we can use to manipulate data in any Microsoft applications like word, excel, power point etc. This tool can be used to find and replace any kind of data including blank spaces, account strings without any data, mis-spelt words, etc. In this article, I will focus on how to use this function in a Microsoft excel worksheet.

How to use Find and Replace Excel Function

Sometimes we receive accounting data from financial information systems applications but this data is not in a format that we need for further analysis. Below is an example:

1 A B C
2  Account Number   Description   Amount 
3 01-20001  Bank one   $       11,000
4 01-20002  Bank two   $         3,456
5 01-20003  Bank three   $       98,765
6 01-20004  Bank Three   $24,355,677
7 01-20005  Bank four   $       23,455
8 01-20006  Bank five   $     122,345
9 01-20007  Petty Cash   $         8,765
10 01-20008  Bank Seven- Payroll   $     997,876
11 01-20009  Bank Eight-Operating   $       12,345
12 01-20010  Bank Nine- Checking   $23,445,656
13 01-20011  Investments   $       98,765

You notice that the account number has a space between the numbers. Now you need to eliminate these spaces. But you are faced with a challenge because the only way you know is to go to the data and manually eliminate the spaces. However, the data is more than one hundred pages long. So this option is not the best because the approach will take you several hours and even if you do it, it is not full proof as it is bound to many errors.

Microsoft Excel has a function that can sort out this issue pretty fast. Below are the steps that you will need to take to solve problem expeditiously:

  1. In our example data, ensure that the data of our interest, that is, the account number is formatted as text
  2. Highlight the data by selecting cells A:3 to A:13
  3. If you are using Microsoft Excel 2007 version then go to Editing>Find and Select>Replace
  4. A Find and Replace window opens
  5. In the ‘find what’ box type  the ‘space’ that is  –     This is the item we need the excel function to look for in our selected data and then replace it (delete it)
  6. Then click ‘replace all’ tab. This function then replaces (deletes) all the un needed information. See the results below, easy eh!
1 A B C
2  Account Number   Description   Amount 
3 120001  Bank one   $       11,000
4 120002  Bank two   $         3,456
5 120003  Bank three   $       98,765
6 120004  Bank Three   $24,355,677
7 120005  Bank four   $       23,455
8 120006  Bank five   $     122,345
9 120007  Petty Cash   $         8,765
10 120008  Bank Seven- Payroll   $     997,876
11 120009  Bank Eight-Operating   $       12,345
12 120010  Bank Nine- Checking   $23,445,656
13 120011  Investments   $       98,765
Share
Posted in Microsoft Excel Simplified | 22 Comments

Excel Functions Simplified

Posted on July 4, 2011 by esol4489

Most accountants and accounting students have some basic knowledge about excel. We even know by name various functions such as VLookup, HLookup, Match, if statements, creating subtotals, Pivot tables etc, but we have no clue of how to execute these functions. We feel no need to learn about these excel advanced functions since we can always go to the internet and learn them and apply the knowledge instantly. This is unsupported assumption. The fact is, it is not always easy to most of us. I was once there. If this sounds like you, then you have come to the right place. I will walk you through most of the ‘must learn excel functions’ for an accountant and make it even easier and simpler to understand with case studies.  If you are in the accounting career you will at least one time face the challenge to apply some of the excel functions we will be discussing in my blog in the coming weeks.So stay tuned!

Share
Posted in Microsoft Excel Simplified | 34 Comments

Detailed Description of the Accounting Process Cycle

Posted on July 2, 2011 by esol4489

Our topic of the Life Cycle of the Accounting Process continues. This time I add more details about the 10 steps of the entire accounting life cycle process.

The detailed description of each of the accounting process cycle follows here below:

1.  Gathering of information About External Transactions from Source Documents

This information relates to the external transactions that involve an exchange transaction with an outside entity. They include: Sales invoices, bills from suppliers, cash register tapes etc.

 2.  Analysis of the Transactions

This involves reviewing source documents to determine the dual effect on the accounting equation, that is, determine which accounts to debit and credit.

3.  Recording of  the Transactions in the Journal

This involves recording the details of the transaction in a journal in order to post the item in the General Ledger. Journals provide a chronological record of all economic events affecting an organization.

 4.  Posting of the Journals to the General Ledger Accounts

Once the journals are recorded the next step is to record the journal into the general ledger. A general ledger is simply a collection of all the entity’s various accounts. Most accounting systems today are computerized. For these systems, the journal input information is automatically and instantaneously posted to the general ledger accounts.

5.  Preparation of  an  Unadjusted Trial Balance

Before financial statements are prepared and before adjusting journal entries are recorded at the end of an accounting period an adjusted trial balance is usually prepared. A Trial balance is simply a list of the general ledger accounts and their balances at a particular date. This step is necessary to ensure that the debits and credits are in balance. This does not mean there are no errors. Compensating errors can still make this ledger balanced.

 6.  Recording of  Adjusting Journal Entries and the Posting to the General Ledger

This is the time to record the internal transactions. These transactions do not involve an exchange transaction with another external entity and therefore are not initiated by a source document. They are recorded at the end of any period when financial statements are prepared. These transactions are commonly known as adjusting journal entries. Adjusting journal entries are required to satisfy the accrual basis of accounting. Specifically, these entries are required to satisfy the realization and matching principle. Further, the recording of adjusting journal entries ensures that all revenues and expenses are recognized appropriately in the accounting period. Adjusting journal entries include:

  • Prepayments (deferrals) -When cash flow precedes either expense or revenue e.g. supplies, deferred revenue, depreciation, rent insurance etc
  •  Accruals- When the cash flow comes after either the expense or revenue recognition e.g. salary expense, interest expense etc
  • Estimates- e.g. depreciation, bad debt expense, etc

7.  Preparation of the Adjusted Trial Balance

After the adjusting journal entries are posted to the general ledger accounts, the next step is to prepare an adjusted trial balance. The term adjusted refers to the fact that adjusting entries have now been posted to the accounts.

8. Preparation of Financial Statements

The purpose of each of the previous processing cycles to this point is to provide information for the preparation of the financial statements. The financial statements are the primary means of communicating financial information to the external parties. The four basic financial statements are:

  • The Balance Sheet
  • The Income Statement
  • The Statement of Cash Flows
  • The Statement of Shareholders Equity

9.    Closing of the Income Statement Accounts to Retained Earnings

An interim reporting period is any period when the financial statements are produced other than at the end of the fiscal year. However, at the end of the fiscal year, two final steps are necessary; closing the Income Statement accounts and preparing the post closing trial balance. The closing process serves a dual purpose as follows:

  • The Income Statement accounts, revenues and expenses are reduced to zero balances making them ready to measure activity in the upcoming accounting period.
  • These Income Statement account balances are closed and transferred to retained earnings to reflect the changes that have occurred in the account during the period.

10.  Preparation of Post Closing Trial Balance (at year-end only)

After the closing entries are posted to the ledger accounts, a post closing trial balance is prepared. The purpose of this trial balance is to verify that the closing entries were prepared and posted correctly and that the accounts are now ready for next year’s transactions.

To make the Accounting Process Flow more fun and  even more easier to understand, in my next blog post I will summarize the Life Cycle of the Accounting Process in a single process follow diagram.

You may subscribe now to be receiving our regular insightful articles

Share
Posted in Accounting Simplified | 30 Comments