After my recent post about some of the spreadsheets I’ve given away online, I got asked if I ever made a general new business planning Excel spreadsheet. I said “not exactly” and gave some excuses why ultimately you need to build a custom spreadsheet as every business is just a little bit different and has different drivers and models, but I have built spreadsheets that do quick estimates and projections, so extending those or combining those or using one of those as a basis might be possible… Other people have attempted this and the biggest problem is including just the right amount of detail.
Some spreadsheets are too big and complicated and only the person who made it can explain how it works. Other examples you find online or in a book are too simplistic and don’t provide all the answers to your questions, or the questions being asked by potential investors. I tried to strike a balance having done many financial projections and more importantly having had to explain my projections to people of varying levels of financial sophistication and from different backgrounds. I still have a few ideas to improve it further, but I think the bugs are all worked out so I decided to let some more people poke at it.
One of the biggest shortcomings of spreadsheets you download online is lack of instructions or explanations on how they work. Before I entered a single number, I thought out what data I wanted to present, how many tabs I would need, and I wrote instructions as to how the spreadsheet works. As I worked on the spreadsheet I updated and improved the instructions in the hopes that people wouldn’t need to look at formulas inside of cells to figure out how it works. Those that want to dig deeper or modify the spreadsheet, of course have that option. I ask that you not take credit for my original work, nor use it commercially. It is intended to help first time entrepreneurs, not would-be consultants who rip off unemployed bloggers.
This spreadsheet allows for the quick creation of financial projections and financial statements when planning a entrepreneurial venture. It is designed to be as general as possible but may not prove suitable for all businesses, particularly those with complicated financial structures or irregular revenue. The spreadsheet assumes you buy all equipment needed. The spreadsheet defaults to 100% debt financing, a mix of debt and equity financing is also possible. No depreciation or amortization expenses are calculated and a simple flat tax rate is assumed, with funds for taxes being set aside as taxable income is earned.
- Fill out the Questionnaire tab. All boxes in light blue should be filled out first. I recommend you raise slightly more capital than you need. Next complete the financing and sales estimate section. Do not forget to include the tax rate.
- Next I recommend you select “Save as…” from the File menu and make a copy of this spreadsheet. I recommend you modify the copy not the original.
- Unless you are confident in your Excel skills and business planning expertise I recommend you skip the Master Control tab. If you must meddle, adjust the orange cells only.
- The Answers tab contains the most useful information when planning a business, estimates of: startup costs, monthly debt payment, revenue, break even sales, and average monthly pre-tax income.
- The Projections tab contains revenue, income, and cost projections for three years. Do not modify this tab unless you know what you’re doing.
- The Balance Sheet is prepared in accordance with IFRS.
- The Income Statement is prepared in accordance with IFRS.
- The Statement of Cash Flow is prepared in accordance with IFRS.
- The Valuation tab attempts to value the proposed business using the venture capital method and the discounted cash flow method.
- Y1 Bookkeeping tab is used to double check math and presents data in a slightly different manner, than previous tabs.
I gave a lot of thought as to what to include but also what to leave out when building this spreadsheet and previously when building similar spreadsheets for the So You Want To Open A Brew Pub series of blog posts. This spreadsheet goes further as it produces three years of projections as well as a hopefully passable: Balance Sheet, Income Statement, and Statement of Cash Flows. I tried to follow IFRS standards, but I’m not an accountant. However by taking the time to estimate your business’ costs and filling in 28 cells on the Questionnaire tab you should get some useful numbers and information. I did anticipate certain questions so I will attempt to answer them:
- Why IFRS? Well I’m not American… but in reality for most startups in their first fiscal year they are unlikely to go public or engage in any accounting shenanigans. It should be easy to covert to U.S. GAAP or change to a different currency symbol. I didn’t spend a lot of time on cosmetics.
- Taxes are assumed to be a simple flat tax rate. Startups should be more concerned with their cash position than tax reductions strategies. My projections focus on pre-tax income, but I also decided to deduct tax as a cash expenses as taxable income is earned. This is considered conservative.
- Depreciation and Amortization I deliberately left out. Not every startup buys a lot of equipment. Not all equipment is depreciated at the same rate. Estimating useful life and salvage value isn’t for beginners. Both are non-cash expenses, it is just simpler to leave them out.
- Equipment Leasing is very common in many businesses across many industries. Unfortunately it is more difficult to account for than simply purchasing equipment. Leasing from multiple vendors likely means dealing with different terms and rates. The easiest way to include leasing costs is to lump them in as miscellaneous monthly expenses, but to do it correctly would require modifying more than just a single tab or cell.
- Seasonal Revenue is also very common and something I’ve had to account for in many business plans. Modifying row 4 in the Projections tab can easily model seasonal sales. Everything else should still just work.
- Exponential Growth is what many business plans predict, but isn’t that common. This spreadsheet has a simple month over month linear growth model. Modifying row 4 of the Projections tab or each monthly sales figure will produce a hockey stick shaped graph with a certain amount of trial and error.
- Inventory, Accounts Receivable, Accounts Payable are assumed to be perfectly managed, every order filled and paid for with cash immediately. This is of course unrealistic but it is the easiest to model and explain.
That’s enough typing, let me know what you think and if you find any glaring mistakes. I came across a few resources when building this spreadsheet, I thought I’d share.
- Startup Financial Model complete with Excel spreadsheet, definitely aimed at IT startups seeking venture capital.
- Various articles and advice from Michael Volker.
- Taylor Davidson sells spreadsheets for entrepreneurs and teaches courses in financial modeling.
- Analyzing Financial Statements by Fred Wilson
- Top 5 Excel Spreadsheets I made that you can steal
- Venture Capital calculations in Excel
- Break Even Analysis in Excel using VBA
This entry was originaly posted on , it was last edited on and is filed under: Excel, Venture Capital and tagged: balance sheet, break even, cash flow, costs, debt servicing, Entrepreneur, equity, financing, net income, new business, revenue, sales, spreadsheet, startup.