Business modeling is the art of projecting the financial impact of a real-life financial situation on Excel. It's a way to analyze risk by looking at a set of variables in any business that is always subject to change. These variables include sales, the number of employees and rent. Financial modeling is a way to capture business scenarios by testing your assumptions against outcomes. This is useful for helping companies foresee business performance, understand their short and long-term plans and incentivize venture capitalists to invest.
A fundamental rule of financial modeling is that all hardcores or inputs should pull out of formulas and be consolidated in an inputs and assumptions section. Never repeat an input and change the color of the cells, so you can easily track them.
Always take a bottoms-up approach when structuring your model. This means, break down everything you want to model into fundamental units, turn those units into inputs and then build up accordingly. For example, monthly shirt sales at a retail store could be the result of the following inputs: foot traffic per day, days open per week, conversion rate per customer, average order quantity and price per shirt. The more granular your model, the more accurate it's likely to be.
In the latest version of Excel spreadsheets, you have up to 1,048,576 rows and 16,384 columns to work with. So use them. Rather than multiply, subtract, and divide all of it in one cell or row, do it in three if you need to. The simpler your formulas are, the easier it is for you and others to follow. You can always create a monthly model or create annual and quarterly summaries.
Use tabs as sparingly as possible. A financial model for startup companies should fit on one tab. A tip to consolidate is stacking your profit and loss, balance sheet and statement of cash flow on top of each other. Summaries may warrant an additional tab but can also be done in columns to the far right of your model.
One Column and Drag
If my model starts in January 2014, I should be able to write formulas row by row until January is complete and then drag my formulas over (CTRL + R) to December 2019 without changing any formulas. Sticking to this rule may be tricky at first, but will make your life so much easier.
In an effort to add dynamicity to your model, you may end up writing a lot of date “logic” into formulas. Rather than write the date logic and the actual calculation in the same cell, always break out the date logic into a system of 1's and 0’s. For instance, if I want my revenue to start when my store opening date input is greater than or equal to the column date, then I would first write a formula that displays 0’s up until the store opening date, and then 1’s thereafter. Once I have this “logic,” not only can I link my revenue calculation to it, but I can link other formulas as well. For instance, I would also want my store employee payroll to start on the store opening date.
Dates are Numbers
In Excel, all dates are real numbers. Once you understand this, it opens a whole new dynamic world of financial modeling. The numerical value allows you to easily reference dates with logical functions. As a general rule every formula in your model should link back to a date cell in the same column. The point being that if your development takes 8 months instead of 4 months, or your store opens 3 months later than expected, your model should adjust accordingly through an easy change of inputs. One last tip mapping out dates: Enter one date (the “model start date”) and then have all other dates as EDATE or EOMONTH functions.
Format and Summarize
When showing your financial model to investors who are busy and don’t have much time, make their lives easier by summarizing your model. Create annual summaries, FORMAT, FORMAT, FORMAT, and throw in some nice graphs that are printable, a la print preview (CTRL + F2).
Rapid Fire Tips
Don’t hide rows. Group them. Don’t hide tabs. Move them to another workbook. Avoid “plugging” for numbers. Keep the “round” function off limits. Save all formatting for the end. Build in checks everywhere. Never sum across empty rows or columns. Don't forget to hit CTRL (or COMMAND) + S to save your work often.
Planning and Predicting
All in all, financial modeling is a way to foresee your business performance. It’s a useful tool when you’re thinking about business strategy and particularly important when a startup is in its growth stage. The more complex a business becomes (e.g. when your company starts offering multiple products or services), the riskier it is. Therefore, financial modeling is a great tool to use internally for strategic planning. Financial modeling also helps you understand your company’s annual, three-year and long-term plan. In the most practical sense, financial modeling helps VCs who are thinking about investing in your company clearly understand the impact of the deal.