When I originally started building financial model templates, the main request I received was to put together monthly and annual profit / loss summaries as well as a final line that showed cash flow. These were to update as the desired assumptions changed. However, this was quite a bit different from building a 3-statement model in Excel. The 3 statements included in that are the Income Statement, Balance Sheet, and Cash Flow Statement. There are all kinds of considerations to keep in mind when modeling this structure.
The 3 statement model is far more complex than a simple profit/loss sheet and requires a great deal of accounting knowledge, including journal entries. I can't tell you how many times I have had to go back and research what the proper journal entries are for a given financial transaction so that I could build the correct formula in Excel to get all the statements to balance out.
I spent the majority of 2022 updating all the financial feasibility models on this site with a 3 statement model. I now build all models with this integration, and it helps to have a large template library to pull from when putting together the accounting logic. This is difficult because different types of businesses require different types of accounting logic so that all the statements make sense and balance out.
For example, you could have a B2B SaaS business model that collects money on its' 24-month contracts up front, but realizes the revenue evenly over the contract period. In that case, you have an unearned revenue line item as a liability on the balance sheet and a reconciliation on the cash flow statement to zero out the earned revenue and account for when the cash came in.
You could also have a situation where vendors are paid for purchases of inventory and there are terms included in that so that you pay a certain percentage of the purchases up front and then some down the road. This happens in the car dealership model. It requires a line item for accounts payable on the balance sheet and then a reconciliation on the cash flow statement. Having the logic included is very important because it affects the initial capital requirement greatly.
One of the most in-depth and complex 3 statement models I've ever built in Excel was the made-to-order manufacturing financial model. This template has options for inventory payments with terms that run off orders received and lead times. It also has terms for when customers paid for the finished product and that could be prior to the item being shipped, at shipment, or after shipment (so potentially creating unearned revenue or accounts receivable). You also had accounts payable for the inventory purchases and terms on that (meaning your company may pay for goods at varying percentages after the purchase date).
Sometimes a business will have expenses that belong in cost of goods sold (COGS), however they are non-cash items. Such things include a scenario where there is a manufacturing company that has equipment and a plant that is directly used to produce products. The depreciation expense for those items is a COGS and belongs above the gross profit or contribution margin line on the income statement. However, when reconciliation down to the proper timing of cash flow, the capex event is captured at purchase and the depreciation expense (happening over time) should be added back if you are starting your statement of cash flows with net income (because COGS hits before net income and you don't want to double count that cash outflow).
Every business is a little different in how the bottom-up assumptions properly fit into a 3-statement model, but the value of having them in your template is that they all speak the same accounting language. It is an easy way to communicate because everyone agrees on what the terms mean, or they are supposed to at least.
You can buy all the finance templates I've ever built in one big purchase with The Super Smart Bundle.
Article found in Accounting and Finance.
