On January 30 Pershing Square released an open-source spreadsheet containing information on CDO guarantees written by MBIA and Ambac.
It's got 551 worksheets and occupies just under 128Mbytes. But it doesn't have to be so big. In 543 of those worksheets, there is space allocated to rows far beyond the last one with any data in it; and eight of the worksheets have many redundant columns to the right of the data too.
This fact can be found manually, on a given worksheet, by pressing CTRL-END, and seeing which rows and columns the cursor lands in. If it's far beyond the last occupied rows and columns, space is being wasted.
The same condition can be found automatically by any of several add-ins for Excel designed to aid development and testing of spreadsheets. They are rather necessary for a quantity of worksheets infeasibly large to explore by individually.
The space can be recovered by deleting the unnecessary rows and columns, saving the workbook, closing it and reloading it. Doing that to Pershing's spreadsheet reduces the size of the workbook to below 87Mbytes, which means that it contains 41Mbytes of fresh air.
That doesn't stop Pershing's work being an impressive piece of analysis.
Saturday, 9 February 2008
Saturday, 29 December 2007
Financial Models for Underachievers: Two Years of the Real Numbers of a Startup
Guy Kawasaki is a widely published entrepreneur, whose career ranges from being one of the original employees of Apple back in 1984 to being CEO of a venture capital firm that backs startups in Silicon Valley. He is also the author of How to Change the World, a must-read blog for anyone who is interested in business or marketing.
His posting of 1 October, "Financial Models for Underachievers: Two Years of the Real Numbers of a Startup", links to a post by "My buddy at Redfin, Glenn Kelman", who "reveals his numbers and his lessons" so that "other entrepreneurs could get greater insight into the witchcraft called financial modeling". This post attracted much comment from other bloggers. A couple of weeks later saw a follow up "Glenn Kelman's Financial Model" which linked to Redfin's actual Excel file.
This spreadsheet makes interesting reading. Guy K is clear about its purpose: "Startups face one primary challenge: To never run out of cash." So let's look at the calculation for the cash balance at the end of each month, which is at 'Monthly Financials'!E57:AB57.
The relevant formula is
=D57+E43-E54+
IF((MONTH(Date_of_series_c_raise)=MONTH(AH56)),
IF((YEAR(Date_of_series_c_raise)=YEAR(AI56)),Amount_raised_series_C,0),0)
which is equivalent to
= CashBalanceBroughtForwardFromLastMonth
+ RevenuesThisMonth
- CostsThisMonth
+ ThatBigLongIFstatement
What ThatBigLongIFstatement s try to do is to say is: IF this month is the month when the series C fund raising takes place, THEN add the amount raised into the cash balance. But the cells mentioned in it, AH56 and AI56, are to the right of the table setting out all the figures, and are all blank. For this reason, the model will never include the proceeds of the fund raising, no matter what assumptions are typed in.
Among the costs deducted in the calculation of cash are
- Net operating costs, dominated by payroll
- Capital Expenditure, dominated by employee setup costs such as computers and desks
- Depreciation, which is derived from the capital expenditure.
Depreciation is an accounting entry which is correctly deducted in reaching various measures of profit, but has no role in determining the cash position of the company and should not be taking part in this calculation.
As the model stands, it shows a cash balance in December 2008 of $897,910. If these two issues are repaired, the comparable number becomes $14,110,152, which is different by a factor of over 15x.
These are just the most obvious glitches in this example model. There are several other treatments that are open to question.
To be fair, the opening paragraph of the spreadsheet says that "This model was prepared by someone with no background in finance, and so may be flawed in many respects.". It says quite clearly "Sample Model Only; Made-Up Numbers". And Guy K's original analysis was about how to think about the assumptions underlying a plan for a new business, not the bits and bytes of spreadsheeting. But the later post does say that "We’re posting this model because its basic structure might help other entrepreneurs who don’t know where to start." Those entrepreneurs need just a little caution.
His posting of 1 October, "Financial Models for Underachievers: Two Years of the Real Numbers of a Startup", links to a post by "My buddy at Redfin, Glenn Kelman", who "reveals his numbers and his lessons" so that "other entrepreneurs could get greater insight into the witchcraft called financial modeling". This post attracted much comment from other bloggers. A couple of weeks later saw a follow up "Glenn Kelman's Financial Model" which linked to Redfin's actual Excel file.
This spreadsheet makes interesting reading. Guy K is clear about its purpose: "Startups face one primary challenge: To never run out of cash." So let's look at the calculation for the cash balance at the end of each month, which is at 'Monthly Financials'!E57:AB57.
The relevant formula is
=D57+E43-E54+
IF((MONTH(Date_of_series_c_raise)=MONTH(AH56)),
IF((YEAR(Date_of_series_c_raise)=YEAR(AI56)),Amount_raised_series_C,0),0)
which is equivalent to
= CashBalanceBroughtForwardFromLastMonth
+ RevenuesThisMonth
- CostsThisMonth
+ ThatBigLongIFstatement
What ThatBigLongIFstatement s try to do is to say is: IF this month is the month when the series C fund raising takes place, THEN add the amount raised into the cash balance. But the cells mentioned in it, AH56 and AI56, are to the right of the table setting out all the figures, and are all blank. For this reason, the model will never include the proceeds of the fund raising, no matter what assumptions are typed in.
Among the costs deducted in the calculation of cash are
- Net operating costs, dominated by payroll
- Capital Expenditure, dominated by employee setup costs such as computers and desks
- Depreciation, which is derived from the capital expenditure.
Depreciation is an accounting entry which is correctly deducted in reaching various measures of profit, but has no role in determining the cash position of the company and should not be taking part in this calculation.
As the model stands, it shows a cash balance in December 2008 of $897,910. If these two issues are repaired, the comparable number becomes $14,110,152, which is different by a factor of over 15x.
These are just the most obvious glitches in this example model. There are several other treatments that are open to question.
To be fair, the opening paragraph of the spreadsheet says that "This model was prepared by someone with no background in finance, and so may be flawed in many respects.". It says quite clearly "Sample Model Only; Made-Up Numbers". And Guy K's original analysis was about how to think about the assumptions underlying a plan for a new business, not the bits and bytes of spreadsheeting. But the later post does say that "We’re posting this model because its basic structure might help other entrepreneurs who don’t know where to start." Those entrepreneurs need just a little caution.
Monday, 24 December 2007
Analysing the analysts
Much policy making by government bodies around the world is underpinned by financial analysis. Little of the analysis is ever checked for accuracy. As a result, decisions affecting the lives of large populations are made on grounds that may be less robust than imagined, or just flat wrong.
Questionable arithmetic isn't unique to the public sector. Private sector firms produce it too. Some might say that the credit crunch underway right now is the product of defective analysis on a global scale. When people start losing their savings and their houses, the consequences can be said to be life changing.
This blog will analyse the work of financial analysts, for no better reason than that doing so is something that amuses the author.
Questionable arithmetic isn't unique to the public sector. Private sector firms produce it too. Some might say that the credit crunch underway right now is the product of defective analysis on a global scale. When people start losing their savings and their houses, the consequences can be said to be life changing.
This blog will analyse the work of financial analysts, for no better reason than that doing so is something that amuses the author.
Subscribe to:
Posts (Atom)