What is Macro

v Macro is a code which consist of set of instruction, utilized to execute particular task.

v These macros are utilized to eliminate the repetitionof steps while executing particular task.

v These are very beneficiary for users while executingregular repetitive tasks.

"Sub" represents to "Sub-Procedure"
VBA code must be written "Sub" and "End Sub" only

About Macro Dialogue Box

Macro Dialogue box consists of 

v Macro Name

v Shortcut Key

v Location where macro stores

v Description

Here is the brief explanation regarding the Macro Name

 Shortcut code for Small Letters

CTRL + Character

Shortcut code for Capital Letters

CTRL + Shift + Characters

About Objects of VBA

VBA is Object based language.
Object hierarchy has been mentioned below

VBA consists of three objects

·         Workbook Object

·         Worksheet Object

·         Range Object

Excel application consists of one or more Workbooks

Workbook consists of one or more worksheets

Each worksheet consists of Range of cells

Visual Basic for Applications(VBA)

What is VBA?

 

VBA is the acronym for Visual Basic for Applications.

An abbreviation for Visual Basic for Applications

Official name is "Visual Basic Applications Edition."

VBA is Microsoft's common application programming (macro) language for Word, Excel, Access, etc

VBA, which stands for Visual Basic for Applications, is a programming language developed by Microsoft

There is no UNDO for macro actions

Loops and IF statement is heart to VBA

This macros exists for Excel, Word, Access

What are the advantages of VBA?

Through the process of VBA we can automate repetetive actions

We can generate reports in regular intervels without manual intervention

We can connect various databases through the process of VBA

We can automate Word documents and Power point presentations

What is the differnce between VBA and VB

VBA and VB having resemble in most areas but both are different.

Excel VBA interview Questions & Answers

1. What is the shortcut to go to VBA screen?

Ans: ALT + F11

2. Why we are using macros?

Ans : If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. Moreover, macro can perform lots of tasks which we can't do manually ( like looping statement)

3. What is the file extension of excel workbooks with macros in Excel 2003, 2007, 2010 ?

Ans : Excel 2003 = xls Excel 2007 = xlsm Excel 2010 = xlsm

4. Can we record a looping statement? Give some examples of looping statements ?

Ans: No. For loop, Do While Loop, Do until Loop

5. How to add a module to a VBA project?

Ans: Right Click on VBA project in VBA screen > Insert > Click on Module

6. What is the meaning of "Option Explicit"? Where it should be used?

Ans : Option Explicit makes the declaration of Variables Mandatory (it forces us to declare all variables used in our codes) Line explicit function makes the compiler to identify all the variables which are not specified by the dim statement. This command significantly reduces the problem of type errors. This is used extensively because VBA deals with information rich applications in which type errors are common.

7. Where it should be used?

It can be used inside a module, before starting any sub procedures.

8. How we will hide a sheet permanently? (How to hide a worksheet so that a normal user cannot unhide it)?

Ans :
Use Sheet's visible property and set it to xlSheetVeryHidden. We can do it in 2 ways.
1. VBA screen > worksheet properties > visible > change to xlSheetVeryHidden
or 2. Use code sheet1.Visible = xlSheetVeryHidden

9. If I require a macro to run every time when a workbook opens, can this be recorded ?
Ans: No
10. where should I add codes for this ?
Ans : goto VBA screen > double click on THISWORKBOOK > then select > Workbook_Open

11. What is the difference between thisworkbook and activeworkbook?
Ans: ThisWorkbook refers to the workbook where code is being written while ActiveWorkbook refers to the workbook which is in active state with active window. In case of only one workbook open, ActiveWorkbook is same as ThisWorkbook

12. Now I have an object variable named "Wkbook", tell the code to assign the activeworkbook to this object ?
Ans : SET Wkbook = ACTIVEWORKBOOK 

13. Code to find a Last used Row in a column or Last used column of a Row.

Ans:                                                

Last Row in a column can be find using End(xlUp) from the last Cell and Last Column in a row can be find using End(xlToLeft). For e.g. Range("A1048576").End(xlUp).Row gives last used row of Column A.

14. How to debug a VBA code?

Ans:

Using Breakpoints (F9), Step-by-step execution (F8), Debug. Print & Immediate Window and Watch window

15. What is the difference b/w Functions and Subroutines?

Ans: There are so many differences. I noted few.
i) Subroutines never return a value but function does return values.
ii) A function could not change the values of actual arguments whereas a subroutine could change them.

iii) A subroutine cannot be used in a worksheet

iv) A subroutine can manipulate the any objects. A Function Cannot.

v) A function will not be listed in the names of macros

16. Draw basic Excel Object Model?

Ans:

Application --> Workbooks --> Worksheets --> Range / Chart

17. Union is used for ___?

Ans:

To unite the different ranges depending on the logic. It is similar to set union, here range works as set. For eg. Set nrange = Union (rng1, rng2).

18. What are properties, methods, events and objects?

Ans:

All the controls in the Toolbox except the Pointer are objects in Visual Basic. These objects have associated properties, methods and events. 
A property is a named attribute of a programming object. Properties define the characteristics of an object such as Size, Color etc. or sometimes the way in which it behaves.
A method is an action that can be performed on objects. For example, a dog is an object. Its properties might include long white hair, blue eyes, 5 pounds weight etc. A complete definition of dog must only encompass on its looks, but should also include a complete itemization of its activities. Therefore, a dog's methods might be move, jump, play, breath etc.
Visual Basic programs are built around events. Events are various things that can happen in a program. Let us consider a Textbox control and a few of its associated events to understand the concept of event driven programming. The Textbox control supports various events such as Change, Click, Mouse Move and many more that will be listed in the Properties dropdown list in the code window for the Textbox control. We will look into a few of them as given below.
* The code entered in the Change event fires when there is a change in the contents of the Textbox
* The Click event fires when the Textbox control is clicked.
* The Mouse Move event fires when the mouse is moved over the Textbox.

How to Calculate Equity Value and Enterprise Value

In this article, we will see how to calculate equity value & enterprise value.

Case study

A few weeks back, a friend of mine bought a house.He was happy to share the news with all friends.He threw a big party and celebrated the purchase of this true asset. While discussing the cost of this asset, he explained the various costs attached to it.

Actual acquisition cost of the house was 15% more than the list price.

Why 15%?

It included hidden costs like unpaid bills,repairs to be done, different obligations and various registration costs.

But, my friend benefited from furniture which he got free with the house.

Ok, You will ask me what this house story has got to do with the headline of this article, right?

Now, imagine you are an equity research analyst and working on the valuation of a company to be acquired.

Take the essence of the house story in this context and you will understand the difference between enterprise  value and equity value clearly.

Equity Value vs Enterprise Value

Equity value will tell you what a company is worth and enterprise value tells you how much it would cost to acquire a company.

So, in this house story, the list price is equity value, whereas, the addition of 15% to list price would give you enterprise value of that house.

Enterprise value will take into account the debt part, obligations and the free things like cash that the company has.

Calculation of equity value and enterprise value

How do you calculate equity

Valuation of Equity Value formula

= Common Shares Outstanding * Share Price

What is enterprise value?

How to calculate enterprise value (EV)?

Enterprise Value formula

= Equity Value – Cash + Debt + Minority Interest + Preferred Stock

Why subtract Cash?

Remember the free furniture my friend got with his house purchase?

That’s a gift and his cost of acquisition would be reduced by the furniture cost.

How?

Simple, he doesn’t have to invest money in buying new furniture and that will save him a lot of money.

The same way, if you are acquiring a company having some cash (including short term and liquid marketable investments) on its balance sheet, you will pocket that cash and your acquisition cost will be reduced effectively by that amount.

Why add Debt?

If the company has debt (loans) on its balance sheet, you will have to pay that loan.

You will have to pay the debts that include short/long term debts, revolvers, mezzanine and so on.

Why do you add minority interest to enterprise value

When you own more than 50% of another company, the minority interest comes into the picture.

It is the percentage that you don’t own.

Why add Preferred Stock?

Preferred stock is similar to debt which has fixed dividend.

You will find Preferred Stock listed on the Liabilities side of the BALANCE SHEET.

Concluding note:

My friend is happy with the newly acquired house.

As an analyst, your role is to see your clients happy with the acquisition of the companies.

I hope, you are now clear with these two concepts: Equity Value and Enterprise Value.

Difference between fundamental and technical analysis

1. Technical analysis aims to estimate short term price movements, fundamental analysis focuses on determining long term values.

2. Technical analysis takes into account price and volume of the security, whereas fundamental analysis focuses on basic factors related to firm,Industry and economy.

3. Technical analysis attracts short term traders. Fundamental analysis attracts long-term investors.

So technical analysis is more to do with short-term trading and fundamental analysis is used for long-term investments.

Fundamental Analysis in Equity Research

How to do fundamental analysis

In this post, we are going to see what is fundamental analysis and how it is useful in equity research.

Many of you might have heard of this term fundamental analysis, but don’t know the meaning of it.

Stock Fundamental analysis concept

Fundamental analysis is a method of evaluating a security by attempting to measure its intrinsic value by examining related economic, financial and other qualitative and quantitative factors.

Fundamental analysts cover macroeconomic factors, like overall economy and industry conditions and management of companies. So, economy-industry-company analysis is a part of fundamental analysis.

Fundamental analysis of stocks

The end goal of fundamental analysis is to produce a value that investors can compare with current market price in hope of figuring what sort of position to take in that particular stock/company.

So, if the security is under-priced, then go for ‘Buy’, and if the security is overpriced then, ‘Sell’.

What are the differences between affiliate, associate and subsidiary companies?

All three of these terms refer to the degree of ownership that a parent company holds in another company. In most cases, the terms affiliate and associate are used synonymously to describe a company whose parent only possesses a minority stake in the ownership of the company

A subsidiary,is a company whose parent is a majority shareholder. Consequently, in a wholly owned subsidiary the parent company owns 100% of the subsidiary.

 For example, the Walt Disney Corporation owns about a 40% stake in the History Channel, an 80% stake in ESPN and a 100% interest in the Disney Channel. In this case, the History Channel is an affiliate company, ESPN is a subsidiary and the Disney Channel is a wholly owned subsidiary company.

difference between franchise and franchisee

A franchise is an investment in which you pay another business for the right to use its business model and products. A franchisor is the party granting the right, and the franchisee is the party buying the right. Essentially, the franchisor acts as a parent, guiding the franchisee through start-up procedures and providing some degree of operations and advertising support.

Relationship between franchisors and franchisees

The relationship between franchisors and franchisees has often been termed a "commercial marriage". In many ways this is true, though the difference is that in the franchise relationship there must by definition be a "senior partner" - the franchisor. Also, the franchise agreement defines the entire basis of the relationship up front, so that both parties know their rights and obligations to the relationship.

Independent Director definition

An Independent director (also known as an outside director) is a director (member) of a board of directors who does not have a material or pecuniary relationship with company or related persons, except sitting fees.

Independent Directors do not own shares in the company.

In the US, independent outsiders make up 66% of all boards and 72% of S&P 500 company boards, according to The Wall Street Journal.

In India

in India as of 2004, a majority of the minimum seven directors of public companies having share capital in excess of Rs. 50 million (Rs 50,000,000) should be independent. Clause 49 of the listing agreements defines independent directors as follows:

• For the purpose of this clause the expression 'independent directors' means directors who apart from receiving director's remuneration, do not have any other material pecuniary relationship or transactions with the company, its promoters, its management or its subsidiaries, which in judgement of the board may affect independence of judgement of the directors.

What is treasury stock

Treasury stock is stock repurchased by the issuer and intended for retirement or resale to the public. It represents the difference between the number of shares issued and the number of shares outstanding.

Example

Let's assume Company ABC decides to buy back some of its shares because it feels that Company ABC shares are undervalued in the market right now. When Company ABC acquires those shares, they become treasury stock.

Treasury stock appears at cost or at par value in the shareholders equity section of the balance sheet and thus appears as a "negative" in the shareholders equity section (known as a contra equity account). It is important to note that if and when Company ABC decides to resell treasury stock, there can be no income statement recognition of gains or losses on treasury stock transactions. That is, if the company profits (or loses) from the resale of treasury shares, it simply records an increase in cash and a corresponding decrease in shareholders' equity.

Note that purchases of treasury stock are uses of cash, and some states limit the amount of treasury stock a corporation can own at a given time (this ensures that shareholders do not jeopardize the interests of debt holders).Treasury stock consists of shares issued but not outstanding. Thus, treasury shares are not included in earnings per share or dividend calculations, and they do not have voting rights.

What is Impairments

Impairment

Accounting rules also require that an impairment charges or expense be recognized if the value of assets declines unexpectedly. Such charges are usually non-recurring, and may relate to any type of asset.

Many companies consider write-offs of some of their long-lived assets because some property, plant, and equipment have suffered partial obsolescence.

Accountants reduce the asset's carrying amount by its fair value.

For example, if a company continues to incur losses because prices of a particular product or service are higher than the operating costs, companies consider write-offs of the particular asset. These write-offs are referred to as impairments. There are events and changes in circumstances might lead to impairment. Some other examples are:

·   Large amount of decrease in fair value of an asset.

·   A change of manner in which the asset is used.

·   Accumulation of costs that are not originally expected to acquire or construct an asset.

·   A projection of incurring losses associated with the particular asset.

Events or changes in circumstances indicate that the company may not be able recover the carrying amount of the asset. In which case, companies use the recoverability test to determine whether impairment has occurred. The steps to determine are:

1. Estimate the future cash flow of asset. (from the use of the asset to disposition)

 2. If the sum of the expected cash flow is less than the carrying amount of the asset, the asset is considered impaired.

Zero Based Budgeting

Budget

Budgeting is the process of translating plans, informed by forecasts, into daily business operations. Managers budget by using forecast expectations to set limits for costs and make decisions about investments.

Budgets are extremely important to guide decisions, communicate expectations to employees, and to control costs.

A budget is a quantitative expression of a plan for a defined period of time.

OR

A budget is a quantified financial plan for a forthcoming accounting period.

zero based budgeting

ZBB is a method for preparing cash flow budgets and operating plans which every year must start from scratch with no pre-authorized funds. Unlike the traditional budgeting in which past sales and expenditure trends are assumed to continue. ZBB requires each activity to be justified on the basis of cost-benefit analysis, assumes that no present commitment exists, and that there is no balance to be carried forward.

Financial planning

Financial planning

Financial planning means to prepare the financial plan. OR Financial planning is the task of determining how a business will afford to achieve its strategic goals and objectives.

A financial plan is an estimate of the total capital requirements of the company. It selects the most economical sources of finance. It also tells us how to use this finance profitably. Financial plan gives a total picture of the future financial activities of the company.

A financial plan is also called as capital plan.

Financial planning is the task of determining how a business will afford to achieve its strategic goals and objectives. Usually, a company creates a Financial Plan immediately after the vision and objectives have been set. The Financial Plan describes each of the activities, resources, equipment and materials that are needed to achieve these objectives, as well as the time-frames involved.

Financial Planning  mathematical Equation

Financial Planning=  Financial Resources (FR) + Financial Techniques (FT)

Types of Financial Plans

Financial Planning: Process - Steps in the Financial Planning Process

There are Six Steps in the Financial Planning Process.

I.Establishing and defining the client-planner relationship 

 The financial planner explains or documents the services to be provided and defines his or her responsibilities along with the responsibilities of the client. The planner explains how he or she will be paid and by whom will be paid. The planner and client should agree on how long the relationship will last and on how decisions will be made.

II.Gathering client data and determining goals and expectations

The financial planner asks about the client's financial position, personal and financial goals and attitude about risk. The planner gathers all necessary documents at this stage before giving advice.

III.Analyzing and evaluating the client's financial status

The financial planner analyzes client information to assess his or her current situation and determine what must be done to achieve the client's goals. Depending on the services requested, this assessment could include analyzing the client's assets, liabilities and cash flow, current insurance coverage, investments or tax strategies.

IV.Developing and presenting the financial planning recommendations and/or alternatives - 

The financial planner offers financial planning recommendations that address the client's goals, based on the information the client provided. The planner reviews the recommendations with the client to allow the client to make informed decisions. The planner listens to client concerns and revises recommendations as appropriate.

 V.Implementing the financial planning recommendations 

 The financial planner and client agree on how recommendations will be carried out. The planner may carry out the recommendations for the client or serve as a "coach, " coordinating the process with the client and other professionals such as attorneys or stockbrokers.

Monitoring the financial planning recommendations 

 The client and financial planner agree upon who will monitor the client's progress toward goals. If the planner is involved, he or she should report to the client periodically to review the situation and adjust recommendations as needed

budget variance

If there is a difference between planned and actual figures, the difference is called a variance

A XYZ company's operating budget, for instance, may forecast spending for employee training. The annual training spending figure may be set first, for management and control purposes, but this can be broken down later into monthly or quarterly figures. Two quarters into an annual budget cycle, figures for budget item "Employee Training" might look something like below

A variance for each quarter is found by subtracting planned spending from actual spending. The variance is usually presented both in currency units and as a percentage of the planned figure. Here, a positive variance means that spending is over budget and a negative variance means that spending is under budget.

Large number of organizations plan spending and revenue management with a budget hierarchy. That means that planning begins with high level budgets, such as the company wide, capital and operating budgets. In these high level plans, spending items and revenues usually correspond closely to the revenue and expense items in the organization's Chart of accounts (COA). Lower level plans may further divide higher level categories, or even represent single, specific revenue or spending items.

Difference between capital budgets and operating budgets?

an expenditure qualifies as a capital expenditure (CAPEX) or as an operating expense (OPEX) depends on what is purchased, what it is used for, and also upon the country's tax laws.

Tax authorities also have a say in what may be considered a capital expense.

·         Tax authorities specify which item categories appear on the balance sheet as capital assets. On the income statement, these items create a depreciation expense for each year of the asset's depreciable life. This lowers reported income , thereby creating a tax savings.

• Tax authorities determine which expenditures for business start up cost, for instance, can be capitalized. In the India and a few other countries, the costs of professional services (for instance systems integrations services) can, under some conditions be "bundled" into the full capital costs of acquiring assets (for example, a large IT system).

Operating budgets, by contrast, cover operating expenses (OPEX), spending on predictable, repeatable costs for items or services that are not registered as capital assets and are not depreciated. This means the company charges the full amount against income during that reporting period, taking all tax savings for these expenses during that period.

Operating expenses typically represent spending for such things as:

• Office space rental and utilities costs

• Electricity bills

• Telephone and internet services
• Employee salaries/wages and overhead    
• Employee travel and training expenses
• Insurance costs
• Outside consultant fees
• Marketing communication / advertising expenses

Capital budget

Capital budgets forecast and spending for capital expenditures (CAPEX), the acquisition of capital assets usually long lasting, expensive acquisitions that goes onto the company's balance sheet as assets. On the company's income statement, capital assets contribute to depreciation expense throughout their depreciable lives.

While  deciding which capital investments to make, companies usually use a combination of formal financial criteria,as below

• NPV=Net present value
• IRR = Internal rate of return
• ROI = Return on Investment
• Payback period