One: Introduction Business Plan Pro Financial Analysis Business Plan Pro® is built around a very powerful financial forecasting tool: a set of linked financial tables. All you do is type your assumptions in the green cells, avoid the black cells, and you don’t have to program anything. Sales, personnel, income, cash, balance, ratios, and other tables are linked together already, so a change in one flows logically to all the others. This financial tool has been developed for more than 18 years now, it has been used with hundreds of thousands of successful business plans, and it works. It is financially and mathematically correct. However, like all business models, any projection of the future is, at best, a very good educated guess. It depends on assumptions. It is a general tool that can be applied to millions of specific cases. It can always be improved, tailored, and customized. The objective of this white paper is to take that basic model and make it better, so it works exactly as you’d like, for your business. Business Plan Pro, in fact gives you extensive spreadsheet-like programmability for your financial tables. Your financials, although they don’t need to be programmed, are built into a very powerful spreadsheet tool that includes a complete MicrosoftExcel™-compatible spreadsheet. It supports financial functions that calculate your loan payments, depreciation, and investment analysis. All of this power is available even while working within the financial analysis. About This White Paper This white paper is about the spreadsheets in Business Plan Pro. It covers some of the critical assumptions and complex formulas so you can understand how they work. Receivables, for example, is based on a complex formula and is also critical to cash flow. Payables and inventory are critical and relatively complex as well. This white paper will explain how these formulas work, and why; it will also cover how you might change them, when you ought to, and what you might change them to. It’s also about spreadsheet formulas you can use, and how to use them. It covers many common formulas people use to calculate cost of sales, for example, or sales commissions, or loan repayments. It shows you how to add common formulas and how to create your own. It’s about how to use built-in functions to supercharge your analysis. The best way to learn this is by doing it. Therefore, this white paper takes you through a series of examples. It starts with simple examples that offer practical tips. It also includes details of more complex formulas, and practical explanations of formulas you might want to add or change. Using the Tool Well Use Business Plan Pro for what it does well. There is a solid, tested, robust financial model at its core, linking all of the key financial views into a complete system. Change any one element and all the others change automatically. Use it to develop your business financials. Use its “what-if” facilities. Work with your business plan financials to understand the key assumptions and think about how they might change. What happens if you sell more or less than planned, and what if you spend more than planned? What if utilities double, or rent increases? You have to at least consider these possibilities, and use your business plan to anticipate the implications. That’s a lot of what Business Plan Pro is about. Remember, however, that the software is a tool. It is only as good as the assumptions you put into it. Furthermore, it depends on mathematical modeling to project some critical elements-receivables, for example--into the future-based on assumptions. These projections are just projections, and the math behind them is complex and elegant, but is still subject to some important assumptions. This white paper will point out some of those assumptions; it will show you in detail how to work with them, and how to change them when you think you should. For example: • For the first three months of the plan, the formula that projects receivables depends on the assumption that sales are steady, that the present month’s sales is a good estimate of previous months. For the first 12 months of the plan, the inventory projection assumes each month’s cost of goods sold is 1/12 of the total year’s cost of goods sold. Taxes are simple math, so unless you make adjustments a steady loss situation could overestimate your cash by assuming a negative tax. Depreciation depends on simple input, not formulas. Loan repayment depends on simple input, not formulas. • • • • Within this white paper we’ll be looking at these points and showing you how to adjust and perfect your specific plan, using the knowledge of Business Plan Pro’s programmability. Planning with Computers Computers don’t think, they just do the numbers. Computers are very dumb contraptions that can be downright dangerous when you expect them to think for you. They are absurdly literal, and, worse still, literal in their own very narrowly defined language. Tell a computer to tell you that 2+2=5 and it will swear that 2+2=5 if you want. Use your financial projections but don’t believe them-especially when dealing with computers and software. Because there is something magical about neatly ordered rows and columns of numbers shining at you through a computer screen or marching from the printer, you may forget how meaningless they really are. So use the dumb thing for what it does well. You do the thinking, let the computer do the numbers, and the division of labor will work wonders. Remember the origins of the spreadsheet, back with the pencils and green lined paper, and think in those terms; you’re still in that realm, now you’ve got a team of tireless workers to change your numbers instantly. Now that’s power. The best analyst uses human judgment to guide the number crunching. If you accept the great split between rational, analytic thinking, and intuitive, creative thinking, then you probably realize the best work requires a little of both. Use your head to guide the analysis. How do you get the right combination? The simplest way is by doing the numbers running the spreadsheet models. Change factors repeatedly. Each new version is another go at understanding a problem, each is another possibility to be considered. The world is far too complex for simple answers. Solving problems is akin to recording stereo albums: the artist sings multiple tracks into a single tape, then magnifies and multiplies the voice and voice effects. The more angles considered, the better the understanding of the final product and its alternatives. Look at your business plan at many different volumes, at many different prices, at many different kinds and levels of expense, and the set of numbers you finally accept will be a better representation of the full range of possibility. There is an irony here. The best market forecasters use a lot of computer models, but they don’t believe them. Output from different models stimulates thinking about alternatives, causes and effects, and different outcomes, but output is not to be believed, just considered. Planning vs. Accounting There is a clear distinction between planning and accounting. Planning begins today and goes forward into the future. Accounting ends today and goes backward into the past. Planning is for making decisions, setting priorities, and management purposes. Accounting is also for information and management, of course, but there are legal obligations related to taxes. Accounting must necessarily go very deep into detail. Planning requires a balance between detail and concept, because there are times when too much detail is not productive. Many people fail to understand the distinction and put disproportionate attention, as they develop a plan, into accounting details. For example, tax reporting and proper accounting requires detailed lists of assets and depreciation, where for planning purposes a good estimate is more efficient. To understand this difference, consider the relative proportion of uncertainty in a forward-looking estimate of depreciation vs. a sales forecast. The AMT computer store, one of our Business Plan Pro sample plans, projects depreciation of approximately $13,000 per year. It expects sales to rise from $5 million to $9 million in three years. AMT could develop a plan that lists future assets and purchases and uses depreciation functions to depreciate each future asset according to accepted formulas. Doing that would probably reduce the uncertainty built into the depreciation estimate, but how much uncertainty would be reduced, for how much effort. A 50% variation in the projected depreciation, either way, comes to less than $10,000, while a 10% variation in the sales forecast in the last year is worth $900,000. A good business plan process maintains proportion between effort and value. Accounting needs detailed depreciation in this company after the fact; but the business plan, looking three years ahead, doesn’t. About Business Numbers This is the business age of numbers. You can’t get a new company financed without a business plan built on believable numbers. You can’t get through your company’s capital budgeting process (that is, you can’t get your company to invest in your idea) without a business plan, usually with a discounted cash-flow analysis. Business Plan Pro is intended to help you meet the numbers demands without getting bogged down in the mechanics. Business Plan Pro will help you generate more numbers: linked financial statements, practical forecasts, milestones with budgets, and more plans. You can do the whole analysis, from start-up to business ratios, by taking the tables one at a time and working in your assumptions. Basics of Business Analysis Business analysis is supposed to make good business sense. It does, when it is done right, But business numbers can be frighteningly fictional, whether they are used for creative accounting (“where did all the profits go?”) of forecasting. The key is to recognize the use of business numerical analysis, which provides the framework for decision making. The numbers help us to understand what is going on; they are not of any real use in and of themselves. Even the accounting statement is an enormous semifictional pageant, revolving around how much money is in the bank, how much we owe, how much they owe us, and how much we’ve spent. Business analysis, by its very definition, has to have some practical use. Time is money, and it has to be worth the effort. The key phrase here is “framework for thinking.” Effective business planning is not in the numbers, it’s in the decisions and the implementation; the numbers provide the key framework for understanding the alternatives, the background, the cause and effect, and the decision itself. Two: Simple Examples Let’s go through some simple examples of Business Plan Pro spreadsheet formulas. To follow along with the examples: 1. First open your Business Plan Pro and 2. Open the AMT Computers sample plan. 3. Then go to your Tables menu, choose the Table Preferences command. 4. Set your tables to show row headings and column headings. Referring to Cells and Ranges As we work with spreadsheet formulas in Business Plan Pro, we’ll refer to cells the way most spreadsheet software does, with their row and column notation. The cell in the fourth column of the fifth row, for example, is cell D5. We refer to blocks of cells by showing the upper left first and the bottom right last, separated by a colon. So the range that goes from B5 to N10 will be written as B5:N10. Automatic Price Formula for Sales Forecast Spreadsheets are based on matrix, the mathematical name for a layout that has rows and columns. There is a fair body of mathematical literature, plus some tricks in probability and statistics that are based on a matrix layout. Rows are always horizontal, columns vertical. In formal uses you can refer to any single cell in the matrix by listing its column letter first and its row number second. In Figure 1 you see a view of the Sales Forecast table of the AMT Computers sample plan. Cell D23 is selected. The formula for cell D23 shows in the Edit bar. That formula is: =C23 Figure 1: Sales Forecast Formula If you just copy that formula into the rest of the 12 monthly columns, then you have a nice improvement on the program. Whenever you change the assumption in the first month, that change automatically ripples through the whole system. With this example, you also see a very simple first view of basic spreadsheet programming. The formula in D23 makes that cell equal whatever is in C23. Some additional tips on this: 1. As you copy the formula in D23 and paste it in the following cells to its right along the row, you can’t paste into cell O23. That would produce a “locked cell” error. O23 is locked, and shows in black font, because it doesn’t accept data entry, it calculates average price for the year by dividing total revenue by total units sold. 2. You might also go to cell P23 and make its formula “=N23” so that it picks up the price from the last month of the first year. 3. Select D23. Hold down the shift key while D23 is selected and then press the right arrow key a few times. That automatically selects a range of cells to the right along the row. Then use the Copy Right command in the Edit menu to automatically copy D23 to the right along the same row. If your selection includes O23, which is locked, you’ll get the locked cell error message. Locked Cells vs. Data Cells As you probably know, Business Plan Pro prevents accidental errors by locking important formula cells. As you look at your color monitor, all black cells are locked, and all green cells are unlocked. You can type into the green cells, typing data, formulas, or text. However, you can’t type into the black cells. When you try, an error message tells you that these cells can’t be changed. This locking, annoying as it may be at times, is done on purpose and for your benefit. In Figure 2, for example, the row that sums the sales rows into a final total has a formula that shouldn’t change. Nobody wants a row labeled “Total sales” to contain anything except the sum of the sales rows above it. In Figure 3, the row named “Sales” is locked because it contains the value for total sales from the appropriate row in the sales forecast. If the cell weren’t locked, somebody who didn’t know the software might go first to the Profit and Loss table, before seeing the Sales Forecast table, and logically type sales estimates straight into the Profit and Loss. After that, the Sales Forecast would no longer be linked into the Profit and Loss table, so changes in sales wouldn’t automatically reflect in other worksheets. Figure 2: Formula for Cost of Sales as Percent Many row labels are locked for the same reason. The row named “Gross Margin” in Figure 3 contains the gross margin, defined as sales less cost of sales. Since the formula can’t be changed, the label shouldn’t be changed either. Nobody would want gross margin to be accidentally renamed to Net Margin or Profit Margin or Contribution Margin or anything else. Make Cost of Sales a Percent of Sales Next, we create a simple formula to make the direct cost of an item a percentage of the price of that item. In Figure 2 you see another view of the modified AMT Computers Sales Forecast. This time cell C38 is selected and its formula, which shows in the edit bar, is: =$B38*C23 This formula takes whatever the value in cell B23 and multiplies that by the value in C23. So the value in B38 is .8 (shown as 80%), the value in C23 is $2,000, and the value in C38 is $1,600, which is 80% of $2,000. You can copy that formula to the right across the other monthly cells, not to the first year summary cell that is locked in column O, and then to the other annual cells in columns P and on. That will make all of these cells automatically equal to whatever value is in B38 multiplied times the price value in the corresponding column of row 23. Relative vs. Absolute Values The $ in the formula “=$B38*C23” makes the reference to B38 an absolute reference to column B on the spreadsheet. If you copy that cell and paste it into D38, the formula in that cell will be “=$B38*D23.” The reference to C23 moved one column to the right, but the reference to column B in $B38 didn’t change. If we copied the formula in C38 and pasted it into C39, the formula would become “=$B39*C24.” In that case, the B column remains absolute because of the $, but the row number adjusts to the change in location. If we had made the formula “=$B$38*C23,” then when we copied to C39, both row and column number would have remained absolute, making the formula “=$B$38*C24.” Make One Cell a Percent of Another You can also use spreadsheet formulas to calculate any one cell using any mathematical formula, including the value in any other cell. Figure 3 shows an example. Cell C15 is selected. Its formula is “=.5*C14.” The value in C14 is 5,000, so the value shown in C15 is 2,500, exactly .5*5000. Figure 3: One Row is a Percent of Another Make a Cell a Percent of Sales “Sales” is one of several dozen built-in variables. Figure 4 shows how you can use this built-in English-language variable to create a formula to make all the cells in a given row depend on sales. They are calculated as a percent of sales. The example shows a Profit and Loss table with cell C6 selected. The formula for C6 calculates that cell by multiplying the variable “sales” by the value in B6. As with a previous example, the reference to B6 uses the $B6 to make sure that the reference to column B is absolute, and the reference to row 6 is relative. That formula was written into C6 first, then copied to cells D6:N6 and O6:R6. Figure 4: Formula Uses Percent of Sales Named Ranges as Variables Just as the formula in Figure 4 uses “sales” as a named range, you can use dozens of other named ranges as variables. These include sales, earnings, inventory, gross_margin, and more than 100 other range names listed at the end of this white paper. Range names automatically adjust for different columns. The formula “=sales” in column G will show the value of sales in column G. That same formula is column D will show the value of sales in column D. Range names will produce errors when used improperly. For example, the range named “sales” starts in column C and spreads across multiple columns. The formula “=sales” in column B produces a #VALUE error because there is no valid value for that column. However, a range name that occupies a single cell can be used as a variable anywhere in the financials. For example, since the range “starting_year” occupies a single cell, you can use the formula “=starting_year” anywhere in the financials without getting a #VALUE error. Three: Linking Tables Modular Financial Analysis Business Plan Pro builds its financial analysis on logical modules, separate tables all linked together. Figure 5 offers a visual explanation. It shows how these relationships work in general. Figure 5: Modular, Linked, Tables The key is that changes in any one of these tables will automatically reflect properly in all the others. • Your Sales Forecast table should show sales and cost of sales. These same numbers are the ones you use in the Profit and Loss table. As with sales, you should normally have a separate Personnel Plan table, but the numbers showing in that table should be the same numbers that show up for personnel costs in your Profit and Loss table. Your Profit and Loss table should show the same numbers as your Sales Forecast and Personnel Plan tables in the proper areas. It should also show interest expenses as a logical reflection of interest rates and balances of debt. Your Cash Flow table reflects your Profit and Loss table, plus changes in balance sheet items and non-cash expenses, such as depreciation, which are found in the Profit and Loss table. For example, when you borrow money, it doesn't affect your profit or loss (except for interest expenses later on), but it makes a huge difference to your checking account balance. • • • • • The Balance Sheet table has to reflect both the Profit and Loss and the Cash Flow tables. Your business ratios should calculate automatically, based on the numbers in the Sales Forecast, Profit and Loss, Personnel Plan, and Cash Flow tables, as well as the Balance Sheet. Working with Linked Tables Figure 6 shows how the links work for the Profit and Loss table in a sample business plan. The view shows the top of the table, not the whole table. The actual links showing in the illustration are coming in from both the Sales Forecast and the Personnel Plan tables. Figure 6: Working with Linked Tables • At the top of the table you see Sales and Cost of Sales. These rows come from the summary rows in the Sales Forecast. The cells are locked on purpose; they can’t be changed. The rows containing the linked information are locked. Users can’t change them. Otherwise, users might not understand linking and type over the linking formulas. • • The formulas in linked cells won’t always show easily that they are linked cells. In the example in Figure 6, the formula for sales in cell C3, which is shown in the edit bar, uses an IF function and named ranges. It isn’t immediately obvious that the named ranges come from another worksheet, one named Sales. That’s clear in documentation and in help files, but not in the formula itself. (TTKK) Building Your Own Links You can use the spreadsheet power to build your own links to fit your planning needs. Here are some examples: Simple Link from Sales to Profit and Loss Assume AMT plans on paying royalties equal to 10% of sales of software. We can build a formula that does this automatically. That formula is in Figure 7. We take a row in Profit and Loss named “Royalties” and link it to the row in the Sales Forecast that contains software cells. Figure 7: Simple Link from Sales to Profit and Loss The formula for cell C6 in the illustration is “=Sales Forecast!C25*0.1.” You could either type that formula into your worksheet, or you could follow these steps: 1. Select cell C6 in the Profit and Loss table. This is going to be the target cell for the link formula. 2. Use the Edit Row Label command in the Format menu to change its row label from “Other” to “Royalties.” 3. Type the equals sign (=, just the “=” key, no quotations or parentheses) into the cell. 4. Go to the Table menu and choose the Sales Forecast table. 5. Find the cell you want to be the source cell, in this case cell C25, and click to select it. Your formula edit bar should at this point show the formula “=Sales Forecast!C25.” 6. At this point you could press the key and your formula would copy the contents of cell C25 in the Sales Forecast into cell C6 in Profit and Loss. However, in this case we want to make royalties equal to only 10% of sales of software, so we type into the formula “*.1.” 7. Press . Your formula is now correct, as “=Sales Forecast!C25*0.1.” You are probably looking at Profit and Loss at this point, with cell C6 selected. 8. Even if you are looking at Profit and Loss, go back to your Table menu and choose Profit and Loss. This puts your Business Plan Pro back into the right table, the same one you’re on. 9. To put this same link into the whole row, copy cell C6 and paste its formula into cells D6:N6, and P6:S6. Linking from User-Defined into Sales Forecast Business Plan Pro uses the User-Defined tables to give you more spreadsheet power and more flexibility with your business plan. In Figure 8 you see how you can link a cell in your Sales Forecast to a cell in the User-Defined table. Figure 8: Linking from User-Defined into Sales Here’s how it is in detail: 1. Select the target cell in the Sales Forecast. In the example, cell P15 is selected. That cell contains the projection for unit sales of systems in 2002. 2. Type the equals sign, “=” into the cell (without quotation marks, just press the key that has = on it). 3. Use the Table menu to go to the User-Defined table that contains the source cell. 4. Select the source cell by clicking on it. In the example, that’s cell C40. The formula becomes “=User 1!C40.” 5. Press . 6. Even though you are seeing the Sales Forecast table, use the Table menu to return the system pointer to the Sales Forecast table. An IF function in Profit and Loss linked to a Sales Row in Sales Figure 9 shows an expense row from Profit and Loss linked to a sales row, with a simple IF function to make the link more effective. What the formula does is check whether training sales (in cell C33 of the Sales Forecast in Figure 2) are greater than $10,000. If they are, then the seminar expense is $5,000. If not, then the seminar expense is $2,000. Figure 9: The IF Function in a Linking Formula Here’s how you would create that formula link, in your own plan, step-by-step and in detail: 1. Select the target cell. In this case it is cell C21 in the Profit and Loss. 2. Type the equals sign, “=IF(” into the cell (without the quotation marks). 3. Go to the Table menu and choose the Sales Forecast table. 4. Find the source cell in the Sales Forecast table and click on it to select it. In this case that cell is C33, the first month of estimated sales of training in the AMT sample plan. At this point the edit bar shows the formula “=IF(Sales Forecast!C33” 5. Type, into the edit bar, the rest of the formula as shown in the example, so it ends up “>10000,2000,5000)” and then press . 6. Use the Table menu to select the Profit and Loss again-even though it’s showing--to set the system pointer back to profit and loss. 7. Copy the formula in C21 to the rest of unprotected cells in the row, D21:N21 and P21:R21. Four: Working with User-Defined Tables Business Plan Pro’s User-Defined tables give you the option of developing your own complete tables to add to your business plan. Pasting to User-Defined Table from an Excel Worksheet You can copy a range from an Excel worksheet and paste it into a User-Defined table. Figure 10 shows an example of the Excel worksheet and the Business Plan Pro User-Defined table. If you want to preserve the formulas within the worksheet, it is a two-step process. Figure 10: Pasting to User-Defined Tables Here are the details: 7. In Excel (the example was developed with Microsoft Excel 2000), select and copy the source area you want to use within Business Plan Pro. 8. Select Business Plan Pro, choose the User-Defined table in the Table menu. Select the cell you want to have as the upper left cell in the target range. 9. Use Paste Special in the Edit menu, the Paste All command. Your Excel spreadsheet will now be pasted into your Business Plan Pro User-Defined table. Tips and Traps with Pasting from Excel to Business Plan Pro 1. A simple copy and paste from Excel to Business Plan Pro copies values only, not formats or formulas. To copy and paste spreadsheet information completely, including both formulas and formats, you need to use the Paste Special command in Business Plan Pro. 2. If you paste a formula from one worksheet containing an erroneous reference, you’ll get a #REF error in the target worksheet. That’s true when using Excel alone, and the same holds true when you paste from Excel into Business Plan Pro. For example: • If you copy the formula “=C9” from cell C10 in Excel into cell C1 in Business Plan Pro (or Excel), you get a #REF error. Business Plan Pro wants to reinterpret the formula to refer to the relative row number: nine is one less than ten, but when you paste into row one you don’t have the possibility of a lower row number. Copy a formula “=XYZ” (referring to a range name not already in Business Plan Pro) from Excel and paste it into Business Plan Pro. Business Plan Pro will behave like Excel does in this instance. It creates a new range named XYZ located in a position relative to the target cell. Copy the formula “=sales” from an Excel spreadsheet and paste it into Business Plan Pro. Business Plan Pro will create a new range named “=sales_2” and will give the target cell the formula “=sales_2”. To make the cell refer to the Business Plan Pro range named “sales,” you have to edit the formula manually. • • Create a Complete User-Defined Table from Scratch For this example, the AMT computer store wants to develop a more detailed sales forecast for computer systems drawing from information, including its projected market size and its projected market share. If you wonder where the numbers come from, please refer back to the book Hurdle: the Book on Business Planning, which is included with Business Plan Pro. The book offers detailed tips on how to develop forecast numbers, using a combination of research, analysis, common sense, and educated guessing. Section 1: Linking Information from the Market Forecast table Figure 11 shows the customized AMT sample plan User-Defined table. Figure 11: Detailed User-Defined Table, Part 1 Here are some of the details: 1. Although the system continues to refer to this table as User 1 for linking purposes, we can rename it to something more appropriate. In the example we call it “Detailed sales assumptions” instead of “User 1.” 2. The top row contains years. Cell B1 has the formula “=starting_year,” which refers to a named range within Business Plan Pro. C1 contains the formula “=B1+1”. 3. We used the Format Menu command to change fonts and patterns in the background. 4. The cells in this section of the table link to the Market Analysis table, which contains projected total market numbers for different market segments. The illustration below shows the reference formula to the Market Analysis table. All of the cells in this block, from A2 through F6, refer to the Market Analysis table. That means that if you decide to change the Market Analysis table for any reason, you don’t have to retype. Section 2: Total Sales Based on Percent of Total Market In the second table block shown in Figure 12, we project the percent of total market expected to purchase systems within each give year. As with the total potential market projections, these are a combination of research, analysis, common sense, and educated guessing. Figure 12: More on Detailed User-Defined Table These cells don’t use formulas; they are manually input. They are assumptions typed into the cells. Section 3: Calculated Unit Sales The third block of cells calculated the estimated unit sales for the geographic market. For each market segment, it multiplies percent of total by total market to calculate projected total sales for the segment The illustration shows the formula for cell B18. It multiplies the unit number in B2 times the percentage in B10, and uses the Business Plan Pro ROUND function to round that calculation to even numbers. The ROUND Function The formula shown in Figure 12 (“=ROUND(B10*B2,0)”) rounds the calculation to the nearest whole number. Here are some more examples of how this function works: =ROUND(B10*B2,1) would round to a single decimal. =ROUND(B10*B2,-2) would round to the nearest hundred. =ROUND(B10*B2,-3) would round to the nearest thousand. The SUM function The formula for cell B23 in Figure 12 isn’t shown, but it is an excellent example of the classic SUM function in a formula. The actual formula is: =SUM(B18:B22) The formula calculates exactly as it seems: the sum of the cells included in the summation range. These SUM formulas are common in spreadsheets, and throughout Business Plan Pro as well. The SUM formula is used to sum unit sales, total sales, and total cost of sales. It’s used to sum rows in the Personnel Plan, in the Profit and Loss, the Cash Flow, and Balance Sheet. It is quite common throughout Business Plan Pro. There is an illustration of the SUM function in Figure 13, showing the formula for cell B40 of the same worksheet, which sums the range (B35:B39). Fourth and Fifth Sections: Market Share and Sales The detailed market model then continues into its fourth section, which contains percentage assumptions of AMT’s share of the local market. Figure 13 demonstrates this. Figure 13: Finished a Detailed User Table The fifth, and final, section of the model multiplies the total market projected sales by the assumed AMT percentage market share, in order to project AMT sales of systems. The final numbers in row 40 of that model are then linked back into the Sales Forecast, which was associated with Figure 8 earlier in this discussion. An Investment Analysis with IRR, NPV, and Valuation The InteliChild.com sample business plan, which is included on the Business Plan Pro main disk, includes an investment analysis in its User-Defined table that uses the built-in functions for Net Present Value (NPV) and Internal Rate of Return (IRR). Figures 14 and 15 show this table. Figure 14: Investment Analysis Table • In 1999, the first-round investors purchase 500,000 shares of a total 1 million, for $1 per share. At that point, they own 50% of the company, as is shown in row 10. The initial founders hold 500,000 shares, the other 50% (in cell B6). The valuation at that point is $1 million (in cell B5). The company goes public in 2003 with a market cap of $200 million (cell F5). At that time, those first-round investors hold only 25%, because their initial 50% has been diluted by new shares and new investors. Their yield at that point is $50 million (cell B12, repeated in cell F15), 25% of $200 million. They invested $500K and received $50 million just a few years later. The cash stream for them is in cells B15:F15, which results in the NPV of $15.984 million, shown in B14. Figure 14 shows the formula that produces the NPV calculation, using the built-in NPV function. The function goes: =NPV(discount rate, cash stream) so you can see how it is applied in this case. The formula shows in Figure 14. • • Figure 15: Investment Analysis Table, IRR • Their IRR is in cell G15, a healthy 216.23%. Figure 15 shows you the exact formula for calculating IRR, following the function syntax: =IRR(cash stream, starting discount rate) In 2000, the company issues 500,000 new shares (note the total increase in cell C4) and sells them to the second-round investors for $4 per share. Total valuation is then $6 million (cell C5, multiplying total shares in C4 times value per share in C9). These investors purchase 1/3 of the company (C10) for $2 million (C7). When the company goes public in 2003, if all goes according to plan, these investors will have 25% ownership, worth $50 million. That means their investment yields NPV of $18.88 million (C14) and IRR of 192.4% (G16). Business Plan Pro produces those values using the NPV and IRR functions. The third- and fourth-round investors purchase additional shares at higher valuations, as shown in the rest of the worksheet. • • • The Net Present Value (NPV) Function Business Plan Pro offers a built-in Net Present Value (NPV) function, as shown in detail in Figure 14. The formula is shown in the edit bar, for the cell selected in the illustration the normal way to put this function is: =NPV(discount rate, cash stream) In the example, both the discount rate and the cash stream are references to a cell and a block of cells. However, you could also use the function: =NPV(.25,-500000,0,0,0,50000000) to generate the same results shown in Figure 14. The Internal Rate of Return (IRR) Function Business Plan Pro offers a built-in Internal Rate of Return (IRR) function as shown in detail in Figure 15. The formula for the cell selected shows in the edit bar of this illustration. The normal way to put this function is: =IRR(cash stream, starting discount rate) In the example, both the starting discount rate and the cash stream are references to a cell and a block of cells. However, you could also generate the same results by citing the block of cells containing the cash flow, and typing the discount rate, as in =IRR(B15:F15,.25) An Example with Assets and Depreciation Although we recommend a much simpler treatment of assets and depreciation, Figure 16 shows another User-Defined table that uses depreciation functions to help manage assets. We also used the built-in depreciation functions to handle depreciation automatically: Note, for example, the use of the SYD depreciation function in the edit bar, for cell C11. • We depreciate Machine 2 with the sum of the year's digits (SYD) depreciation method, a built-in depreciation function compatible with Microsoft Excel depreciation functions. The syntax is as shown: =SYD(purchase price, salvage value, years of life, period) We copied that formula over to the rest of the row to calculate depreciation for that machine for five years. We depreciated Machine 1 according to the straight-line method, another built in function. The syntax for that function is: =SLN(purchase price,salvage value, years or life) which means that the actual formula in cells B10:F10 is: =SLN($B$2,10000,10) We purchase Machines 3 and 4 according to the assumptions shown in the illustration, using the depreciation functions as well. Machine 3 gets straight-line depreciation and Machine 4 gets SYD depreciation. For the last line, which summarizes all other capital expenses for the various years, we depreciate by taking 20% of the value of the purchase in each year, for each of the successive years. The underlying assumption is that all purchases are assets good for five years, depreciated straight line at 20% per year, with no salvage value. • • • • • • Errors and such 1. Locked cells. 2. Absolute reference vs. relative reference 3. How to insert a row 4. The IF function The IF Function in Business Plan Pro Worksheets The formula shown in Figure 5 works with an IF function, and important element of spreadsheet programming used frequently in Business Plan Pro. Here’s the syntax: =IF (test, yes, no) Figure XX offers an example that’s easy to follow. Go to an empty User-Defined table in Business Plan Pro and select cell B1. Type the formula “=IF (A1=1,5,7)” into cell B1. If A1 is empty, then the formula in B1 should make it have the value seven. If A1 were equal to one, then this IF formula would make B1 equal to five. Try it yourself and you’ll see how it works. If the test value is true, then the function executes the formula in its second portion, after the first comma. If not, it executes the formula in the last part, after the second comma. Figure XX: IF Function in Action So the formula you see in the edit bar in Figure 5, which is “=IF (SFCA=1,Sales2, Sales3)” tests first whether SFCA is equal to one. If it is, then the cell picks up the value in a range named Sales2. If it isn’t, the cell has the value in the range named Sales3. SFCA is a named variable. We use it in Business Plan Pro to keep track of whether you’ve chosen (in the Plan Wizard) to forecast sales by units or value. If you’ve chosen the value forecast, Business Plan Pro sets SFCA to one. If not, it doesn’t. You wouldn’t need to know this to work with Business Plan Pro, but in this context it helps you understand the use of the IF function. 5. Range names Accounts_payable Accounts_receivable Accumulated_depreciation Acid_test Administrative_salaries Asset_turnover Assets_to_sales Burden_in_Income Capital Capital_assets Capital_expenditure Capital_input Collection_days Cost_of_sales Cost_of_unit_sales Debt_and_equity Depreciation Dividend_payout Dividends Earnings Earnings_Year Fixed_cost General_and_Administrative_Payroll General_and_Administrative_percent Gross_margin Gross_margin_percent Indexor Insurance Interest_expense_LT Interest_expense_ST Inventory Liabilities Long_term_Assets Long_term_interest_rate Long_term_liabilities Net_cash_flow Net_profit =Balance!$C$18:$BS$18 =Balance! $C$6:$BS$6 =Balance! $B$12:$Q$12 =Ratios! $B$29:$Q$29 ='P&L'! $B$25:$Q$25 =Ratios! $B$30:$Q$30 =Ratios! $C$26:$C$26 ='P&L'! $C$26:$S$26 =Past! $A$25:$D$27 =Balance! $B$11:$AQ$11 ='Cash Flow'! $B$16:$AQ$16 ='Cash Flow'! $B$10:$AQ$10 =Assumptions! $C$6:$AS$6 ='P&L'! $C$8:$BS$8 ='P&L'! $C$4:$BS$4 =Balance! $B$16:$Q$16 ='P&L'! $C$27:$BS$27 =Ratios! $C$32:$Q$32 ='Cash Flow'! $C$17:$BS$17 =Balance! $B$28: $AQ$28 =Balance! $O$28:$Q$28 ='Break-even'!$B$8 ='P&L'!$24:$24 ='P&L'!$B$35:$BQ$35 ='P&L'!$C$9:$BS$9 ='P&L'!$B$10:$BQ$10 =Logistics!$C$20:$AN$20 ='P&L'!$B$32:$Q$32 ='P&L'!$C$46:$BS$46 ='P&L'!$C$45:$BS$45 =Balance!$C$7:$BS$7 =Balance!$C$24:$AQ$24 =Balance!$B$10:$AQ$10 =Assumptions!$C$4:$AS$4 =Balance!$B$23:$AS$23 ='Cash Flow'!$B$19:$AQ$19 ='P&L'!$C$48:$BS$48 Net_profit_margin Net_worth New_accounts_payable Other_Operating_Expenses Other_ST_liabilities Paid_in_capital Past_Earnings Past_Gross Past_range Past_Sales Past_Years PastAccDepr PastAP PastAR PastCapital PastCash PastCollectionDays PastGM PastInv PastLTA PastLTL PastOpex PastOthSTA PastOthSTL PastPaymentDays PastSales PastSTL PastTurnover Payables_Percent Payment_days Payroll Payroll_burden Payroll_Expense Personnel_burden_percent Personnel_Complex Personnel_Medium Personnel_Simple =Ratios!$C$4:$Q$4 =Balance!$B$31:$AQ$31 =Logistics!$C$16:$AQ$16 ='P&L'!$B$36:$BQ$36 =Balance!$B$20:$AQ$20 =Balance!$B$26:$AQ$26 =Past!$B$34:$D$34 =Past!$B$4:$D$4 =Past!$A$1:$D$41 =Past!$B$3:$D$3 =Past!$B$2:$D$2 =Past!$D$19 =Past!$D$25 =Past!$D$13 =Past!$D$32 =Past!$D$12 =Past!$D$7 =Past!$D$5 =Past!$D$14 =Past!$D$18 =Past!$D$30 =Past!$D$6 =Past!$D$15 =Past!$D$27 =Past!$D$39 =Past!$D$3 =Past!$D$26 =Past!$D$8 =Assumptions!$C$9:$AS$9 =Assumptions!$C$5:$AS$5 =Personnel!$C$53:$AS$53 =Personnel!$C$54:$AS$54 ='P&L'!$25:$25 =Assumptions!$C$11:$AS$11 =Personnel!$A$22:$BS$55 =Personnel!$A$5:$BS$20 =Personnel!$A$1:$BS$3 Price_per_unit Production_payroll Profit_before_int_and_taxes Ratio_Analysis Ratios_range Receivables_turnover Retained_earnings Return_on_Assets Sales Sales_and_marketing_expenses Sales_and_Marketing_percent Sales_Breakeven Sales_Months Sales_on_credit Sales_on_credit_percent SalesForecastCostOfSales SalesForecastDirectCost SalesForecastTotalSales SalesForecastUnitCosts SalesForecastUnitPrices Short_term_interest_rate Short_term_notes Start_date Start_up_inventory Starting_month Starting_year StartUpAP StartUpCapital StartUpCash StartUpInventory StartUpLTA StartUpLTL StartUpOtherSTA StartUpSTL StartUpSTLO Subtotal_short_term_liabilities Tax_rate_percent ='Break-even'!$B$6 ='P&L'!$5:$5 ='P&L'!$C$44:$BS$44 =Ratios!$N$2:$Q$32 =Ratios!$A$1:$R$32 =Ratios!$C$9:$Q$9 =Balance!$B$27:$AQ$27 =Ratios!$C$5:$AQ$5 ='P&L'!$C$3:$BS$3 ='P&L'!$B$12:$BQ$12 ='P&L'!$B$22:$BQ$22 ='Break-even'!$B$3 ='P&L'!$C$3:$N$3 =Logistics!$B$15:$AQ$15 =Assumptions!$C$10:$AS$10 ='Sales Forecast'!$A$46 ='Sales Forecast'!$A$10 ='Sales Forecast'!$A$33 ='Sales Forecast'!$A$40 ='Sales Forecast'!$A$27 =Assumptions!$C$3:$AS$3 =Balance!$B$19:$AS$19 =Logistics!$C$1 ='Start-up'!$A$14:$D$14 =Logistics!$B$7 =Logistics!$B$8 ='Start-up'!$B$33 ='Start-up'!$B$30 ='Start-up'!$B$13 ='Start-up'!$B$14 ='Start-up'!$B$18 ='Start-up'!$B$37 ='Start-up'!$B$15 ='Start-up'!$B$34 ='Start-up'!$B$35 =Balance!$B$21:$AQ$21 =Assumptions!$C$8:$AS$8 p6Taxes_incurred Total_assets Total_cost_of_sales Total_debt_and_equity Total_equity ='P&L'!$C$47:$BS$47 =Balance!$B$14:$AQ$14 ='P&L'!$B$8:$BQ$8 =Balance!$B$30:$AQ$30 =Balance!$B$29:$AQ$29 Total_General_and_administrative_expen ='P&L'!$C$34:$BS$34 Total_Liabilities Total_long_term_assets Total_Operating_Expenses Total_other_op._exp. Total_Sales Total_Sales_and_marketing_expense Total_short_term_assets Units_Breakeven Variable_cost_per_unit =Balance!$B$24:$AQ$24 =Balance!$B$13:$AQ$13 ='P&L'!$C$43:$BS$43 ='P&L'!$C$40:$BS$40 ='P&L'!$C$3:$AQ$3 ='P&L'!$C$21:$BS$21 =Balance!$B$9:$AQ$9 ='Break-even'!$B$2 ='Break-even'!$B$7 

Traditional Copyright:All rights reserved

views:190

downloads:0

rating:1(1)

reviews:1

posted:5/2/2008

language:English