ModelOff 2014

Excel Modeling World Championships 2014

Message from John, one of “the organizers” to all my fellow Excel mates -

Invitation to the Excel Modeling World Championships 2014 ModelOff 2014

We’d like to invite members of the ​Excelytics community to the annual Excel and Financial Modeling World Championship 2014 Event (known as ModelOff, www.modeloff.com). The Advanced Excel educational competition helps celebrate Excel in Financial Services. The fun, challenging and innovative competition has a mission to inspire skill development with Microsoft Excel, Financial Modeling and Financial Analysis which is central to global businesses and communities. The competition showcases some of the fastest, hard-working and talented Excel minds from 100+ countries. Round 1 starts on 25th October 2014 (8 weeks away)

Background

Over 3,000 participants competed in the ModelOff 2013 event. Major Global Partners and Sponsors are: Microsoft, Intralinks, S&P Capital IQ, Kaplan Education, Bloomberg, AMT Training, Corality and Ernst & Young.  Participants come from diverse companies and jobs – such as Analysts, Associates and Managers at Investment Firms and Accounting Firms, CFOs, Analytics Professionals, In-House Excel Gurus and Consultants with a shared passion for Microsoft Excel and Finance.  Students comprise ~35% of all entrants worldwide – most studying Commerce, Accounting, Finance and Masters university qualifications. The countries most represented have typically been: United States, UK, Poland, Russia, Canada, Australia, India and Hong Kong.  Female participation is ~20% of all competitors – hopefully higher this year (the reigning champion is Hilary Smart 26yo from London).

 

How It Works

The ModelOff competition involves two online qualification rounds (2 hours each) conducted simultaneously around the world. The Top 16 performers are flown to New York for the Live Finals at the offices of Microsoft and Bloomberg in early December 2014.  Questions are mostly case study and multiple choice format – with some ranging from a basic understanding of discounted cash flow (DCF) analysis, 3-way integrated cash flow models to more complex project finance and simulation techniques.  Some basic Accounting, Finance and Excel knowledge is likely needed to progress to Round 2.

 

Free Training and Preparation

All past questions and tests from ModelOff 2012 and 2013 are free and available on the ModelOff website. The organizers believe in accessible excellence and this can be a great starting point for anyone looking to become involved for the first time and improve their Advanced Excel skills in Financial Services and Financial Modeling.  We actively encourage all participants to visit all our community partners, bloggers, our major global sponsors during and following the event for their own learning, mentoring and professional development. Competitors in the Top 10% of ModelOff 2014 will be eligible for exciting local and international opportunities, offers from community partners and fun learning experiences (e.g. Trips to Microsoft Excel in Redmond). We’re also hosting free networking events in major Financial Centres including Hong Kong, London, Sydney, New York and Regional Meetups in the coming months for anyone interested in networking and mentoring with Excel-users in their local cities. Entry to the competition is $20 for students and $30 for professionals.

John R Persico is one of the lead organizers of the ModelOff event. John can be contacted at john@modeloff.com.  Get involved at: www.modeloff.com

Converting Monthly data to Weekly data in Excel

For something that’s not so catchy, this is an awfully innovative solution I found. Sometimes, what’s out there on Excel surprises you to the extent of making you feel like a noob every now and then.

Most usually, data for modeling comes in the form of Monthly data, which needs to be converted to Weekly figures. Where does the problem lie? Take a look at the data and you’ll know right away.

Excel Monthly-Weekly

Here’s a neat trick that solves the problem with one formula – that’s right, just ONE formula!

=IF(C$2<$B3,MAX(0,C$2-EOMONTH($B3,-1)+6),MAX(0,MIN(7,EOMONTH($B3,0)-C$2+1)))/(EOMONTH($B3,0)-$B3+1)

And you get this -

Blog - Excel v1
Blog - Excel

I must give credits to this guy or “AlphaFrog” it is, he was the basis for such a complete one step solution! Take a look at this thread -

http://www.mrexcel.com/forum/excel-questions/627101-converting-data-monthly-weekly.html

I’m attaching a snapshot of the thread just in case. This is good stuff!

Example - Blog

Round “time” in Excel

The “Time” format is one of those least understood formats, at least used to be for me! Until, I had to work on it extensively for one of my projects.. And it’s actually not that complex.. :)

Here’s a quick snapshot on how the Time format works -
– The DATE & TIME is integrated to be represented by one number – a floating point number!
– The decimal part characterizes the time, while the integer part the date.
– [0,1] = [00:00 HRS,00:00 HRS + 1 day] This is the set that determines the time of the day. For eg, 0.5 = 12 NOON.
– Hence, “1/1/2012 12:00:00 PM” is nothing but “40909.5”
– While working with manipulations over time data, understanding the underlying number structure proves critical to work around it. Some eg.s include rounding time to the nearest hour/half hour or quarter hour.. Or finding the difference in time between one day and the next.. etc..

Now that the background’s covered, let’s jump in into the post’s topic – Rounding Time in Excel!

Here’s a list of formulae that do the trick -
= (ROUND((A1 * 1440) / 15, 0) * 15) / 1440
= TIME(HOUR(A2), ROUND((MINUTE(A2)/60)*4, 0) * 15, 0)
= ROUND(A1*(24*60/15),0)/(24*60/15)
= INT(A1*(24*60/15),0)/(24*60/15)
= B2-MOD(B2,15/24/60)
= MROUND(MOD(B2,1)*60,15) (Note: Excel 2007 Addin – Not tested)

All of these work, and they resort to using the decimal numbers to get to the TIME manipulations. Have a go at some of these formula, and you might be able to understand how it works!

Where EXCEL fails?!

All the while it’s been Excel can do this, and Excel can do that. But what are its pitfalls? Where does it fail? What should we beware?
Surprisingly, there’s a whole community that’s at this topic! And I only recently stumbled upon them. That while trying to learn the new software “R”.

Here’s a link to Excel’s shortfalls, something that only authentic Statistical softwares are capable of.

http://biostat.mc.vanderbilt.edu/twiki/bin/view/Main/ExcelProblems

I’d like to take the time to highlight some of these below -

1. Excel’s greatest boon – the spreadsheet page – is it’s greatest bane! Formulae and Values in the same cell, with no line of demarcation between the functions and the end results. Some operations such as sorting result in erroneous output.

2. Data Validation is a big problem

3. Computational complexity and inconsistencies, not to forget the time.

4. Bad graphing processes (I always thought Excel graphs were good!)

5. Excel Statistics is anything but Statistics! The probabilities, accuracy, plots, regression etc.. nothing matches with Statistical Software packages. (RAND function is potentially a great bug)

6. Solver is not something that can be relied upon, convergence issues.

7. No “HISTORY” to track the list of data actions/manipulations for ease of debugging or other reasons.

And the list goes on.. I’ve only scratched the surface I believe, but these are pretty much the high level accusations.

And now, what I’d like to take away from all this -

I know and understand that every software has its set of pluses and limitations, and Excel is no exception. It’s Microsoft, it’s unpredictable, it’s got bugs, it’s not perfect.. you can go on..
But it’s still the most widely used and understood piece of software, easy to use since we already know it so well, and works wonders on quick visual inferences. And Excel is powerful enough usually to do stuff that is beyond the average tasks, and I’ve myself used it for purposes that are beyond what I thought possible.

IF anything, it’s a great software, and currently, the easiest at handling data, charting and computation. And as long as we don’t rely on using hardcore Statistics using Excel’s built-in tools, I don’t see any harm why we can’t boast of Excel’s functionality in other domains! Also, most stuff above about Excel’s limitations on data complexity or computational complexity is something we already know, and here’s the best part – once used to Excel, we end up working around it always. ALWAYS! We just know where it hits the limit. It’s like having this old piece of machine and knowing how to use it. Agreed there are better tools out there, but currently, this one rules the majority of the population and until a time when the others take over, we can still boast of Excel’s capabilities and keep learning newer stuff to boost our skills and make our everyday life easier…

Coloring the scatter plot in Excel in groups

Scatter plots are generally a nice tool to represent data, especially ones where we’d like to show a pattern of some kind. But what happens when we have a sub-pattern of data which we need to plot to understand the pattern in the scatterplot?

Let’s take an example here. This is how the data looks -

Name X – Axis Y – Axis
Region1 3% 17%
Region1 4% 34%
Region1 3% 14%
Region1 3% 37%
Region1 4% 28%
Region2 6% 23%
Region2 5% 30%
Region2 7% 33%
Region2 6% 25%
Region2 6% 30%
Region3 7% 50%
Region3 7% 70%
Region3 6% 59%
Region3 7% 48%
Region3 8% 59%

 

and the scatterplot like this…

Chart1

 

 

 

 

 

 

 

But we’ve got more information that could be plotted, only it’s not directly possible in Excel. The regions are a second level of data that could aid us in plotting and visualizing the data, maybe there some kind of pattern within the grouping that we’re not able to see currently from this chart!

Take a look at how this might look -

Chart2

And what a difference it seems to make on what we can infer from the chart!

Wouldn’t it be great if we could do this instantly? Well, excel doesn’t seem to be supporting such a feature yet directly from the menus, but I guess we’ve always got the folks who just can’t keep it quiet when it comes to tweaking!!

Here’s a nice link that shows how this can be done!

http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx

As for me, I’ve got my own piece of code, not the most robust & efficient one, but definitely works! I’ll probably put that up sometime soon…

Enjoy the excel experience…

THANK YOU to all my READERS! HAVE A GREAT 2013~!

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 7,600 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 13 years to get that many views.

Click here to see the complete report.

Extracting Text from Right of String in Excel

How often have you wanted to badly extract text from the right of a text, just the way the LEFT function works from the left. I’ve just craved for a function like

RIGHT(A2,FINDRIGHT(“,”,A2,-1))

and it picks the characters from the right counting till it finds the ‘,’!

For instance, “Columbus, OH” is the text in a cell and you need to extract “OH” from the right after the ‘,’ – one way is to find where the comma exists and then find the number of characters from the right by subtracting the total length of string from it… too tedious! But that works…!
Here’s how it looks
= RIGHT(“Columbus, OH”, LEN(“Columbus, OH”) – FIND(“,”,”Columbus, OH”,1))

Now is that all? Have we a universal solution at hand? Or should I say, have I no more problems at hand? Well, as usual, the answer is ‘not really’! What if you had multiple commas in a text string – like “Columbus, OH, United States, 40023″ and you wanted the last part after the ‘,’ i.e. 400023. If we went about working the solution like above, we’ll have Three FINDs to find the location of the last ‘,’!

Worse still?? What if you didn’t know how many commas’s existed in each cell, i.e. if the number of commas in every cell is not fixed! Now that’s a problem and we sure would love to have a function like FINDRIGHT I mentioned above!

What’s interesting is, there indeed is a nice unique solution to this problem, quite innovative I’d say.. great logic!
Here’s the idea – how about if we counted the number of occurrences of the delimiter per cell, and then, temporarily change that delimiter to another delimiter and use that in the FIND function I’ve mentioned above. Voila!!

Step1 :

Find number of occurrences (this is the most important step in all this)

= LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)) (What a great way to get the number of occurrences – very thoughtful!)

Step2:
Substitute the last occurrence only!
= SUBSTITUTE(A1,”,”,”|”, … ) (… follows from the function above which gives the instance to be substituted)

Step3:
Extract using the first formula above
RIGHT(A1, LEN(A1) – FIND(“|”,…,1))

Putting them all together -
=RIGHT(A1,LEN(A1)-FIND(“|”,SUBSTITUTE(A1,”,”,”|”,LEN(A1)-LEN(SUBSTITUTE(A1,”,”,””)))))

And there we have it…

Now this is also not without shortcomings, but we’ll take that up in another post. For now, this is a great tool to use, and just copy that last function and put it in the cell next to where you want to extract!

One great link that helped me with this is given below for reference -
http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba” target=”_blank