This is second part of 2 part series on conditionally formatting dates in excel.
In yesterday’s post we have learned how to conditionally format dates using excel. In this article, you will learn how to use these conditional formatting tricks to highlight repeat customers in a list of sales records.
The problem: Highlighting repeated customers in a list
Let us say you run a small retail store. And you want to give special discounts to all the repeat customers.
Repeat customer is someone who bought at least twice from you in last 30 days. (If the person bought twice but the gap between 2 purchases is more than 30 days they are not repeat customers).
The Data:
Let us assume your sales data has these 2 columns – customer ID and purchase date. I have shown first few rows here. Let us assume the data is in the range B4:C53.
Finding if a customer is repeat – The Formula:
If we just want to highlight without considering the purchase dates,
we can use a simple formula like =COUNTIF($B4:$B53,$B3)>1 in the conditional formatting applied over the range B4:C53.
But we need to consider the date as well,
hmm, now that is tricky !??
May be time for a sip of that coffee. Go take it, I am waiting..,
How about the SUMPRODUCT? We all know that sumproduct formula can be used to test more than one condition.
The formula:
=IF(C4>TODAY()-30,IF(SUMPRODUCT(--($B$4:$B$53=B4),--($C$4:$C$53>(TODAY()-30)))>1,"R","N"),"N")
Now that is one lengthy badass formula. Like Sergeant Martin Riggs. Bad, but still cool.
So what is this formula really doing? It is going to give us “R” if a customer is repeat and “N” if not. See this explanation to understand how it works.
Note: the double dashes “–” make the values as 0s and 1s from a bunch of “trues” “falses”. To know why sumproduct is such a beautiful and robust formula look no further.
The Conditional Formatting
Now that you have figured out the formula to determine a customer is repeat or not, applying conditional formatting is a piece-o-cake.
Just select the range B4:C53, go to conditional formatting and select “formula” option. Now specify the above formula and check if its output is “R” and apply formatting.
I am not telling you how to do this. It is your homework. Go figure!
That is all. Your workbook now highlights repeat customers in the last 30 days. Remember, as you reopen the file a week later, the highlighting logic changes since the date has changed.
Download the example workbook
Click here to download the example workbook and understand how to highlight repeat customers using conditional formatting. The file works in Excel 2003+.
What is your experience?
Share your tips and ideas on using conditional formatting with dates. What are the situations you usually face and how to solve them? I am never too old for this, so please share.
15 Responses to “Modeling Interest During Construction (IDC) – Excel Project Finance”
Thanks again for a very helpful post.
I had a similar problem when trying to model a balance sheet and profit and loss projection. The problem was that interest expense (in P&L) was dependent on a cash shortfall (in BS) which had to be funded. The cash shortfall depended on how much interest was paid, so the mutual dependency made a circular reference.
I addressed it with a macro that calculated interest outside of the P&L, then pasted the calculated amount into the P&L as a value. The model was out of balance, but by repeating the pasting and calculating loop the imbalance reduced to zero. It was a bit messy, and had to be repeated every time a line changed - but it worked.
If I have to do it again I'll read this article again first and see if it can be done more elegantly.
Hi,
The use of a circular reference can be avoided in this case. Just make use of the geometric sum to calculate the interest required. I’ll walk through the example from the spreadsheet.
First calculate the cash needed each year without the interest expense. So you year 1 you need 55 Mn, year 2 105 Mn, and 190 Mn for year 3. The total amount to borrow for year 1 is then (50 Mn)/(1-interest_rate) = (50)/(1-0.1). For years 2 and subsequent the amount borrowed is the cash needed in that year plus the interest_rate times the amount already borrowed. For year 2 (105 + interest_rate * sum(previous debt raised))/(1-interest_rate)=(105+0.1*61.1)/(1-0.1).
This process avoids the need for a circular reference, and makes the calculation more stable.
Thanks,
Tristan
The question is for the year 1 in your case, the amount works out to 45 mn. However in the year 2 you have applied the loan amount as 61.1 mn.
Am I missing something ! Please help !
very helpful information!!!
using circular references and to make model more stable we can use combination of "IF" and "ISERROR" functions. i.e
=if(iserror(formula1),"",(formula1))
this formula will return blank value if there is any error otherwise give the result required.
I usually use this in my models and it makes them very stable......
🙂 🙂 🙂
@Terry: Thats right. Exactly same problem is seen in Interest - Cash cycle in P&L and Cash Flow statement as well. In our trainings on financial modeling in excel, we demonstrate using both the circular loops as well as the macros to take care of this problem. Circular loops have their own pitfalls. If the model enters into a state of error, the error percolates!
@Tristan: Thanks for pointing out. I agree with you that if circular loops can be avoided, they should be avoided.
@Yogesh: This is one way of avoiding the problem. Although circular loops have another problem that they make your sheet slower. Each time, there is a change in the sheet, all the calculations are redone. So if they can be avoided, they should be avoided.
Please note that this was an example (a large one indeed) and I didn't have space to speak about the pitfalls of this approach! I just wanted to illustrate an approach and am glad that some of you found it useful!
I think while posting, there is an error in the images! The last image should be flipped with the one that is posted in step VII!
I think you can try the following simple solution given by Microsoft itself to make the circular works:
Windows: Excel Options -> Formulas -> Put a tick on "Enable iterative calculation"
Mac: Excel -> Preference -> Calculation ->Put a tick on "Limit iteration"
You can change the maximum number of calculation iterations as well as the maximum changes which iteration stops for goal seeking or for resolving circular references based on the number you type in the maximum change box.
Thank you.
Hey All
I heard that we can take care of the circularity with the help of macro for IDC. Can anybody help on the steps to construct the macro for the same.
Regards
Vinay
Hi Vinay,
If you look closely, you are essentially copying the values from the interest calculation to the IDC in project cost.
Basically you can record a macro, that takes the values from interest and pastes special the values in IDC row in project cost.
Then you can run that recorded code in a for loop.
Hope this helps.
Thanks Param for reply.
But before calculating interest, i need to provide for Upfront Equity and Equity, which are essentially part of total project cost. Hence, i need to put in Upfront Equity and Equity to calculate the IDC which is again hitting the total project cost.
Bit of confused on how to remove this circular reference.
Regards
Vinay
Wow, this was a brilliantly simple post. I was looking online for a while before I found this page. Never seen this been explained so beautifully yet so crisply before. Thanks for saving my ass at work! (i'm relatively new to finance + modeling)
I'm not sure why but this web site is loading very slow for
me. Is anyone else having this issue or is it a problem on my end?
I'll check back later and see if the problem still exists.
[…] Project Finance Modeling using Excel – Part 1 & Part 2 […]
I have been reading your blog since my college days. Today, I'm writing just to say thanks.
We have calculated Financial Rate of return of a hydropower projects, and the observer has raised an observation regarding Total Project cost with IDC Rs. 8616.01 million (PKR) and with-out IDC 8352.46 million (PKR). How does the Financial nalysis be calculated on the basis of with-out IDC Or With IDC?????
Please helpf. if possible to spare some time.