Untitled

Car Indicators – How they can help you model in Excel

How could flickering car indicators possibly help you model in Excel. All will be revealed below.

On, off, on

Think about what happens when you put your car indicator on. The light flicks on, then off, then on and so on, right? A similar concept can be used in Excel modelling to simplify often quite complex problems.
Introducing binary indicators. Similar to car indicators, binary indicators turn on and off. If you’re not familiar with the world of binary operators then below is a brief description. When a binary operator is equal to:

• 1 it is on – an illuminated car indicator
• 0 it is off – non-illuminated car indicator

Yeah, yeah, that is great, but how can these binary indicators help me in Excel? Well let’s take a look at a couple of examples.

NOTE: You can follow these examples yourself by downloading the Excel spreadsheet and watching the YouTube video.

Example 1

Imagine you’re asked to model construction costs for a project based on the following assumptions:

1. Construction costs are $500,000 per month
2. The construction period runs for 2 years from 1 January 2011 to 31 December 2012

How would you go about modelling this in Excel? Some of you may say that’s easy… I would just do the following formula:

=500,000 x IF(AND(1 January 2011<=31 December 2012),1,0) or IF(AND(1 January 2011<=31 December 2012),500000,0) – see Figure 1

 

Figure 1: Long and cumbersome formula for Example 1

Whilst this works, the formula is long and cumbersome. This can cause troubles for external parties such as model auditors, may cause you to lose your mind trying to work out what you did previously and the construction IF statement cannot be used again in other formulas (you will see what we mean in a second). Don’t despair we can break this down into much more manageable components as follows:

1. Input the assumptions Construction Cost per month, Construction Start Date and Construction End Date
2. Create a Construction Indicator line: =(1 January 2011<=31 December 2012). Notice that we have got rid of the AND logical function. The new formula will create a one when the formula is true for both of legs and a 0 when at least one of the legs is false. Note that this Construction Indicator can be used in other construction period formulas (hence this construction indicator can be referred to over and over again)
3. Construction Costs: Multiply the Construction Cost per month by the Construction Indicator created in (2)

See Figure 2 for the above workings.

 

Figure 2: Simpler and easier to understand solution for Example 1

What do you think? Is this a lot easier to understand than the original formula? Let’s take a look at another example.

Example 2

Our next example asks us to sum the number print media sources from an online survey. See Figure 3.

 

Figure 3: How’d you find out about us categories

How do we solve this problem? Let’s use our binary indicators (aka car indicators) in conjunction with an OR function. Noticing that there are only two print media sources we would input the following formula:

= OR(How’d you find out about us data=”Newspaper”,How’d you find out about us data=”Magazine”)*1 – see Figure 4

You’ll note that we have multiplied the OR function by one. This is because the OR function alone will only return TRUE or FALSE. If we multiply the OR function by 1 then we will get 1 for TRUE and 0 for FALSE.
Now copy and paste this formula down. Add all the 1’s using the SUM function and you’re done. You should end up with something similar to Figure 4.

 

Figure 4: Solution to Example 2 along with formula for OR function

You now know how many print media sources are included in the survey.

NOTE: Example 2 could have been solved using a COUNTIF function; however that’s a topic for another day.

So now you see. Those flashing things called car indicators really do help you to model in Excel.
If you like this Blog, check out our Excel Functions training course which has plenty more tips and tricks.