SUMIFS, COUNTIFS – ignore criteria

Sumifs and Countifs are used to sum or count using multiple criteria. Recently while making a report i was using multiple drop down menu linked to the criteria field of formula, to give user the flexibility of having a dynamic report. As one of the option I wanted to ignore the criteria and select all the options that were there in the criteria range. After a bit of struggle i found that using wild card “*” (asterik) can be used in drop down menu as it ignore the criteria and select all the options.

bolg

Advertisements

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.

ProjStat1

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.

PROJSTAT2

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:-

https://drive.google.com/file/d/0B2FLZ9YjqpJTdEhNdnNwcEVDYVk/view?usp=sharing

Other related entry :-

Gantt Chart

 

 

 

DCF Valuation

In the financial modeling series we have covered term loan schedule, depreciation schedule, tax schedule and an integrated financial model.This is probably the last link in the financial modeling series.

Here we have valued the company using the most widely used valuation technique called Discounted Cash flow (DCF) valuation method. We arrive at Free cash flow of firm (FCFF), discount it by Weighted average cost of capital (WACC) to present value to find the Enterprise value (EV) and then subtract net debt to get the Equity Value of the company.

val1

I follow Prof. Damodaran of Stern NYU for valuation related queries.

link to file

https://drive.google.com/file/d/0B2FLZ9YjqpJTeVloRUNjSWc1SnM/view?usp=sharing

 

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.

outlook1

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.

outlook2

I use office 2010.

outlook3

Economic Value Added

Analyzing equation:
EVA = Net Operating Profit after taxes (NOPAT) – Cost of Capital Employed (CoCE)

EVA=NOPAT – ( CE x WACC )

EVA/CE = NOPAT/CE – WACC

EVA/CE = ROIC – WACC

EVA = CE x (ROIC – WACC)

*ROIC – Return on Invested Capital, CE – Capital Employed

Therefore EVA can also be defined as spread between the return
on invested capital and the cost of capital times the amount of invested capital.

prashant99

It appeared in HUL Annual Report 2012-13. Found it worth sharing.

EVA is residual income after charging the Company for the cost
of capital provided by lenders and shareholders.It represents the
value added to the shareholders by generating operating profits
in excess of the cost of capital employed in the business.

EVA will increase if:
a. Operating profits can be made to grow without employing
more capital, i.e. greater efficiency.
b. Additional capital’s invested in projects that return more
than the cost of obtaining new capital, I.e. profitable growth.
c. Capital is curtailed in activities that do not cover the cost of
capital, i.e liquidate unproductive capital.

EVA = Net Operating Profit after taxes (NOPAT) – Cost of Capital Employed (CoCE)

NOPAT is a company’s after- tax operating profit for all investors, including shareholders and debt holders or EBIT x (1-Tax%)

CoCE = Weighted Average Cost of Capital (WACC) x Average
Capital Employed

View original post 35 more words

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.

chand1

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

link to the file: https://drive.google.com/file/d/0B2FLZ9YjqpJTNnNGWWFTemd0RTg/view?usp=sharing