Find Last Record

Do you know if you have to find the last record value in a column

a)      Use CountA(column)

a.      It would give the number of items in that column

b)      Use offset Function

a.      Use the Offset refering to row increment of the above value

 

Frequently used Excel Short Cuts

Now, let us see Top 10 shortcut keys we use on day-to-day basis. Of course we will use more than 10 shortcuts but these are the most required shortcut keys while working on Excel.
  

1. To select the data in a sheet - - > CTRL + A (active cell should be within the data)
    
• To select the complete sheet - - > CTRL + A ('once' if the active cell  is outside the data, 'twice' if the selection is within the data)
   
• After selecting data if we want to see corner cells of each side - - >CTRL + . (dot)
   
2. To filter the data set - - >CTRL + SHIFT + L (hold CTRL and SHIFT then press L)(To activate filters and to deactivate filters)
    
• To activate the filters we can also do ALT + D + F  + F (this option is imported from Excel 2003)
   
3. To auto adjust the cell width and height - - >ALT + H + O + I and ALT + H + O + A
    
• To auto adjust we can also use double click from mouse.
   
4. To use Format Painter - - >ALT + H + F + P (select the format you want to add to clipboard and then select the cell or data where the format to be applied)
    
5. To select the appropriate formula, type the first letter in the cell after '=' and choose one among the list by using down arrow and then press TAB to auto select the formula. This will actually avoid typo error and also time. eg: To select Vlookup, type =VL and then TAB.
    
• To refresh the formulas in the work sheet and work book  - - > Shift + F9 (function key) and F9.
6. While working on formulas take example above as =VLOOKUP( 'after coming here press CTRL + A will get formula arguments. This will help the new bees to know how to import the data to the arguments.
    
• To select the name manager - - > CTRL + F3 (function key).
7. To convert the data into a Table format use - - > CTRL + T (then ENTER).
    
8. To get data validation list - - > ALT + A + V + V + TAB + L + ENTER (looks complicated but very useful).
    
9. To apply 'all borders' for the data selection - - > ALT + H + B + A
    
10. To edit and copy the above cell content  - - > CTRL + ' (apostrophe)

The bonus shortcut 1 is - - > CTRL + 1 (one) gives the format cell window and this is very good features in Excel. We can use this shortcut for data format, table format and graph format.

Source 

http://www exceltoxl com/index php?p=50&more=1&c=1&tb=1&pb=1

String Extract options

Hi,

 

Here are various method to work on some data within the string

 

Data

A5 = Custom Audience Blend 110.00 132,439.34

 

 

1)      Need “ data after first Space”

A6                         =             =TRIM(MID(A5,FIND(" ",A5),100))

Output                 =             Audience Blend 110.00 132,439.34

 

2)      Put a Special character for the last set of data easy to split

 

A6                         =             SUBSTITUTE(A5," ","^",(LEN(A5)-(LEN(SUBSTITUTE(A5," ","")))))

Output                 =             Custom Audience Blend 110.00^132,439.34

 

Varaible               Len(a5)                                               =             39

                              (LEN(SUBSTITUTE(A5," ","")))                      =             35          

                                             (SUBSTITUTE(A5," ",""))                 =               CustomAudienceBlend110.00132,439.34

 

 

 

               Repeat the same process on the resultant data to extract number of last columns

Read from Right

Q: Is there a function to read values starting from the right of a cell and pulling characters until I reach a space?

Two examples:
SGA3220R_IAOAKTREE (BTL-3) 1177 64089 - would return "64089"
SGA3321R_IAREDROAD (BTL-3) 1177 975 - would return "975"

I know we could do a Text to Columns with a space delimiter but if there are multiple spaces it would distort the data.

 

 

Try using this formula

=RIGHT(A2,(LEN(A2)-FIND(" ",A2)))

Use Search command in Vlookup

Vlookup can also pick the like fields – here is the command

 

 

Search	Name	ID	DOB	Team
eo	Vikram Deo	0012327	12/12/2012	1

 

Search	Name
eo	=VLOOKUP("*"&H2&"*",'Employee List'!$A$2:$D$11,1,0)

 

Is Italic Function

Public Function Isitalic(c As Range) As Boolean

 

On Error GoTo Handler

Isitalic = c.Font.Italic

Exit Function

 

Handler:

If Err.Number = 94 Then Isitalic = True

 

 

End Function

Related Party Transactions :: Clause 188 of New Company Bill

In old company law there were few section which were regulating the transaction with related parties whereas in new bill clause 188 covers all the provision of old act and also widens the scope. Under new bill, Section 2 (76) describes the meaning of related parties with reference to company.

 

Who are all comes under this section let us see:

 

1.      Director or his relative.

2.      A key managerial personal or his relative.

3.      A firm in which directors, manager or his relative is a partner.

4.      A private company in which director or manger is member or director.

5.      A public company in which director or manger is director or holds along with his relatives, more than 2% of paid up share capital.

6.      Anybody corporate whose board of director, managing directors or managers is accustomed to act in accordance with the advice, direction or instruction of a director or manager;

7.      Any person on whose advice, direction or instruction a directors or manager is accustomed to act.

Provided that nothing in the sub clause (VI) and (VII) shall apply to the advice, direction or instruction given in a professional capacity.

8.      Any company which is –

a. A holding, subsidiary or an associate company of such company ; or

b. A subsidiary of a holding company to which it is also a subsidiary;

 

9.      Such other person as may be prescribed;

 

After coming across the related party definition let us analyze the “related party transaction under new bill –

 

Clause 188 of the new bill 2013, deals with the related part transaction. In the old act only two clauses were explaining the types of transaction to which board sanction is required to enter in to the  contract. i.e.,

 

1. For the sale, purchase or supply of any goods, material or services; or

2. For underwriting of shares in or debentures of the company.

 

The old act was silent about the contract involving immovable properties. However in the new bill, considering the above problem, it has introduced a 5 more clause to the section 188 to specify types of contract which requires board consent.    

 

They are as follows:-

 

1.      Selling or otherwise disposing of, or buying, property of any kind;

2.      Leasing of property of any kind;

3.      Availing or rendering of any services;

4.      Appointment of any agent for purchase or sale of goods, materials, services or property;

5.      Such related party's appointment to any office or place of profit in the company, its subsidiary company or associate company; and

 

However sub clause (f) was already covered under section 314 of the Companies act, 1956.

 

Previously on Companies Act, 1956, Section 297 Provides the Provision as below-

 

Except with the consent of the board of directors of a company, a director of a company or his relative, a firm in which such a director or relative is a partner, any other partner in such a firm or private company of which director or member or director, shall not enter into any contracts.

 

However in the new Bill section 188 wipes off the sub clause (1) of section 297 of old companies act and has inserted as follows – “(1) Except with the consent of the Board of Directors given by a resolution at a meeting of the Board and subject to such conditions as may be prescribed, no company shall enter into any contract or arrangement with a ‘related party’ “. And hence it has brought many transactions in to the boundary of this clause.

 

Let us analyze with a small situation wherein how the old Law and new bill will implicate its provision –

 

“XYZ ltd enters into a contract of leasing of a property with ABC ltd in which XYS ltd.’s director Mr. H holds more than 2% of the Paid up capital of ABC ltd.”

 

According to Companies act, 1956-

 

Section 297 (1) (a) /(b) doesn’t provide any provision that brings the Leasing contract also requires the consent of the board and also there is no directors interest  in the later company  and hence the Board’s consent is not required for the above said contract.

 

However according to New Bill, 2012

 

Sub Clause (c) to subsection (1) of section 188 includes the contract of leasing also require boards consent and section 2 (76)(v) brings in its definition of related party that  if a public company in which a director or manager is a director or holds along with his relatives, more than two per cent of its paid-up share capital.

 

Thus Boards consent is required for the above mentioned transaction.

 

Therefore we can conclude that the new Company Bill,2012, has done much effort to overcome  form the many loopholes of the previous act and to broaden the scope of the regulation over the companies by inserting many new provisions so as to cover all the drawback. However MCA has notified only 98 section of the Company bill. Clause 188 has not yet notified by the MCA but on phases it will be notified by the MCA.

 

is bold function

You can use to check if the particular item is a bold or not

 

Public Function IsBold(c As Range) As Boolean

On Error GoTo Handler

IsBold = c.Font.Bold

Exit Function

Handler:

If Err.Number = 94 Then IsBold = True

End Function

 

Privileges of a private limited company

Section	Description of the matter
3(1)(iii)	A Private Company need to have Minimum paid-up capital of Rs. 1 lakh as against Rs. 5 lakhs for Public Company.
12(1)	A Private Company can be formed by just two persons as against minimum seven persons required for incorporation of a Public Company.
58A	Deposits taken by Private Company from its members are exempt from the rigors of this Section. As per the provisions of this Section read with rule 2(b) of the Companies (Acceptance of Deposits) Rules, 1975 — amount received from its shareholders by a Private Company (provided the shareholder concerned furnishes at the time of giving the money to the Company, a declaration that the amount is not being given out of funds borrowed or accepted from others) is not included in the meaning of deposit. If the depositor ceases to be a shareholder, the deposits made by him cease to qualify for exemption from the date of such cessation
70(3)	A Private Company need not file Statement in lieu of Prospectus with ROC.
77(2 & 3)	There is no prohibition on a Private Company, which is not a subsidiary of a Public Company, to provide financial assistance to anyone for purchasing or subscribing for its own shares or of its holding Company.
81	A Private Company including subsidiary of a Public Company can issue its further shares to any person in any manner as it thinks best in its own interest
85 to 90	The Provisions of these Sections deals with kinds of share capital and that voting rights should be proportionate to the paid-up capital, prohibiting disproportionately excessive voting rights. These Sections are not applicable to a Private Company unless it is a subsidiary of a Public Company and such Company may issue share capital of any kind and with such proportionate or disproportionate or other voting rights as it may think fit.
108, 109, 110	The provisions of these Sections are about transfer of shares and debentures which shall not prejudice any power of a Private Company under its Articles to enforce the restrictions in rejecting a particular transfer of shares of the Company.
111(13)	The right of appeal to the Company Law Board against rejection of a transfer of shares is not available as long as the Private Company is only enforcing the provisions of its articles in rejecting a particular transfer. It appears from this section that a right of appeal will be available where the rejection is outside the provisions of the Private Company’s Articles. The right of appeal is also available where there is transmission by court sale or sale by other public authority [s. 111(11)]
149	Procedure for obtaining Certificate of Commencement of Business do not apply to a Private Company. A Private Company can commence its business as soon as the Certificate of Incorporation is issued by the Registrar of Companies.
165	Private Company is not required to hold statutory meeting or prepare any statutory report.
170 to 186	The Provisions of these Sections relating to General Meetings applies to a Private Company unless in any particular Section it is specifically expressed that the applicability is not intended or unless the Articles of a Private Company which is not a Subsidiary of Public Company make any other provisions in respect of any of the matters covered by these Sections. Relaxation in the length of Notice for calling General Meeting, contents and manner of Service of Notices, Explanatory Statements, Quorum for meeting, Chairman of meeting, Restrictions of voting rights, etc. can be made to the extent to which the Company makes provisions in its Articles.
192A	Passing of resolution by Postal Ballot is not relevant for Private Company.
198	Ceiling on overall managerial remuneration not applicable to a Private Company. A Private Company, which is not subsidiary of a Public Company, may remunerate those in management, by such higher percentage of profits or in any manner as it may deem fit.
204	Restrictions on appointment of any firm or body corporate to office or place of profit is applicable to a Private Company which is not a subsidiary of Public Company.
220	Only the Member of Private Company which is not a subsidiary of Public Company is entitled to inspect or obtain copies of Profit and Loss Account of the Company .
224(1B)	The ceiling on the number of Companies an Auditor can audit, does not include audit of Private Limited Companies.
252	Minimum Directors for a Private Company is 2 (two) against 3 (three) in case of Public Co.
255 & 256	The Provisions of appointment of Directors and proportion of those who are liable to retire by rotation are not mandatory to a Private Company which is not a subsidiary of a Public Company
257	The provision requiring to give 14 days notice by new candidates seeking election as directors and depositing of certain amount (Rs. 500) are not mandatory for Private Company which is not a subsidiary of Public Company.
259	Central Government approval for increasing number of directors beyond the permissible maximum (presently 12) not required for Private Company which is not a subsidiary of Public Company.
262	The provision relating to manner of filling casual vacancy among directors and the duration of the period of office of those so appointed do not apply to Private Company which is not a subsidiary of Public Company.
263(1)	Appointment of two or more persons as directors by a single resolution can be done by Private Company which is not a subsidiary of Public Company.
264	Filing of consent of candidate for directorship with the Registrar of Companies is not applicable to Private Company which is not a subsidiary of Public Company.
266	Restrictions on appointment of director and subscription to qualification shares are not applicable to Private Company
268, 269	Central Government approval for amendments relating to appointment/re-appointment of a Managing Director/Whole-time Director/not liable to retire by rotation is not required by a Private Company which is not a Subsidiary of a Private Company.
270-273	Requirements of qualification shares holding by directors the time within which the qualification shares to be acquired and filing of a declaration by each director of the qualification shares held, is not applicable to Private Company
274(1)(g)	The disqualificationunder this Section does not include directorships of Private Company
274(3)	A Private Company which is not a subsidiary of a public Company may in its Articles provide special grounds for disqualification for appointment of person for the office of a Director.
275 to 279	The Directorships of Private Companies are not to be considered while calculating the limit on number of Companies in which a person can be director.
283 (3)	A Private Company may in its Articles provide special grounds for vacation of office of a Director .
292A	Provisions relating to formation of Audit Committee are not applicable.
293	Restrictions on certain powers of Board of Directors regarding selling, leasing, remitting or giving time for payments of debts, investing or borrowing moneys, or contributing to charities other than for political purpose are not applicable to a Private Company which is not a subsidiary of a Public Company
295	Restrictions on loans to directors/relatives, etc. does not apply to Private Company
300	No restrictions on interested directors from participating in the proceedings of the Board and exercising their votes are applicable to a Private Company which is not a subsidiary nor a holding Company of a Public Company
309, 310, 311	A Private Company which is not a subsidiary of a Public Company, is free from restrictions on payment of remuneration to the directors or increase in their remuneration. The procedures like filing Form 25C not required in case of Private Company
317	Restriction on period of appointment of managing director/manager for more than 5 years at a time do not apply to Private Company unless it is a subsidiary of a Public Company.
349, 350	Provision relating to the determination of net profits and ascertainment of depreciation shall not apply to a Private Company.
372A	Restrictions on giving loans or guarantees to other Companies or on making investment in the shares of other Companies do not apply to Private Company unless it is a subsidiary of a Public Company.
386, 387, 388	No. of Companies in which a person may be appointed as manager, the remuneration of a manager and the application of Sections 269, 310 to 312 and 317 in relation to managers do not apply to a Private Company unless it is a subsidiary of a Public Company.
409(3)	Powers given to the Central Government to prevent change in the Board of Directors are not applicable to a Private Company unless it is a subsidiary of a Public Company
416(1)	Restrictions on Contract by agents of the Company in which the Company is the undisclosed principal shall not apply to a Private Company which is a not a subsidiary of a Public Company.

 

Subtotal Values

The following are the values in subtotal for a function

 

=SUBTOTAL(function_num,ref)

• Function_num corresponds to the number of the function that you use to calculate the subtotal:

·         1  AVERAGE

·         2  COUNT

·         3  COUNTA

·         4  MAX

·         5  MIN

·         6  PRODUCT

·         7  STDEV

·         8  STDEVP

·         9  SUM

·        10  VAR

·        11  VARP

                               

• Ref is the cell range that you want to subtotal.

 

 

Service Tax on Builders

Please refer CBEC Circular No. 151/2/2012-ST, dated 10-02-2012, 108/02/2009-ST dated 29.01.2009 for taxability of the flats given to landowners and for valuation , please refer section 67(1) (iii) read with rule 3(a) Service Tax (Determination of Value) Rules, 2006.

 

You will get the answer of all your queries.

 

 

---

DISCLAIMER: The information contained and transmitted by this electronic mail (email) is proprietary to i-Vista Digital Solutions Pvt.Ltd (i-Vista) and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this email may not have been sent with the formal approval of i-Vista. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, disclosure, transmission, printing, copying, or dissemination of this information either whole or partial, in any way, or in any manner is strictly prohibited. If you have received this communication in error, please delete this email immediately & notify i-Vista at postmaster@ivistasolutions.com

Tax Planning - Tool to Reduce Cost - Indirect Taxes

“Tax planning” needs to be differentiated from tax evasion wherein one suppresses recording his sales/ services or undervalues the same deliberately. Tax planning involves the arrangement of ones affairs in a way in which one can pay the least amount of tax. Surprisingly there is any article on tax planning in indirect taxation though 100’s on direct tax. In India IDT accounts for double the DT if one were to add VAT. In these difficult times when everyone is trying to offer better quality / choice of services/ goods at lower prices and providing value added services, industry and trade may do well to look at this area in greater depth. In this paper an attempt has been made to touch on some possible areas of tax planning and also incidental value adds which may arise due to this exercise.

Tax planning under IDT could be in the following segments:

1.       Claiming an exemption- Small scale [ available upto Rs. 150 lakhs per independent entity manufacturing unbranded/ own branded goods] or Location based exemption which are now on the way out but available in some places. Examining whether services are as per definition or excluded ; in negative list; or exempted fully or partially. Today locating outside India is also an option for reducing costs which large business houses have found useful especially if they have global customers.

 

2.       Deciding on the method of doing business – Dealer, manufacturer, service provider [ centralized registration/ ISD] , through job workers, import with MRP or otherwise. Each would have to be examined on its effect on the final tax/ duty payable.   

 

3.       Deciding on form of organization- The option of being an SEZ, 100% EOU or a domestic unit could be examined. Here the net benefits considering the export incentives, attendant paperwork, ted tape and corruption are all major factors to be considered.

 

4.       Evaluating long term decisions considering the impact of IDT – Transaction structuring is a one time exercise but difficult to change once resources committed. As and when new products thought of being added, the alternatives could be examined.

 

5.       Claiming deduction from payment of tax – credits in the form of ITC [ Input Tax Credit – VAT] or cenvat credit [ Manufacturer/ service provider] Perhaps this is the area where maximum advantage could be possible. Not availing ineligible credits is also a value adder as indirectly it avoids cost of interest and penalty added to time and effort involved in resolving the dispute. Invariably the reduction in cost of goods used is a benefit of credit optimization exercise.

 

6.       Claiming a deduction from value for payment of duty/ tax – Invariably any deduction comes with conditions to claim the same. Hereagain the comparison and whether the IDT can be passed on would be critical to the decision. Decision to go under the regular scheme for those who account all transactions would invariably be advisable to the composition scheme under VAT.

 

7.       Examining the available benefits of doing certain transactions – Getting contract under International competitive bidding entails one to get some benefits in terms of import, local purchases for which oen needs to prepare proper documentation and make applications in time.

 

8.       Examining the benefits available to certain large customers – Certain sections have been given some benefits and like the Defense sector where the possible procurement of goods without payment of customs etc is possible.

 

9.       As an exporter ensuring all alternatives of import and export examined to minimize the cost of materials and maximize benefits as available under the Foreign Trade Policy. Accumulation of credits and their resolution for Exporters could also be a challenge worth looking at.

 

10.   Minimise the disputes and costs of litigation. Being clear on what one is doing avoids the sapping of resources as well as at times confidence of the person.

 

11.   Minimise the possibility of revenue audits [ pay in time and file returns in time] Once audit is fixed minimise the time of departmental audit cost [ interest/ penalty] by being ready.

 

12.   While interpreting laws to ones advantage have a policy of full disclosure in acknowledged disclosure to avoid charges of suppression and consequent penalty and longer period demands.

 

13.   Be updated to take advantage of changing laws. The recent high court decisions on reimbursement of expenses not being taxed, chit funds being out of net,  accommodation, supply of food by hotels not being taxable under service tax are examples of proactive change in billing.

 

14.   ..many more

 While advising/ examining transaction w.r.t. IDT laws the other complementary areas of value addition could be as under:

a.       Advice on integration of IDT to main ERP- Very rarely even large business houses have SAP or other ERP linked to the VAT or Central excise or service tax. Duplication and more importantly manual/ excel errors are likely.

b.      Automation serves purposes of ensuring completeness of entries and ease of getting information. However the block to back dated entries should be built in. The overstaffing or under staffing issues could also be highlighted. 

c.       Management Information Systems do not normally highlight the information on IDT though the same constitutes 20-25% of cost in most organsiations. [ Ratio of purchases to sales should be somewhat equal to the ratio of credits to the total payment due. ] This could be a good preventive as well as corrective check on optimizing credits.

d.      The internal audit to include the areas of IDT check. [ 90% of organizations conducting internal audit do not have IDT in scope!!]

e.      In the IDT review some incidental errors in inventory levels, material usage, reconciliation, job workers stocks, obsolescence would also be incidental information for management.

The organizations which plan for IDT from the starting and when they go for expansion/ large project analysis would find that they are able to be more cost effective. Those who have built in the regular health check in their many models to ensure cost control and reduction would also benefit by the same.    

Madhukar N Hiregange

.

---

DISCLAIMER: The information contained and transmitted by this electronic mail (email) is proprietary to i-Vista Digital Solutions Pvt.Ltd (i-Vista) and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this email may not have been sent with the formal approval of i-Vista. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, disclosure, transmission, printing, copying, or dissemination of this information either whole or partial, in any way, or in any manner is strictly prohibited. If you have received this communication in error, please delete this email immediately & notify i-Vista at postmaster@ivistasolutions.com