Dynamic Chart in Excel the Simple Way

The Background

I live in a EU country that is rich in 2 things – forest and educated people. The proportion of people with higher education is one of the best in the world. We are a relatively small nation, just below 2 million people. Population density is quite low, so there is plenty of land for the other resource that Latvia has – forest.

In Scandinavian and Baltic countries it is quite common to invest into forest just like you invest into real estate. Both types of investments are very similar. A specific thing for forest investments is the fact that your capital grows in the most direct meaning of the word. 

I’ve recently created a financial model for a forest investment fund that would allow them to calculate the price they should pay for a land with forest on it. As usual it turned out to be a nice little model: key inputs, a table with annual projections and output in the form of a dashboard.

Dynamic Chart in Excel

The tricky part started when I wanted to design a chart that would show project cashflows and annual costs and revenues. Just like with real estate projects, the holding period is a variable and could be very different. Sometimes you just buy the forest, cut it, re-plant for the future and sell – all within 1 year time frame. Sometimes you hold it for 10, 20 and even more years. If you make a 20-year chart and then the user decides to hold the forest only for 8 years, then your chart would look quite terrible no matter how well you design it:

The Solution – How to Create a Dynamic Chart in Excel

To follow the further explanations, you might want to download a free redacted version of the file on our CFOTemplates.com website here. You might want to enable iterative calculations in the options of the model.

   There is no way you can make a dynamic chart in Excel using conventional chart settings or formulas. You have to use a small trick here.

• Use the Define name button under the Formulas button and add a new name

• Let’s call the new name “Income”. It should definitely refer to the revenues of the first year’s income, so I first click onto cell D51.

• Depending on the number of years that the investment should be held, I want my chart to include the corresponding number of cells. This could be done by OFFSET formula. Just wrap the current =Valuation!$D$51 with the OFFSET formula. Use the reference to the investment holding period in cell E9 as the last parameter of the formula. You can read more about the OFFSET formula in my previous blog post OFFSET formula: An Easy Example to Understand.

• Now edit the Chart data and the income series. Instead of writing a cell reference in the series values, just write the new name together with the sheet reference: Valuation!Income

• Repeat the same for the costs. And then… MAGIC! Your chart dynamically changes its time frame, as long as you change the holding period in cell E9.

I hope that the Excel team at Microsoft will introduce some more user-friendly functionality for dynamic charts in the future versions of Excel.

P.S. You are welcome to connect or follow me on LinkedIn to get new ideas about financial modeling. I will be posting useful tips and tricks, as well as tutorials in the field of financial modeling.