SOPHISTICATED FINANCE

Miscellanea--II

The Miami Herald has a nice story today on the Entrepreneurship Center at FIU where I am on the Board. Story includes an interview with Mike Tomas, the Chairman. Given the recent issues with the AP and blogger quotes, I have decided to only include the link.

This morning I received an email from Daniel Chow in Hong Kong. He has organized a site called Financial Modeling Guide with a Ning community, Financial Modeling. I have added this site to my Google custom search engine, Sophisticated Finance Excel, so I definitely think these sites are worth checking out. The first few posts you see may be some re-posts from Sophisticated Finance but dig around and you will find some good stuff.

VC Confidential reported today that there were no VC-backed IPOs so far in 2nd quarter 2008, according to the NVCA. Since the NVCA started recording data this has never happened before. The post goes on to advise conserving cash and getting any deals done as soon as possible. Valuations are coming down! My thoughts in August 2007 on preparing for a credit crunch still look like good advice for what VC Confidential expects.

Need to find a title for these catchall posts. Recommendations welcome.

Private Equity Firm Mistakes

The private equity firms have been hugely successful for many years. However, two industries stand out as consistent failures for the PE firms--telcom and retail--two industries that I know something about. The PE firms lost a reported $50 billion investing in telcom in the late 90s and early part of this decade and now PE-backed Alltel is reportedly going to be sold one year later to Verizon at the original acquisition price . In 2008 six PE- backed major retailers all filed for bankruptcy, including Sharper Image, Lilian Vernon, Linens n' Things and Fortunoff. (Source)

Private equity firms employ a strategy of improved capital efficiency combined with revenue growth. In other words they improve working capital management, sell off excess assets and reduce operating costs in part by aligning management incentives with performance. While all of this is going on they are typically implementing programs to increase revenue and thereby cash flow. Improved cash flow leads to better valuations and exits for them. So what is it about telcom and retailing that does not allow their model to work.

The answer lies in the concept of the marginal capital required to generate an additional dollar of revenue. In manufacturing, to increase revenue you only have to purchase additional product inputs. In retailing to increase revenue you have to create a whole new department, completely re-merchandise a department or build a new store (with additional inventory). In each case there is a big capital requirement which reduces cash flow. In telcom the situation is similar. For every group of new customers, you have to add switches, ports, routers and/or interconnection capacity. All of this equipment requires additional capital in large amounts. In summary, both industries are capital intensive to generate marginal revenue. In both industries you also have to invest large amounts of capital upfront and then hope you get the customers. Misjudge the customer demand and the invested capital is almost a total writeoff. (Misjudge the customer demand in manufacturing and you typically have excess input inventory that can be used next month.)

In the early days of venture capital the firms invested in a wide range of industries. Then the VCs learned that high tech companies were the best fit with their model (high risk, high return). PE firms need to realize that retail and telecom do not fit the PE model. If you are considering an acquisition or a new strategic direction make sure to carefully examine and understand the marginal capital required to increase revenue and whether this profile matches your model (and capital resources).

Early Stage Financing Site

Through a post by Brad Feld I came across a very informative site on early stage investment, angel financing and venture capital. The site is Angel Blog and is written by Basil Peters. Mr. Peters is a former tech CEO who achieved sufficient success to become an angel investor and then started managing technology oriented hedge funds. He writes very coherently about seed financing in all of its variations. His current posts are on friends and family financing.

Before you bombard him with requests to finance your startup, please note that he is located in Vancouver and not likely to finance a startup very far from there.

Due Diligence

I have spent a good part of this year doing due diligence on acquisitions for clients and today I began my fourth project. The client sent me a copy of their proposed due diligence information request and asked for my comments. After reading the client's attempt at such a list, I decided it might be useful to my valued readers to discuss due diligence.

In the way that I approach due diligence for an acquisition, there are five parts:

1. Financial
2. Tax
3. Legal
4. Operations
5. IT

Many people would probably limit the scope of their due diligence to 1-3. While discovering undisclosed liabilities, shareholding discrepancies and misstated financial statements in parts 1-3 obviously is important, the real value added part of due diligence is not in parts 1-3. When you buy a company there are three questions that should always be on your mind:

• Are there any operating risks which if they were to occur would severely catastrophically affect the business, e.g. loss of a customer, distributor, license, etc., and thereby lead to a re-examination of valuation or to walking away from the deal
• How are we going to integrate this acquisition into our existing operations and what will it cost
• What will be the cost in capital and management time to execute the post acquisition strategy (while you should have addressed this issue when you did the original valuation, in due diligence you are confirming the accuracy of the hypothesis)

While parts 1-3 of due diligence may identify some of the operating risks in a business (customer concentration from an AR aging), they do little to address integration or the capital requirements of the go forward strategy. Therefore, an equal or greater part of due diligence should address operations and IT. It is a big mistake to limit due diligence in these areas because you know the industry, had extensive pre-LOI (letter of intent) discussions or the existing management has a continuing financial interest post acquisition.. The real opportunity to understand in depth the acquisition target's operations and IT begins during due diligence.

To clarify what I mean by "operations", we are basically talking about understanding the market and the industry. In other words, sales, marketing, distribution, pricing, new technology and competition. Looks very similar to the analysis of the growth drivers in the business, but in due diligence one should be equally focused on the upside and the downside risks.

The focus of the investigation in IT is typically on integration--how easy will it be to integrate the automated systems, procedures and accounting of the acquired company into the acquiror. Understanding the importance of any proprietary software and its role in operations is also a key issue in order to fully appreciate the human resources and related costs to maintain it. Of course, you also want to have a complete understanding of back up procedures and network security. (You would be surprised at the number of companies that keep their backup in their head office.)

To further illustrate my concept of due diligence I have posted below a sample due diligence information request. While it is a generic request for information, and may occasionally need to be edited, resist the temptation to edit or delete. The list is designed to be open ended in the hopes of encouraging the receipt of new and complete information. Resist editing out subjects that "do not apply". You may be surprised what shows up.

Several years ago I found on the web Arthur Andersen's standard due diligence list. Many years later I still think it's the best basic information request for due diligence that I have seen. Click to download a copy.

Download due_diligence_list_for.doc

Commodity Prices and Monetary Policy

I have been troubled for some time about the declining value of the dollar, high oil prices and the affordability of food in the third world. I have read quite a bit on the subject but most articles were superficial or presented analysis which was not supported by the underlying facts. A paper released this month by International Monetary Fund staffer Noureddine Krichene entitled Recent Inflationary Trends in World Commodity Markets provides a very comprehensive explanation. Mr. (Dr.?) Krichene's expertise and specialization is in the area of the relationship between oil and gas prices and monetary policy and he works on the Africa Desk at the IMF.

Key points from the paper are:

1. The All Commodity Price Index has increased 23 percent per year in the period 2003-2007, the highest rate of inflation since World War II
2. The Consumer Price Index (CPI) in most developed countries showed comparatively small increases of 2-3 percent for the same period
3. The depreciating dollar has not lead to higher prices in the U.S. or to an improvement in the U.S. trade balance
4. The CPI is the principal indicator of monetary policy for most central banks and government monetary authorities and may have mislead them in their analysis of price stability and the correct monetary policy

In summary, we have two major economic anamolies--high commodity prices that are not flowing through to the CPI and a declining dollar which is not leading to a better U.S. trade balance. Krichene believes that excessive expansion of the U.S. money supply and resultant low real interest rates in the period 2003-2007 have produced a "demand shock". Effectively, there is too much cheap money chasing too few goods.

To correct the situation Krichene proposes a Milton Friedman like approach whereby the U.S. government should tighten the money supply and raise interest rates. Such an approach would raise the comparative value of the dollar, lead to lower domestic oil prices and reduce speculative and organic demand for commodities. It would also lead to a recession, depress asset values and bring on a major wave of bankruptcies in the U.S. In other words, it's time to pay the piper. We have been living high on cheap borrowed money and at some point fundamental economic forces have to be recognized.

If I was running a company today I would be looking at reducing debt, tightening up on credit policies for customers and staying liquid. I think the next President, whoever the winner is, faces a very complicated economic situation and will probably be a one term President (we do not re-elect Presidents who have bad economic performance). And in closing, remember that we have not yet paid the bill through higher taxes for a very expensive war in Iraq. This little discussed fact complicates the economic situation even further. Buckle up--the ride is going to get even rockier!

Update: Steve Forbes, owner of Forbes magazine, appears to agree with the analysis and role of monetary policy, according to a post on Fractals of Change today. Maybe I should run for President :).

Note: In a post on global capital markets in January of this year I stated that because of investor liquidity I thought interest rates would stay low. I am changing my position. In the medium term (1-2 years) I think the Federal Reserve will have to raise interest rates fairly dramatically and strengthen the Dollar.

I found the Krichene paper on Research Recap .

Controllers

In response to yesterday's post where I asked if anybody knew a good controller looking for a job in South Florida, a reader posted a comment to say "not being able to find an employee in South Florida is like not being able to find a prostitute in Thailand". I have not been to Thailand in a few years so we will have to trust the reader on the availability of companions in Thailand, but I can tell you that good controllers are not plentiful in South Florida. The accounting industry in South Florida is very understaffed, jobs are plentiful and people easily choose smaller firms until they find the lifestyle they want.

The minimum requirements that I set for a controller also narrows the field:

1. Public accounting experience (2-4 years), preferably with one of the ten largest accounting firms in the U.S.
2. CPA
3. At least two years experience working in a company as a controller
4. Current knowledge of GAAP, FASB, etc.
5. Strong Excel skills and model building experience
6. Self-directed
7. Speak English (the reason they need to speak English will become apparent)

This type of person would earn $80-120,000 per year in South Florida depending on experience.

Many company owners confuse controllers with bookkeepers and save money by hiring the wrong type of accounting personnel. Then they realize that they "need" a CFO to do capital raising and spend $120-150,00 to fill this position. A better alternative for an early stage company is to hire a strong controller and forgo a CFO. A good controller should be able to model the company and provide any schedule required in due diligence. The CEO should be able to write a good Executive Summary in a weekend. Between them they should be able to handle the meetings with capital sources, many of whom will not speak Spanish.

I consistently see owners of early stage companies saving money by hiring bookkeepers instead of qualified controllers. They typically end up with non-GAAP accounting, poor accounting information and no management reporting. They typically never realize that they hired the wrong accounting personnel.

Note: if you had two strong executives in the company who could both actively participate in capital raising you might get away with a Spanish speaking controller but they still have to meet all my other requirements and prove they can stay current on U.S. GAAP (which I believe is only available in English).

Death Spiral Financing

A couple of readers emailed to ask what a "death spiral" was, which was referenced in a recent post on Financing Fees . A death spiral is a financing where massive equity dilution is attached as a condition if a certain event does not take place. For example, you issue convertible bonds which convert into one million shares of common stock, but if the company does not go public by 2009 the conversion ratio changes to 50 million shares. In most death spirals, the existing controlling shareholders lose control if the death spiral triggers.

In my experience two types of investors offer death spiral financings--very large sophisticated investors (like the Walton family) and hedge funds. Sophisticated investors offer death spirals because they see sufficient value to be happy if they end up as the controlling shareholder. Hedge funds use them because they are another alternative to balance risk and return. The bravado and unceasing optimism of entrepreneurs make them very susceptible to death spiral financing offers. They call them death spirals for a reason. They should be the last alternative that one considers and you should be facing imminent liquidation before you accept such a financing.

Random Thoughts

My muse appears to be on vacation this week. Yes--I know this is getting to be a recurring theme, but preparing those SEC documents shuts down the desire to write about anything else. A few quick thoughts follow:

1. Microsoft pulled out of its negotiations with Yahoo because Balmer always had reservations about the deal and used the price difference as the excuse to "gracefully" exit. Nobody walks away from a deal that size over $1-2 per share. No--Steve Ballmer did not call and tell me this, but my explanation is the only logical explanation for the deal collapsing or Microsoft not going hostile. Why did Ballmer start down this road and then change his mind, consider that he and his CFO are a relatively new team that may still be finding their way.  My previous posts on this deal are here and here.
2. Warren Buffet has come out and recommended investment in Europe due to the comparative strength of the Euro versus the US Dollar in the medium term. My thoughts on the subject were here.
3. UBS has reportedly sold a $5 billion portfolio of sub-prime mortgages to a U.S. hedge fund at a discount of only 25 percent. This comparatively minor discount would support claims last week from the Bank of England that banks had over reacted in writing down the value of their sub-prime portfolios.
4. Data has been circulating on the Internet this week that shows the most popular apps on Facebook are for "fun". And all along, I thought Facebook was going to solve world hunger. Everyone building apps for Facebook may want to reconsider their business plans.

Financing Fees

Ask the VC is a blog written by some savvy guys who are all VCs, including Brad Feld from Foundry Group. Today's post deals with a real case where a founder gave up ten percent of his company to get an advisor on board with connections to VCs. I recently saw a case where a founder paid 30 percent for a 90 day bridge loan involving warrants (120% per annum). While obviously both founders were stareing a death spiral in the face, or at least I hope they were, why do so many startup entrepreneurs overpay for capital. There are several reasons:

1. There are a lot of unscrupulous advisors out there who will overcharge; that's why you do background checks and get multiple proposals
2. Entrepreneurs do not consult their advisors and attorneys; mentoring reduces the risk in startups partly because it stops you from doing stupid things and anybody can afford a five minute call to an attorney/accountant/mentor to ask what market fees are
3. Entrepreneurs do not know how to calculate the value of the equity, warrants and options they are giving up; if you do not know how to calculate the value of these instruments you should not be trading them like baseball cards
4. High stress makes for poor decision making; plan ahead

A couple of guidelines to avoid overpaying to raise capital:

1. Loan sharks charge 4-8 percent per month. If the interest rate you are paying approaches these levels (after imputing the cost of any equity you are giving up), then you can be sure you are overpaying
2. The highest fees I have ever seen to successfully raise equity was for a company with eight years of losses. The fees were 10 percent in cash and 10 percent warrant coverage (1 warrant for every 10 shares sold). I would like to point out that these fees were 100 percent contingent on the capital being raised and there was no upfront fee.
3. Upfront fees to raise capital range from 0-$25,000 and the arranger should be putting some real effort into improving the business plan and/or financial model. Fees may vary based on the city where you are located but not by too much. Anybody short of a well known investment bank does not get a six figure upfront fee. (Remember that these fees are for early stage companies and not for a $100 million private equity deal.)

The more you need the capital, the more likely you are to make a mistake. Start the capital raising process early and do not overpay.

A thank you to loyal reader, Kendall, for pointing out the Ask the VC post.

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.

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.

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.

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.

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.

Excel model Tips-III

This is the third in my series of tips for how to do financial modeling more easily in Excel. If you have read the two earlier posts (here and here) you will notice that I frequently refer to logic as a key part of good modeling. I am not exactly referring to Aristotle's three laws of logic, but I may be using logic in a way consistent with Boolean algebra. Basically I believe that Excel can capture any way of thinking or "logic" that we can conceive in a model. Perhaps an example will clarify.

Suppose we are building a ship for $25 million dollars. We are using $4 million in equity and $21 million in bank financing and the equity is drawn down  first. All interest is capitalized during construction. We may change the mix of equity and bank financing and the logic we have to build in Excel is how to have the bank financing start after all the equity is used (with no hard coding of any number). Click on the following link to download the model. Download Example.xls

First notice that the financing assumptions for equity and debt are blue bold in Column B to indicate that they are assumptions and any changes to these values should flow through the model without any changes required to any other part of the model. If I had a full model with a Sources and Uses Statement, these assumptions would have linked back there and would have been unbold black.

The logic that forces the bank debt to start when the equity reaches $4 million is in Row 16 starting at cell C16. The formula I used is =IF(C12=$B$9, SUM($C$6:C$6)-C12-C15,0). To limit the equity to $4 million I used Row 11starting with cell D11 and the formula is  =IF(C12=$B$9,0,MIN(D6,$B$9-SUM($C$11:C11))). The IF command is the key to building the logic in these examples but the logic also required nesting some other commands inside the IF statement, such as the use of the MIN command to minimize two values. By taking the time to build these IF statements I avoid hard coding any values and do not have to rebuild the formulas if the equity or bank debt assumptions change.

Another point about building models. Never use the B Column for period values (Month 1, Month 2, etc.) Column B is for assumptions, to represent prior period values of zero and for command criteria indicators, such as in =SUMIF(range, criteria,[sum range]). Suppose you build a model that includes expense budgets for 100 departments each with six categories of expense. After you build the department budgets your boss says he changed his mind and he wants to know the total expense by each expense category. You have two choices--build six formulas each with 100 links (and hope you don't miss a department) or you can build one formula using SUMIF and copy it five times for the other five categories. A simple example of SUMIF can be downloaded in the following link.Download sumif.xls

In both of the models included here I have made frequent use of the $. $A$4 means that this cell remains unchanged when it is copied; for example when an assumption remains unchanged over all periods in a model. $A4 means that the column is fixed and the row changes when the cell is copied (A4, A5, A6). A$4 means that the column changes but the row is fixed when the cell is copied (A4, B4, C4); for example where you reference the same row across multiple periods in different parts of a model. If you subscribe to my view that you build one period models and then copy>paste for all the other periods, the $ is very useful.

Note: I have no idea where the Blue Bold convention comes from to indicate assumptions, but it is widely used in financial modeling.

Happy modeling.

 

1,000000

Excel Model Tips-II

This is the second in my series of posts on how to build financial models in Excel more easily. The first post is here. This post focuses on the business model.

1. Build the Business Model For an investor to understand your business, they must understand the business model. The business model explains the revenue, pricing and sales and distribution model, i.e.the growth drivers in the business. Therefore, your Excel financial model should make it clear and easy to understand these three critical parts of your business. For example, for a bus company I would probably detail each of the following revenue assumptions--buses in operation, trips per day, operating days per month, bus seating capacity, occupancy percentage per bus, and revenue per passenger. If the business was seasonal, I would add a seasonality factor to be applied against occupancy. I might alternatively build the model driven by sales or more precisely customer acquisition cost. If my monthly budget for customer acquisition was $12,000 and the per customer plan was $4, then I will have 3,000 customers per month. From the number of customers, using the revenue build logic above, I would back into how many buses and trips I need to operate. Many people build their models and focus on matters outside the business model, such as taxes, head office expenses, advertising, etc. While these subjects may have to be included, invest your time and logic in clearly explaining the three key parts of the business model.
2. Sophisticated Investors Analyze the Variability of Revenue and Cash Flow Investors initially want to understand the size of the market opportunity and what the growth drivers are. They also want to understand the effect of a change in a growth driver assumption on cash flow. In order for this to be easy for the investor you need to do two things. First you have to anticipate correctly what assumptions the investor will want to analyze. Secondly, you have to put these key investor  assumptions on the assumption tabs so the investor does not have to read through ten tabs to find the variable he wants to adjust. If you want to make it even easier, build a matrix which sensitizes two key variables for cash flow or revenue. (One or two matrices maximum.)

By the end of this second post on Excel tips for financial modeling, you should be realizing by now that good modeling is all about the logical structure of your model and the assumptions tab. Most of the time in building a model should go into identifying the key assumptions (variables) related to the growth drivers and the logic of how you are going to organize the model. This work involves no real knowledge of finance or accounting. As I tell my students: Financial Plan=Logic+Analysis+Excel.

Excel Model Tips-I

A reader emailed me to say "there is a lot to learn about financial modeling". This got me thinking about why people find it so hard to build financial models. I am going to do a series of posts on how to build models more easily. Probably two major points per post. The first thoughts follow:

1. Build a Re-usable Template Every financial model has an income statement, balance sheet, cash flow statement and a summary tab. If you use generic titling for each part of the model, it easily becomes reusable. (See Startup Excel Model to see an example of the generic titles to use on the income statement, balance sheet and cash flow statement.) By re-using the template, you save time for the important part of the model. If you have six classes of long term debt to model, build a separate debt schedule and link the total back to the long term debt cells on the template balance sheet. Do not change the balance sheet template to include six rows-one for each class of debt-because then you have to also change the cash flow and summary tabs in six places. When you build the template build it to encompass most situations. For example, in long term liabilities have a separate row each for senior debt, mezzanine debt and due to shareholders. (Remember, putting in a zero value also provides information.) If you have to build models for very different industries, such as banks and manufacturers, you may need to build two generic templates. In summary, use schedules to detail information and link back to the generic template.
2.  Build One Period Models Models should be built with the idea that the first period is representative of the whole time period modeled. To do this every cell in the first period (except totals) links back to the assumptions page and every subsequent period is just a copy>paste of the previous period. For example, if you have seasonal revenues, build a seasonality index on the assumptions page and then in the revenue detail schedule add a row for the seasonality factor. Use Vlookup or Hlookup to properly link to the seasonality on the assumption page. If you only pay taxes quarterly, then use an indicator (0,0,1,0,0,1 etc.) to indicate when the payments are made. To create the indicator, use the MOD command and count the columns [=IF(MOD(COLUMN(),3)=0,1,0)]. (This command counts the columns and puts in a 1 every third column.) There are many other tricks to creating one period models, but it is really just a matter of logic. "This one period logic" also applies if you have multiple economic units--say stores. Each unit would have its own tab for P&L and maybe balance sheet to show individual unit economic contribution. Now if you build the first unit's first period model correctly using the assumptions tab, you can copy>paste the first period model not only across multiple time periods but also to multiple economic units. To clarify, what I just said was you could build a 100 store, 120 period model by just building the model for the first store, first period correctly. (Worst case you might have to enter the store name or number on each of the hundred tabs.)

More tips to follow. Let me know if this is helping you aspiring Excel modelers.

Microsoft's Strategy with Yahoo

The Sunday New York Times had an interesting article on Microsoft's acquisition of Yahoo. In part I found the article interesting because it followed the same logic that I outlined in Microsoft Yahoo Makes Little Sense. The NYT basically said that Microsoft would be better off acquiring an enterprise software company like SAP rather than Yahoo. According to the NYT, approximately 50 percent of Microsoft revenue comes from business customers. Therefore, if a company was going to make acquisitions, they should be in their core business. I faulted Microsoft, in part, because Yahoo's online advertising was not part of the core Microsoft business and required different core competencies to succeed.

The article got me thinking about the benefit to shareholders from acquisitions in the core business. The NYT article had cited Oracle as one example of an acquisitive company that focused on its core competency. Oracle made 37 acquisitions in the period 2005-7. I also looked at Cisco, the network equipment manufacturer, who made 29 acquisitions in the period 2000-2. Basically Cisco acquires new products to compliment its product line through acquisitions. I then considered Intel as a non-software computer-related technology company to compare to Cisco. Interestingly, Intel appears not to have made any significant acquisitions since 2000. I also included Microsoft, whose acquisitions are all over the map. With the exception of Great Plains and Solomon (SME accounting packages), Microsoft  has rarely acquired in the business/enterprise software area.

The return on the stocks (excluding dividends) for the period 2003-7 is shown below. (A pretty Yahoo Finance chart is here.)

Recognizing that the sample is limited, focused acquisition strategies (Cisco and Oracle) where the company is acquired for its product (not the business) generated better returns in the period than Microsoft's wide ranging acquisition strategy. Intel, with its laser focus on its core business, also outperformed Microsoft. The fact that the NASDAQ index outperformed all four companies probably shows that young, fast growing companies generate better returns than the mature company examples I chose. Of course, on a risk adjusted basis Cisco and Oracle look pretty good.

Real Estate Excel Model

It is a very busy week for me with due diligence on two client acquisitions and the blogging muse is not speaking very clearly to me. So I thought I would post on another Excel model. This time I have posted a real estate model. Real estate is not an industry I specialize in, partly because there are so many specialists in the field and also because it is project related rather than an ongoing business.

A few words about the model:

1. It was for a town house project in Central America where the new owners buy their properties outright
2. In real estate projects the use of proceeds is particularly important in order to clarify when the developer is getting their return (after the lenders being preferred)
3. This project has a more involved debt structure than the last model (which was for a startup); there is senior debt and mezzanine debt, which has an interest component and an equity participation component (if it was a business model I would have added a current portion of mezzanine debt to reflect a repayment schedule)
4. I hate to rebuild models for new assumptions so I built this model so you could easily change the construction time to build a town house and the time to sell the unit by just changing one cell (G47 and G48 respectively on the Assumptions tab); mostly I was concerned about a slow down in the market affecting sell through
5. Cash flow is managed by draw downs and repayments on the debt schedule under the construction loan; personally I find it confusing to use the cash flow statement to manage cash

Download the model by clicking this link. Download real_estate_model.xls

Now I need to get back to figuring out VAT taxes in Central America.