SOPHISTICATED FINANCE

A Collection of Excel Posts

In an effort to make it easier for Google searchers and readers to find the correct post they need on Excel or financial models, I have collected them here.

A reader wrote to say that they had not seen anybody give away for free so much help on Excel. Hopefully my small contribution helps.

I have also added some new sites to the custom Google search engine--Sophisticated Finance Excel--which may prove useful to readers.

The Consequences of Technology Adoption

Two Harvard Business School professors, Diego A. Comin and Bart Hobijn, have just published an interesting paper entitled An Exploration of Technology Diffusion. The paper basically looks at the rate at which fifteen technologies have been adopted by 166 countries over the period from 1820 to 2003 and the resultant affect on economic development. The main conclusion of the paper is that the time to adopt a new technology is the leading factor to explain economic development.

Two examples illustrate the point that shortening the period between invention and adoption of a new technology accelerates economic development. The first example cited by the authors is the Meiji Restoration in Japan (circa 1860) where one of the major objectives was to industrialize Japan by using western technology (and thereby shrinking the time frame to adopt new technologies). As a result of these efforts begun in the Meiji restoration per capita income increased 33 percent. The second example is the Four Tigers (Hong Kong, Singapore, Taiwan and Korea) who in the period from 1960 to 1995 each averaged 6 percent annual economic growth. The data shows that it was the speed to adopt new technologies that explains in large part their phenomenal growth. The data also shows that Latin America is comparatively slow to adopt new technologies and consequently has had much slower economic growth than many other regions.

The paper provides the data to draw some other interesting conclusions about globalization, government policy, new business development, and business management.

Globalization The rapid diffusion of technologies in recent years is probably not due to globalization or the Internet revolution. Over the nearly two hundred year period studied, newer technologies are adopted faster than older technologies. As we consider the free trade dialog, rather than focusing on jobs going off shore the better discussion would be about how to encourage U.S. companies to adopt new technologies faster.

Government Policy The shorter the lag in the adoption of a new technology the greater the economic benefit. Government interference in the development and particularly in the time to adopt a new technology has dramatic negative consequences for economic growth. Government policy on stem cell research and global warming, to cite two examples, could severely slow adoption of these technologies in the U.S. and put economic development at a severe disadvantage.

New Business Development When the number of technologies available for a production method are small an increase in production methods, i.e. a new technology, have a very large effect on economic productivity. Given the limited number of communications methods available pre-Internet (Telephone, radio, television, newspapers) it should not be a surprise the profound effect the Internet has had. More importantly, when looking for new business ideas, look at industries or needs where there are a limited number of technologies for production; energy generation comes to mind.

Business Management A widely held view is that one does not invest in new technology when the cost of using people at low salaries is cheaper. This view is particularly prevalent in emerging markets. The key factor to explain the lack of development in emerging markets is their slowness to adopt the new technologies. Invest in new technology, even if you can not afford it.

While much is said about globalization, economic development and third world governments, it all comes down to how fast can you adopt new technologies--assuming that the goal is economic development.

If you are interested in following the thought leaders at Harvard Business School their new work is here.

Target Customer

A short post entitled Build Value in a blog called Peter's Thoughts got me thinking about the all important and often neglected concept of target customer. The key point in Peter's post was that you create value by satisfying the customer need. He goes on to say that management should never define the product features, but instead management should talk to a lot of customers. I agree 100 percent with what he says, but here is the problem. Who is the customer and more precisely who is the target customer. Most business plans, start ups and troubled companies lack an understanding of their target customer, which is usually demonstrated by their inability to fully describe the customer. Let's take an example. Here are five descriptions of a target customer:

1. A woman
2. A woman with children
3. A middle income woman with children
4. A low middle to middle income woman with children
5. A single, low middle to middle income woman with children

Notice how your perception of the woman changed with each additional descriptor. As your perceptions changed so did the probable needs of the woman.

Probably the most important concept in business after cash flow is target customer. Target customer defines the product, targets the selling strategy, refines the pricing and most importantly gives a concrete reality to the customer by defining their needs. Target customer is not a theoretical concept and is defined and refined by reviewing sales data, meeting customers, analyzing product sales trends and talking with the sales force for their feedback. Understanding the target customer, who may be in a state of constant change, such as for a retailer, is a continuing, constant process. Through this constant process of analysis you insure that your definition of the customer's needs is current, that you have not overlooked a key need which may still be unsatisfied and perhaps most importantly you keep management focused on the customer.

In many of the business plans that I see for new social networking sites I see detailed lists of site features but never get an idea of who would use the site. Most social networking sites are now oriented toward a particular vertical market, but this approach actually places a greater importance on understanding the target customer. If ten sites target photographers, the site that wins is the site that better satisfies the needs of photographer. This will probably be the site that has the most profound understanding of their target customer and not necessarily the site with the longest feature set.

In summary, a target customer is defined by their needs which provides the concrete information that fleshes out the standard demographic or segment analysis. While a target customer will undoubtedly evolve, particularly in a start up, one is well advised to define a target customer early in the business plan process in order to provide a concrete reality for the market you propose to serve. Otherwise you may end up with a product or feature set that does not match a customer need.

Bank Losses

Research Recap had a short story on the losses of major banks from mortgage securitization. A summary of the losses by bank is shown below. Note that this week's $12 billion loss at Royal Bank of Scotland is omitted.To put these losses of approximately $218 billion in perspective, the losses are comparable to the annual GDP of the Caribbean and Central America combined. Since the mid-1990s I have thought that derivatives would be the force shaping global finance. I never imagined that such a helpful tool could be so abused.

Cost Cutting?-Update

Yesterday's most popular search that brought readers to this blog was "Deutsche Bank Brothels". Google is not yet sophisticated enough to tell me whether searchers were looking for a list of the brothels or just more details on the story.

For those of you who wrote or commented to say that the bank never took you to a brothel I can only say that you should have proposed a bigger loan.

For those of you looking for a new business idea, I think a guide called "Brothels of the Leading International Banks" would be very popular. Of course, you would first need to document whether any other leading international banks allow expense reimbursement for such activities.

For those of you actually interested in cost cutting I apologize, but some stories are just too funny.

If you missed yesterday's breaking news story on Deutsche Bank it is here.

I will return to more serious posting as soon as I get that 10Q done, but until that time I may ....

Cost Cutting?

In this blog I try to deal with finance and related topics in a serious way. However, sometimes we must digress and thereby provide you with real insight into the world of "high" finance. This morning's lead story on NPR (National Public Radio) is just such an opportunity. NPR reported that Deutsche Bank, one of the largest commercial banks, will no longer allow employees to use company credit cards to entertain clients in brothels and strip joints. This drastic measure is reportedly part of a new cost cutting effort at the bank.

There are several important lessons to be learned from this story:

• The ladies who work in the brothels were unwilling to accept sub-prime mortgages in payment or could not agree on the mark to market discount for the mortgages, an ample amount of such securities being available at Deutsche Bank and presumably the reason for the cost cutting
• The brothel owners patronized by Deutsche Bank will be diversifying into new businesses so that the charge slips will not fall into the prohibited category, thereby leading to a boom in new barber shops, restauarants, etc.
• Deutsche Bank will be at a competitive disadvantage to other leading international banks who can still patronize brothels, so now is the time to bring Deutsche Bank your marginal projects in Eastern Europe, South Asia and Central America.

There was no announcement this morning on whether the bank would finance brothels. We hope that the sleuths at NPR do a follow up on this important unanswered question.

Understanding Auditors

Every audited financial statement has an auditor's opinion which includes in the first paragraph the statement "these financial statements are the responsibility of the company's management". Many auditors draw the conclusion from this statement that the client must prepare their own accounting entries in order to preserve the accountant's independence.

Now let's look at the purpose of an audit. An audit serves two purposes:

1. To confirm that the financial statements are prepared in accordance with GAAP (Generally Accepted Accounting Principles)
2. To establish whether there are sufficient financial controls in place to confirm the accuracy of the financial statements.

The paradox arises because if the auditor prepares an accounting entry then they supposedly compromise their ability to determine the integrity of the financial statements. This is hogwash. One of the benefits of a good accounting firm is that they can provide the accounting entries for complex transactions, such as employee stock options or mandatory redeemable stock. When your auditor tells you that you need to prepare the accounting entries and they will review them, what it usually means is that the auditor has exceeded the man hour budget for the audit. Nothing to do really with financial statement integrity.

Why do auditors end up exceeding their man hour budgets. Simply put--the client's accounting is terrible. No financial controls, no reconciliation of key balance sheet accounts, significant transactions not accounted for, etc. I see poor accounting practices all the time with early stage private companies. The reasons companies have so much trouble with accounting are:

1. Owners hire inexpensive bookkeepers and think they have accounting professionals
2. Owners focus on cash flow and tend to ignore accounting
3. Owners do not appreciate the value of an audit and see it only as an expense

If you are having trouble with your auditor, it almost always means you have problems with your accounting, accounting management and most importantly the financial statements. Get some outside help with your accounting and the relationship with your auditor will definitely improve.

Colombia Free Trade Agreement

Last week it was announced that Democrats in the House of Representatives are going to block a vote on the treaty for a free trade agreement with Colombia. The New York Times quotes Nancy Pelosi, "We’re first and foremost here to look out for the concerns of America’s working families". The NYT then goes on to quote Condoleezza Rice on behalf of the Bush administration, saying that the U.S. needs Colombia's support to stand up to "very hostile anti-American states and forces in Latin America".

These quotes explain why the U.S. reputation internationally is in a tailspin. Ms. Pelosi puts partisan politics ahead of a good trade agreement with one of the two or three remaining U.S. allies in South America. Ms. Rice responds with "national security" issues related to Venezuela. The Boy Scouts of America could handle Chavez in Venezuela if he became "very hostile". Idiots Politicians on both sides use arguments that a toddler would see as specious.

On a positive note, Peru has invited Central America to enter into negotiations for a free trade agreement. Maybe Peru will replace the U.S. as the world leader in free trade, democracy, human rights etc.

Quick Way to Do Due Diligence

Over the last few months I have been working with several new clients and I have noticed an interesting correlation. The tighter the company's restrictions for access to the Internet, the more poorly managed the company. (Note:companies that do not allow any network access to outsiders do not come under this finding.)

With almost every client I use their on premise network, which is usually a security enabled wireless network. The longer it takes for me to re-configure my computer to work on the network is the first indication that management is not paying attention to IT. The number of people that have to be consulted for me to get access to the network is another leading indicator of management issues (3 is my all time high number). The third key piece of data comes when you see what sites are blocked by the client. When you can not access your email account you know there are management issues. When you see LinkedIn or Facebook blocked you have further proof of management issues.

I had all of this happen with one client recently. When I asked a senior executive at the company who made all these restrictive decisions he told me he did not know who was responsible for IT. True story. I think it proves my point.

So, if you have to do due diligence on a company, just try to log on to their network and see what you can access. If access takes 30 minutes to be arranged or is highly restrictive, and the company is not Wal-Mart or an NSA sub-contractor, you know that you have a company with senior management that does not pay attention to IT. If senior management does not pay attention to IT, you have learned a valuable piece of information--probably senior management does not understand IT and therefore can not properly supervise IT subordinates or senior management does not care about IT.

One might argue, in this day and age, that you have just finished your due diligence with the discovery that there is no senior management of IT. Another partner, acquisition, licensee or merger candidate should probably be your recommendation.

New Business Idea--SEC Wikis

I have spent the last two weeks working on a prospectus for a company that is backing into a public shell as a means to get a public listing and additional capital. This is not my usual type of client assignment, but they needed some extra hands to meet a tight time line and a friend asked me to help. The amount of work involved in preparing a prospectus is gargantuan and explains why I have not been blogging much.

I have written a few prospectus over the years and one of the biggest challenges is catching up on the latest requirements from the SEC and the changes since you last wrote a prospectus. When I started thinking about what to propose for the MD&A, the Management Discussion and Analysis, which deals with explaining the financial statements and the business segments, I Googled for some guidance. Fortunately I found a great set of  disclosure guidelines. Unfortunately, the guidelines were 200 pages. Ugh!

This got me thinking about why nobody had built a wiki to write selected, key sections of an S-1,10-K or 10-Q such as the MD&A. Years ago I was given a copy of Sumitomo Corporation's (very, very large Japanese international trading company) contract book. This book contained four drafting alternatives for every conceivable part of an international contract, with a rating from acceptable to "super strong" for each alternative for each section. Book was about 300 pages and one of the best guides I have seen for how to draft a contract.

Seems to me that if Sumitomo could put together a book on how to draft contracts for everything from oil trading to technology licensing, somebody should be able to build a wiki to help draft the MD&A and certain other sections of SEC filings. I would be happy to discuss with interested parties.

Note: to all the lawyers who refer work to me I can only say that perhaps you would like to join this project

Excel Model Tips IV

This is the fourth and last post for awhile on Excel modeling. Otherwise I will lose my tech readership. Readers have emailed me with questions about balance sheets, how to build balance sheets that are dependent on events or acquisitions and how to build self-balancing models. Let's start with how to build a self-balancing model first because it may help to answer the other questions. The diagram below may help.

From Excel Model Tips II we know that we should start every model by first defining the business model, the growth drivers and the assumptions related to those growth drivers. This work makes it very easy to then build the P&L. From the P&L we link the net income for the period to the net income for the same period in the net worth part of the balance sheet. This insures that net worth rolls forward properly. (Remember that retained earnings period 1 plus net income period 1 equals retained earnings period 2.)

Next we build out the rest of the balance sheet except cash. I usually use a days on hand method to build AR, inventory, AP and accrued expense. Plant, property and equipment is developed separately and usually comes from the business model, it either being a growth driver or directly correlated to a growth driver. I link in my financing from the sources and uses statement to model debt and equity, if any, and complete my balance sheet. (I usually model simple balance sheets and the effort goes into clearly showing the debt and the working capital components.) With everything on the balance sheet completed except cash I then prepare the cash flow statement. The ending cash for the period is then linked to the balance sheet cash for the same period. If you did the cash flow properly the ending cash value will create a balance sheet where total assets equals total liabilities plus net worth, which I guess is why they call it "self-balancing". Last step is to look at cash balances on the balance sheet to make sure that cash is sufficient in each period. If it is not, go back to the sources and uses statement and increase debt or equity. If sources are linked to the balance sheet debt and equity, the changes in them automatically flows through to increase cash and the final sources and uses statement defines the total financing need. Effectively, by managing the cash balances you determine the financing need. (Obviously there are constraints on the amount of debt based on EBITDA, cash flow and leverage (EBITDA/debt, debt/equity).

Now what we have seen so far is that the P&L basically drives the balance sheet in my model building method. So how do we handle uncertain events that affect the balance sheet? The answer is that we reflect them on the P&L and then they automatically flow through to the balance sheet in a linked model. How do we model uncertain events on the P&L? We use an on-off switch. For each event we use a 0,1 indicator where "0" means the event did not happen and "1" indicates that we expect the event to happen. Download the file to see a simple example. Download Event.xls (Assumptions are in Bold Blue.)

By building a matrix using various combinations of events and the financing required we would be able to determine the range of financing required for the planning period. Possible acquisitions would use the same indicator logic (0,1). You would build the model with each acquisition P&L and balance sheet on a separate tab. Each tab would have an indicator cell which would link to the revenue and expenses (and thereby to the balance sheet for the acquisition). Start the year with the acquisitions as "0" and turn them on when you close the deal. Build the assumptions for the acquisition with a close date for each acquisition and when you switch the indicator to "1" the P&L will start in the correct month. An example of this using event dates is in the model you already downloaded.

Download this file to see the simple balance sheet template I use. Download bal_sht.xls

In closing this four part series, remember to build your model once with assumptions so you do not have to rebuild it. 15 minutes to figure out the logic of assumptions is much better than an investor who can not do sensitivity analysis for a change in assumptions.

U.S. Dollar Weakness

Last week I had a very surprising conversation with a stock broker at Citibank. He told me he did not understand why the U.S. Dollar was so weak. The same week two people asked me for advice on where they should be investing. These two themes are related and I thought a quick analysis might be helpful.

Look at the two charts below. The first chart is the Dow Jones average for the last two years. The second chart is the exchange rates of selected currencies relative to the U.S. Dollar for the same period. Notice that when the U.S. stock market began declining in September 2007 all four of the currencies started to strengthen against the Dollar. As foreign investors withdrew their money from the U.S. markets they not only chose other markets to invest in but also switched out of Dollars. The longer that U.S. markets are not attractive, the stronger the Euro and Yen will become.

So the answer to "where to invest"? Euro and Yen denominated securities look promising. Even if the securities do not perform, the currency gains should provide a decent return. For the more adventuresome investor, Brazil and Chile look interesting.

Investing in foreign currency denominated equity securities is not for the faint of heart and short term interest bearing notes denominated in a foreign currency may be more appropriate for certain investors. Consult your licensed investment advisor.

The FX chart came from the Sauder School of Business at the University of British Columbia and can be found here. This is the best site I have found for analysis of FX.

Forbes New Blog Network

Forbes, the business magazine, is organizing a new network of blogs called the Business and Financial Network. Initial reports indicate that there will be 400 blogs in the network, which will be housed within Forbes.com. In return for joining the network, members will be required to run ads that they approve from Forbes and other advertisers. Sophisticated Finance has been asked to join this network.

When I started this blog I never expected for it to have advertising and merely hoped for the readership to grow to a size where it would ...... Now I think I have a chance for the readership to increase significantly but I have to accept ads from Forbes.

Thoughts from readers on whether to join this network would be appreciated. Leave a comment or email me.

Setting aside my new apparent conflict of interest, Forbes.com is actually a pretty cool site with a very neat wiki for org charts and a nice selection of widgets that you can use.