Once you have outlined the essential elements of your business plan, you will want to generate financial statements based on key assumptions about your business. These are called forecasted financial statements. The process of generating forecasted financial statements is known as financial modeling. Forecasted financial statements are different from those generated based on US Generally Accepted Accounting Principles (GAAP). Forecasted financial statements are based on specific, conservative assumptions you have made about the future of your business. When forecasted financial statements are generated with accurate and conservative assumptions, they can be a very useful decision-making tool, both for you and outside parties (banks, investors, employees, etc.). In contrast, US GAAP financial statements are based on actual financial results from past operating periods. Given you are in the business planning phase, it is not possible to generate these financial statements yet. Please note that it is illegal to file taxes based on forecasted financial statements. Forecasted financial statements are simply meant as a decision-making tool, not as an accurate accounting or tax document.
In this article, I will walk you through the process of creating a basic set of forecasted financial statements- namely, the income statement, balance sheet, and cash flow statement. In the world of financial modeling, this is called a three-statement model. I will also show you how to enhance your financial model through the addition of a break-even analysis and output dashboard.
Without further ado, let’s jump into the financial modeling process!
Link to download model: COMING SOON!
Approaching the Model
There are generally two extremes taken to financial modeling. One is to create incredibly broad assumptions- borderline guesses- to save time and costs. While this may be efficient, you may find your forecast to be wildly unrealistic, to the extent that it is no longer a valuable decision-making tool. The other extreme is to generate such complicated financial forecasts that every single account has a supporting schedule validated by pages of research. Although this may make your forecast marginally more accurate, it is purely counterproductive. Not to mention, it makes your forecasts very difficult to understand.
Experts recommend you approach financial modeling somewhere in between these two extremes. If you are presenting to investors or banks, perhaps you may want to consider introducing some complexity to the financial statements. However, if these forecasts are for internal use, it may be better to lean towards a simpler approach. Regardless, I trust this article will prove helpful in the modeling process and provide you with the foundation you need to understand financial modeling. Note that this model leans towards a more complex approach.
Formatting & Forecasting Best Practices
Implementing correct formatting and financial modeling best practices is a great way to introduce integrity and credibility to your financial models. If your model is hard to follow, poorly organized, or lacks details that stakeholders expect to see, it may reduce the extent to which they trust your forecasts. For this reason, I want to briefly describe how experts recommend you format and build your financial model, so that it aligns with industry best practices. For the purposes of small business ownership, this list is summarized and excludes practices that are only necessary for large corporations.
Planning and structure
Your financial model should have an end goal that drives the complexity and design of your model. Divide the model into three sections: assumptions (also called “inputs” or “drivers”,) calculations (also called “schedules”), and outputs (financial statements, executive summary, charts, etc.). Since the income statement is the basis for the other statements, the income statement goes first, usually followed by the balance sheet and then the cash flow statement.
assumptions
When you gather assumptions, experts recommend using the most authoritative, timely market data. For example, if you are forecasting business license and permit expenses, it is better to use established forecasts by your state government than to base your assumptions on what your uncle paid twenty years ago in a different state. A common phrase in finance is “garbage in, garage out” (GIGO). Your model may be beautifully formatted and have all the correct schedules and calculations, but if the assumptions are poor, the financial statements will be poor. Conversely, the opposite can also be true: “quality in, quality out.” The more accurate your assumptions are, the better the final results will be in terms of accuracy, usability, and integrity.
Sensitivity and scenario analysis
Sensitivity analysis usually involves creating tables that display the sensitivity of certain outcomes to your base assumptions. For example, you might have a table that shows different net income numbers based on different revenue growth rate assumptions.
Scenario analysis is the process of creating a different set of assumptions for each scenario. These scenarios are usually the downside case, base case, and upside case. For example, you might project a downside revenue growth rate of 4%, a base case of 6%, and an upside case of 8%. I have included a “switch” in the template that automatically changes your assumptions based on the selected scenario, which is then flushed through the entire model. Although this is generally seen as a more advanced financial modeling technique, it adds an incredible amount of detail and integrity to your model. The only drawback is that you will need to create three sets of assumptions, instead of one. However, I think you will find that this is easier than it sounds.
Presentation and usability
Presentation and usability are aspects of your model that can enhance your professionalism and improve the user experience. This is important, because users need to be able to find the numbers they are interested in and understand how you arrived at these numbers. There are a few ways you can accomplish this (all of these methods have been integrated into the template). The first method is to create a single sheet model (also known as a vertical model). This means every section of the model (assumptions, calculations, outputs) is on one Excel sheet. There are some who will argue that a multi-sheet model (also known as a horizontal method) is better, which is where you have each section of the model in a different sheet. Neither is “correct”, but experts generally don’t recommend any other methods than these. We are going to run with the single-sheet model for now, since it avoids linking between worksheets, which can be confusing. The second method is by grouping each section in Excel. This is only really necessary for single-sheet models. By grouping each section, users can collapse sections they don’t want to look at, which makes the model easier to navigate. The third method is to create a cover sheet that explains the general purpose of the model, your contact information and name, as well as the switch for your scenarios. This is also important if you want to include a legal disclaimer in the financial mode, especially if you are using it for an application for a loan or venture capital (VC) investment. A bonus design tip is to use your business colors for any background colors in the model.
time horizon
Your financial modeling’s time horizon is the length of time for which you are forecasting. Depending on the requirements of lenders and investors, as well as your business circumstances, forecasts typically range from 2 to 5 years. Smaller time horizons typically produce more accurate models, but provide less information for potential lenders and investors. Longer time horizons are more difficult to build and often results in less accurate models, but may provide more information for potential lenders and investors.
Refinement
If your time horizons is particularly long, you may want to revisit your model to plug in actual results and recalculate your assumptions. You might even add or modify specific schedules and line items as you become more knowledgeable about your business, industry, and the direction of the overall economy. For example, you may have used a 2.5% inflation rate as an assumption for a specific cost category but then discover that the cost category actually varies with sales. These refinements will give you a better understanding of your future business performance. After all, planning is an iterative process. It allows you to acknowledge where you have been, set realistic goals, and create strategies to achieve those goals.
Assumptions
Assumptions are the cornerstone of your financial model. Assumptions drive each calculation, which affect your calculations, which ultimately generate the financial statements and charts that investors and lenders are interested in. This should be the most time-intensive section of your financial model, because it involves detailed research about certain items which you may not have experience with.
income statement assumptions
Revenue: revenue is based on two key assumptions: sale volume and sale price. You will want to split up this component to reflect each major source of revenue in your business and create these two key assumptions for each of them. Now, this sounds fairly simple, but not if your business sells many products at different prices through several different sales channels. If this is the case, consider creating a supporting schedule for revenue that incorporates the following suggestions:
1. Break down your offerings into distinct categories based on pricing and sales volume patterns. For example, a clothing store may separate revenue streams by product type (e.g., shirts, pants, accessories) or price tiers (budget, mid-range, premium), and a garden store may segment by plants, tools, fertilizers, and seasonal products.
2. If you have many price points, calculate a weighted average sale price based on historical sales data. For example, if 60% of sales come from $20 items and 40% from $50 items, the average sale price is: (0.6×20)+(0.4×50)=32.
3. Segment revenue by sales channel. Different sales channels may have different pricing and volume trends, so consider segmenting revenue by:
- Online vs. In-store sales
- Wholesale vs. Retail
- Subscription vs. One-time purchases
As I mentioned earlier, it is very easy to go overboard and introduce too much complexity. However, if there is any line item that you should approach with caution, it is revenue.
Cost of Goods Sold (COGS): if you are a manufacturing business, this includes all of the raw materials, direct (“touch”) labor, and allocable overhead costs. I will not go into detail about these items, because most true small businesses are not manufacturing businesses. If you are a merchandising or retail business, COGS is the original cost of inventory sold during the period. For example, if you project to sell 50 cups that originally cost you $5 a piece, COGS for the period is estimated at $250 (50 cups x $5 per cup). If you are a pure service business (consultant, coach, etc.), COGS will not be a consideration since you are not incurring any costs to sell your services. However, if you incur the cost of any materials or labor to sell your services, you may want to include these assumptions in COGS.
In the model template I have created, I have split the COGS calculation into variable and fixed components. Variable expenses “vary” with the revenue you generate, while fixed expenses remain relatively constant regardless of the level of activity in the business.
Operating Expenses: there are a host of operating expenses that most businesses will aggregate into Selling, General, & Administrative (SG&A) expenses on the income statement. Generally, these operating expenses are pretty constant year over year, so the main assumption for the purposes of financial modeling is the inflation rate. The Congressional Budget, Statista, large banks, the Fed, and many other organizations make public inflation projections for the next several years. Any of these will be perfect for our model!
To actually obtain the number for operating expenses, you will need to pull up a separate spreadsheet and calculate the total number of operating expenses you expect to incur. Here is a brief list of common small business operating expenses; you will notice that depreciation is included here, but just know that depreciation is usually shown separate to SG&A on the income statement:
Rent or lease, utilities, salaries & wages (that have not already been included in COGS), payroll taxes & benefits, software & subscriptions,
insurance, loan payments (only the principal portion, not interest payments), depreciation, legal & accounting services, business licenses & permits, marketing & advertising, office supplies, website hosting & maintenance, travel expenses, shipping & delivery costs, professional development, client & employee meals, business networking & memberships, IT & tech support.
Interest Expense: the main assumptions you need to calculate your interest payments are:
1) original principal amount (excludes down payment)
2) interest rate
3) term of the loan
An amortization schedule is included in the model to automatically calculate this number based on the above assumptions. Please note that the model assumes your loan works with a fixed interest rate. Do not worry if your loan has a variable interest rate, it should not significantly skew the results.
Tax Expense: while we could create adjustments for accounting and tax depreciation in an income tax schedule, we are going to forego that process for simplicity’s sake. If you already have estimated tax payments set up with the IRS, then use this number. If not, use the effective tax rate you expect to pay on any profits made by the business.
Balance Sheet assumptions
Working Capital Accounts: to keep this section as simple as possible, we are going to focus only on changes in accounts receivable, accounts payable, and inventory. For each, estimate the number of days it will take each account to turnover. For example, if you expect the average customer to pay on their account in 20 days, you will use this number as your accounts receivable days assumption. You can even use the following formulas to make these estimates:
Accounts Receivable Days= Accounts Receivable / Total Credit Sales * 365 days
Accounts Payable Days= Accounts Payable / Total Cost of Goods Sold * 365 Days
Inventory Days= Inventory / Cost of Goods Sold * 365 Days
Cash flow statement assumptions
Capital Expenditure: the total amount you expect your business to spend on purchasing its fixed assets. Fixed assets are physical items of value to your business that typically last for more than one year. This includes items like land, buildings, vehicles, equipment, etc.
Calculations
I have included several schedules in the model to help you easily calculate the necessary items for your output section. I have also already pre-linked all of the cells together. However, I want to explain very briefly how this section works.
Revenue and COGS Schedule: since these are extremely important items in the income statement, they get their own schedules. These schedules break down each of the components of revenue and COGS, which should result in a reasonably accurate figure. However, since I cannot possibly align these schedules with every unique business, please adjust these sections to include the components that most affect your business.
Working Capital Schedule: the working capital schedule feeds the working capital assumptions you made earlier as well as the revenue and COGS figures calculated in the above schedules to calculate the cash effect of changes in working capital. These will flow into the cash flow statement to determine cash from operations. There is an important note on this schedule: you need to make sure to input a number to represent the percentage of total revenues that you expect to be made on credit. Otherwise, the resulting accounts receivable number will be wildly off.
Depreciation: I have included a simple depreciation schedule, which uses the straight-line method and assumes your business has no existing assets. Of course, if this is not the case, please adjust the schedule to reflect your situation. Again, we are looking for ballpark figures, not exact numbers.
Amortization Schedule: although it could be unnecessary for a simple financial model, I think an amortization schedule gives great insight into what is likely your business’s biggest debt: your bank loan. The numbers from this schedule flow into financial statements automatically.
Outputs
The financial statements, break-even analysis, and dashboard graphs form the outputs section of the financial model. Think of this as the “executive summary” that shows stakeholders the key highlights of your business. As we will discuss shortly, the output section is also a great way to audit your model and make sure it makes sense. If you are projecting $10B of revenue in your small candle shop for year 1, you can probably assume there is an issue in the revenue schedule or a linkage error.
If you have a solid understanding of the financial statements, feel free to skip these explanations.
Income Statement: The income statement, also known as the profit & loss statement, displays the projected accounting profit for your business over a period of time. It shows stakeholders a breakdown of your business’s revenue and expenses, which ultimately result in a net profit or net loss. Successfully generating a net profit every year is attractive to outside stakeholders because it indicates your business has an internal source of funds to expand, pay the owners (you), and repay debts. It also means the business’s net worth (equity, measured as assets-liabilities) is increasing, which makes investments more attractive to venture capitalists. Of course, profit does not necessarily equal cash inflow under accrual accounting, which is why we need a cash flow statement to make sure your business has enough cash on hand to covers its operations and help you sleep soundly. Note that we have a multi-step income statement in our model, which displays several subtotal categories to give more measures of profitability and insights into how the business earns and spends.
Balance Sheet: The balance sheet displays the projected financial position of your business at a specific point in time. What constitutes your business’s financial position? Assets, liabilities, and owner’s equity. The balance sheet, then, displays your assets, liabilities, and owner’s equity at a fixed point in time. Assets are anything your business owns that provide a benefit to your business. Supplies, equipment, land, buildings, inventory, accounts receivable, and cash are common examples. Liabilities are anything your business owes- these are obligations for your business. Any type of payable accounts, loans, unearned revenue, etc. Equity is the net worth of your business. You can think of equity as the sum of the owners’ claims to the assets of the business. The balance sheet is modeled after the accounting equation:
Assets = Liabilities + Owner’s Equity. The balance sheet ensures this equation remains true, and shows who has claims to the assets of your business (lenders, suppliers, government, etc or you, the owner).
Cash Flow Statement: The cash flow statement shows the total cash inflows and outflows for a period of time. As I have mentioned already, this is important to ensure that the business has enough cash to cover its short-term obligations. It is especially important to banks and certain investors. Banks usually require you to keep a certain amount of cash on hand in the bank as part of a loan covenant. On their part, it is an effort to control the risk of lending you money. Investors may also require or encourage you to keep a certain amount of cash on hand at any point in time as part of a strategic plan. They want to ensure that their interest in the business does not suddenly lose value because the business has defaulted on loans and payables.
Break-Even Analysis: The break-even analysis shows the point at which total expenses equals total revenues. In other words, it is when your business begins earning a profit. As I discussed earlier, profitability is an important metric for stakeholders, so a break-even analysis gives them insight into when you believe this will occur. A quick word of caution on the break-even analysis: when we refer to a break-even analysis, we are specifically referring to when the business breaks even, and not when you as the owner break even on your initial investment. Your investments and withdrawals have no effect on the profitability of the business, so they do not impact the break-even analysis. From the business’s perspective, your investments or withdrawals do not represent revenues or expenses; they only represent an addition to or reduction from cash and owner’s equity.
Graphs and Sensitivity Analysis: the graphs included in the model are suggested but are not necessarily the “end-all be-all” of financial modeling outputs. If you believe a certain trend is more important for your business than another, then switch them out! Sensitivity analysis refers to the process of evaluating how “sensitive” your model is to certain factors. For example, how sensitive is net income to sales prices? How sensitive is cash flow to changes in payables? This will also help you evaluate any assumptions that may need changing.
Auditing the Model
Auditing your financial model simply refers to make sure the model itself is sound and that the assumptions you have entered are correct. The model template should be correct as it stands, but you still need to audit the model to make sure it properly aligns with your business and that your assumptions are fair. You can even use Excel functions to audit the model if you are feeling fancy. Trace dependents allows you to select a cell and see which cells are “dependent” on it. Trace precedents is essentially the opposite- you select a cell and see which cells are feeding it information. Another handy Excel tool is Formula Auditing Mode, which allows you to see the formulas in each cell. Regardless of how you go about checking the information, it may be a good idea to get someone who is business savvy to scan the model for any glaring errors.
Once you have audited the model and are satisfied with it, you have yourself a very valuable decision-making tool that you can present to banks and investors, make financial decisions, and evaluate whether or not you want to start the business!
And there you have it! With your financial projections in hand, you are well on your way to launching a fantastic, well-grounded business!
The Accounting Geek 
Leave a comment