To Iterate or not to iterate?

By Finrobot | Published: February 10, 2014

In this post we would like to share our thoughts on the most debated topic in financial modelling – interest rate charges and circular references in Excel. As all modellers know, ideal calculation of interest charges in a financial model requires beginning and end balances for funding positions: average debt * rate = period charge.

However, the formula is by definition circular: to calculate end balance one needs to know how much interest is paid out during the period. Fortunately, Microsoft Excel deals with this issue handsomely. Enable iterative calculation in Excel Options menu and voilà.

However, most our colleagues would say it is a big no-no. Why? Running models on iterations is rather dangerous. The reason is that Excel can not tell which circular references are present by design and which are mistakes where user accidentally “looped” some cells: for example, A1 = A9 and Excel would run on the last value inputted into either A1 or A9.

Hence, many best practice guides strongly advise against running Excel on iterations. Instead, modellers come up with clever code which runs the iterations to balance interest charges and debt end balances and then return Excel back to non-iterative standard setting.

We personally think such approach is an overkill and not very convenient to work with. Every time a new operating assumption is introduced into the model, iterations code needs to be rerun to make balance sheets balance. If the capital structure is complex it may even take some time to complete its job. Pain in the neck.

Is there a better solution? We think there is. Our Models run in dual mode. At the top of ‘Financials’ tab there is a switch to turn on/off interest rate iterations. Both Base Model and Case Builder Model are equipped with ‘Iterate Interest’ button is shown below.

Set the switch to ‘Yes’ and your Model will run iteratively. Don’t forget to set Excel Options to iterations too. If Excel is set to linear calculations you would immediately see a warning that circular references are present in the model. Ignore and do not chase them. Change Excel settings. The warnings would go away.

Want to make sure there are no erroneous loops in the model? No problem, set the switch to ‘No’. Turn off iterations in Excel Options. If no circular reference warnings are showing it means the model is clean from any accidental loops.

That’s it. No extra code. No twisted guesstimates of interest charges. Plain and simple. Enjoy!

Lots of New Stuff

By Finrobot | Published: January 30, 2014

Dear Users and Guests!

FinRobot team was really working hard in January: we just released a new model and a new add-on.

The Quick IRR Model allows users to perform back-of-the-envelope IRR calculation for a new project. By entering few parameters on-line you can get Free Report showing funding required, cash flow profile and expected returns for your new idea. To gain access to more functionality and to run your own investment scenarios off-line you can purchase the underlying Excel Model for a modest fee. We now have three financial models in our catalogue. Come on in and give it a try!

To supplement our Models - no matter how flexible – we started publishing a series of helpful add-ons. The most recent add-on allows replacing of the traditional unit sales assumption with a new schedule of acquiring and retaining customers. We call it Customer Driven Revenue module. Currently, we are offering four add-ons available here free (requires registration).

We hope you like our new stuff. And don’t forget to cast your vote which model you would like us to release next. The poll is on the main page of the site - look for Shape the Future banner!

Thank you and kind regards,

FinRobot Team

Help us Shape the Future

By Finrobot | Published: January 23, 2014

Holidays or not, bad weather or good weather, FinRobot team has been busy. We published our second model and are working hard to launch few more. With our commitment to quality and detail we can only release one model at a time. Which one should it be? We decided to ask our users. Please participate in ‘Shape the Future’ poll on the front of our main site and let us know. You can also see what others are saying.

Thank you and kind regards, FinRobot Team

New Model released

By Finrobot | Published: January 14, 2014

Dear Guests!

We are pleased to announce that FinRobot has released its second model. The Case Builder Model allows users to build, analyse and compare up to five forecast scenarios, or operating cases, for a business or a project. New feature – specific to Case Builder Model -generates consolidated report for all cases at a push of a button. In comparison to our first Base Model we reduced the number of steps required for on-line assembly. We hope you like it. Come on in and give it a try!

Helpful Tips and Add-ons: Revenue Channels module

By Finrobot | Published: January 2, 2014

Our new Revenue Channels Module helps to detail revenue assumptions for the Base Model. For each product line the add-on replaces single volume and single price assumptions with multiple sales channels. For example, wholesale and retail channels may have specific unit sales allocation and pricing. The Modules picks up data from individual revenue channels and feeds the total into the existing revenue line.

Model users can expand on the example provided and build their own complex rules with ease. The Model will take care of the rest. You can download the Revenue Channels Module here free (requires registration). Alternatively, you can always browse FinRobot add-ons’ library located in your user Account .

It should not take more than 10 minutes to merge the Module into the live Model: download an add-on xls file and follow the instructions to import the code into your Base Model. No advance knowledge of Microsoft Excel is required. It is a simple copy-paste operation. Remember to keep a separate copy of your Model prior to changes to revert to if you make a mistake.

Enjoy!

Next Page »« Previous Page