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.

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.

Excel Keyboard Shortcuts

If you have become an accomplished user of Excel for financial modeling, then long ago you probably abandoned using the mouse and do everything using key board shortcuts. An example of a keyboard shortcut is "Control-C" to copy a cell or "ALT-I" to open the "Insert" menu choices. Keyboard shortcuts are much faster than using the mouse; for those without good hand to eye coordination (like me) shortcuts are more accurate.

Yesterday I found the most comprehensive listing of Excel keyboard shortcuts at Microsoft Excel. This blog is managed by the Excel programming team at Microsoft in Redmond. Many of these keyboard shortcuts also work for other Office applications such as Word and Powerpoint. Click on the following link to download the file of keyboard shortcuts.Download Charlie_Keyboard_Shortcut_Book.xls

Modeling in Excel is not a fun task for many because it is perceived as very time consuming. Give up your mouse, learn keyboard shortcuts and it will go much faster.

The Purpose of Financial Models

I found a very good blog this weekend, Parallax: Calculating Technology's Future (definition of parallax). Mostly the blog covers trends in  software and Web 2.0 at the technical level and I had to refer to Wikipedia.com for explanations ocassionally.  In a post on financial models the writer makes the point that when he reviews a new business plan he frequently goes first to the financial model because it provides an easier way to understand the business model. The distinctions between business models, financial models and business plans are in a previous post of mine.

Financial models serve five purposes:

1. to demonstrate the size of the market opportunity
2. to explain the business model
3. to show the path to profitability
4. to quantify the investment requirement
5. to facilitate valuation of the business

From the hundreds and hundreds of financial models I have reviewed over the years I would say that most people build their models to answer question 3 or 5. The second most frequent purpose is to quantify the investment requirement (4). Of course, sophisticated early stage investors and venture capital firms are principally interested in the answers to questions 1 and 2--how big is the market and what is the business model. The business model provides the answer to the all important question--how will you grow the business.

When you build financial models the first question you should ask is "what question does the recipient of the model  want answered". The answer may change  but almost all investors want to understand the business model. Also, remember that the business model in the financial plan gives you a simple way to demonstrate again your knowledge of the industry (another important point for VCs.) Present the assumptions for the business model in a clear, logical, detailed way and you will answer a lot of the questions the recipient has and you may just accelerate the pace of your capital raise. A VC once told me that a financial model we submitted saved him two weeks of due diligence time. Never hurts to be helpful and answer the questions the recipient wants to know.

I found the blog Parallax through a post by Brad Feld.

Rants on Excel Models-II

I have refrained from ranting on Excel models since July when I posted the first rant. This new rant I attribute to preparation for my upcoming workshop on October 26 at FIU--Preparing Financial Models for Business Plans. The example  below is representative of an attempt to develop revenue detail, albeit with quite a few opportunities for improvement. It may be easier to follow along if you download the file Download september_rants.xls

The most obvious shortcoming is that the time series (January-December) is presented vertically. I have never seen a case where time series presented vertically made more sense than horizontally. First of all, every financial statement prepared by a CPA shows time series horizontally so we have been trained to understand time periods horizontally.  Secondly, the logic of the assumptions (Submitted Applications, Approved Applications, etc.) presented  horizontally is much harder to comprehend (at least for me). Lastly, linking the data on this page to another worksheet would be very time consuming (involving a manual link to each cell) because the time series data is not presented horizontally. (Hint: if you have to manually link to every cell in a series you should probably transpose the data.)

Second, this spreadsheet is designed to show the logic for revenue but no where does it indicate the revenue per approved application (one of the key assumptions). Revenue per approved application is a hard coded assumption in each row of the fourth column. Consequently, this key assumption is hidden and the model needs to be re-keyed every time you want to do sensitivity analysis on revenue per approved application.

Third, Submitted Applications drives the whole model but the values are all hard coded, basically giving you no insight into how to determine what affects the number of Submitted Applications per month. When I queried the preparer on what affected the number of applications she told me it was based on the number of offices and the number of application processors per office. This is very valuable information to better understand the business logic, but it was omitted in this model. It turns out that the number of offices is the key revenue driver that makes clear the key strategic issues in the business--how many offices can be opened and how many offices open per year. Never would have known this from the model.

The last major issue is that this model mixes revenue assumptions with balance sheet detail, making it much harder to understand the logic of the revenue detail. Separate balance sheet detail from revenue detail/logic and your models will be much more self-explanatory.

I would present the revenue detail as shown below. The file is here Download rants_1.xls

With the clarity of this presentation the revenue drivers for the business are self-explanatory. If this detail were part of a complete model I would have put each of the key assumptions in blue on a separate page with all the key assumptions for the model and linked to that page. If these assumptions had changed over time I would have presented them on the assumptions page by period and then used the VLOOKUP command to insert the assumed value in the proper period in the revenue detail. 

(Note: the Total Locations Available already exist in this particular business, are therefore a matter of fact and require no additional supporting assumptions.)

Rants on Excel Models

I have just spent the last three days re-building a very detailed client model. (Yes-I know that’s what I get paid to do.) When I first started modeling in the 1980s, people were amazed just to receive a spreadsheet. In the 1990s you had to talk people through a spreadsheet for them to understand it. Today the common expectation is that a spreadsheet or model is self-explanatory. Today, if your model is not self-explanatory, it reflects badly on the company (and the preparer).

My client’s model was not self-explanatory for several reasons:

1. There was no logic to the organization of the detail. Revenue detail was not all grouped together. For example, VAT (foreign companies have “value added tax”) calculations were mixed in with revenue and cost of sales detail even though they only had a balance sheet effect.

2. There was no balance sheet-- only a P&L and cash flow statement. This omission of the balance sheet happens frequently with models I get that are prepared by Europeans. I must assume that debt leverage (by any measure) and working capital are not concepts used frequently in Europe. (It can not be the case but their models don't make it easy to calculate!!)

3. There were color coded cells to indicate a change in an assumption instead of using date-based or period-based “if” statements. Takes quite awhile to determine why changing the assumption has no effect on the model because the timing of the assumption is hard coded. (Hint: whenever you see colored cells used anywhere in a spreadsheet except for title rows, you can safely assume that the model is not self-explanatory.)

A few tips on building better spreadsheets and models:

1. Build the spreadsheet as a stand alone document that does not need explanatory comments (or other spreadsheets that were not attached)

2. Group detail logically together. Put all the revenue detail in one section or tab. Then do the same thing for cost of sales. To improve understanding, calculate gross margins by product line or line of business. In models with multiple products, provide grand totals for revenue, cost of sales and gross margin. Going to a revenue cell on the summary P&L and finding links to 10 cells does not aid comprehension. (Hint: on the summary P&L link back to a grand total.)

3. Learn to use VLOOKUP, HLOOKUP and “name” cells instead of using "links". It makes makes it much easier to understand the model (and provides a smaller model size which passes through spam filters more easily).

4. Delete cells with values that are never used in the model. Maybe you were going to use that assumption and then changed your mind. That’s OK, but delete those cells before you send out the model. If you re-use the model, put in an on-off switch (Hint: multiply by 0 or 1) for assumptions you use but not for all cases.

5. Do not hard code numbers unless they are assumptions. Every number should be a formula or linked to an assumption. Sensitivity analysis and "what if" analysis don't work well when there are hard coded numbers outside of the assumptions.

Enough ranting. A good site for improving Excel skills is here courtesy of the Fuqua School of Business at Duke University.