SOPHISTICATED FINANCE

PDF to Excel

Have not written anything on Excel in a while despite the continuing popularity of previous posts. For example, Googling "startup Excel model" brings up this post from SF as the first choice.

Today I received an introduction to PDFExcel, a product that converts PDF files to excel format. Very useful tool. Check it out here. 

A collection of all my ever popular Excel posts is here.

Related articles

Another Excel Tip

Accounting and Finance Workshop

Image via Wikipedia

I just presented a two day workshop called "Accounting and Finance for Non-financial Managers" for a large international company. The workshop focuses on theoretical concepts in micro-economics, finance and accounting and the practical application of the concepts to business management and decision making.

The client wanted the workshop to focus on the analysis of new projects using the discounted cash flow methodology. What was particularly interesting is that the company shared their own discounted cash flow template for project analysis. Every CFO makes adjustments to such a template to achieve certain strategic or risk management objectives. When I was CFO of the Indonesian company I added seven percent to the actual weighted average cost of capital discount factor to reflect the uncertainty of the operating environment. The differences between the client template and the theoretical model for discounted cash flow gave me quite a lot of insight into the strategy of the company. I would like to illustrate this point more fully but I would be violating my non-disclosure agreement with the client. One point worth noting is that the next time you do due diligence on a large acquisition ask to see their capital expenditure evaluation template. May give you some real insights into how they think about the business.

If you are interested in refreshing your knowledge of theoretical finance and valuation, Aswath Damodaran, a professor at the Stern School at NYU, has a terrific website here. What could be more fun on this 4th of July weekend than reading about beta and weighted average cost of capital. Enjoy the weekend even if you don't do any finance.

 

More Excel Tips

I have always told my students and workshop attendees that any logical statement they can conceive can be captured in Excel. Therefore, there are almost no limitations in modeling a business in a financial plan. Of course, the issue becomes one's knowledge of Excel.

I recently saw some readers from chandoo.org reading my blog so off I went to see what this blog was about and any reference to Sophisticated Finance. I never found the reference to SF but what I did find was a very rich resource of tips and tricks for Excel. To date this is the most comprehensive and insightful web resource I have found on Excel. It would probably take a whole weekend to fully harvest the information at Chandoo, but it would be well worth the time.

EXCEL Tips--Double Click

It has been awhile since I wrote a post related to Excel. The last Excel post was by guest blogger Rickert Warnelid. Today's post focuses on productivity tips to work faster in Excel. For those of you still addicted to using the mouse, these tips should be helpful.

The tips come from a post here, which appears to be a site devoted to Excel and charting. Of the seven double click tips, I did not know three and I think only one of the tips can be done with keyboard shortcuts. If anyone knows how to do any of these tips using just the keyboard put instructions in the comments.

Image credit

Another Excel Tip

 

A second year MBA student wrote to me recently asking for help in developing his Excel skills for financial modeling. He said he had read the Excel posts on my blog but still could not get comfortable with how to translate financial issues (valuation, DCF, etc) into the logic of Excel. In the course of writing my reply I mentioned that he should look at other people's Excel models.

I have been using Excel or its predecessors for about 25 years, but I always take the opportunity to look at other people's models. Yes--some people's models are poor, but every once in a while I learn a new trick or see a logic presented that I can re-use. The less experienced you are, probably the more you can learn about Excel from just looking at a lot of models.

For the early Excel user one source of Excel models is here at Docstoc. Keep scrolling through the advertising and there are a lot of Excel examples to look at. Another site is Damodaran Online which has a wealth of finance spreadsheets by Professor Damodaran from the Stern Schoool at NYU. Great stuff!

If you are just interested to learn basic Excel, the best tutorial I have found is from the Fuqua Business School at Duke. Available here.

Picture Credit: Microsoft

Repost--Excel Models and Tips

For new readers of this blog I thought I would repost the most popular Excel posts based on Google Search statistics.

• The Purpose of Financial Models
• Startup Excel Model
• Real Estate Model
• Excel Model Tips I, II, III, IV
• Excel Keyboard Shortcuts

Re-format Data Easily

Flowing Data had a very helpful post today on Dabble DB's Magic/Replace. If you work a lot in Excel, have to consolidate data or spreadsheets from multiple sources or let a mild case of OCD dictate the look of your spreadsheets, then you have encountered the problem of data not being formatted correctly or consistently. Magic/Replace may be your solution.

This very intuitive, web-based utility makes it very easy to reformat massive amounts of data in seconds. Changes such as letter case, separators (/-(), name order, phone number style and dates are all very simple because you change one row and all the other data rows immediately conform to the new style.  If it is not obvious how the site works, watch the on-site video.

Project Finance Modeling in Excel

I began my career as a lending officer at Chase Manhattan Bank in New York. In my second job there I was involved in several project financings in Latin America. Project financings typically involve a take-or-pay contract for some natural resource such as minerals or petroleum and require the construction of a huge infrastructure in some very remote part of the third world. For the Cerro Matoso nickel project in Colombia, which I worked on, to successfully commercialize the nickel deposit required the building of a power plant, a three hundred mile railroad spur and a port. Debt financing is typically the largest part of the capital structure and the debt covenants to control cash flow are the most complex that I have seen. In summary, I know enough about project finance to be dangerous.

The remainder of this post is a guest post by Rickard Warnelid, Director of Navigator Project Finance. Navigator, based in Sydney, Australia, specializes in preparing the sophisticated excel models required for project financings. Rickard's post contrasts the modeling requirements of a project financing and a private equity deal and makes very clear the all important point--the model must meet the requirements of the financing source (and not what you think is important). You should now be hearing the drum roll to welcome the first guest writer to Sophisticated Finance--Rickard Warnelid.

"Financial models are developed at different stages of a project/company for a number of different reasons which results in very different structures depending on the purpose. Some people assume that ‘a financial model’ is a universal description of a tool that can be used to solve any problem in relation to the financing situation, but nothing could be less true.

The scoping phase of the development of a financial model is often the hardest part. This is when the modeler’s expertise is tested to make sure that not just a general financial model is constructed, but one that is tailored and optimized for the users’ current needs. To illustrate this I will give you some examples of the difference in a private equity transaction model and a project finance debt model. This will highlight the major differences in focus between these two otherwise closely related areas of finance and clearly show why it is important to make sure what the purpose of the model is before one starts the modeling of the transaction

Timing

Private equity

A private equity model generally has a lower timing resolution than a project finance model, but the biggest difference is generally the modeled operational life. Private equity transactions are often based on a valuation of a company as a going concern and very little focus is spent on anything beyond an assumed ‘exit’ for the PE investor.

Project Finance

Project finance models are often (in transactions including a construction phase) modeled monthly during construction and quarterly during operations. As the construction can extend to 5+ years on larger infrastructure deals the banks will analyze the debt draw down on a monthly basis during this time. The modeled operational life is commonly extended 20-30 years, depending on the type of asset that is being modeled. Resources project generally have a shorter life, as banks are reluctant to bank on reserves extending past 10 years. Flexible timing in a financial model is shown here.

 

Valuations

Private Equity

The valuation section is the core component of a private equity model as the main reason for analysis is to make sure that the right exit multiples are achieved and that the investment is worthwhile. Often a number of different valuation techniques are used to cross-check findings before making an investment decision. Using scenario analysis investors try to work out structures that will protect their investment in downside cases and at the same time does not restrict the upside potential in the investment.

^{_{Project Finance}}

The valuation component (if any) of a project finance model is generally limited to project and equity NPV and IRR. More detailed models would have a comparison of pre-/post-tax effects but in general this section does not get much attention as the model was built primarily to facilitate the bank’s analysis of debt recovery in downside scenarios.

Debt Modeling

Private Equity

 

Private equity models are commonly extremely simplistic in regards to the debt analysis compared to a project finance model. Drawdown dates, refinance terms, margins, covenants and other assumptions are not focused on in much detail.

^{Project Finance}

This is the heart of a project finance transaction and often where the majority of the complexity sits. Features like revolving facilities, borrowing base calculations, cash sweeps, refinances, debt sculpting, lock-ups, debt service reserve accounts (DSRA), look-forward/look-back-DSCRs can make the modeling daunting for inexperienced modelers as it can sometimes be modeled in +1,000 lines of Excel code." An example is here.

About Navigator Project Finance

Founded in 2004, Navigator Project Finance Pty Ltd (Navigator) is the project finance modeling expert. This fast growing, dynamic company based in Sydney , is raising the global benchmark in financial modelling services to the project finance sector. Navigator’s main service is designing and constructing financial models for complex project financings, as well as offering comprehensive training courses throughout the Middle East, Asia and Europe, and conducting independent model reviews of project finance transaction models. Navigator is focused on delivering fast, flexible and rigorously tested project finance services that provide unparalleled transparency and ease of use.

More information and Free Tutorials on financial modeling for project finance are here:"

 

 

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.

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.

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.

Excel Model Critique

A reader of this blog emailed me and asked if I would critique a financial model posted on Guy Kawasaki's blog, How to Change the World. I read this blog regularly and recalled the model of a web-based real estate company--Redfin. The two posts on the financial model are here and here. Click on the following link to download the model Download RedfinFictitiousModel.xls.

Redfin describes itself in the model as follows:

"We are an online real estate brokerage, using technology to lower our customer acquisition costs and to automate paperwork, but we employ licensed agents to offer clients advice and negotiate deal terms. In a typical real estate transaction, our clients earn a $10,000 refund.

We have no marketing budget to speak of, and compete against marketing behemoths like Century 21. Unlike other venture-funded real estate sites, our search site offers direct access to the MLS broker databases for a complete view of inventory, and when we broker a deal, our agents are paid customer satisfaction bonuses, not commissions. Having completed over 1,000 transactions in our first year of service, we have the highest customer satisfaction rate in the industry. "

This model raised capital from a real, live VC so it is obviously an adequate model. What I like about this model is that it was thoughtfully prepared by someone who sees the value in detail. The headcount plan is excellent (Staffing tab) and is a suitable template for any company where headcount is time dependent. The productivity indicators are very well thought out and match the key drivers in the business (Monthly Financials tab-row 59). The expenses are very well developed particularly for a web-based business.

As you review the model, you should be trying to determine what are the growth drivers in this business. With a bit of work, you should realize that there are two growth drivers:

1. New markets
2. Number of agents per market

I would probably put an exhibit on the assumptions page which made it easier to understand the number of agents by month in each market. Ideally the number of agents per market would automatically change if the start date for the market changed. The model appears not to be very forgiving if you change a start date for a market (lots of error messages) which may be annoying to anyone who wants to do sensitivity analysis on the pace of new market openings. I probably would have modeled each market on a separate tab and then consolidated the results. This would have enabled me to develop per market balance sheets and determine the ROI per market. I find the model difficult to use to understand the economic return per market. With this approach of per market tabs the overall headcount and CAPEX of the company would not be as obvious as it is in the Redfin model. I would need to add summary headcount and CAPEX tabs to reach the same level of transparency as the Redfin model.

One of the key assumptions in the model is, in my opinion, somewhat underdeveloped. On row 18 of the Assumptions tab there is the "signed to close ratio". Change the assumption and see the dramatic change in sales and net income. I would have built the model so this ratio could change by year or maybe per month depending on the number of months in the market. This assumption is too critical to be a single cell, fixed for all years in all markets.

The last general comment I would make is that it is very difficult for me to determine the customer acquisition cost. Seems like an important number to understand but I can not determine it easily from the model. Maybe it's there and I just can not find it.

A few more general comments about models:

1. The Redfin model lacks a balance sheet and cash flow statement. Even though the model successfully raised capital, I strongly recommend against omiting the balance sheet and cash flow. There is no transparency into working capital without these two tabs.
2. Look at cell F20 on the Per-Market P&L tab. Personnel expense is a key cost in each market but this cell combines over twenty different variables, which makes it nearly unauditable and not understandable. This complex formula was probably required because all the markets were on the same tab and there was no easy way to break out the details of this formula. Note: build models to create transparency, and when you have twenty variables in one cell, you should know that you need to change the structure of the model.

Everybody builds models differently. Frequently the design of a model is limited by the skills of the preparer. Before opening Excel to build a model, think carefully about what the key growth drivers in the business are and build in the flexibility to make sensitivity analysis on these key drivers easy for the investor. Remember--making things easy for the prospective investor is always a good policy.

Sophisticated Finance Excel

In a temporary period of delusion, I actually thought about starting a second blog--Sophisticated Finance Excel. Hundreds of people have visited this blog through Google searches for "startup model", "excel model", "debt schedule" and "headcount plan". My post Startup Excel Model is the second most popular post ever on this blog. Based on my students, I imagine that a lot of people need practical guidance to build financial models. (Did I mention my workshop this Friday that will cover building financial plans in Excel? ;)

A post on The Extreme Presentation Blog saved me from the foolish idea of starting a second blog. The Extreme Presentation Blog is an excellent blog I read regularly on presentation and design. Their post Presentation Wisdom-Search Engine talks about a new feature from Google--custom search. Custom search allows anybody to create their own search engine that only searches the websites and blogs you include in the searchable sites list. The folks at Extreme Presentation created a custom search engine called Presentation Wisdom that searches only ten very good sites on presentation and design.

I am now pleased to announce the Sophisticated Finance Excel custom search engine. This search engine is devoted to Excel and financial modeling. Today it searches only three blogs. The other two blogs I have mentioned here several times before--Juice Analytics and Microsoft Excel. I can add additional blogs and websites to the searchable site list. If you have a good site for Excel or financial modeling send me an email and I will consider adding it. On the home page for Sophisticated Finance Excel Google provides the code to embed this search engine in other blogs. A directory of Google custom search engines is here. The number of custom search engines is small today but it will grow quickly.

Yesterday I discussed ad widgets. Today I have a branded search engine. Next I'll be thinking about starting the Sophisticated Finance social network on Ning.com. (Hmmm, maybe that delusional state is returning.) Quite amazing the number of branding opportunities available through Web 2.0.

Note: Sophisticated Finance Excel has Google ads which I can not eliminate without paying Google an annual fee or claiming to be a university or government agency. My apologies for helping Google to cover the very high marginal cost of hosting my custom search engine, which must be about two cents per month. I do not endorse any company or person that may be cited in an ad.

Financial Model Excel Workshop

The Pino Global Entrepreneurship Center at FIU hosts a series of workshops twice a year for entrepreneurs and early stage companies. The workshops are open to anybody looking to hone their skills in entrepreneurship and the admission fee goes to the Pino Center. Further information is available here or one can call Lauren Suarez at 305-348-7625.

This Friday, March 14th, I will be presenting an eight hour workshop on building financial plans for business plans. I am going to change the format this time and split the program. The morning session will focus on using a well defined business model as the foundation for a financial plan. The afternoon session involves a case study where we will build the financial plan in Excel.

For more understanding of a business model, please review this post.

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.

Startup Excel Model

About half of the readers of this blog originate from Google searches. The most popular searches involve Excel, financial models for startups, sources and uses, CAPEX and headcount plans. In an effort to fill the apparent void on the Internet related to complete financial models built with Excel, I have posted a  model which includes examples for all of the frequent search terms that bring readers here (cash flow, CAPEX, sources and uses, headcount, etc.) The model is complete and a good example of what to send to a venture capital or private equity firm or a hedge fund to raise any type of financing.

A few words about this model:

1. It is a real model that I built for a client (prospective clients you can see my work)
2. It is not a template for you to use (I suppose if you changed all the assumptions it's usable, but...), but the component parts are recommended for capital raising
3. The model is for a "triple play" telcom business (cable, voice and data) so the cost of sales part of the model is over 100 assumptions and the CAPEX is fairly involved including physical constraints; this "complexity" is designed to show industry knowledge to financing sources; for more on the purposes of financial models see this post
4. The model includes the following tabs:
   • Sources and Uses
   • Assumptions (which also includes the Financial Summary--P&L and balance sheet information)
   • Financial Statements (which includes the P&L, balance sheet, revenue detail, COS detail, CAPEX row 178, and debt schedule row 251)
   • Cash Flow Statement
   • OPEX (operating expense detail and the headcount plan)
5. The model makes extensive use of named fields (after you open the model press F5); named fields make it easier to understand formulas because you use a name instead of a cell reference in the formula
6. In addition to being a startup, the strategy called for a  series of acquisitions (a rollup) which are modeled
7. The model uses a customer acquisition cost driver for the organic growth of subscribers

A few things that would have changed the model design include:

1. If I had a complex debt structure (combination of a line of credit, term loan and mezzanine debt) I would have used a separate tab for the "debt schedule" to make it easier to find
2. If I had an operating company (instead of a startup) I would have included historical information for at least three years in the "financial summary" and probably have given it a separate tab; if you have a good operating history of revenue growth or a turnaround I would include five years of historical data
3. If I had historical information on each acquisition, I would have forecasted/modeled each acquisition on a separate named tab (with a separate P&L and a balance sheet) and then consolidated them; such a presentation would have made it easier to understand the economics of each business unit and to evaluate the reasonableness of the purchase price
4. If the headcount additions were based on time instead of capacity requirements (as shown in the model) I would have used date triggers to drive headcount additions

Click the following link to download a copy of the model. Download sf_blog_model.xls
The auditing tool bar in Excel may aid in understanding. Click View>Toolbars>Formula Auditing

Please remember that the complexity of the model serves a purpose--to demonstrate knowledge of a complex industry. Most models can be successfully built with fewer assumptions about revenue and cost of sales. Some additional thoughts on detail in models are here.

Choosing Charts in Excel

How many times have you seen people choose the wrong type of chart to present their data? The folks at Juice Analytics have developed a "chart chooser" and it is available here. Juice Analytics is a consulting firm that provides heavy duty data analysis and their blog is an excellent source of useful tips on Excel and data presentation.

Chart Chooser begins by asking you to pick the purpose of your data and offers six alternatives:

1. Comparison
2. Distribution
3. Composition
4. Trend
5. Relationship
6. Table

Based on your choice, the app selects the appropriate charts from a universe of seventeen chart types.  You then pick the chart form you want to use and can download a template (Excel or Powerpoint) to create that chart using your own data. The templates are particularly useful if you do not know how to create bubble charts in Excel or scatterplot charts in Powerpoint.

Now that Juice Analytics has taken the guess work out of chart selection, maybe those Powerpoints will not be so confusing.

I found Chart Chooser on the Extreme Presentation blog, an excellent source of advice on presentations.

How Entrepreneurs Think

Yesterday's post on Better Business Plans prompted a comment from Matt Winn, the VC I referenced who blogs at Punctuative. Matt said:

"Glad I could offer some blog fodder and hopeful that as more of us cover these topics, quality improves. Presentation SUBSTANCE matters a great deal as indication of how an entrepreneur thinks." (my emphasis)

While I have mentioned before that financial models and business plans demonstrate how an entrepreneur thinks about his business, I think this point deserves further clarification. When an investor evaluates management they look for four key things in addition to trustworthiness:

1. passion to succeed (and overcome obstacles) and strategic vision
2. personal compatibility (can I work with this CEO/founder) and judgment
3. analytical skills to properly understand the business and its performance metrics
4. industry knowledge

Judgement, analytical skills and industry knowledge combined probably go a long way toward defining "how an entrepreneur thinks". (May need to add a dash of strategic thinking.)

Business plans and investor presentations are the first opportunity to demonstrate analytical skills and industry knowledge to an investor. However, most entrepreneurs present what they think is just enough to get to the next step with the investor rather than full blown, analytical thinking on industry, distribution alternatives and competition. The lack of this detail is part of the reason VCs have to spend so much time to confirm the size of the market opportunity. I also think many entrepreneurs fear that they will have nothing to say at subsequent meetings if they present a complete analysis at the first meeting or in the first document. Of course, the investor having to drag information out of you may raise questions about how you think about the industry, especially when the critical risk factor only surfaces two hours into the industry discussion.

Many, many entrepreneurs also fail to properly use their financial models to demonstrate analytical skills and industry knowledge. While not all investors will read a business plan, it is rare for a financial model to go unread. The financial model is an excellent place to make clear the business model. (For the distinction between a financial model and a business model see this post.) By properly developing the business model one makes clear the growth driver(s) and the pricing and sales/distribution strategy. The key assumptions that build up to provide this analytical clarity typically also use values which can be confirmed against industry norms (further demonstrating industry knowledge).

In my definition of business model I also included capital expenditure requirements and headcount plans. Capital expenditure requirements are an excellent way to demonstrate industry knowledge, especially in telecom, Web 2.0, mining (a recent prospect) and SAAS, to name a few industries. For example, if you are presumably going to scale your Web 2.0 business, how are you going to manage the increasing need for servers and database storage. A complete CAPEX plan would make this obvious and demonstrate both analytical thinking and industry knowledge. Detailing a headcount plan is one of the best ways to demonstrate how you think about the business and your logic. Putting in 5 "C" level executives or 10 sales people the day after funding says quite different things about how you think about growth strategy and capital efficiency.

If you follow this approach to your presentation or financial model for an investor, you will give Matt and his VC industry cohorts a lot of insight into how you think about your business. And, as I said in yesterday's post, give the investors the information they want.