Business Planning - Financial Model

The Financial Model By Tim Berry President, Palo Alto Software Copyright September, 2004 Table Of Contents Table Of Contents Introduction .................................................................................................. 2 Accounting Principals ...................................................................................... 3 Simplifying Assumptions............................................................................... 6 Three Main Statements ..................................................................................10 The Balance Sheet......................................................................................12 The Cash Flow ...........................................................................................15 Cash-Critical Assumptions.........................................................................18 Links, Logic, and Assumptions ........................................................................23 Other Tables ................................................................................................27 Index ..........................................................................................................29 i Links, Logic, and Assumptions Introduction The Business Plan Pro Financial Model The illustration below shows the integrated Business Plan Pro financial model. We've been working with variations of this model since the early 1980s. It links a business’ main financial statements into a logical system that facilitates financial forecasting. THE BUSINESS PLAN PRO FINANCIAL MODEL   The Business Plan Pro financial model depends on standard financial principals that link the  Income statement (Profit and Loss) to the Balance Sheet and Cash Flow.     As the diagram indicates, this is a system of linked tables. Linking the tables is critical for practical forecasting, because of the way business financial projections work and the way people deal with information. Tables must be able to stand alone as, for example, a Sales Forecast or Personnel Plan; but at the same time, changes in one must necessarily affect the others. When you change projected sales, that change should reflect immediately in the Profit and Loss, Cash Flow, and in most cases the Balance Sheet as well. The financial model is based on established financial principles including standard accounting, double entry bookkeeping, and the underlying importance of the fundamental accounting equation: assets = liabilities + capital. It also depends on understanding the difference between planning vs. accounting. The model runs on the three main projected statements: Income, Cash Flow, and Balance. It also depends on some logical links between these statements; the most important are the cash-sensitive links used to estimate behavior of receivables, payables, and inventory. 2 Links, Logic, and Assumptions 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. 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 counter-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, whereas 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 versus a sales forecast. Imagine a computer store that projects depreciation of approximately $13,000 per year. It expects sales to rise from $5 million to $9 million in three years. The business could develop a plan that lists future asset 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, does not. Accounting Principals The Financial Principles The Business Plan Pro financial model respects the principles of proper accrual accounting. It has to -- without that, it can't do the financial analysis people expect. Ratios would be wrong, and Cash Flow would be wrong. This financial model respects all of the following basic principles: 1. Based on Standard Accrual Accounting. The standard accounting method, called Accrual Accounting, is the accounting method that matches expenses incurred by a company with the revenue it generated during a particular period of time. Tax authorities and bookkeepers deal with accounting as either accrual or cash based accounting. Cash based is easier, but accrual accounting gives a more accurate picture of cash and profits. 3 Links, Logic, and Assumptions 2. Assets = Liabilities + Owner’s Equity. This fundamental principle flows throughout the business model. "Owner’s equity" in this case is also called capital, and is also the same as net worth, which is normally defined as Assets less Liabilities. 3. Double Entry Bookkeeping is the foundation of finance. For a $100 check for rent, you credit the checking balance and debit rent expenses. Every transaction must have an equal amount of debits and credits. Every check you write is a credit against your checking account balance, a negative amount. There must be an equal amount as a debit to expenses. When you enter a check for rent you debit rent expenses and credit the checking account. 4. Profits are NOT Cash. Profits are an accounting concept, basically equal to sales less costs and expenses, interest, and taxes. What’s the difference, why does it matter? Consider several extremely important examples: • • • Buying assets doesn't affect Profit and Loss. Purchasing inventory, either materials or goods for resale, doesn't affect profits until that inventory is sold. Repaying debts doesn't affect Profit and Loss (although there might be a change in future interest payments. Interest is deductible and therefore does affect Profit and Loss). Receiving investment doesn't affect Profit or Loss. • Yet obviously, all of these transactions do affect your Cash Flow. Accrual Accounting is Standard. Cash basis is optional. Accrual accounting is the accounting method that matches expenses incurred by a company with the revenue it generated during a particular period of time. Tax authorities and bookkeepers deal with accounting as either accrual or cash-based accounting. Accrual accounting is about the timing of transactions. For example, expenses belong in the month incurred, not in the month the related bill is paid. More examples: • Purchasing goods as inventory (goods you are going to sell later) increases your Inventory as a Current Asset on the pro forma Balance Sheet, but doesn't affect your Cost of Goods Sold until the goods are actually sold, which could be months later. Delivering goods to a customer is a sale, whether the goods are paid for or not. Profit and Loss records a sale. However, if they aren't paid for on delivery, then the sale is a sale on credit, and the unpaid amount goes into the Balance Sheet as Accounts Receivable. • Cash-based is easier, but accrual accounting gives a more accurate picture of cash and profits. Business Plan Pro can do either; accrual based accounting is the standard. 4 Links, Logic, and Assumptions Cash-based Accounting When Business Plan Pro shifts from standard accrual financial projections to easier cash-based projections, it makes the following changes: • • • There are no Accounts Payable. The Accounts Payable row no longer appears in the Balance Sheet. The Payments Details table no longer appears. There are no Sales on Credit, and no Accounts Receivable. All sales are cash sales. There is no inventory. The purchase of goods or materials for sale go directly into direct cost of goods in the month purchased. These small changes set your financial projections to cash basis instead of accrual. After making these changes, you should also make your projections with cash basis in mind. This is a matter of inputs, specifically: 1. In your Sales Forecast, you make a sale when you receive the money, not when you deliver the product or service. This isn't normally a problem for cash-based businesses; if you sell everything in cash, the distinction is irrelevant, because the transactions should occur simultaneously. The cost of sales portion of your Sales Forecast should project your costs as happening when you pay for them, not when you receive the related goods or services. 2. In your Profit and Loss, your expenses should be timed according to when you pay for them, not when you incur the debt. For example, the advertising expense happens not when the ad appears in the newspaper, but rather when you pay for it. The timing is when the money changes hands, not when you agree to take on the expense, and not when it is actually delivered. Debits and Credits You don't need to understand debits and credits to work with this financial model, but you may want to know that it does understand the underlying system of double-entry bookkeeping. It works very much on top of that system, transparent to you as you make your projections. Debits and credits relate to double-entry bookkeeping, and the underlying balance. Bookkeeping uses debits and credits to make sure that every business transaction balances the money spent with what it was spent on, and the money received with why it was received. • For example, a rent payment of $1,000 is a $1,000 debit to rent expense and a $1,000 credit to the checking account. That means that rent expenses increased by $1,000, and the checking account decreased by $1,000. The equal amounts keep the business in balance. Similarly, a cash sale of $10 produces a $10 credit to sales and a $10 debit to cash. The double entry preserves the balance. • 5 Links, Logic, and Assumptions The financial model works with monthly totals, so nobody has to calculate debits and credits, but the calculations are built on the same financial system. The model is able to calculate Cash Flow because it understands debits and credits and maintains a balance. Many calculations depend on the underlying principle of balance, which is that assets will always be equal to capital and liabilities. • For example, buying inventory automatically increases the amount of inventory in the Balance Sheet, and automatically decreases either the amount of money owed (Accounts Payable) or the cash balance, depending on assumptions. The balance is always maintained. Similarly, a $10,000 loan adds $10,000 to the cash balance, an asset, and at the same time adds the same amount, $10,000 to the liabilities. That means assets are still equal to liabilities plus capital. • Simplifying Assumptions Simplifying Assumptions Because of the differences between planning and accounting, there are diminishing returns in trying to create detailed itemized assumptions of some detail items for years into the future. Business Plan Pro uses several simplifying assumptions to project future financial results without getting lost in impossible future detail. • Taxes are simple mathematics. Business Plan Pro calculates taxes by multiplying the tax rate in General Assumptions by the earnings before taxes. In years producing loss instead of profit, taxes are set to zero. Although you can set Business Plan Pro to deal with losses carried forward, it doesn't do that automatically because it's about planning, not tax accounting. Interest rates are simple mathematics. Business Plan Pro calculates interest expense by multiplying the outstanding balance for either current or longterm liabilities times the interest rates in General Assumptions. Depreciation is a simple input. You don't have to guess years of future assets data to project depreciation. Treat it as what it is, an expense, and estimate its amount for future years. The Balance Sheet is Summarized. You don't need all the details because this is planning, not tax accounting. • • • Tax Rates as Simple Mathematics Taxes in the financial model are based on simple mathematics. In general, the estimated tax in the pro forma Income Statement is the product of multiplying pretax profits by the tax rate in the General Assumptions table. However, Business Plan Pro looks at your annual profits and sets the tax rate to zero if you have a loss for the year. This avoids the awkward situation of overestimating your cash by assuming negative taxes in a loss year. 6 Links, Logic, and Assumptions Furthermore, Business Plan Pro does not automatically calculate loss carried forward situations. The loss carried forward refers to losses in previous tax years that reduce taxes in a profitable year. That happens when a company finally makes a profit after initial losses, and those losses are deducted from current year taxes. The simplifying assumption in this case is that taxes are figured using simple mathematics. When you have a profit for the end of the year, Business Plan Pro multiplies pretax profits by the tax rate percentage in your General Assumptions table, and that is its estimate for taxes. If you do not have a profit, Business Plan Pro sets your tax rate to zero. This is rather simple. We automatically change the treatment for loss situations, but only in a simple global way, setting the effective tax rate to zero when the entire year produces a loss. This is about planning, not accounting, and simple is good. The estimated tax rate is easy to understand and easy to apply. You get a lot more planning power from a simple and obvious estimated input than from a complex, hard-to-follow formula. Interest Rates as Simple Mathematics Business Plan Pro treats loan interest and principal payments according to standard accounting: • • Interest, which is deductible against income as an expense, is in the Profit and Loss table. Principal repayment, which is not deductible, affects the Cash Flow and the Balance Sheet, but not the Profit and Loss. Following the accounting standard, the program calculates monthly Interest Expenses by multiplying the balance for any given month by the Interest Rate assumption in General Assumptions. It calculates Annual Interest by taking the average balance and multiplying that times the Interest Rate in the General Assumptions. Both of these calculations are logical and easy to understand. However, as easily understood and as logical as these calculations are, they are not quite as exact as you would want them for accounting purposes, after the fact. • One potential problem is that the bank likely calculates interest based on the beginning balance, while Business Plan Pro calculates based on the ending balance. Another problem is the timing of the loan: a loan taken out on January 28 produces the same interest estimate in Business Plan Pro as a loan taken out on January 3. Furthermore, Business Plan Pro takes a loan received at any time during the second, third, fourth, or fifth year of a plan and calculates its average balance as ending balance plus beginning balance divided by two. Since the beginning balance was zero, that calculation divides the loan amount by half, for planning purposes. That means that interest is figured as if the loan were taken out on June 30, the middle of the year, regardless of whether it was planned for the first month or the last month. • • 7 Links, Logic, and Assumptions Most of these problems are not really problems in business plans. Good practical estimates, easy to explain, are more valuable than detailed exact calculations based on uncertain assumptions. Remember this is a plan, not tax accounting, and it needs to summarize and simplify. If the business plan estimate does not match to the penny, do not worry; an estimate is acceptable. However, if your business plan should require more detailed interest calculations, because of the significance of the amounts, you can adjust the estimated interest rates in the General Assumptions table to produce exactly the interest you want. Then all you need to do for your plan is explain the reason for the interest rate assumptions in the text that accompanies the General Assumptions table. PRINCIPAL PAYMENT TIP Business Plan Pro includes financial functions you can use to determine correct principal payments and interest payments on loans, according to standard financial formulas. Look in your Help for "PPMT". Summarized Balance Sheet A pro forma (projected) Balance Sheet in a business plan is different from a Balance Sheet as an accounting statement. The pro forma Balance Sheet must necessarily be more summarized and aggregated that the related accounting statement. The row items must still balance, and assets must still be equal to liabilities plus capital, but there will be less detail and less itemization. The difference between the business plan's pro forma Balance Sheet and the accounting statement balance sheet stems from their use and purpose. In the business plan, the pro forma Balance Sheet is used to make projections of the future, sometimes years in the future. In the accounting statement, the Balance Sheet is used to determine detailed financial position, as part of tax reporting, stock reporting, and other reporting purposes. Therefore the pro forma Balance Sheet will not break the balances into detailed items nearly as much as a standard accounting balance. It will not break inventory, or current assets, or long-term assets, or current liabilities, or long-term liabilities into different component items. It will not break cash into checking accounts vs. liquid securities. Also, the accounting statement Balance Sheet can be much more detailed than a pro forma Balance Sheet. It will break the various row items into much more detail than a pro forma because the accounting statement, unlike the planning projection, is used to deal with taxes, and ownership, and partners, shareholders, and other stakeholders. This use demands detail and itemization. 8 Links, Logic, and Assumptions Depreciation as a Single Row of Simple Input Business Plan Pro uses a single row of data input to contain your estimated depreciation. This simplifying assumption is much better than a complex set of depreciation formulas, for several good reasons: • • Government tax authorities set strict rules for when and how assets can be depreciated. These rules are not always logical. Different assets are allowed to be depreciated according to different formulas: there is straight-line depreciation, double declining balance, sum-of-theyears-digits, and several other formulas. The formulas cannot be applied to groups of assets; each asset generally has its own set of rules, according to which type of asset it is. If we decided to apply detailed formulas to future assets, we would have to estimate the purchase of those assets, and keep track of each significant asset separately. We would also have to keep track of each asset's depreciation formula. The detail required would produce an artificially difficult data problem without really reducing uncertainty in any practical way. • • An educated guess is a better option. If you take a percent of assets value per year, based on past years’ depreciation, that is a good option. If you do not have past data to use, then ask somebody who does. Ask your accountant. In the worst case, you can take 10% of assets value per year and not go too far wrong. About Financial Projections The Business Plan Pro financial model has been developed and refined for more than 20 years now, 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, or any other projection of the future, it 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 document is to give you an understanding of the underlying assumptions, the mathematics, and the mechanics of the Business Plan Pro projections. Remember, however, that the software is a tool. It is only as good as the assumptions loaded put into it. Furthermore, it depends on mathematical modeling to project critical elements—receivables, for example—into the future, based on assumptions. These projections are just that -- projections -- and the math behind them is complex and elegant, but still subject to some important assumptions. The best analyst uses human judgement 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 while running the financial analysis. Change factors repeatedly. Each new iteration of the plan 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 9 Links, Logic, and Assumptions music: the artist sings multiple tracks into a single tape and 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 with many different volumes, with many different prices, with 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 do not 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. Three Main Statements The Three Main Statements Expert opinions may vary, but most would agree there are three main financial statements in accounting and financial forecasting alike. These are sometimes called the Pro Forma Statements, a verbal shortcut for a longer phrase. • It starts with the Pro Forma Profit and Loss, or Income Statement. This statement incorporates sales, Cost of sales, operating expenses, and Profit. In most cases it should show sales less cost of sales as Gross margin , and gross margin less operating expenses as profit before interest and taxes (also called gross profit, and contribution to overhead). Normally there is also a projection of interest, taxes, and net profits. The Pro Forma Cash Flow is the most important of the three main pro forma statements. Businesses run on cash. No business plan is complete without a Cash Flow plan. Some would call it a Cash Plan. The Pro Forma Balance Sheet is the third of the main three: Aside from cash and income, there is the balance of assets, liabilities, and capital. • • The three statements depend on some very important conceptual links between them. The Profit and Loss depends on the Balance Sheet for debts that affect interest. The Balance Sheet depends on the Profit and Loss for earnings, and on the Cash Flow for many other financial transactions that affect cash but not profits. These pro forma statements are automatically linked up and pre-programmed in the Business Plan Pro and Cash Compass software. Any changes made to any of the statements are set to automatically record correctly in all others. 10 Links, Logic, and Assumptions The Profit and Loss is Income, or pro forma Income We call it Profit and Loss most frequently, but it is also frequently called the Income Statement in accounting. Technically, in a business plan, it is a Pro Forma Income Statement because the "pro forma" means it contains future projections, not past facts. The Profit and Loss in the financial model follows standard conventions, the format that most bankers, investors, and accountants are used to, by putting Sales at the top, followed by Cost of Sales or Cost of goods sold, to show Gross Margin, which is Sales minus Cost of Sales. After that comes operating expenses, and, at the bottom, Profit as the bottom line. Figure 2 shows an example. FIGURE 2: A SIMPLE PROFIT OR LOSS Click to View     The standard Profit and Loss table is arranged the way bankers and accountants expect to see it.   Business Plan Pro gets the sales and direct cost of sales on the Income Statement from the Sales Forecast. The payroll information comes from the Personnel Plan. Interest expenses are determined by liabilities balances and interest rate assumptions. The Income Statement can be much more complex than the illustration here. Many Income Statements divide expenses into categories, such as General and Administrative Expenses, Sales and Marketing Expenses, and others. Many Income Statements show "Other Income," also called Non-operating Income. 11 Links, Logic, and Assumptions The Balance Sheet pro forma Balance Sheet The Balance Sheet in this financial model is entirely determined by assumptions in the Income Statement and the Cash Flow. Figure 3 shows an example. FIGURE 3: THE PRO FORMA BALANCE SHEET Click to View   The pro forma Balance Sheet shows assets, liabilities, and capital, which is the financial position of a  company at a certain date.  A pro forma Balance Sheet is normally summarized, with major categories  summed instead of itemized in detail. On this point it is not like the accounting statement Balance  Sheet, which itemizes information from the past.   12 Links, Logic, and Assumptions The Balance Sheet is a picture of a business’ assets, liabilities, and capital -- its overall financial picture -- on a given date, at a given time. With this financial model, we can calculate the Balance Sheet entirely from other worksheets that affect it: starting balances from either the Start-up table or Past Performance; Profit and Loss; and Cash Flow. The financial model calculates all the balance items automatically: • • Net profit and depreciation come from the Profit and Loss. Accounts receivable, accounts payable, and inventory are calculated from the separate Payment Details, Receivables Details, and Inventory Details tables. Changes in other assets and liabilities come from the Cash Flow table because they need to be entered into the Cash Flow first and then absorbed into the Balance Sheet. • Starting balances come from either the Start-up table or Past Performance. Other changes, such as new borrowing or debt repayment, or new capital or dividends, or changes in other assets and liabilities, come from assumptions typed into the cash flow first. For example, when you borrow money you set the borrowed amount into the cash flow, and the balance changes automatically. Ultimately, the business model has to treat its pro forma statements very much like you are used to treating financial statements in standard accounting and reporting; Balance Sheet items in the plan mean exactly what they mean in reporting past results. In the initial balance, the software recalculates retained earnings as a plug amount, forcing retained earnings to make sure that assets are always equal to capital plus liabilities. Retained earnings remain unchanged through the 12 monthly columns, then at the end of the year the software moves earnings into retained earnings. Many Balance Items Depend on Inputs in Cash Flow Many Balance Sheet items depend completely on inputs in the Cash Flow table. For example, compare the entries in Other Current Assets in the Balance Sheet to the entries in rows related to Other Current Assets in the Cash Flow, which is the row named “Sales of Other Current Assets” in the upper section and “Purchase of Other Current Assets” in the lower section. What you input in Cash Flow changes the Balance Sheet. That is also true with all three of the Liability rows, and Long-term Assets as well. Amounts typed into Capital Input in Cash Flow directly affect Paid-in Capital in the Balance Sheet. Dividends in the Cash Flow subtract from Retained Earnings. 13 Links, Logic, and Assumptions More Detail on Three Key Estimates Many of the Balance Sheet items come directly from inputs in Cash Flow. Business Plan Pro tracks the three Cash-critical Balance Sheet items in their own separate detail tables: • • • It estimates Accounts Payable in the Payments Details table. It estimates Accounts Receivable in the Receivables Details table. It estimates inventory in the Inventory Details table. Understanding Earnings and Retained Earnings Business Plan Pro handles Retained Earnings according to standard accounting convention. The amounts in current-period earnings are moved into Retained Earnings once a year, at the end of the year. Retained Earnings remain unchanged throughout the rest of the year. The Earnings row accumulates earnings for the year, rather than showing the earnings of each month. The result of this treatment is that the Earnings shown for the years in the years columns are the total earnings for the full year. This matches normal accounting conventions for an end-of-year Balance Sheet, displaying Earnings for the full year. This is why we do not move Earnings to Retained Earnings monthly, but only at the end of the year—so your annual Balance Sheet column, which is what prints in the text of the plan, shows the full year’s earnings. TIP: RETAINED EARNINGS If you have trouble with the flow of retained earnings, remember that earnings and retained earnings are essentially the same thing...we like to see the earnings show just the last period's earnings, but the sum of the two is what's really important. And you can watch how retained earnings closes the loop on the Balance Sheet, so that Capital plus Liabilities equals Assets. 14 Links, Logic, and Assumptions The Cash Flow The Pro Forma Cash Flow The Financial Model is built around a powerful Cash Flow model that deserves detailed explanation. To understand this logic, we look at the Cash Flow table in detail, as shown in Figure 4. FIGURE 4: CASH FLOW TABLE   15 Links, Logic, and Assumptions The Cash Flow table focuses on money flowing in and out of the business, regardless of profits or  losses.  The Business Plan Pro cash flow logic is based on a direct cash flow, listing cash received, then cash spent, and then summing at the bottom to calculate Net Cash Flow. Conceptually the Cash Flow reconciles the Profit and Loss with the Balance Sheet; the difference is Cash Flow. The direct cash flow method is generally preferred over the indirect cash flow, which starts with net income and adds back depreciation. The Cash Flow is the most important of the three main statements because cash flow is different from profits, and much more important. Profits are not cash. A business spends cash, not profits. Cash from operations depends on three critical assumptions, related to three critical estimates: Accounts Payable, Accounts Receivable, and Inventory. Each of these three components has its own separate table. Cash Flow Logic The logic of the Cash Flow follows its format. It starts with items that generate cash, first from regular operations, then from balance-related items such as new loans and new investment. Then it plans items that cost cash, including normal payments in cash and payments of accounts payable, loan repayment, and investing in new assets. Cash in less cash out equals cash flow, and last month’s balance plus this month’s cash flow equals this month’s ending balance. Cash Received Cash received starts with cash sales. The second item in Cash Received is Cash from Receivables. These are the payments received from customers owing a business money for past sales. Business Plan Pro calculates them using assumptions in the Receivables Details table. Plans set for Cash Basis or not having Sales on Credit do not have Accounts Receivable. In the following group of user-input rows, the Cash Flow includes the many kinds of balance-related business transactions that are not included in the Profit and Loss, but add cash to a business. For example: • • • Taking out new loans Receiving investment Selling assets Expenditures Expenditures begin with Cash Spending, which comes from the Payment Details table. Cash spending normally includes payroll and payroll taxes, but might also include any other payment obligation a business chooses to pay immediately. The following row shows payments of Accounts Payable. Business Plan Pro calculates these automatically from assumptions and estimates included in the Payment Details table. 16 Links, Logic, and Assumptions In the next section of user-data-input rows, Business Plan Pro includes the many kinds of balance-related business transactions that are not included in the Profit and Loss, but absorb cash. For example: • • • Repaying loans Purchasing assets Paying dividends Cash Received Less Expenditures = Net Cash Flow At the bottom of the Cash Flow table, Business Plan Pro adds up the cash received and subtracts the expenditures to calculate net cash flow. Profits vs. Cash Several of the most important elements of the Business Plan Pro cash model are explained in greater detail in the "Cash is King" chapter of the Hurdle book . This chapter provides a few key points that are important to understand: • • “Cash” in a business plan is not bills and coins; it is checking account balance. It includes liquid securities. It is the most vital resource in your business. Changes in Balance Sheet items can have a huge impact on your Cash Flow. Companies can and do go broke while making profits. If all your cash is in inventory and accounts receivable, for example, you can be broke and profitable at the same time. Although the following rules do not make sense in every case, with the way the numbers work, accept these rules: • Every dollar of increase in your accounts receivable means one dollar less of cash. If you do not believe that, pull a dollar out of your wallet and loan it to a friend. Every dollar of increase in accounts payable means an additional dollar of cash. If you do not believe that, pay a dollar less of your bills than you otherwise would have. You have an extra dollar each in payables, and in cash. Every dollar of increase in inventory means a dollar less of cash. Sure, that can be cancelled by a dollar of increase in accounts payable, but you get the point. • • How can this be? Simple logic. Your Profit and Loss statement ignores the real cash implications because Profit and Loss doesn't record whether you were paid for your sale; it is just recorded as a sale. Your cash balance, however, does change -- it isn't cash until you receive the money. Similarly, Profit and Loss doesn't distinguish whether you paid for all your costs and expenses, or not. Therefore, the changes in balance items mean changes in cash. 17 Links, Logic, and Assumptions Cash-Critical Assumptions Cash-Sensitive Assumptions The cash projections of a normal business depend especially on three key factors: • Accounts Payable, which you estimate automatically using the Payments Details table and logical assumptions. If you set your plan for Cash Basis instead of Standard Accounting, Payables will not show up. Accounts Receivable, which you can estimate automatically using your Receivables Detail table. If you set your plan for Cash Basis, or if you set your plan for no sales on credit in the plan settings, then Receivables will not show up. • • Inventory, which you can estimate automatically using the Inventory Detail table. If you do not have your plan set for inventory, in the plan settings, then Business Plan Pro hides the inventory and sets the balance to 0. If you do have inventory, then Inventory is in your Balance Sheet and the estimation formulas are in the Inventory Details table. Although your cash flow normally includes many other rows and assumptions, these three lines in the Balance Sheet drive your projected cash flow up or down more than most other elements. Recognizing how these balances influence the Cash Flow, the Business Plan Pro cash methodology uses the balance predictors to calculate the Cash Flow table as a result of the balances. TIP: CASH BALANCE AS A SUMMARY INSTEAD OF A DETAIL Because a business plan needs to summarize instead of exploding into detail, Business Plan Pro ignores the subtle differences between cash, checking, and liquid securities. However, you can show interest income in the Extraordinary Items row at the bottom of the Profit and Loss table. Estimating Accounts Payable Business Plan Pro includes a simple table for estimating Accounts Payable. Called Payment Details, this table uses logic and algebra to calculate Accounts Payable. It also provides an override option for special cases. The table is shown below in Figure 5. 18 Links, Logic, and Assumptions FIGURE 5: PAYMENT DETAILSO VIEW   The Payment Details table calculates Accounts Payable using assumptions for Payment Delay in  Days and Cash Spending.  The software uses several relatively simple assumptions: 1. Payment Delay in Days is an educated guess, an estimate. Most businesses wait at least 30 days before paying a bill. You type this estimate into the appropriate cell. 2. Business Plan Pro calculates the estimated ending Accounts Payable balance as a function of the estimated payment delay in days and the business obligations. For example, a business that waits 30 days to pay bills will have a month's worth of bills in Accounts Payable at the end of the month. A business that waits 15 days will have half a month's bills in payables at the end of the month. A business that waits 60 days will have two months' bills in payables. 3. Cash Spending is another educated guess. A business pays some of its obligations immediately, without leaving them to wait in Accounts Payable for any time. For example, most businesses pay their payroll immediately. Since these amounts are never included in Accounts Payable, they affect other calculations. 4. Business Plan Pro uses other assumptions in other tables to calculate New Obligations Incurred automatically. The table shows the details. New obligations include non-depreciation expenses, interest, taxes, non-inventory direct costs of sales, and inventory purchase. With those calculations available, the Payment Details table shows the flow of Accounts Payable from month to month with a simple row-by-row equation: Accounts Payable = Previous Balance + New Obligations - Cash Payments - Payments Made 19 Links, Logic, and Assumptions Estimating Accounts Receivable Not all businesses sell on credit. For those that do, Accounts Receivable is so important to Cash Flow that Business Plan Pro reserves a special table, Receivables Detail, to track it. Figure 6 shows an example. FIGURE 6: RECEIVABLES DETAILS Click to View   Business Plan Proʹs Receivables Details table brings together important assumptions on Collection  Days and Sales on Credit and uses them to calculate receipt of money owed on sales on credit.   The illustration shows how Business Plan Pro calculates Accounts Receivable from two logical assumptions: 1. Estimated Collection Period is an educated guess. The default is 60 days, but actual payment days vary by business and by industry. 2. Sales on Credit % is an educated guess about what percent of a business' sales will be made on credit. A good general rule is that sales to consumers are usually in cash, but sales to businesses are almost always on credit. Credit does not mean credit cards or checks, which are considered cash sales. With these two assumptions determined, Business Plan Pro calculates Accounts Receivable automatically. It uses past sales on credit and collection days to calculate money received, and then a simple formula calculates the ending Accounts Receivable balance: Accounts Receivable = beginning balance + sales_on_credit- payments received To provide an override for special cases, Business Plan Pro allows a user to override its estimated payments received with any number for payments received. When this override is used, the rest of the calculations remain intact. Therefore you can manage special cases by direct input of the amounts received. 20 Links, Logic, and Assumptions To calculate annual receivables, the default formula establishes the mathematical relationship between total sales on credit in the first year and the ending balance of receivables, and applies that relationship to the next year’s sales on credit. Remember, this is just an educated guess. You do not harm your business plan in any way by replacing this with your own guess, if you believe your own data is more accurate. All the Cash Flow assumptions and the rest of the financials still work correctly, whether or not you keep the original receivables formula. Estimating Inventory For companies that manage inventory, it can be an important component of Cash Flow. Business Plan Pro uses the Inventory Details table, shown in Figure 7 below, to calculate Inventory and Inventory Purchase. FIGURE 7: INVENTORY DETAILS Click to View   The Inventory Details table tracks projected levels of inventory and inventory purchase. It applies to  companies that manage inventory.   The calculations depend on two relatively simple estimates: 1. Months of Inventory on Hand: In the top row of the table, estimate how many months' worth of inventory, on average, the business keeps on hand. This can be less than 1, as in .5 for half a month, or .25 for one-fourth of a month, on average. 2. Minimum Inventory Purchase: Different businesses have different realistic minimum purchase values when they purchase inventory. Using these two estimates, plus assumptions from the Sales Forecast about Direct Costs of Sales, Business Plan Pro is able to calculate inventory usage and inventory purchase automatically. However, as with other key estimates, there is also an override facility for special cases. A user can assume a higher or lower inventory purchase by typing that assumption directly into the green data entry cell. 21 Links, Logic, and Assumptions To calculate annual inventory, the default formula establishes the mathematical relationship between direct cost of sales in the first year and the ending balance of inventory, and applies that relationship to the next year’s projected direct cost of sales. Remember, this is just an educated guess. You cannot harm your business plan in any way by replacing this with your own guess, if you believe your own data or estimates are more accurate. All the Cash Flow assumptions and the rest of the financials still work correctly whether or not you keep the original receivables formula. Direct vs. Indirect Method There are two generally-accepted methods for calculating Cash Flow: the direct method, which Business Plan Pro uses, and the Indirect Method. Either one of these methods produces a valid cash forecast. The Direct Method that Business Plan Pro uses for its main cash flow adds money received, then subtracts money spent, to calculate Net Cash Flow. Depreciation is excluded because, although it is an expense that affects Net Profits, it is not money spent or received. The Indirect Method starts with net income, adds back depreciation, then calculates changes in Balance Sheet items. The end result is the same Net Cash Flow produced by the Direct Method. The Indirect Method adds depreciation into the equation because it started with Net Profits, from which depreciation was subtracted as an expense. Business Plan Pro uses the Direct Method because of a ruling made in 1987 by the U.S. Financial Accounting Standards Board (FASB). That ruling, FASB 95, prefers the direct cash flow reporting showing cash receipts and expenditures, which is what Business Plan Pro uses. It allows the indirect method only in special cases, and only when the Net Cash Flow resolves to the same amount as for the direct. Business Plan Pro's Premier version also produces a matching indirect cash flow, which is the same as a Sources and Uses of Cash financial statement. Figure 8 shows an example. 22 Links, Logic, and Assumptions FIGURE 8: THE INDIRECT CASH FLOW K TO VIEW   The Business Plan Pro Premier also produces an indirect cash flow estimate that is also a Sources and  Uses of Cash statement. There are no inputs because it uses assumptions typed into the main cash flow  table.   The Indirect Method and Sources and Uses of Cash produced by the Premier Version are optional and additional; they are not required for normal business planning Links, Logic, and Assumptions Estimating Cash Requirements Here are concrete steps involved in developing your cash requirements. 1. First, finish up initial drafts of Sales Forecast, Personnel Plan, and Profit and Loss — you may well change those again later, but you need to have good initial estimates before going to cash. Then continue with the following steps: 23 Links, Logic, and Assumptions 2. Review Cash Spending in the Payment Details table. Most businesses pay for relatively few things in cash, putting most of their purchases into Accounts Payable (money owed and waiting to be paid) where they wait for a month or so before payment. The default formula assumes payroll is always paid immediately, instead of waiting in Accounts Payable. That formula is: =payroll_expenses Many companies pay additional amounts in cash beyond payroll, but that is up to you. You can make an educated guess or, if you have business history, look at past results. For example, if you suspect you will pay payroll plus an additional $1,000 per month as cash spending, then the formula for that might be: =payroll_expenses+1000 3. Review payments of Accounts Payable, also in Payments Details table. "Cash Spending" calculates your payments of Accounts Payable based on the estimates of new obligations and payment delay in days. 4. If your plan settings include sales on credit, review Cash from Receivables in the Receivables Details table. Most businesses that sell to other businesses sell on credit, meaning that they deliver goods and services to their business customers along with an invoice to be paid. Business customers are supposed to pay those invoices in 30 days (in most cases), and some do, but many pay in 45 days, 60 days, or more. The amount of money you are waiting for customers to pay is called Accounts Receivable, or, in this case, “Receivables.” The average wait time is called collection days. These are important concepts, because your sales have been made, profits are calculated for Profit and Loss, but the money is not in the bank. Business Plan Pro calculates Cash from Receivables based on your Sales on Credit and your estimated collection period in days. 5. If your plan settings include inventory, review Inventory and Inventory Purchases in the Inventory Details table. Although Inventory does not show up directly in Cash Flow, unrealistic inventory estimates affect cash flow through the payables estimates. Check the purchase of new Inventory, which Business Plan Pro calculates using your direct cost of sales (or COGS) and your estimated inventory on hand. 6. Is your cash balance negative? Then you have to generate more cash by borrowing money or bringing in new investment, or even selling assets—in the upper middle section of the Cash Flow. If you are a start-up company, and cash flow is negative, you can also revise your Start-up Funding table to estimate higher initial loans or investment, to raise your starting cash. In the example shown in Figure 4, the business needed to borrow $13,000 in Month 2 to keep its checking account balance (which is what we call Cash) above zero. As the plan was developed, initial investment did not fully cover cash needs, so the plan had to stipulate new loans to make the cash plan viable. They might also have increased the amount of initial investment, or borrowed more money initially. 24 Links, Logic, and Assumptions Timing Distinguishes Cash Flow vs. Start-up Most of the items in the Cash Flow table, including expenses, liabilities and assets, are the same concepts as in start-up assets and expenses in the Start-up table and start-up financing in the Funding Table. The difference is timing. If the new liability or asset purchase happens during or after the first month of the plan, it belongs in the Cash Flow. If it happens before the first month, it belongs in the Start-up table. Handling of Loans, Interest and Repayment Business Plan Pro handles loans, interest, and repayment following standard accounting convention. • • • New loans go into the Cash Flow table in the upper section, as sources of cash. Interest, which is an expense deductible against income, goes into the Profit and Loss statement. Business Plan Pro calculates interest automatically. Principal repayments go into the Cash Flow table in the lower section, as uses of cash. Some people are confused by the concept of separating the payment into interest and principal. A common example, at least in the United States, is when you make payments on a mortgage: most lending institutions clearly separate payments into interest and principal components. Even if you write a single check each month to repay the mortgage loan, the payment is divided into interest and principal. REPAYMENT SCHEDULE If you do not have documentation on repayments, your local bank, a standard financial worksheet, or even a financial calculator can give you the detailed repayment schedule to use with Business Plan Pro. If you understand how to program spreadsheet formulas, you can also use the built-in Principal Payments function (PPMT). An example is provided in the Expanded Principal Payments Formula section. Expanded Principal Payments Formula Business Plan Pro also has built-in financial functions you can use to calculate loan payments. Here again, the normal expectation is that because this is planning, not accounting, you do not need to do that in this much detail. However, you can use this function if you want, and it can make sense when the numbers are very large. This formula uses the Principal Payments (PPMT) function, which calculates monthly principal payments for a loan. The PPMT function syntax specifies: =PPMT(interest rate, payment #, total payments, -loan amount) 25 Links, Logic, and Assumptions FIGURE 8: PPMT EXAMPLE Click to View   The example here shows how the PPMT function  calculates the principal payment portion of debt  repayment.  The formula shows a payment made for the 41st month (40+plan_month) of a 120‐month  payment schedule for a loan amount of $300,000.    Figure 8 shows this function in use for the calculation of principal repayments of long-term debt. In that illustration, the edit bar shows the PPMT function as applied to cell G31, the selected cell. That formula is: =PPMT(long_term_interest_rate/12,40+Plan_Month,120,-300000) • Long_term_interest_rate/12 sets the interest rate. The range "long_term_interest_rate" contains the annual interest rate, and the formula divides it by 12. The source of this information is in your General Assumptions table. 40+Plan_Month establishes that this is the 41st payment of 120 total payments. 120 is the number of payments. This is specific information for your loan, your business situation. In this example, the loan involved is for five-year terms. -300000 is the amount of the loan principal, but with a negative sign. This is the repayment amount, which is why it is negative. The cash flow implication is a negative $300,000 in this case, because there is a $300,000 loan to be repaid. • • • 26 Index Other Tables Other Tables The phrase pro forma, as in Pro Forma Income Statement or Pro Forma Cash Flow, means projected. An Income Statement is a common financial statement, the one showing profit or loss. A pro forma Income Statement (Profit and Loss Table) is a projection of future results, an educated guess, or an estimate. Accountants, academics, bankers, financial analysts, and other sophisticated business people will use this terminology to distinguish between accounting statements and the projections in a business plan. The most important pro forma statements are the Profit and Loss (Income Statement), the Balance Sheet, and the Cash Flow. The Business Plan Pro financial model also includes: • • • • The Sales Forecast and Personnel Plan tables, which provide information to the Profit and Loss. The Start-up and Start-up Funding tables which provide starting balances to the Balance Sheet for start-up businesses. The Past Performance table, which provides start-up balances to the Balance Sheet for ongoing businesses. The General Assumptions table, which holds assumptions for interest rates and tax rates. Sales Forecast Forecasting is more art than science. Think of it as educated guessing. Like the weather forecast, it is a combination of knowing as much as you can about conditions and guessing what they mean for the future. You can do it. It may be hard to forecast, but it is even harder to run a business without forecasting. The standard Sales Forecast in Business Plan Pro is simple. It has a group of rows for Sales, and a group of rows for Cost of Sales. Both of these sum to totals that reappear in your Profit and Loss table. Personnel Plan The Personnel Plan in standard Business Plan Pro is simple. It has a group of rows for either individuals or groups of individuals, and a summary and totals that reappear in your Profit and Loss table as Wages and Salaries, or Payroll. Start-up Table The Start-up Table summarizes start-up costs for a new business, including startup expenses and start-up assets. In the Business Plan Pro financial model, these become part of the starting balances on the Balance Sheet. 27 Index Start-up Funding The Start-up Funding table is for start-ups. It sets the starting balances for the Balance Sheet for cash, liabilities, and capital. Past Performance The Past Performance table in Business Plan Pro sets the starting balances in the financial model for ongoing companies. Start-ups use the Start-up and Start-up Funding tables instead. General Assumptions The General Assumptions table in Business Plan Pro has a small but specific function. It holds the assumed interest rates for both current and long-term debt, and the assumed tax rate. These rates are applied to liabilities balances to calculate debt, and to profit before taxes to calculate taxes. 28 Index Index A Accounting ......................... 3, 4, 5, 7 Accounts Payable ......... 11, 16, 17, 18 Accounts Receivable ...........11, 17, 19 Accrual accounting ......................... 3 B Balance Sheet . 11, 17, 18, 19, 20, 24, 26 C Cash Flow ....1, 16, 17, 18, 19, 20, 21, 23, 24 Cash Spending ..................14, 18, 23 Cash-basis accounting .................... 3 Critical Factors... 1, 13, 16, 17, 18, 19, 20 Current Asset ....................11, 14, 16 D Depreciation............................. 7, 21 E Earnings ...........................10, 13, 16 F Financial principals .................. 3, 4, 8 I Income.................................... 1, 10 Interest expense............ 6, 10, 24, 27 Inventory.................... 11, 16, 20, 23 L Loan repayment.................. 6, 13, 24 P Payable ............................11, 16, 18 Payroll................................... 10, 26 Planning vs. accounting .................. 1 Principal Repayments .............. 13, 24 Profit or loss.............. 5, 6, 10, 13, 16 Pro-forma statements .............. 10, 26 R Receivable .................. 11, 16, 17, 19 S Sales................. 4, 10, 14, 16, 23, 26 Sales Forecast ........................ 23, 26 Sales on Credit ....... 14, 16, 17, 19, 23 Starting balances .................... 11, 26 Start-up ................................ 24, 26 T taxes....................................... 5, 27 three main projected statements ....10, 11, 14 29