# Data Analysis by Date

Recently, I was given sales data to be analyzed by date. It was suspected that more sales is done towards the end of month to meet the month end target. Essentially they wanted to know the sales pattern in months across the years to be divided between dates 1-20, last 3 days and remaining days. Different ending date of months made the problem difficult.

The function EOMONTH was there for rescue. It returns the last day of the month that is the indicated number of months before or after start date. It is extensively used in financial world.

I have created dummy data to show the analysis. First I created helper columns to breakdown the problem. Columns were created for first date of the month, 20th date, 3 days before last date and last date.

By giving -1 as months after start date and then adding 1 to it gives me first date of the month. The last date of the month was EOMONTH function with 0 month after start date. Finding 20th date of the month and 3 days before the last date is matter of simple adding and subtracting number.

After making helper columns using simple IF function did the job for me. Then I collated the functions in helper column to make a single cell formula. It did the job for me. Later I used pivot table for further analysis.

Thanks!

# NPV, IRR and their variants

Net Present Value (NPV), as the name suggest it is the sum of present value of outflow  and inflow cash discounted at a rate. Lets assume some Cashflow (CF) and find out the NPV using discount rate formula

At a discount rate of 8%, Present Value (PV) factor is calculated and multiplied with CF. The the discounted CF gives the NPV. The same can be achieved by using NPV formula as seen in the screen shot.

Internal Rate of Return (IRR) is the rate at which NPV is zero. If we separately find out the sum of NPV of outflow and inflow using a rate, we will get a value. Using Goal seek function of excel, we set the target value of sum to zero by changing the rate cell, we will get IRR in the rate cell. The same result can be achieved by using IRR function.

Now that we have learned the basics lets move forward. The NPV and IRR function assume the CF to happen annually, which may not be the case. For that we have functions like XNPV and XIRR at our disposal. Lets assume some random dates and find out the XNPV and XIRR.

We can observe the difference between two types of function.

People working in Project finance related assignment know that IRR is an optimistic number because the intermediate CF are reinvested at IRR, which may not represent the realty and hence making IRR a highly optimistic or inflated rate of return. To counter this we use Modified IRR (MIRR). MIRR considers both the cost of the investment and the interest received on reinvestment of cash.

Finance rate is the rate at which cash outflow is funded, usually cost of capital. Reinvestment rate is the rate at which intermediate CF are assumed invested, usually a mutually agreed conservative rate. Therefore MIRR represents a relatively fairer rate of return on the investment. Lets again assume some numbers and verify this.

We can see that MIRR is lower than IRR, when a conservative reinvestment rate is used. MIRR equals IRR when FR & RR equals IRR, which cannot be true in most cases.

But wait, again MIRR assumes annual cashflow, what if CF are not annual. And there is no direct function for it!

This requires a deeper understanding of how MIRR is calculated. Lets recalculate the MIRR in above example.

As you can see in above screen shot that outflow and inflow are separated. For outflow the NPV is found using Finance rate. For Inflow the future value is found using formula at time period 5. The MIRR is calculated using IRR function with NPV of outflow and total future value of inflow.

We have to use above logic to calculate MIRR with non periodic CF, lets call it Non Perodic MIRR (NP MIRR).

We calculate the present value of outflow using XNPV and future value of inflow using yearfrac function and formula. After getting the value we simply calculate the XIRR of CF, which gives us the NP MIRR. We can see that MIRR and NP MIRR are different.

Thanks!

# Project Management (Time)

In Project Management, we essentially manage Time, control Cost without compromising Quality of the work. In today’s blog entry I present a worksheet which I made recently to manage time and cost of a Project.

Below you see the snapshot of Summary of the Report. The first two columns tells you about the number and name of ongoing Projects. Next 5 columns tells you about the financial status of the Project. Like Budget Amount vs Budget Consumed etc. The remaining columns tells you the status the different activities whether they are completed, ongoing or not yet started.

However the activity status columns do not give us the detailed picture, the management may need. For that we need to refer next Project worksheets.

Below you see the snapshot of Project worksheet. For different activities Planned vs Actual dates are given. Based on dates the excel Gantt chart gets mapped, blue for planned and orange for actual. The dates are on weekly basis, can be put on daily basis. Filters are provided to view only planned or actual activities. % Done column is provided for the Manager to fill the % activity completed as on date.

I have used conditional formatting for making the excel Gantt chart. The dates and values assumed are approx. User need to correct it as per his/her need. Hope you find it useful. Link to file:-

Other related entry :-

Gantt Chart

# Excel file embeded in MS Outlook

For some reason one of my colleague wanted to embed the excel file attachment in the main body of outlook letter he was typing. However he  wasn’t getting through as the embedded image was large and showing content of file. The display as icon option was also not working.

Later we found by right clicking on the file image throws you option to convert the the file to other formats where the display as icon option is live. By selection the option the file is embed nicely in mail body.

I use office 2010.

# Chandoo.org KPI performance charts – My entry

Recently the contest entries of chandoo.org KPI performance charts & dashboards were published, the winner are yet to be announced. Many of the entries are amazing and great to learn from. I also participated in the contest and will discuss my entry today. The details of contest and other entries can be seen on the website. The contest was about showing variance analysis of KPI data from bank. I made an interactive chart in which the KPI could be selected from the dropdown.

The data in base, corresp, target shows variance only w.r.t to current. Green and pink bars show positive and negative variance respectively.

# Marginal percentage within a slab

Recently I was asked to make an incentive plan with following slabs. One who achieves his target within the slab will get corresponding percentage of his bonus.

Therefore someone who achieves his target with say 45% will get 25% of his bonus amount. Target achievement of above 100% will also get bonus as per highest slab. This task was fairly easy with nested if and easier with vlookup function.

However later it was discussed that it would be unfair to give same percentage of bonus to someone who achieve 45% and other 25% of his target. So it was decided that the percentage will be on pro-rata basis within that slab. Further anyone who achieves his target by more than 100% will get incremental 120% of his bonus for every one percent of target above 100%.

This task also would have been easier had the marginal increase was same but it was not. As you can see in the table below that marginal increase is different for different slabs.

The equation of a straight line y=mx+c and mighty vlookup function comes to our rescue here. I came up with following solution.

Helper column is same as lower limit of target. And yes one has to achieve 125% of his target to get 100% bonus amount in this case. You can update the table as per your need like not giving any bonus for 0-25% target or different slabs etc. Following is the link to file for your perusal…