A Dashboard on Temperature and Rainfall

In this post, I have tried to make an excel based dashboard taking data from past questions asked in Finals of 2017 Modeloff, Section 4 – Data Diving. The question gave temperature and rainfall data of 18 states of the USA for the year 2010. The task was to bring the data to life, show pattern and information hiding in it.

A map was also given and asked to use it optionally or we could use Excel’s newer map tools. However, I have not used the map or any other map tools for the dashboard.

The time given in test was 60 min, but I have taken way more time making it.

What interested me to make a dashboard on data was because a lot of Bigdata analysis is done on temperature and rainfall. I thought it would a learning for me as well to make such a dashboard in excel.

The dashboard gives a standalone and relative position of the city selected in terms of highest/lowest/average temperature and rainfall.

 

2018-06-21_1037

There are two drop downs, one for a city other for a month. The three tables give yearly, monthly, and overall data. The last table giving overall data also highlights selected city and maximum/minimum data.

2018-06-21_1035_001

Two line and bar combination charts are given which talks about the temperature and rainfall for the year and month in detail. With chart at the top, one can find hottest months in the year and most/least rainy months. The chart at the bottom can help you to analyze the specific month.

2018-06-21_1037_001

Two scatter plots are also given to put the data of city for the month you choose in perspective. Here you can find cities which more or less hotter/rainy than the city and month of your choice. Please take care of the axis name while you read the charts. The red dot is your city.

2018-06-21_1038

link to Dashboard

Hope you find the the dashboard useful. Any comments on further improvements are welcome.

 

 

Advertisements

Adjusted MIRR for time and scale difference

The post is continuation of earlier article NPV, IRR and their variants. For capital budgeting  Internal Rate of Return (IRR) is popular measure, however,  Net Present Value (NPV) method is preferred by academics since the rankings of mutually exclusive projects by IRR may not always select the project which will maximize the value of the firm, due to an implied reinvestment rate assumption by IRR. This weakness was removed by the Modified Internal Rate of Return (MIRR), the results are usually consistent with NPV.

Sometime back, I was solving capital budgeting problem and MIRR results were not consistent with NPV. After some research I found MIRR may also lead to erroneous rankings when projects have different initial outflows to start the project i.e. the scale problem, or the projects have different lives i.e. the time span problem. So here is the solution.

Scale Difference :

Imagine two projects with cashflow as shown.

2018-04-27_1505_1

As you can see the initial outlays are very wide and hence the MIRR results are inconsistent. The logic behind this is the acceptance of the smaller project also implies the acceptance of a shadow investment, equal to the difference in size between the smaller and larger projects, which earns the cost of capital. Therefore we make the shadow project with initial outlay of difference between big project and small project, with NPV of 0. Use Goal seek function of excel to make shadow project NPV 0.

2018-04-27_1507_2

After we add the shadow project and small project cashflow, and MIRR the results are consistent with NPV.

Timeline Difference:

For the project with the different timelines, use the life of the longest project.

2018-04-27_1544_3

Scale and Timeline Difference:

Imagine two projects with varying initial outflow and different timeline. The adjusted MIRR can be calculated by using the largest initial outflow and the largest number of periods.

2018-04-27_1603_4

This will give rankings that are consistent with NPV for mutually exclusive projects, even if the projects are of different sizes and in some cases, different lives.

Comparing two sets of values – Graphically

Given with two sets of data like below, and asked to compare them graphically, what charts will come to your mind.

2018-04-19_1014_1

2018-04-19_1015_2

2018-04-19_1015_3

Today, I will show you to compare two sets of values using scatter plot chart. something like this

2018-04-19_1017_4

the data points above the (x=y) slanted dotted line show increase in value vs first value and vice-versa for points below the line. Nifty right! It visually shows at once the points have higher values than previous ones.

Just select the data >> goto insert chart >> select scatter plot chart, all your data points are plotted. Now for an x=y slanted dotted line, the plot below as another series

0                0          or lower than your lowest data point

100            100     or higher than your highest data point

select the chart first, goto chart tools >> design >> select data

2018-04-19_1031_5

like this

2018-04-19_1034_6

select the red dots, right click and add the trendline.

format the dots to become invisible, format the line to a different color and dotted.

format the x and y axis to numbers hide the highest number of slanted (x=y) line.

In my case, I have axis formatted from 10 to 90.

2018-04-19_1017_4

Bingo!!!

Make chart say more with XY chart labeler add-in and other formulas, look like below

2018-04-19_1223_7

 

Comparing List

Sharing a quick tip on comparing list. Select two list and press Ctrl+fore slash (usually above enter button).

2018-03-15_1157

It highlights the row difference i.e. whenever there are differences in text/numbers in two lists will be highlighted. Similarly column diference can be highlighted with Ctrl + Shift + fore slash

2018-03-15_1205

Start from Home

Does it irritate you to open and see an excel file with active cell not at the first cell or top left corner and then you have to scroll every worksheet to see it from start or It might simply annoy your boss to open an excel file sent by you with active cell not in top left.

CTRL + HOME is short cut key to take to the top left corner and CTRL + PAGE UP is the short key that takes you to worksheet on left.

So if you start with the last worksheet of excel file keeping CTRL button pressed, start pressing HOME  and PAGE UP alternatively.

After doing this you will end up at the first worksheet of your excel file with active cell at top left of every worksheet.

For that matter, it can be an excel best practice to save every file with active cell at top left.

CIAO!

Depreciation calculation – Maximize the benefit

Today I got a mail from Modeloff for registration of their 2017 championship event, in addition, new past questions were uploaded. This blog refers to one of their past questions asked in 2016 championship event Round 2 Section 4 – Case Study – Maximize the Benefit. In the question, they essentially asked to calculate different depreciation method and recommend the most tax efficient tax method. The time awarded was 36 min. The question served me as a great refresher to different depreciation method calculation, although took much longer time than that was given. I thought sharing the solution would be great.

The depreciation methods in question were Declining balance (DBM), Straight line (SLM), Units of production (UOP), Sum of years (SOY). The excel functions like DB, SLN, SYD serve little purpose here as the calculation is far more complex.

A method for calculation of DBM, UOP & SOY was same as to calculate the yearly depreciation rate >> reverse the rates >> calculate depreciation. The reverse ticker method is used to reduce the calculation load when we have different depreciation rates each year coupled with CAPEX lasting number of years, otherwise, the calculation will be difficult to handle.

All the yearly depreciation rates are calculated first and then reversed using OFFSET function. The reversed rates are then multiplied with CAPEX using SUMPRODUCT.

dep4blog

Have a better understanding of formulas after looking at the worksheet linked. It takes a time to understand and even longer to master. But very helpful in building complex models.

In SLM the depreciation rate is constant for the given years. It is relatively easy to calculate, the challenge here is to remove the assets which are fully depreciated otherwise the depreciation will be inflated. The formula keeps accumulating the depreciation till the assets are fully depreciated, as the years goes beyond the life of asset it starts subtracting the fully depreciated asset depreciation from the accumulated depreciation. The formula again requires practice to master.

2017-07-27_1150

The depreciation tax shield is calculated by multiplying the tax rate and total yearly depreciation. NPV of tax shield is calculated using discount rate given. Higher the NPV more preferred the method

Please go through the file linked to have a better understanding of different depreciation method calculation. The question file is also inserted in the worksheet.

Link to the file : Depreciation_Maximize_Benefit

Thanks!

 

 

 

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.

 

2017-06-08_1536

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.

2017-06-08_1532

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!