AGE OF A PERSON in Years; Months & Days format

One of my trainee asked me a user defined function to calculate the age of a person with reference to a date.  This is required for him to ensure the candidates age for selection process.

My suggestion would be to use the below code in Excel VB enviornment.

The following code is named as Myage, to call from Excel.

The 2 input are date of birth of candidates and the standard date used for age validation.

Input : DOB - 04 Oct 1980, 01 Dec 2015

Output: My age is 35 YEARS; 1 MONTHS & 27 DAYS.

Copy the below code and paste in your excel VB environment in Module 1

-------------------------------------------------------------------------------------------------------------------

Function Myage(DOB, asondate)

Td = asondate
X = Year(Td) - Year(DOB)
Y = Month(Td) - Month(DOB)
Z = Day(Td) - Day(DOB)

If Z < 0 Then
Y = Y - 1
Z = Z + 30
End If

If Y < 0 Then
X = X - 1
Y = Y + 12
End If

Myage = "My age is " & X & " YEARS; " & Y & " MONTHS & " & Z & " DAYS."

End Function

Excel and Business Modeling - Project Planning - 1

We can business model with the excel; which supports us with various financial functions to understand the business results

 

The steps involved are;

 

Operational Models

1.       Staffing model

2.       Revenue model

3.       Cost of goods sold model

4.       Cost of sales and marketing model

5.       Cost of product development model

6.       Cost of operating expenses model

7.       Capital expenditure model

 

Financial Model

1.       Statement of profit and loss

2.       Statement of cash flows

3.       Investment and valuation model

4.       Balance Sheet

 

Operational Project Planning

 

1.       Define the purpose of the project (company):

 

 

2.       Define the Scope:

 

 

3.       Define the names

 

 

4.       Define assumptions and risks

 

 

5.       Define roles and responsibilities

 

 

6.       Define the master schedule and milestones

 

 

On the basis of the above business planning; we have to plan the financials with the business potential and demand analysis data.

 

Will continue,……………..

Nested IF statements in a Cell of a worksheet

One of my friend asked me; how many IF statements can I nest in a cell?

 

Interesting, It is 64 IF statements in  a cell (version 2007 and above)

 

In excel version 2007, a cell can contain up to 32,000 characters.

 

 

Lookup Functions in Excel

When we generate reports and list, we can look up values from master sheet;

 

The advantages of lookup function are

 

1.  will eliminate the repeated typing of same data

2.  will eliminate the copy paste error; i.e. accidently pasting the irrelevant data to the record

3.  dynamic data update with reference / lookup value

4.  also will link multiple workbook

5.  easy for retrieving the respective records

 

The function syntax is as follows

 

VLOOKUP(lookup value, table range, column index, [range lookup])

 

Ø  Lookup value is the reference value that computer will match and pull the row data with respective to the value

Ø  Table range, master table data to look up value; in this the first column will be used as reference value and other columns will be the value result for the look up function

Ø  Column Index is the number i.e. n^th column value for the result

Ø  Range lookup is the optional argument (True / false) ie. Exact / approximate match

 

 

Note : Excel will result the first match instance; if the master table have more than one value.  Hence use the unique id / reference for vlooup formula.

Excel formula - Relative and Absolute references

In Excel there are two references used in the formula to refer the
input value for calculations

At times we have to use the relative and absolute references to meet
our requirement of copy and paste formula with all records in the
table / database

Press the F4 key as you are entering a formula to toggle a reference
through the four possible reference types.

Press the F4 key again. Excel changes the reference from an absolute
reference to a mixed reference, with the row portion of the reference
locked

Press the F4 key again. Excel changes the reference to a mixed reference,
with the column portion of the reference locked

Net working days - calculations in Excel

Last week I have received a mail from my team members  stating that he was wasting his time to find the working days between two days to calculate the mandays calculations for the project.

I have given the below suggtions to make his job easier

Excel has a date function called NETWORKDAYS; which is a function to find out the working day i.e. Monday to Friday in between two given dates.

For example;

Let us assume - Project start date : 25 Oct 2013  
Project completion date : 31 Jan 2014

and the following are the closed holidays - 5 & 6 Nov; 25 Dec; 1, 14,15,26 Jan

The simple formula would be {=NETWORKDAYS(Startdate, enddate, [holidays])}

The formula will give you the result 65 working days.

There are 71 weekdays in that 6 closed holidays given in the range. Hence the result is 65 working days. 

The same formula may be applied to set of records in the excel database; it eliminates the cumbersome calculations and formulas for the recquired result.

Internal rate of return calculation by excel

How do we calculate IRR for a project and would excel help me to find it for a project.

I want answer in two parts i.e. what is IRR and how to apply in excel for calculation;

Discounted cash flow means;
"present value of an investment is the maximum amount a firm could pay for the opportunity of making the investment without being financially worse off."

IRR method is popularly known as time adjusted rate of return method / discounted rate of return method. 

The internal rate of return is defined as the interest rate that equate the present value of expected future receipts to the cost of the investment outlay. This internal rate of return is found by trial and error.

How to apply in excel for calculation;
In the build in financial functions IRR is used to calculate the value in the project planning

Refer the below project cash flow statement as a example:

In the five year project the IRR calculated from the total cash flow for the project with the function IRR

The syntax is IRR(Year0:Year5) of the total cash flow values.  Excel will calculate and gives the value 16.24%.

Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Upfront Investment -13,40,22,148           Cash flow from operations   2,71,51,433 4,27,40,707 4,01,61,229 3,74,01,189 3,44,47,945 Less Investment in fixed assets 0 0 0 0 0 0 Release of working capital           1,80,00,000 Scrap value of the project           1,75,56,000 Total cash flows for the project -13,40,22,148 2,71,51,433 4,27,40,707 4,01,61,229 3,74,01,189 7,00,03,945 IRR for the project 16.24%