## Archive for the ‘Spreadsheets’ Category

### The efficient frontier for financial modelling

June 17, 2012

There is a trade off between the explanatory powers of a model and its complexity: the more a model explains, the more complex it will be.

Would you disagree with that?

Before you answer, let me make a claim:

Mathematics is about revealing patterns that simplify.

The mathematician’s work is actually based on producing simplicity, showing how complex problems can be reduced to component pieces that are simpler.

Think about all the beautiful proofs you’ve known. They are beautiful because they have shown how a simple and harmonious vision can achieve a target better than alternatives that will seem like a hack in comparison. Wikipedia explains it well, here.

### Making neat Excel charts (quickly)

May 9, 2012

If, like me, you spend a lot of time building spreadsheets with charts, you will want to make sure they are all lined up and neat.

This is how I do it. You can get this whole process done in seconds with a bit of practice :-).

1. Select the range of cells that  you want your chart to cover.
2. Name it “foo”. You can do that quickly with the keyboard shortcut Alt+i n d (so Alt+i, then ‘n’ and then ‘d’) which brings up the Name Range box.
3. Use Alt+F11 to bring up your VBA window and select View->Immediate (or use Ctrl+G).
4. Select the chart.
5. Paste the following code into the immediate window:
```ActiveChart.Parent.Left = Range("foo").Left
ActiveChart.Parent.Top = Range("foo").Top
ActiveChart.Parent.Height = Range("foo").Height
ActiveChart.Parent.Width = Range("foo").Width```
6. Run each line: put your cursor at the end of the first line and hit Enter to run each line by line.

### Project finance funding: a simple Excel model

May 4, 2012

Financing of infrastructure projects is a hot topic at the moment.

Penny Lynch has kindly made a simple cashflow Excel model available which shows the key components (equity vs debt) of project finance funding.