Over on Twitter, I came across this beautiful chart, aptly titled – Joyplot. It is the kind of chart that makes you all curious and awed. So I did what any Excel nerd would do. Recreated it in Excel of course. This post takes you thru the process.
Peak time for sports and leisure #dataviz. About time for a joyplot; might do a write-up on them. #rstats code at https://t.co/Q2AgW068Wa pic.twitter.com/SVT6pkB2hB
— Henrik Lindberg (@hnrklndbrg) July 8, 2017
First let me share the final outcome.
Joyplot in Excel – Peak time of the day for sports and leisure
Here is the final overlapped area chart with a bit of formatting thrown in. It is a pretty close imitation of Henrik’s original chart. Click on it to enlarge.
Creating Joyplot in Excel – Tutorial
As you can guess, the chart is a just an overlapped area chart (ie each area sits behind another, unlike stacked area chart where they are umm, well, stacked!)
Let’s start with a look at data. Henrik’s original data has 10,656 rows, each row containing activity name, time and p value – how much survey respondents enjoyed [@activity] at that time.
Here is a snapshot of first few rows.
Scrubbing and re-arranging the data
As you can see, while this format is excellent for storing, it is very tedious if we want to make one chart with all series. So let’s scrub.
- We need to figure out if an activity should be included or not. I am using the same criteria as Henrik’s. Exclude activities with p value less than 0.003 or activity title “Playing sports n.e.c. *” (not elsewhere classified)
- To do this, we first pivot the data on activity and max(p). Then filter this pivot two ways – max(p) >=0.003 and label not equal Playing sports n.e.c. *
Tip: You may need to enable multiple filters per field in the field settings of row labels. - We will end up with 28 activities.
- Then add a helper column to original table that looks up the pivot and tells if an activity should be included or not
- To do this, we first pivot the data on activity and max(p). Then filter this pivot two ways – max(p) >=0.003 and label not equal Playing sports n.e.c. *
- Add two more columns to original table to tell peak time and modified time. This will help us in rearranging and sorting the data. Modified time just moves time by 3 hours (Henrik’s chart is plotted from 3AM to 3AM). At this stage our data looks like this:
- Now, pivot the data once again. This time,
- exclude activities by using report filter on include? column.
- Set up peak and activity in row labels area, modified time in column labels area and p in values area.
- Arrange the report in tabular format, turn off sub-totals.
- We get this:
- Calculate normalized values by dividing each p value with maximum p value for that activity. We can use another range of 28×288 cells to do this. We get this:
- The next 2 steps may seem confusing. It will become clear once you look at the charts.
- Define an offset value. Start with 0.5. You can change this later. In a separate 28×288 cell range, calculate gaps by multiplying offset with position of an activity. Something like this:
- Now, finally calculate activity + gap values by adding up respective cells in each of the 28×288 ranges. We get this:
At this stage, our data is a shape ready for visualizing.
Creating and formatting overlapped area chart
The chart creation process has 5 steps.
- Select the 28×288 range of cells created in step 7 and insert an overlapped area chart.
- Now, copy the gaps range (created in step 6 above) and paste them on to area chart as new series (just ctrl+c your data and select the chart, press ctrl+v)
- Adjust the order of series so that each activity is sandwiched by appropriately named gap series
- Tip: adjusting 56 series is painful with the chart select data > move series up/down buttons. Instead, just select the series, look at formula bar. The SERIES formula has last parameter as order. Change this number. It is easy to figure out the number once you try a few.
- Change all gap series fill color to white. This instantly creates the floating area chart effect.
- Change the colors of activity series. Apply white / off-white border to these series. Your joyplot is ready.
Quick overview of the chart creation process:
Let’s examine the result of each those 5 steps with a smaller dataset so you can see how everything fits together. Here is the data for this example:
- Create an overlapped area chart with activity+gaps data. We get this:
- Add gaps as new series to chart. You get this:
- Move the gap series so that they sandwich activity series. Use Chart Data > Move series up/down buttons or SERIES formula
- Apply white color fill formatting for gap series. This creates floating area chart effect as below:
- Finally, format the chart by apply some colors and border formatting etc.
So there you go. The final outcome does look joyful.
Alternatives to Joyplot
While joyplot is awesome, it is not easy to make. Fortunately, there are a few simpler alternatives that we can whip up in Excel as soon as you have either the pivot or normalized values. Below I have shown two such examples. Read about sparklines or conditional formatting heatmaps for more.
Joyplot alternative – using sparklines:
Tip: to get axis on your sparkline, just type the times separated by a single space. Then go to format cell (ctrl+1) and set horizontal alignment to distributed. Viola, Excel will fill the cell by adjusting spaces.
Joyplot alternative – Conditional Formatting Heatmap
Download Joyplot Workbook
Click here to download Joyplot Excel workbook. Examine the data scrubbing formulas, pivot and chart settings to learn how this is created.
If you are familiar with R, then go thru Henrik’s R code. It is much shorter than the Excel gymnastics we did with circular pivot table referencing. That said, some of the data re-arrangement could be done with same ease in Power Query too.
Your thoughts on Joyplot?
The only step we missed in Excel implementation is moving average smoothing of the area charts. It can be easily added as a step between 3 and 4 in data stage.
How do you like Joyplot? Would you create something like this for your business / personal data? Share your stories and thoughts in the comments section.
More joy for you…
If you love this, you are going to enjoy these charts too.
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.