Abhinay Tiwari

The Ultimate Guide to Microsoft Excel Pivot Table to Analyze Worksheet Data

In Microsoft Excel, large amount of data is handled by the Pivot table however not many people know that it is a powerful analytical tool for the managers. Before diving into the knowledge base, it is important to know the uses.

Pivot table is used for summarizing the data reports in a readable format. In addition, the information is summarized into categories and subcategories. People can easily filter and sort out the data into different subsets. By dividing the information, one can spot the trends that might help in taking decision in the near future.

Users are able to create sub totals that are quite useful for business purposes. For instance, Excel Pivot Table assists in summing up the sales information according to the region. In such a case, managers can find the location where the revenues are not meeting expectations. As an entrepreneur, you can also rotate rows and columns to view summaries in different format. There are numerous summing up functions in excel that are used to deliver effective presentations.

Creating Pivot Table:

Microsoft Excel Training suggests that creating pivot table is an absolute breeze and involves few steps as the modern version of the application is extremely fast.

Step 1: Organize The Data Into Rows And Columns

The first step is to organize the data into rows and columns and thereafter it is vital to transform the data range into excel table. In order to accomplish the task, you should click on the insert tab and invoke the table link.
If the source data is excel file, you can reap lots of benefits. The data range can be extended according to the requirements and specifications of the users. In fact, table can be expanded or contracted in real time situation.
Users can add meaningful headings to the columns so that they can be turned into the field’s name.
One should also ensure that the source table is devoid of rows and columns.
In order to name the data source, click on the design tab and enter the table name into the field located in the upper right corner of the worksheet.

Step 2: Select The Source In The Data Table

It is vital to select the source in the data table by choosing all the cells containing the vital information. In addition, people should click on insert tab and invoke the table group menu. Thereafter, click the Pivot table link located beneath the above mentioned link.

It would open the Create Pivot table window right on the screen. Inside the table range field, the whole range of cells in highlighted in the mix. One should also select the target location for the excel pivot table. The new worksheet link would create the table inside the new worksheet right from the top left corner of the sheet. Inside the location box, it is vital to click the collapse Dialog button to identify the first cell for positioning the pivot.

After the process is complete, you need to click on the Ok button in the target location. The pivot table is to be created in a separate worksheet to prevent the cluttering of information. In case, the data is being created from another work sheet incorporate the same by using the inbuilt syntax. As an alternative option, you can always click on the collapse dialog button by selecting range of cells from another worksheet.

In Guide to Microsoft Excel, you can always click on the insert tab and open the charts group. Under the menu, locate the Pivot chart button and invoke the Pivot chart or Pivot table. In other versions, one may find the same under the button.

Step3: Creating The Layout For The Data

One of the most popular aspects of the Pivot table is that it has a user friendly interface. The area in which the fields of pivot table are to be placed is also called the PivotTable Fieldslist. It is situated in the right hand side of the worksheet to segregate the header and body sections.

Field section comprises of the names of the fields that can be placed into the pivot table. Field names are in fact the column names of the pivot table. Lay out section comprises of the report filter section along with the Column labels and Row labels. The fields could be arranged in a definite format in the pivot table.

Once the modifications are made into the PivotTablefields list, they are immediately reflected in the table on the sheet. Hence, you can get the results instantly to facilitate decision making process.

Step 4: Remove Field From The Pivot Table

In order to remove field from the pivot table, you should uncheck the check box nest located in the field section of the PivotTable pane. You can also right click on the field located in the pivot table and invoke the Remove Field_name option.

Step 5: Arranging The Fields In The Layout Section Can Be Performed In Namely Three Ways

It is important to drag and drop fields between the 4 areas of the layout section by deploying the mouse. You can either click or hold the field name inside the field section of the pivot table. Once the process is completed, drag the same into the layout section to the main area.
Thereafter, right click on the field name and also locate the area where it is to be added. After accessing the layout menu, you should select the optin available for the field. It is a short and simple process that even a layman can perform.

Step 6: Select The Suitable Function To Pass Values

Generally Excel Formulas includes sum function inside the sheet by default however there are many options available for the users. In case, numeric data populates the cell, count function is activated.

In order to get customized summary function, click on the value field and select the summarize field value by option in the context menu. Some of the alternatives include average, Max and Min. To get advanced alternatives, click on the more options and summarize the data according to the specifications. One can also access the full list of functions to get the best report.

Accounting Practice: Adjusting Entries Accurately

Most corporate accounting services in Singapore are well equipped to handle all their client’s accounting requirements on a regular basis. However, as an added measure, clients are also encouraged to understand the basics of accounting so as to ensure that everything is properly tended to with no mistakes.  For those seeking to understand accounting, a good accounting practice, to begin with, would be to learn about adjusting entries. Here’s what to know:

Adjusting entries usually involve adjusting the journal entries containing information that converts the corporation’s accounting records into the accrual basis of accounting. The adjustment process is typically performed before corporate financial statements are issued. This accounting practice is usually performed in situations where accounting records for expenses/revenues have nothing entered despite having occurred or something has been entered but the amount requires further division between multiple accounting periods. Factors that adjusting entries usually affect are the income statement accounts and balance sheet accounts, since it ensures that both accounts are up to date.

Begin reviewing the balance/amount displayed in the balance sheet accounts and take note of the asset account- make sure the preliminary balance amount located in the general ledger agrees with the bank reconciliation. Check the bank statement to see if there are additional charges included, these additional charges should be entered into the cash account if they have not been done so. Next, check your list of customers for those who have unpaid invoices in the accounts receivable section. As a standard accounting practice, balance sheets must come with reports of all amounts the corporation has the right to receive- including the unpaid amounts, and not just the ones that have already been billed. Income statements should also be reporting all revenues earned- not just the ones that have already been billed.

Entry adjustments are also typically sorted by accruals and deferrals. Accruals are typically the adjustment type for entries where nothing is recorded into expense despite the corporation having incurred expense during the accounting period, as well as when the corporation has a liability/obligation for the unpaid interest up to the accounting period end. Deferrals are the type of adjusting entries that are performed when both expense and revenues have already been entered but require further division between accounting periods.

Another recommended accounting practices to minimise the need for adjusting entries would be for accountants to ensure that the expenses for each accounting period are paid in the period from when they occur. For example, arranging for expenses and other interest payments to be charged to the account at the end of every month, using the current month’s interest- this way, accountants can ensure that they will only have to record interest payment.  


How To: Import Data Into MYOB Premier

Different professional accounting firms in Singapore can often form and rely on their own systematic schedules and techniques when providing bookkeeping and accounting services. So before beginning with essential tasks, it is highly recommended for users to consolidate data into one working software program and incorporate it into their company files. As an added improvement to general user experience, MYOB Premier allows users to import external data through a variety of formats and methods for further efficiency. Here are some ways users can do so:

All data that requires importing are prefered to be either tab-delimited file formats or comma-separated file formats. It is advisable to export relevant files into the above mentioned format from external programs. To begin importing, users can locate the File menu of MYOB Premier and select import data. This will enable different data type importing options which are chosen based on requirement. It is imperative to select a method of handling duplicate records as well, to minimise problems. Once accomplished, users will be prompted to select the relevant files and open them. They will then be required to match import fields by manually selecting and clicking the relevant fields in the columns. Importing can be performed once all relevant data fields have been matched. Should the process be unsuccessful, a log file will be generated for warnings and errors.

Some common importing procedures and sources that are often used with MYOB Premier includes:

Accounts Lists (From Accountants Office)

Perhaps one of the most straightforward import options supported by MYOB Premier, users can choose to import by obtaining a text file (.txt) and pick the AccountantLink option under file menu in their MYOB software. Selecting Receive from Accountants will lead them from the submenu to a list, where users can confirm the importing process and simply open up the relevant files.

QIF (Quicken) Transactions

MYOB Premier allows the importing of Quicken transactions as long as a QIF file containing relevant transactions have been created. Be sure to deselect category tracking under the preferences window of the MYOB software before beginning the import. Users are recommended to check for any matching accounts associated with the QIF file transactions, if there aren’t any matches, they will be prompted for an account selection. MYOB Premier requires users to match their Quicken account with one account in the software or one shall be created with a default name.

MYOB RetailManager/RetailBasics

These file types can be imported by locating the setup menu in MYOB Premier and choosing preferences. The MYOB RetailManager option can be found under the system tab, once selected, users can exit the software and open up the relevant file- essential transactions will automatically be imported. RetailBasics data however, has to be exported into text (.txt) files and saved in the location of the MYOB company file so that MYOB Premier can check for the text file. Users are advised to ensure that the same tax codes and rates are being used.

5 Basic Accounting Principles and Guidelines To Understand

Accountants are highly encouraged to regularly study, practise and understand accounting principles as well as guidelines before offering bookkeeping and accounting services in Singapore. For beginner accountants, there are 5 basic accounting principles to first familiarise oneself with:

  1. Entity Assumption

As a basic accounting principle, accountants are advised to keep business transactions of sole proprietorships separate from personal transactions of the business owners. While the sole proprietorship business, as well as the business owner, are technically considered one entity, they are kept as two separate entities when it comes to accounting.

  1. Time Period

Time period assumption is one of the most crucial basic accounting principles to keep in mind- the shorter the time intervals are, the likelier it is that the accountant will be required to perform estimate amounts that are relevant to the indicated time period. A good bookkeeping and accounting practice would be to display clearly the period of time (time interval) in each income statement’s heading, as well as the stockholders’ equity statement and cash flow statement.

  1. Full Disclosure

Out of all basic accounting principles, the requirement for full disclosure is one accountant should take care not to miss. Information that is considered important to investors and lenders should be fully disclosed within the financial statements, or at least attached to them in the form of footnotes. Accountants should also monitor the going concern accounting principle while at it, since this principle would assume that corporations will continue existing long without the possibility of liquidating in the near future. If the accountant believes the corporation may not continue on, the accountant should disclose the assessment.

  1. Matching Principle

To follow this principle, corporations would be required to use an accrual basis when it comes to accounting so that expenses are matched with revenues. For corporations who decide to use services that are difficult to measure when it comes to future economic benefits- like advertising, for example, the accountant may experience trouble when attempting to match such expenses with future revenues. What can be done, is for the amount to be charged to the expense in the time period where the corporation is using the service.

  1. Cost Principle

Costs are typically defined when the amount of cash (or equivalent) is spent on products/items that are originally obtained- regardless of when the purchase happened. These purchase amounts are usually displayed on financial statements as historical cost. As a result, the asset amounts will not be adjusted upwards to reflect any increase (inflation).

Break even point

5 Accounting Equation Rules Suited For Sole Proprietorships


Accounting procedures for sole proprietorships contain certain differences compared to accounting catered to other corporations. Most corporate accounting services in Singapore are well versed enough to conduct accounting procedures for sole proprietorships. However, as a method of safeguarding and minimising potential errors, business owners are encouraged to remember these 5 accounting equation rules:

  1. Basic

The basic accounting equation for sole proprietorships are calculated with Assets being a sum of Liabilities as well as the Owner’s Equity (Assets = Owner’s Equity + Liabilities). Business transactions for sole proprietorships are recorded into specific accounts that are contained in the corporation’s general ledger. Some of the accounts that users have to pay attention to would be the Temp service expense account, Advertising account, Service revenues, Notes payable, Accounts Payable, Equipment purchase, Accounts receivable, Cash, the Owner’s Equity account and Contra Owner’s Equity account.

  1. Draws

Draws are termed for withdrawal of corporate assets by the owner of the sole proprietorship for personal use. These withdrawal of assets are not considered expenses, and therefore the transactions are not typically reported in the income statements. In the event that withdrawals were made, the accounting equation of the sole proprietorship would reflect the decrease in both assets as well as owner’s equity, thereby making the equation in balance.

  1. Loans

Another factor that affects the accounting equation would be loans from banks. Should the business owner borrow currency, there will be an increase in the sole proprietorship’s assets as well as liabilities. This transaction should be recorded in both the Cash section of the asset account, as well as Notes Payable in the liability account. Do take note that the bank loan is not considered as earned revenue, therefore there will be no effect from this transaction in the income statement. One can however, interpret two sources for the sole proprietorship assets- the owner of the company’s provisions, as well as the creditors (bank).

  1. Earned Revenue

As per standard billing procedures where payment due dates are usually after 30 days, the revenue transaction is not recorded into the capital account just yet despite the fact that both assets and owner’s equity displays an increase based on the payment amount. The amount earned should be recorded under the Service revenues section of the revenue account so that the revenues can be reported into the income statement at any given time and transferred to the owner’s capital account after the year end.

  1. Missing Amount

In the event that there are missing amounts, the accountant may rely on the statement of changes recorded in the owner’s equity to determine the components that are unknown. For example, relying on the draw amount at the beginning as well as end balances of the owner’s equity to calculate and derive the net income value.

5 Accounting Equation Methods Catered For Corporations

With the ever thriving plethora of corporate accounting services in Singapore, it is not uncommon for companies to consider the option to outsource accounting to professional accounting firms. While that may be so, business owners are still highly encouraged to familiarise themselves with the basics of accounting so as to double-check and minimise the occurrence of subtle errors. For starters, here are 5 basic accounting equation examples catered for corporations:

  1. Basic

As a general practice, the basic accounting equation for corporations is denoted with assets being the sum of liabilities as well as Stockholders’ equity (Assets = Stockholders’ Equity + Liabilities). Different transactions will affect the corporation’s accounting equation, balance sheet and income statement in differing ways. Pay attention to the cash amount, accounts receivable, equipment, common stock, notes payable, retained earnings, treasury, service revenues, advertising expense, accounts payable and temp service expense amongst other factors.

  1. Stock

When stocks contain no par value and no stated value, the assets of the corporation as well as stockholders’ equity would increase in an equivalent amount, thereby balancing out the accounting equation. This accounting equation reveals that the source of the corporation assets are from the stockholders. Stocks that have been purchased from the stockholders by the corporation would be stored in the Treasury stock. In situations where corporations purchase their own stock for cash, the corporation’s total assets as well as stockholders’ equity decreases equivalently.

  1. Equipment

Another factor that affects the way the accounting equation is applied would be the purchase of equipment. Equipment purchasing is typically not treated as an immediate expense- the purchase only serves to become a form of depreciation expense only when the equipment has been placed into service. Pay attention to the date where the equipment is placed into service. If it hasn’t been done so, then there would be no expenses in the particular transaction, or earlier transactions when reported in the corporation’s income statement.

  1. Service Revenues (Earned Revenues)

Payment and billing methods differ from case to case. However, most corporations choose to bill their clients and offer a 30 day leeway before the payment amount is due. Once the corporation has performed and completed the service for their clients, this automatically means that the corporation has earned revenues, and reserves the right to receive the agreed upon amount from their clients. In situations like these, the stockholders’ equity in the accounting equation will increase. However, one must take care to note that the revenue transaction is instead recorded in the Service revenues section instead of directly into the Stockholders’ Equity account.

This is usually to provide corporations with the opportunity to report the revenues account through it’s income statement at any given time. The amount located in the revenues accounts will only be transferred into the retained earnings account after the year end.

  1. Temporary Service Expense

If corporations outsource work to temporary help services, the invoice can be recorded immediately while the amount is paid only after a certain amount of days. The accounting equation will then be amended to reveal that the corporation’s liabilities increase whereas the stockholders’ equity decreases by the amount of payment that is owed to the temporary help services. The liabilities incurred should be recorded under the Accounts Payable section whereas expenses should be recorded in Temporary Service Expenses.


Handling Miscellaneous Cash In MYOB Effectively- What To Remember

Miscellaneous cash can be handled in multiple different ways by using the MYOB software. To do so, certain steps have to be undertaken to do so. If you find yourself with excess miscellaneous cash, you can deal with this in MYOB by using these methods. Here’s what you should remember and try out:

Voiding Cheques

In the event that cheque forms are damaged, cannot be used for printing, contains mistakes and other relevant circumstances, you will want to consider voiding the cheque. To void cheques you have not recorded into your MYOB software, look for the Banking command centre in your MYOB software and choose Spend Money so that the window opens. Look for your cheque account and type in the cheque number you want to void under the cheque number field. Make sure to leave the amount and account allocation options blank and choose record. Once you are done, choose OK and your cheque will be voided.

If your cheque had already been recorded and you want to void it, you will have to change your transaction. Take note that paycheques, refund cheques and bank deposits that have been recorded will not be able to be changed- You can only reverse the transaction. To do so, find the transaction and look for your edit menu to select the reverse option. You will then be able to record the reversal transaction without fuss.

ATM Withdrawal Record

Look for your banking command centre again and choose the spend money option- You will be prompted by the MYOB software to choose your account. Pick the account you withdrew money from and enter a non-numeric word/phrase into the cheque number section so that you can describe your withdrawal clearly. This is just so you can know that the transaction is not an actual cheque when it is subsequently listed in a report. You will then be able to skip the transaction when you have to print a numbered cheque range. Once you have finished with the details, click record again.

Miscellaneous Cash Payment Record

If you have miscellaneous purchases that your business does not regularly deal with, you can record a miscellaneous cash payment. Open the Banking Command Centre in your MYOB software again and choose the spend money option, you will be able to enter payment details once the window pops up. You can then decide if you wish to pay from your account or by grouping it with electronic payments. Make sure to enter the relevant details, including your cheque number and date as well as transaction amount. Fill in as much detail as possible before selecting record.

Miscellaneous Cash Receipt Record

Miscellaneous cash receipts can sometimes be received from suppliers, investors, loan providers and other related personnel. When this happens, you can decide to record it by using the receive money option from the Banking Command Centre. You will then be able to enter the details of the receipt you have received accordingly.

These are the highly recommended methods of dealing with miscellaneous cash in your MYOB software. While everyone has different methods for handling them in multiple situations, these are highly recommended in order to keep your data accurate. The more consistent you reach out to take the additional effort, the easier it would be to gain better data and plan ahead for your company’s progress.

Page 1 of 1812345...10...Last »