Financial modeling

It struck me as peculiar that my accounting degree did not include any financial modeling. It was in the real world that I learned, initially as a CFO, and then in finance.

As a working capital financier, you see plenty of cash flow projections. Working capital finance is usually needed because people have made mistakes with their modeling at the outset. Financiers have to have own models into which they plug the numbers. It’s better than trusting the sanguine figures that are usually presented.

So that’s what we did.

As the spreadsheet guru, I was tasked with creating a model that integrated the balance sheet, income statement and cash flow projections.

Getting all three to balance is tricky, but crucial. Errors in assumptions become evident as the ratios go out of kilter, and that let’s you know if the numbers can be trusted.

Once the model is working, the starting point is to enter the last three months figures. If the assumptions and calculations are right, the most recent balance sheet will pop out from the input. If it doesn’t there are errors. Fix them, and you’re on your way.

The assumptions are key. They will guide the strategic planning, and the executive that has a grip on them is likely to succeed.

One of the best tools for getting a feel for the key factors in a business is also one of the simplest: break-even analysis.

To the uninitiated, break-even analysis is like a magic trick. Sitting in a pub, you can scribble down the variable expenses, then the fixed expenses, calculate the contribution, and tell a desperate friend why his business is struggling, and how long it will take to fix.

The financial model must include the results of a proper break-even analysis. Then, slotting in past figures will immediately reveal any misallocation of fixed and variable expenses.

The exercise of entering past figures has another benefit.

It provides the executives with confidence that the model works, and the understanding that by continuing to enter results they will know in advance whether something is out of line.

They become proactive.

They look good.

They are good.

1.00 avg. rating (43% score) - 1 vote

The Excel mistake that changed the world

In 2010, two leading Harvard economists, Carmen Reinhart and Kenneth Rogoff, published a paper. They had found that debt levels exceeding 90% of GDP led to a decline in economic growth. Governments took notice, and policies were implemented to reduce debt, with sometimes draconian austerity measures.

George Osborne quoted the paper as he introduced his measures to repair Britain’s economy. We are still living with the consequences.

The problem is that their results were wrong.

The Harvard professors had used Excel, and had accidentally only included 15 of the 20 countries under analysis in their key calculation. It made a big difference to their results. Their finding of a decline in GDP of 0.1% was really a increase of 2.2%.

As their data had grown, they had failed to change the formula.

Excel is a great tool. It’s intuitive, easy to work with, and presents results well with graphs and pivot tables.

It’s the best tool for creating and checking complex algorithms, doing financial projections, and quick and dirty calculations.

It falls down when handling big data, data that grows over time, or there are multiple users making updates.

It’s easy to end up with multiple versions of the data, and then have to decide which is correct.

Excel makes entering data easy. But it’s also easy to make mistakes. Validations to guard against bad data have to be programmed in. Garbage in – garbage out.

Ray Panko, a professor at the University of Hawaii and the elder statesman of small coterie of research academics who study Excel errors, claims that 84 percent of spreadsheets contain some kind of materially significant error. Other economists agree.

Databases are not visual, not in the way that Excel is. The terminology and design can seem arcane. You can’t just open a database and start entering data. They’re not intuitive. They are also not as complex as they seem.

So, which should you use? – The answer: both.

It’s a matter of deciding which is the right tool for the job. It’s not necessary to decide up front. Using Excel to become familiar with the data is a great way to start a database project. When you get going, importing the initial data into the database is easy. so nothing’s lost.

Having an existing spreadsheet that should be a database is an excellent way to learn. The best choice for a project is one where you’re struggling to tie up the relationships in the data. What’s difficult in Excel is easy in a database.

Great, now the data is in a database, but now you need to present the result of your analysis. Simple. Export the summaries into Excel and do it there. It’s easy to do, and it’s the best tool for the job.

What about the cost? Unlike Excel, many of the best databases are open source and free.

So, how do you get started. Next time.

1.00 avg. rating (43% score) - 1 vote

A surprise solution

Like many wealthy countries, Switzerland’s population is aging fast. In 1960, the median age was 32.7. Now it’s close to 43. The fertility ratio is well below the 2.1 replacement level, at 1.54. The age dependency ratio has moved from 15.5% in 1960 to 27.6% today. But Switzerland’s incredible wealth hides the growing problem.Switzerland age dependency ratioSwitzerland fertility rate

The country is attractive. It’s close to the top of most global rankings. The immigration queue is long. Few get in. Even the very rich can struggle. Roman Abramovich was refused citizenship because he posed a “reputation risk”. And for those who aren’t in the 1%, the cost of living is a concern.

The growing shortage of young working people shows up fastest in the job categories that are least attractive. When demand exceeds supply, the price goes up. That’s happening.

A recent experience provides an example. We had a problem with hot water. The plumber quoted CHF4,148 ($4,160) to replace the hot water cylinder, an extraordinary amount in any other country. Not only that, a little experimentation revealed that the hot water cylinder was not the source of the problem.

Refugees offer the solution. In a conservative country, that seems like political suicide. Not handled properly, it is.

Xenophobia aside, refugees become a real problem when they can’t or won’t integrate. Integration works best at the community level. National policies cannot create community acceptance. Acceptance requires leadership at the community level, and a community that is a community.

Switzerland accepts refugees. Anecdotal evidence suggests that refugees are increasingly turning to crime. In the past 9 years Switzerland’s overall crime rate has dropped by over 20%. That improvement hides the growth in some categories of minor crime. If the anecdotal evidence is correct, then some refugees are not integrating.

The two problems have one solution. Train the refugees to provide services that are becoming overpriced. To be sure, it’s not that simple. That’s where the community comes in. People working together find answers to challenges that initially seem intractable.

And working towards a common goal integrates the community.

1.00 avg. rating (43% score) - 1 vote

Letter to the Managing Director of Pickfords

Attention: Mr Russell Start
Managing Director
Pickfords

Dear Sir,

Moving home is stressful even for people that do it regularly. It is no doubt the reason that Pickfords’ advertising places emphasis on customer satisfaction, telling prospective clients that everything is in good hands.

With years of familiarity with the brand, it was reassuring to hear that Pickfords had been selected as my mover. My experience has not borne out that confidence.

The moving team were courteous, efficient and competent. The rest of the experience was horrible.

Like an increasing number of Britons, I’m a keen cyclist. Packing a high end bicycle requires specialist knowledge. The pre-packout inspection report raised the suspicion that Pickfords does not have that knowledge.

The most crucial components on a bicycle are left exposed to damage with the usual approach using plenty of bubblewrap and a bespoke box. It is more cost-effective, both for the client and the mover, to use boxes that are specifically designed for transporting bicycles. Anyone who has bought a bicycle online will be familiar with the packing that works.

On raising the concern, the response from Pickfords did little to allay my fears. The clichés about following international standards failed to confirm that Pickfords uses boxes that protect a bicycle properly. And so it turned out.

During the packout inspection, I’d stressed the bicycles, and the Chiwara, an almost irreplaceable artefact from Mali. The issue of batteries for the airfreight portion of the shipment was raised, and I had confirmed that the batteries from the drone would be removed.

On packout, the failure to provide the correct boxes for the bikes increased the shipping weight exponentially. For example. the frame of one of the bikes, which weights less than 5kg, resulted in a packed weight of 15kg, with an increased risk of damage to some of the most expensive components.

Soon after packout the Pickfords adminstrator contacted me saying that customs had raised an issue about the batteries in the airfreight, particularly the drone which locked in a protective casing. These items had not been shipped. I explained that the lock is TSA compliant, and that all batteries were in compliance with international airfreight regulations. It was clear from the exchange that the Pickfords team had no familiarity with the relevant regulations and entered into a polemic on the subject. Eventually, once provided with the regulations, they conceded that they were wrong.

In frustration, I provided the combination for the TSA lock to the Pickfords administrator, under the condition that the drone was repacked exactly as it had been when opened, allowing them to confirm that the battery had been removed. To justify their ignorance of the regulations, they claimed that the battery in the drone controller was an issue, and removed the controller.

Without informing me, the items with batteries that were in the airfreight were put into my wife’s shipping. My wife is headed for the Americas. I am in Europe. I will never see those things again.

The bikes arrived undamaged. The Chiwara is broken.IMG 557927742 The drone had not been properly repacked, and the gimbal was left exposed, putting that sensitive part at risk of damage.

Perhaps these failures explain why, in Pickfords’ 400 year history, in the past 10 years it has been under administration twice. Companies that put their customers first succeed. Pickfords is not one of them.

Sincerely,
Roy R Dalle Vedove

0.00 avg. rating (0% score) - 0 votes

Where are Vietnam’s traffic police?

With it’s extraordinary road mortality rate, it’s fair to wonder what Vietnam’s traffic police are doing.

2017 05 25 10 11 48 1

The largest proportion of road fatalities are motorcyclists. In Vietnam, motorcycles aren’t just a form of transport. They are an important part of the haulage system. It is not unusual to see a television or a washing machines, bleating livestock, and passengers on the back of a motorcycle.2017 05 26 08 57 30 So, the traffic police concentrate on motorcyclists. It is in the implementation that the problem becomes evident. The police usually stop motorcyclists to inspect papers, rather than for infractions of the road regulations.

201512110010While casual observation suggests an almost equal distribution of the sexes amongst motorcyclists, it is often women who are the target of inspections. Because women frequently have to transport their children, so tend to be conservative drivers. The men, particularly the young ones, are are those who are reckless.

IMG 522466305

Also. the location of the inspections is questionable. They appear to be chosen for a suitability to ambush, rather than with any consideration to the danger that they may pose. Motorcyclists are banned from using the motorways, except when they are the only means of crossing rivers. The on ramp onto a motorway is potentially hazardous under any circumstances, and especially so if it is selected as an ambush point.

And so it proved when a motorcyclist swerved into me as a traffic officer ran into the road to make a stop. No one fell off, and in Vietnam minor collisions don’t even warrant stopping to check for damage. Except that the point of contact on the bicycle was the derailleur, the most expensive component on an already expensive bike.2017 07 23 09 55 46

The traffic police denied all culpability. If anything, they appeared to be amused. In their opinion, it was the motorcyclist’s fault. Their failings, in the location of the ambush, causing the motorcyclist to swerve, and failing to prevent him from leaving the site of an accident, were not a problem in their opinion.

There is an issue with training, and until that is resolved, Vietnam’s motorcyclists will continue to die.

0.00 avg. rating (0% score) - 0 votes