Ads 468x60px

Thursday, August 30, 2012

Best Practice Modeling – Make these 5 changes today

Best Practice Modeling – Make these 5 changes today:
This article is written by Myles Arnott from Excel Audit
This article provides a high level overview of Best Practice Modeling and highlights five simple Best Practice Modeling concepts to put in place today. In next 2 parts we will talk about implementing some of these ideas.
Best Practice Modeling using Excel - Make these 5 changes to your Excel models today

Why is a standardized approach to Excel spreadsheet modeling important?

Excel spreadsheets are the most widely used business tool with over 500 million users worldwide. Spreadsheets are used within all aspects of businesses from simple day-to-day tasks to being a critical element within complex business processes such as business planning and regulatory reporting.
In the absence of a generally accepted and widely applied standard, Excel developers build models according to their own tastes. This can result in:
  • spreadsheets being built in an inconsistent and sometimes haphazard way;
  • frustration and confusion as model developers struggle with spreadsheets developed by somebody else;
  • increased complexity and risk; and
  • reduced efficiency.

What are the benefits of implementing a best practice modeling approach?

  • Accuracy: Spreadsheets are less prone to errors.
  • Consistency: Spreadsheets have a consistent structure and look, making sharing easier.
  • Clarity: Spreadsheets are clear and structured, reading like a book, navigating like a website. This makes them easier to share and audit.
  • Efficiency: Spreadsheets are easier to use and share, saving time at key points in critical processes.
  • Flexibility:Spreadsheets are easily changed and extended without the need for a complete re-work.

Five changes to make to your Excel spreadsheet development today

Businesses are often put off the idea of implementing a best practice standard as they see it as very time consuming and an unnecessary level of bureaucracy. I have therefore picked five simple changes that you can make to your Excel spreadsheet modeling right now.

Change 1: Apply a modeling life cycle

Considering the five stages of a model’s life cycle shown below formalizes the process of developing spreadsheets, be it for yourself or another user:
Define & use a modeling life cycle
Specify: Document the functionality and outputs required by the user(s)
Design: On paper map out the structure and data flows of the model
Build: Build the spreadsheet to best standards
Test: Testing is a vital step to identify and resolve errors
Use: Model is issued for use

Change 2: Give your spreadsheets structure

A well-structured spreadsheet is more transparent and is therefore much easier to use, test and audit. Separate your spreadsheets into the three key types shown below:
Use a 3 sheet structure to simplify things

Change 3: Make cell content and cell purpose visually identifiable at all times

The content and purpose of every cell should be easily identifiable to the user at all times. In its simplest form you should distinguish between the following two cell types:
Use Cell Styles and consistent color schemes to make your models easy to understand
More on this: Use cell styles in Excel
In the final part of this two part series I will share some simple macros to add to your Ribbon (or tool-bars in Excel 2003) that make this really simple to put in place.

Change 4: Use consistent formulas

When more than one adjacent cell contains a similar type of output the formulas within the cells should be designed to be consistent. In essence this means that you should be able to copy a cell down or across the relevant range without needing to make changes to the underlying formulas.
This greatly reduces the risk of error, speeds up the development process and makes the workings area of the spreadsheet more transparent and therefore easier to audit.
Related: Structuring your financial models – best practices

Change 5: Build error checks into your Excel spreadsheets

When designing and subsequently building the spreadsheet you should be aware of the key checks that should be in place to assure the quality and accuracy of the model outputs. Building in error checks during the build process ensures that key checks are made and most importantly that any issues are clearly flagged to users.
Build error checks for each critical calculation and then communicate all errors through a dedicated error summary page.

Conclusion

Hopefully this article has given you a useful overview of the need for and benefits of implementing a Best Practice modeling Standard. Whilst the article has only touched the surface of Best Practice modeling, I hope that you will find the five simple changes easy to implement and that you will find them beneficial.

Some useful links:

In the next part of this series I will show you how to create some simple macros to automate best practice cell formatting (change 3).

Thanks to Myles

Many thanks to Myles for compiling all the tips & sharing this with us. If you have enjoyed this article, please say thanks to Myles. You can also reach him at Excel Audit or his linkedin profile.

Monday, August 27, 2012

Excel Salary Survey Dashboard Contest Winners

Excel Salary Survey Dashboard Contest Winners:
Finally the wait is over. Eager to know who won our Excel Salary Survey Dashboard contest? Read on.

3rd Prize winner – Nicholas Mone

Nicholas’ made a dynamic dashboard with simple colors and easy to understand messages.
Nicholas will get a Kindle Touch ($99 option) very soon.

Option 46 by Nicholas R. Moné

Dashboard to visualize Excel Salaries - by Nicholas R. Moné - Chandoo.org - Screenshot #02Dashboard to visualize Excel Salaries - by nick.mone@gmail.com.xlsm - Chandoo.org - Screenshot #02Dashboard to visualize Excel Salaries - by Dynamic charts - Chandoo.org - Screenshot #02

2nd Prize winner – Joey Cherdarchuk

Joey’s dashboard has brilliant colors, nice layout and clear explanations. It was a pleasure to play with it and understand various things about Excel salaries worldwide.
Joey will get a Amazon Kindle Fire worth $199.

Option 26 by Joey Cherdarchuk

Dashboard to visualize Excel Salaries - by Joey Cherdarchuk - Chandoo.org - Screenshot #02Dashboard to visualize Excel Salaries - by joey@ualberta.net.xlsm - Chandoo.org - Screenshot #02Dashboard to visualize Excel Salaries - by Excellent design & colors - Chandoo.org - Screenshot #02

1st Prize winner – Richard Stebles

Richard’s work is well laid out, has lots of analysis. He presented the results in an interesting way that attracted lots of votes too.
And he gets XBOX 360 (4GB) + Kinect Sports bundle worth $299.

Option 56 by Richard Stebles

Dashboard to visualize Excel Salaries - by Richard Stebles - Chandoo.org - Screenshot #02Dashboard to visualize Excel Salaries - by richard.stebles@gmail.com.xlsx - Chandoo.org - Screenshot #02

Highly commendable entries

Apart from these 3, there were certain entries that are truly knock out. Congratulations to John Michaloudis, Peter Damian, Ben Jones, Ekaterina Batranets, Luis Hernandez, Ganesh Madhyastha, Aditya Nanduri, Joerg Decker & Daniel Rosenberg.

Thank you for participating

Thank you so much for taking part in this contest. In my book, you all are winners. The fact that you can take huge pile of data and turn it in to something beautiful & analytical shows that you are awesome. Go ahead and celebrate.
Special thanks to our Judge Hui and all the voters.

Congratulate the winners

If you enjoyed this contest, say congratulations to the winners & other participants. :)
PS: Explore Excel Salary Survey Dashboard contest & 66 entries.

Thursday, August 23, 2012

Formula Forensics No. 027 – Remove Leading Zeroes

Formula Forensics No. 027 – Remove Leading Zeroes:
A few weeks ago Chandoo received an email from a reader, Chandu:
“I am in search of a formula for the below scenario, please suggest:
 I am trying to delete the zeros before the numbers.
Eg:
002459J
0002459R
02459O
I need one unique formula in case of huge data, please suggest.”

Chandoo responded with two solutions:
If you want to get rid of all 0’s:
=Substitute(B2, “0″, “”)
If you want to get rid of all leading 0’s:
=Mid(B2, Min(Iferror(Find({1,2,3,4,5,6,7,8,9}, B2), “”)), LEN(B2)) CTRL+Shift+Enter

So today we will look at these two formulas and see what makes them tick.
As always at Formula Forensics you can follow along using a Worked Example which you can download here: Excel 97-2013.

Substitute(… )

Chandoo’s first formula uses the Excel Substitute() function to replace all the 0’s with a Null character. The Null character is what is between the two quotation marks “” (Nothing).
The syntax of the Excel Substitute Function is:

In Chandoo’s formula =Substitute(B2,”0″,”") we see that
Text: = B2
Old_Text: “0″
New Text: “” Null Character
So this formula says: Replace all the 0 characters in the text in Cell B2 with “”

Advantages: Simple formula if the text only has leading zeroes eg: 002459J will correctly return 2459J
Disadvantages: Doesn’t work if the text has internal or trailing zeroes eg: 0024509J will incorrectly return 2459J

Mid( … )

Chandoo’s second formula was the array formula:
=MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),”")),LEN(B2)) Ctrl+Shift+Enter
This formula is based on the Excel Mid() function which returns a Sub-String from within the source String
The string is the text that starts at the position of the first non zero number in the string

Let’s start in the middle and work our way out to see what is happening here:
=MID(B2,MIN(IFERROR(FIND({1, 2, 3, 4, 5, 6, 7, 8, 9}, B2),”")),LEN(B2))
In the middle of the formula we see the Excel Find() function.
The syntax of Find() is:

In Chandoo’s Formula
Find_text: {1,2,3,4,5,6,7,8,9,}
Within_text: B2
Start_num: 1 (Default)

So the Find() function will look for the values 1 to 9 in the cell B2
Lets see that in an example
In C26 put one of the values 0024059J
In a blank cell D28 put =FIND({1, 2, 3, 4, 5, 6, 7, 8, 9}, C26) then press F9, not Enter
Excel responds with: ={#VALUE!,3,#VALUE!,4,6,#VALUE!,#VALUE!,#VALUE!,7}
Looking at this we can see that the formula has returned 4 values of 3, 4 6 & 7 with some #VALUE! Errors in between.
The values 3, 4, 6 & 7 are the positions in cell C26 of the values 1, 2, 3, 4, 5, 6, 7, 8 & 9
We can see that C26 contains: 0024059J and that in positions 3, 4, 6 & 7 we have values from the array 1, 2, 3, 4, 5, 6, 7, 8 & 9

Stepping out of the original formula a little bit =MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),”")),LEN(B2))
We can see that the above Find() formula is surrounded by an Iferror() function.
This will take the results of the Find() function and where there is an error insert a “”

In a blank cell D30 put =IFERROR(FIND({1,2,3,4,5,6,7,8,9},C26),”") then press F9, not Enter
Excel responds with: ={“”,3,”",4,6,”",”",”",7}
The #VALUE! Errors have been converted to “”

Stepping out a bit more in our original formula we encounter a Min() function next.
=MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),”")),LEN(B2))

The Min() function will return the Minimum value from the Iferror() function
So Min(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),”")) is the same as Min({“”,3,”",4,6,”",”",”",7})
Which we can see is 3
But lets check that:
In a blank cell D32 type: =MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},C26),”")) then press F9, not Enter
Excel responds with: 3 as we deduced above,

Finally we arrive at our original formula: =MID(B2,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9},B2),”")),LEN(B2))
Which can now be simplified to
=MID(0024059J, 3, LEN(0024059J))
The length of 0024059J is 8 characters long and so the formula becomes
=MID(0024059J, 3, 8)
What this is asking is return the Middle 8 characters of the text 00245059J starting at position 3
Which is 24059J
This has effectively stripped of the left or leading zeroes as Chandu required.

Download

You can download a copy of the above file and follow along, Download Here – Excel 97-2013.

Formula Forensics “The Series”

This is the 27th post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.



Growing a Money Mustache using Excel [for fun]

Growing a Money Mustache using Excel [for fun]:
Mustache and Excel?!? Sounds as unlikely as 3D pie charts & Peltier. But I have a story to tell. So grab a cup of coffee and follow me.
Few months ago, I chanced up on a highly entertaining blog on money, retirement & living a kick-ass life. Reading Mr. Money Mustache is much like I am talking to myself. Almost all of his money philosophies, values & hacks are similar to what we practice at Chandoo household. Immediately I got hooked. In a span of 2 weeks, I read more than 100 articles, often making Jo suspicious what I was doing so much on her iPad.
At this point, you must be thinking -”Dude, what has all this got to do with Excel?!?”
And I am coming to that. One of the ideas Mr. Money Mustache preaches is small regular expenses can add up to massive amounts of cash (or ‘stash as he calls it) over several years. Now that we do not have a full time job, live in a small town & crave little, we barely spend anything. But I can relate to his idea. For example, if you spend a few dollars everyday at local coffee shop, over 10 years, this could add up to more than $10,000. Money that could be used for other worthy goals like early retirement or starting your dream company. Mind you, I have nothing against coffee. In fact, I brew two cups of lovely cappuccino every morning so that Jo and I can savor it before the kids wake up and start the hulk_in_the_house program. It is another thing that the last time I bought a cup of coffee is when I was in Australia in June. But the important idea here is that regular expenses should be carefully monitored and pruned.
“What?!? You are talking about coffee and kids. Where is Excel?!?”
Ok, I am done with the build up. So one fine morning, I emailed Mr. Money Mustache, introduced myself as somewhat spreadsheet skilled and shared a file I created with him, using which community at his site can see how regular expense cuts can impact their savings. He was kind enough to publish it here.

A growing mustache chart

Well, I am not sure what else to call it. So lets stick with growing mustache chart. Here is how it works:
  1. You enter a sufficiently large number ie the money you want to accumulate to retire or do something equally awesome.
  2. You also enter your regular expenses (daily, weekly, monthly, annual or one time) and amounts.
  3. Then it magically calculates how much money you would save by cutting them.
  4. All this is shown in a dynamic chart that depicts your target and actual as mustaches
See this demo:
Growing Money Mustaches - a Dynamic chart in Excel

This is so cool, how is it made?

There are 4 steps to our growing mustache Excel chart.

1. Calculating future value of regular expenses

Question: If you consume $3.50 latte every day for next ten years, how much would you spend?
Answer: Gee! Sounds like a big problem, let me grab a cup of coffee first!
On a more serious note, the future value of these little expenses depends on rate of return as well. That is, instead of gulping down $3.50 in a hurry, if you saved the money the return you get on yearly basis.
For our calculations, we can assume a 7% return.  This gives a future value of$18,498.
You can use the formula =FV(7%/365,365*10,3.5) to get this value.
So the multiplication factor is 5,285 (18,498 divided by $3.5)
For our calculations, we can use a simple multiplication factor table so that we can focus on growing mustache than financial mumb0-jumbo.
Multiplication Factor Table - FV Calculations for regular expenses

2. Calculating Totals

Once we know the future values of all such regular expenses, we just need a small table like this that shows the totals:
Mustache target vs. actual calculations for bubble chart

3. Create a bubble chart

Next, we create a bubble chart with 2 bubbles. 1 for the actual mustache & 1 for target mustache.

4. Convert bubbles to mustaches

Hermione would know a great spell to instantly turn our boring bubbles to mighty mustaches (bulla-mustacium ?). But since we are muggles, lets focus on Excel trickery.
We need the chart on right from our bubbles:
Convert bubbles to mustaches in excel bubble chart
First get a nice handlebar mustache image from web, like this:
Mustache images - bubble chart
  1. Then, copy the gray color mustache (ctrl+c)
  2. Next, select outer bubble (target) and press paste (ctrl+v)
  3. Now, the bubble becomes mustache!
  4. Repeat the steps for actual bubble too.
That is all!

Download Excel Mustache Chart

Click here to download this chart and play with it. Examine the formulas in “Stash chart” sheet to see how it works.

Do you like the growing mustache chart?

I really liked how this turned out. Simple yet effective. Readers at Mr. Money Mustache site loved it too.
What about you? Did you enjoy this trick. Are you planning to cut any regular expenses after reading this?  Please share using comments.

More on Excel and your money

I believe in being frugal, consuming less and living a simple life. So naturally we talk about using Excel to keep track of your expenses, investments, understand the impact of small changes etc. Check out below links to see more on Excel & your money.

Friday, August 17, 2012

Homework: Can you extract dates from text?

Homework: Can you extract dates from text?:
So who is up for a challenge? Can you use only formulas and extract dates buried inside text?

Your mission, if you choose to accept it,

  1. Download this file.
  2. In column C, write a formula such that you can extract the date in column B
  3. If you succeed, post your solution here as a comment.
  4. If you fail, drink some coffee, start afresh.
Extract Dates from text using formulas
What are you waiting for? Start extracting!

Need some help?

If you want some clues, check out below links:

Want more challenges? We got’em too

You better have access to lots of coffee or something strong, cause it going to get tough.
PS: If you feel like cheating, here is a solution.